Carriage Returns when exporting grid using ExcelML

[Matt]

Hi,

I cannot seem to figure out why carriage returns are not being displayed in the excel documents being produced by using the .ExportToExcel() call.

My .aspx file includes the following:

    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <telerik:RadGrid ID="radGrid" runat="server"></telerik:RadGrid>
    </form>

My code behind pulls data from my database and then does the following:

        radGrid.DataSource = ds;
        radGrid.DataBind();

        radGrid.ExportSettings.ExportOnlyData = true;
        radGrid.ExportSettings.IgnorePaging = true;
        radGrid.ExportSettings.Excel.Format = Telerik.Web.UI.GridExcelExportFormat.ExcelML;
        radGrid.ExportSettings.FileName = "excel_export.xls";
        radGrid.MasterTableView.ExportToExcel();

When I query the database I loop through the record set to manipulate the data.  For example, I use System.Web.HttpUtility.HtmlDecode("string here") to render html encoding properly and if I add .Replace("\n","[line break here]") I see the bracketed text in the resulting excel document.

But I can't get an actual line break to appear!  I've tried replacing both "\r", "\n", and "\r\n" with Convert.ToChar(10).ToString(), Convert.ToChar(13).ToString(), and Convert.ToChar(10).ToString() + Convert.ToChar(13).ToString() --- nothing works.

If I use .Replace("\n","<br />") and change the export format to Telerik.Web.UI.GridExcelExportFormat.Html I see the line breaks, but new rows are created is Excel - which renders common excel functionality like sorting and filtering useless.

Help!

[Daniel]

Hello Matt,

At this point, newline characters are not supported in ExcelML format.

Let me know if you need further assistance.

Regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Matt]

:-( Any idea when this capability will be added?

[Daniel]

Hello Matt,

I cannot commit to an exact estimate since this is not just a missing functionality in RadGrid. We will have to find a workaround (if such workaround exists at all) since Microsoft Excel refuses to parse the newline characters - they are just ignored.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Chuck Callahan]

One thing you might try as a workaround is to export it as HTML format (as above) but frame your data in html tables.  Excel will recognize anything between TD tags as contents of one cell, and then your line breaks will behave properly.  I've used this approach before exporting Excel documents from a Cold Fusion app and it worked like a charm - should work here too.

Quick edit for clarity... If you give Excel the following output:

Line1 <br /> Line 2 <br /> Line 3

you will get Line 1, Line 2 and Line 3 on separate rows (Line 2 and Line 3 will each start their own row, each in its own cell).  But if you format it this way:

<table>
    <tr>
        <td>Line 1 <br /> Line 2 <br /> Line 3</td>
    </tr>
</table>

Excel will put the entire text blob in ONE cell, with line breaks processed the way you would ordinarily expect.

Hope that helps!