Export to Excel with line breaks

14 posts, 0 answers
  1. Dave
    Dave avatar
    36 posts
    Member since:
    Jan 2008

    Posted 01 Mar 2011 Link to this post

    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?
  2. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 07 Mar 2011 Link to this post

    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
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Dave
    Dave avatar
    36 posts
    Member since:
    Jan 2008

    Posted 07 Mar 2011 Link to this post

    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)?
  5. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 08 Mar 2011 Link to this post

    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
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
  6. Dave
    Dave avatar
    36 posts
    Member since:
    Jan 2008

    Posted 08 Mar 2011 Link to this post

    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?
  7. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 11 Mar 2011 Link to this post

    Hello Dave,

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

    Regards,
    Daniel
    the Telerik team
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
  8. Dave
    Dave avatar
    36 posts
    Member since:
    Jan 2008

    Posted 15 Mar 2011 Link to this post

    No, unfortunately, I still have the same problem.
  9. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 15 Mar 2011 Link to this post

    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
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
  10. Dan McAlister
    Dan McAlister avatar
    4 posts
    Member since:
    Apr 2010

    Posted 17 Feb 2012 Link to this post

    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
  11. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 20 Feb 2012 Link to this post

    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
    Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
  12. Dan McAlister
    Dan McAlister avatar
    4 posts
    Member since:
    Apr 2010

    Posted 22 Feb 2012 Link to this post

    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
  13. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 27 Feb 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  14. William
    William avatar
    9 posts
    Member since:
    Dec 2012

    Posted 28 Dec 2012 Link to this post

    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
  15. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 02 Jan 2013 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017