Export to Excel with GridView

[Ken O.]

Using ASP.NET Rad Grid. I am trying to address requirement to display HTML in a grid, and to render same to Excel without extra html characters.

The issue is that data might contain html (inputs from email in html format) as well as plain text. The difference is that plain text has char 10 representing a line break, and html is a <br> tag. One attempt is to replace char 10 with <br> tags. This renders great in the grid, but presents false data when exported to excel, due to extra <br> tags. To remedy this, I think I could either (1)remove the <br> tags at time of exporting, or (2)have a hidden column without the extra <br> tags, and export that field instead of the one used for the grid.

I've been struggling with this for the longest time. Closest I came up with is link below, but this applies to WinForms, not ASP.NET. Can anyone help?

http://www.telerik.com/community/forums/winforms/gridview/export-to-excel-with-gridview.aspx

[Daniel]

Hello Ken,

It is possible to handle the ExcelExportCellFormatting event and replace the characters as suitable for your scenario.

void RadGrid1_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
{
    if(e.FormattedColumn.UniqueName == "...")
    {
        e.Cell.Text ...
    }
}

Let me know if you need more info.

Regards,
Daniel
the Telerik team

[Ken O.]

Thanks Daniel! I tried it out, and seem to have code that should do the trick, but somehow it gets bypassed. Can you point out what I am doing wrong? Snippet of relevant parts below from ASPX and CS pages.

ASPX

<telerik:RadGrid ID="grdActivity" runat="server"
AllowFilteringByColumn="True" AllowPaging="True"<BR>AllowSorting="True"
AllowAutomaticDeletes="False" GridLines="Both"
Skin="Outlook"<BR>OnNeedDataSource="grdActivity_NeedDataSource" TabIndex="10"
AutoGenerateColumns="False"<BR>OnItemDataBound="grdActivity_ItemDataBound"
OnPageIndexChanged="grdActivity_OnPageIndexChanged"<BR>OnExcelExportCellFormatting="grdActivity_ExcelExportCellFormatting"
OnSortCommand="grdActivity_OnSortCommand"<BR>Width="96%">

CS

protected void grdActivity_ExcelExportCellFormatting(object sender,
ExcelExportCellFormattingEventArgs e)<BR>{<BR>    if
(e.FormattedColumn.UniqueName == "Details")<BR>
{<BR>        e.Cell.Text =
e.Cell.Text.Replace("<br>", String.Empty);<BR>    }<BR>}

[Ken O.]

I seem to have not been able to format the code properly in the Format Code Block feature in my last post. Sorry about that, hopefully you can get the point of what I was saying. Below is same without formatting.

ASPX
<telerik:RadGrid ID="grdActivity" runat="server" AllowFilteringByColumn="True" AllowPaging="True"
AllowSorting="True" AllowAutomaticDeletes="False" GridLines="Both" Skin="Outlook"
OnNeedDataSource="grdActivity_NeedDataSource" TabIndex="10" AutoGenerateColumns="False"
OnItemDataBound="grdActivity_ItemDataBound" OnPageIndexChanged="grdActivity_OnPageIndexChanged"
OnExcelExportCellFormatting="grdActivity_ExcelExportCellFormatting" OnSortCommand="grdActivity_OnSortCommand"
Width="96%">
CS
protected void grdActivity_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
{
    if (e.FormattedColumn.UniqueName == "Details")
    {
        e.Cell.Text = e.Cell.Text.Replace("<br>", String.Empty);
    }
}

[Daniel]

Hello Ken,

Your code looks ok. The only reason I can think of that may cause such problem is if you use ExcelML format which has its own events. Can you please confirm that the debugger hits this event handler?

Kind regards,
Daniel
the Telerik team

[Anthony Mangini]

I am also trying to change text and set cell backcolor based on the cells containing certain words.  I tried using the ExportCellFormatting but it does not work and I am Using ExcelML Format

[Daniel]

Hello Anthony,

Please examine the following links:
ExcelML basics
Export to ExcelML demo

Note that the ExcelCellFormatting event is HTML Excel-specific. ExcelML export has its own events.

Best regards,
Daniel
the Telerik team