Export to Excel with line breaks

[Dave]

I have a RadGrid which gets populated with data from a SQL Server database. One of the columns in the RadGrid shows data that was collected from a multiline textbox (which allows user to obviously enter carriage returns). In the codebehind of my aspx page, I convert the database line breaks to BR tags so that the column properly displays the line breaks in the browser. I give users the ability to export the grid to excel, however, I just noticed that the BR tags are causing extra rows to be created in the excel file that gets generated (a new row is created in the excel file for each BR tag instead of keeping all the data for each record in one row). Here is the code I use to generate the excel file:

grdMetrics.ExportSettings.ExportOnlyData =true;
grdMetrics.ExportSettings.IgnorePaging =true;
grdMetrics.ExportSettings.OpenInNewWindow = true;
grdMetrics.MasterTableView.ExportToExcel();

How do I fix this? What are my options?

[Daniel]

Hello Dave,

Try the following approach if you have the latest version of RadControls for ASP.NET AJAX:

protected void RadGrid1_HTMLExporting(object sender, GridHTMLExportingEventArgs e)
{
    e.Styles.Append("br { mso-data-placement: same-cell; }");
}

If you have an older version try this one:

protected void RadGrid1_GridExporting(object source, GridExportingArgs e)
{
    if (e.ExportType == ExportType.Excel)
    {
        string css = "<style type='text/css'> br { mso-data-placement: same-cell; } </style>";
        e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
    }
}

Best regards,
Daniel
the Telerik team

[Dave]

I have the latest version (2010.3.1317.40) but I don't see the HTMLExporting event. Are you referring to the latest build version (i.e. not yet released)?

[Daniel]

Hello Dave,

"Latest version" means the Q1 2011 BETA release in this case.
Q1 2011 BETA
We introduced some improvements to the exporting capabilities of RadGrid in this version.

Let me know if you need more information.

Kind regards,
Daniel
the Telerik team

[Dave]

I have the following method but it doesn't solve the problem:

protected void grdMetrics_GridExporting(object sender, GridExportingArgs e)
{
    if (e.ExportType == ExportType.Excel)
    {
        string css = "<style type='text/css'> br { mso-data-placement: same-cell; } </style>";
        e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
    }
}

I guess I'll have to wait for the next Q1 2011 to release (I'd rather not use the BETA version in my production site). You think adding the "mso-data-placement: same cell" declaration to the HTMLExporting will solve the problem?

[Daniel]

Hello Dave,

Please download the attached sample project and let me know whether it works properly on your end.

Regards,
Daniel
the Telerik team

[Dave]

No, unfortunately, I still have the same problem.

[Daniel]

Hello Dave,

It seems I'm leaving something out as I was confident that this approach will do the job. Could you please modify my project or provide another runnable sample that shows the problem?

Regards,
Daniel
the Telerik team

[Dan McAlister]

Hello,

I am having the exact same problem.  When I export data from the grid control I get a new row for each linebreak.
The version I am using is: 2010.3.1317.35.  I was wondering whether a resolution to this was found or not.  I tried the exampel code above which did not work.

Thanks, Daniel

[Daniel]

Hi Daniel,

Please run my demo and attach the exported file (as RAR or ZIP) so that I can examine it locally. Also I need to know what version of Microsoft Excel you use to open the file.

Thanks,
Daniel
the Telerik team

[Dan McAlister]

Hi Daniel,

I ran your example and and it actually worked perfectly.  However that is not the issue I am experiencing.  The issue I am having is when a RadEditor is being used to enter data and the user enters a line break with ctrl-Enter.  It does not create <br /> tags as in your example it creates a special character and adds <p> tags around the following text.  The data is saved to the database.  On another screen the data is pulled up in a grid and when it is exported excel treats the line breaks in the columns as extra lines.

I am looking for a way to solve this issue.

Thanks, Daniel

[Daniel]

Hi Daniel,

I believe it is possible to replace the paragraph tags with span by intercepting the GridExporting event.

void RadGrid1_GridExporting(object sender, GridExportingArgs e)
{
    e.ExportOutput = e.ExportOutput.Replace("<p>", "<span>");
}

Of course, you should do this for the closing tags also.

Regards,
Daniel
the Telerik team

[William]

I have also the same problem.
http://i.stack.imgur.com/9Vna0.png

When I export to excel, I see that it is spread to multiple rows in Excel.

This is the content of only one cell

<table width="600" class="MsoNormalTable" style="width: 450pt; mso-cellspacing: .7pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 0cm 0cm 0cm;" border="0" cellspacing="1" cellpadding="0">      <tbody>          exists="1"></o:p></td>          </tr>      </tbody>  </table>

And it seems fine in RadGrid, but when I export to Excel. This same cess becomes 7-8 rows

[Daniel]

Hello William,

I'm afraid that Microsoft Excel parses the HTML data as per the Office HTML specification. It is not something we could control directly.

Regards,
Daniel
the Telerik team