Excel export with multi line cells

19 posts, 0 answers
  1. Steve Bough
    Steve Bough avatar
    16 posts
    Member since:
    Jul 2007

    Posted 28 Jul 2008 Link to this post

    I've been experimenting with the Excel export function to see if we can use it to support some of our client's requirements. So far it's very impressive, handling obscure character sets and bullet points and so forth very nicely indeed.
    The only problem I have with it is when exporting a multi-line cell.
    Currently I'm rendering the cell as a single Label control with a <br/> where the new lines are supposed to start. The export function handles this nicely insofar as it creates a new row in excel for each line, merging cells on columns not containing them.
    Unfortunately this means that you cannot sort by any of the other columns since Excel complains that the merged cells are not the same size.
    I've tried replacing the label with a multi-line textbox but that exports as some form of text box in Excel where you can't see the text.
    Is there any way to override the multi-row behaviour for multi line cells so that it can export a grid cell into a single Excel cell with new-lines in it?
  2. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 29 Jul 2008 Link to this post

    Hello Steve Bough,

    What we can suggest is manually remove the <br /> tags in the labels of the items when exporting RadGrid. However, this will not ensure that the text in the cell will be placed in two lines, as this would depend more on the default cell configuration in MS Excel. Nevertheless, if you decide to take this approach, here is what you need to do:

    1. Set a boolean session variable in the event where you are calling RadGrid's ExportToExcel() method.
    2. In the ItemDataBound event of RadGrid, check if such a session variable exists, and if is defined. If true, you can get the required data cell from the data item and remove the <br /> tag from its text:

    void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e) 
        if (Session["export"] != null && (bool)Session["export"]) 
        { 
            if (e.Item is GridDataItem) 
            { 
                GridDataItem item = e.Item as GridDataItem; 
                item["ColumnUniqueName"].Text = item["ColumnUniqueName"].Text.Replace("<br />"" "); 
            } 
        } 

    Sincerely yours,
    Veli
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Steve Bough
    Steve Bough avatar
    16 posts
    Member since:
    Jul 2007

    Posted 30 Jul 2008 Link to this post

    That does solve the issue about multiple cells, but isn't something we can really use since our clients have taken to pasting multi-line selections into our database and expect them to come back out the same way.
    There doesn't seem to be a lot of documentation regarding the ExcelML export option for example, which I might be able to use possibly.
    Fundamentally Excel looks for \x0A surrounded by double quotes when it's importing a multi-line cell from CSV, and I was kind of hoping to spoof it in a similar way. Unfortunately the excaped characters are getting turned into whitespace somewhere along the line.
    I don't suppose there are any low level events I can intercept to alter the data sent to excel are there?
  5. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 30 Jul 2008 Link to this post

    Hello Steve,

    When exporting to ExcelML format, an event is fired for RadGrid when a data row gets exported - the ExcelMLExportRowCreated event. You can attach to this event to loop through the cells in the current item and modify their values as needed:

    void RadGrid1_ExcelMLExportRowCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e) 
        e.Row.Cells[1].Data.DataItem = "123";     

    The above code sets the text inside a data cell with index 1 of the current exported row to "123". Please note that you need to set

    RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;

    for this event to fire and

    RadGrid1.ExportSettings.ExportOnlyData = true;

    for valid export to ExcelML format.

    Greetings,
    Veli
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  6. Steve Bough
    Steve Bough avatar
    16 posts
    Member since:
    Jul 2007

    Posted 30 Jul 2008 Link to this post

    Thanks, but I've tried that already. The problem is that nothing I insert into the data item comes out as a newline in a cell in Excel.
    I've tried all the variants of html character encoding, direct escaped characters in C# and so forth I can think of, but nothing seems to work.

    Thanks for the quick response though.
  7. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 31 Jul 2008 Link to this post

    Hi Steve,

    I also tried with whatever I could find could represent a newline character, and yet Excel refuses to recognize it when exporting. I will write back if any workarounds come up.

    Best wishes,
    Veli
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  8. Michael Von Foerster
    Michael Von Foerster avatar
    16 posts
    Member since:
    Oct 2007

    Posted 28 Aug 2008 Link to this post

    The correct format for a new line in the Excel xml is &#10;
    However if one tries to put this into the DataItem it is changed to &amp;#10;
    Would it be possible for telerik to replace a "\r\n" with "&#10;" when exporting the excel document?
  9. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 28 Aug 2008 Link to this post

    Hi Michael,

    The result seems to be the same - the literal characters are added to the string, instead of an actual line feed.

    Sincerely yours,
    Veli
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  10. Michael Von Foerster
    Michael Von Foerster avatar
    16 posts
    Member since:
    Oct 2007

    Posted 05 Sep 2008 Link to this post

    The Style on the cell has to allow for word wrapping and the height of the row must be enough to display all of the text.  Here's an example that Excel produces:

    <?xml version="1.0"?> 
    <?mso-application progid="Excel.Sheet"?> 
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
     xmlns:o="urn:schemas-microsoft-com:office:office" 
     xmlns:x="urn:schemas-microsoft-com:office:excel" 
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
     xmlns:html="http://www.w3.org/TR/REC-html40"
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"
      <Author>p</Author> 
      <LastAuthor>p</LastAuthor> 
      <Created>2008-09-05T18:05:25Z</Created> 
      <Company>A</Company> 
      <Version>12.00</Version> 
     </DocumentProperties> 
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"
      <WindowHeight>9150</WindowHeight> 
      <WindowWidth>14175</WindowWidth> 
      <WindowTopX>120</WindowTopX> 
      <WindowTopY>60</WindowTopY> 
      <RefModeR1C1/> 
      <ProtectStructure>False</ProtectStructure> 
      <ProtectWindows>False</ProtectWindows> 
     </ExcelWorkbook> 
     <Styles> 
      <Style ss:ID="Default" ss:Name="Normal"
       <Alignment ss:Vertical="Bottom"/> 
       <Borders/> 
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> 
       <Interior/> 
       <NumberFormat/> 
       <Protection/> 
      </Style> 
      <Style ss:ID="s62"
       <Alignment ss:Vertical="Bottom" ss:WrapText="1"/> 
      </Style> 
     </Styles> 
     <Worksheet ss:Name="Sheet1"
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" 
       x:FullRows="1" ss:DefaultRowHeight="15"
       <Column ss:AutoFitWidth="0" ss:Width="54"/> 
       <Row ss:AutoFitHeight="0" ss:Height="32.25"
        <Cell ss:StyleID="s62"><Data ss:Type="String">5678&#10;1234 &#10;5678</Data></Cell
       </Row> 
      </Table> 
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"
       <PageSetup> 
        <Header x:Margin="0.3"/> 
        <Footer x:Margin="0.3"/> 
        <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> 
       </PageSetup> 
       <Selected/> 
       <ProtectObjects>False</ProtectObjects> 
       <ProtectScenarios>False</ProtectScenarios> 
      </WorksheetOptions> 
     </Worksheet> 
     <Worksheet ss:Name="Sheet2"
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" 
       x:FullRows="1" ss:DefaultRowHeight="15"
      </Table> 
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"
       <PageSetup> 
        <Header x:Margin="0.3"/> 
        <Footer x:Margin="0.3"/> 
        <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> 
       </PageSetup> 
       <ProtectObjects>False</ProtectObjects> 
       <ProtectScenarios>False</ProtectScenarios> 
      </WorksheetOptions> 
     </Worksheet> 
     <Worksheet ss:Name="Sheet3"
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" 
       x:FullRows="1" ss:DefaultRowHeight="15"
      </Table> 
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"
       <PageSetup> 
        <Header x:Margin="0.3"/> 
        <Footer x:Margin="0.3"/> 
        <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> 
       </PageSetup> 
       <ProtectObjects>False</ProtectObjects> 
       <ProtectScenarios>False</ProtectScenarios> 
      </WorksheetOptions> 
     </Worksheet> 
    </Workbook> 
     

  11. Rosen
    Admin
    Rosen avatar
    3234 posts

    Posted 10 Sep 2008 Link to this post

    Hi Michael,

    You can apply attributes to element using element's attributes collection. Therefore you can assign the TextWrap to Style element inside the ExcelMLExportStylesCreated event. In order to replace the &amp; you can use another RadGrid event which is called GridExporting. I have attached a simple page which demonstrates how to accomplish the desired functionality. Please give it a try and see if this helps.

    All the best,
    Rosen
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  12. Steve Bough
    Steve Bough avatar
    16 posts
    Member since:
    Jul 2007

    Posted 11 Sep 2008 Link to this post

    Guys, that's really helpful. That was the only problem we had with the grid export, but it was one which meant we couldn't use it.
    That solution saves me having to write an entire excel export library from scratch, so thanks a lot.
  13. Mark Galbreath
    Mark Galbreath avatar
    171 posts
    Member since:
    Jul 2009

    Posted 20 Nov 2009 Link to this post

    This is an excellent thread!
  14. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 20 May 2010 Link to this post

    In the sample provided by Rosen,this line of code:
                //replace the characters 
                cellElement.Data.DataItem = ((string)cellElement.Data.DataItem).Replace("<br/>""&#10"); 

    is missing a semicolon after "&#10" - it should be:
                //replace the characters 
                cellElement.Data.DataItem = ((string)cellElement.Data.DataItem).Replace("<br/>""&#10;"); 

  15. Deepak
    Deepak avatar
    1 posts
    Member since:
    May 2012

    Posted 30 May 2012 Link to this post

    I had the same problem. I arrived at the solution while browsing this blog :

    http://shawpnendu.blogspot.in/2009_03_01_archive.html

    in the section - "Export to Excel with proper formatting:"

    I modified my code as follows:

    string brstyle = @"br { mso-data-placement:same-cell; }";

    Response.Write(brstyle);

    Response.Write(stringWriter.ToString());

    It worked for me. Note that you have to retain the <br /> tag in the text.
    Now the text with linebreaks is appearing in single cells rather than in new cell (row) for each linebreak.

    Hope this helps.

  16. Martin Roussel
    Martin Roussel avatar
    246 posts
    Member since:
    Jan 2010

    Posted 06 Feb 2013 Link to this post

    Hi, im trying to implement Rosen sample into my code to enhance Excel multiline exporting... but have issues. I want to make the ExcelMLExportRowCreated() event more generic and apply string replacement on all columns (I dont want to bother specifying columns):

    protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
            {
                if (e.RowType == GridExportExcelMLRowType.DataRow)
                {
     
                    for (int i = 0; i <= e.Row.Cells.Count - 1; i++)
                    {
                        CellElement cellElement = e.Row.Cells[i];
     
                        cellElement.StyleValue = "MyItemCustomStyle";
     
                        cellElement.Data.DataItem = ((string)cellElement.Data.DataItem).Replace("<br/>", " ");
                    }
                }
            }

    The problem im facing is e.Row.Cell.Count seems to always be 0 if I dont use a DataTable for the grid DataSource. In my case, I use an ObservableCollection<CustomClass> to fill my grid. Is there a workaround for this? Worse case, to convert an ObservableCollection of a custom class into a Datatable?


    UPDATE: I discarded Rosen code and succeeded by simply adding the following event to my grid:
    protected void RadGrid1_HTMLExporting(object sender, GridHTMLExportingEventArgs e)
     {
                e.Styles.Append("br { mso-data-placement: same-cell; }");
    }

    Hope this may help others :)

    Martin
  17. Rajesh
    Rajesh avatar
    2 posts
    Member since:
    Mar 2013

    Posted 20 Mar 2013 Link to this post

    I have almost the same type of code and it is returning only 100 rows in the excel.

    What are the changes i have to do inorder to make excel display 150 rows?
  18. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 25 Mar 2013 Link to this post

    Hello Rajesh,

    Could you confirm that e.Row.Cells.Count returns only 100 rows when you actually have a 150 rows? If that is the issue I would ask you to provide us with your code declaration and the related code behind in order to investigate it further.

    All the best,
    Kostadin
    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.
  19. Rajesh
    Rajesh avatar
    2 posts
    Member since:
    Mar 2013

    Posted 25 Mar 2013 Link to this post

    Hi,

    We have the below code for saving the results to Excel. We are getting only 100 rows as output in Excel

    var aBOMItems = oBOMC.Results;
      if ( oBOMC.Results.length == 0 ) aBOMItems = null;
     Response.Buffer = true;
      Response.Clear();
     Response.ContentType = "application/vnd.ms-excel";
     Response.AddHeader("content-disposition","attachment; filename=\"" + sXlsFilename + "\"");
      Response.Write("<?xml version=\"1.0\"?>");
      Response.Write("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
      Response.Write(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
      Response.Write(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
      Response.Write(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
      Response.Write(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
      Response.Write(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
      Response.Write("  <ProtectStructure>False</ProtectStructure>");
      Response.Write("  <ProtectWindows>False</ProtectWindows>");
      Response.Write(" </ExcelWorkbook>");
      Response.Write(" <Styles>");
      Response.Write("  <Style ss:ID=\"delete\">");
      Response.Write("   <Interior ss:Color=\"#FFCC99\" ss:Pattern=\"Solid\"/>"); //Tan = Delete
      Response.Write("  </Style>");
      Response.Write("  <Style ss:ID=\"change\">");
      Response.Write("   <Interior ss:Color=\"#CC99FF\" ss:Pattern=\"Solid\"/>"); //Change = Lavendar
      Response.Write("  </Style>");
      Response.Write("  <Style ss:ID=\"add\">");
      Response.Write("   <Interior ss:Color=\"#FF99CC\" ss:Pattern=\"Solid\"/>"); // Rose = Add
      Response.Write("  </Style>");
      Response.Write("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
      Response.Write("   <Alignment ss:Vertical=\"Bottom\"/>");
      Response.Write("   <Borders/>");
      Response.Write("   <Font/>");
      Response.Write("   <Interior/>");
      Response.Write("   <NumberFormat/>");
      Response.Write("   <Protection/>");
      Response.Write("  </Style>");
      Response.Write("  <Style ss:ID=\"s24\">");
      Response.Write("   <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>");
      Response.Write("   <Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
      Response.Write("  </Style>");
      Response.Write(" </Styles>");

     // BOM Items Report Format
      if (iFormatType == 0)
      {
      Response.Write(" <Worksheet ss:Name=\"BOM_Compare\">");
        Response.Write("  <Table ss:ExpandedColumnCount=\"7\" x:FullColumns=\"1\" x:FullRows=\"1\">");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"25\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"50\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"350\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"100\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"100\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"75\"/>");
      Response.Write("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"75\"/>");
      Response.Write("   <Row ss:StyleID=\"s24\">");
      Response.Write("    <Cell><Data ss:Type=\"String\">Item</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">Required</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">Ordering Description</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">Drawing Number</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">K-C Number</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">Grade Change Part</Data></Cell>");
      Response.Write("    <Cell><Data ss:Type=\"String\">Safety Stock</Data></Cell>");
      Response.Write("   </Row>");
      }


    Please check the code and let us know if we can make the rows returned is 150.

    Thanks,
    Rajesh.
  20. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 28 Mar 2013 Link to this post

    Hello Rajesh,

    As far as I see you are implementing your own export functionality. I would suggest you to use the build-in export functionality of RadGrid. You could simply call ExportToExcel() method and specify the excel format by setting Format property to ExcelML.
    ASPX:
    <ExportSettings>
         <Excel Format="ExcelML" />
     </ExportSettings>
    C#:
    RadGrid1.MasterTableView.ExportToExcel();

    Additional information could be found at the following help topic.

    All the best,
    Kostadin
    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