Excel export with multi line cells

[Steve Bough]

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?

[Veli]

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.

[Steve Bough]

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?

[Veli]

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.

[Steve Bough]

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.

[Veli]

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.

[Michael Von Foerster]

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?

[Veli]

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.

[Michael Von Foerster]

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>

[Rosen]

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.

[Steve Bough]

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.

[Mark Galbreath]

This is an excellent thread!

[Martillo]

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;");

[Deepak]

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.

[Martin Roussel]

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

[Rajesh]

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?

[Kostadin]

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

[Rajesh]

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.

[Kostadin]

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

[Shane]

Is it possible to set the index of a cell in the code behind? I'm trying to merge my headers on two rows, with the last column merging across 5 sub-headers. But every time I add the sub-headers it corrupts the file. If I open the XML and set the ss:Index of the first sub-header cell, and delete all the cells above it for that row, then the file opens and I get exactly what I want. But I can't find a way to set this in the code behind.

 

I'm using the ExcelMLExportRowCreated event.

[Eyup]

Hi Shane,

Generally, RadGrid provides multi-header columns:
http://demos.telerik.com/aspnet-ajax/grid/examples/columns-rows/columns/multi-column-headers/defaultcs.aspx

There is also the Biff exporting format:
https://admin.telerik.com/docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/export-formats/excel-biff-export#styling-columns--rows--cells

Which uses the ExportInfrastructure and has methods like ShiftRowsDown:
http://docs.telerik.com/devtools/aspnet-ajax/controls/export-infrastructure

By default, the Index and ColIndex properties of a cell are read-only, but you can set the ColSpan and RowSpan of the cell and customize its cell.Value depending on your specific requirement.

I hope this will prove helpful. If you have different requirements or further instructions, please open a formal support ticket and elaborate on your specific configuration.

Regards,
Eyup
Progress Telerik

[shekher]

hi
Line break not displaying for Address Field in excel file(.xlsx) while importing from RadGrid. 
we need to display the Address field line by line
Example: Line1
               Line2
               Line3
In the RadGrid Address field is  line by line Displaying Correctly but not in excel file.



in design code below
<telerik:RadGrid ID="AuthorAddressGrid" MasterTableView-CommandItemSettings-ShowExportToExcelButton="true" AllowSorting ="true" OnNeedDataSource="AuthorAddressGrid_NeedDataSource"  OnGridExporting="AuthorAddressGrid_GridExporting" OnHTMLExporting="AuthorAddressGrid_HtmlExporting" runat="server" Height="640px" >
        <ExportSettings HideStructureColumns="true"  ExportOnlyData="True" >
            <Excel Format="Xlsx" />
        </ExportSettings>
       
        <MasterTableView DataKeyNames="ArticleContributorId" Name="ArtContAddress" CommandItemDisplay="Top">
            <CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" ShowRefreshButton="false" />
            <Columns>   
                                <telerik:GridBoundColumn DataField="AddressBlockForHtml" htmlencode="false" DataType="System.String" HeaderText="Address" ReadOnly="True" AllowSorting="True"
                    UniqueName="AddressBlockForHtml" HeaderStyle-HorizontalAlign="left" ItemStyle-HorizontalAlign="left" HeaderStyle-Width="250" itemstyle-width="250" />
                           </Columns>   
        </MasterTableView>
    </telerik:RadGrid>

Protected Sub AuthorAddressGrid_GridExporting(ByVal source As Object, ByVal e As GridExportingArgs)
            If AuthorAddressGrid.Columns.FindByUniqueName("AddressBlockForHtml") IsNot Nothing Then
                e.ExportOutput = e.ExportOutput.Replace("<br />", Environment.NewLine).Replace("&", "&amp;")
                HttpUtility.HtmlDecode(e.ExportOutput)
               
            End If
        End Sub
we wrote some line break code in another class file like below

Public ReadOnly Property AddressBlock As String
            Get
                'Dim breakline = String.Format("{{0}}{0}", ControlChars.NewLine)
                Dim breakline = "{0} " & Environment.NewLine
                Dim block = New StringBuilder()
                If Not String.IsNullOrEmpty(Me.AddressInst) Then
                    block.AppendFormat(breakline, Me.AddressInst)
                End If
                If Not String.IsNullOrEmpty(Me.Address1) Then
                    block.AppendFormat(breakline, Me.Address1)
                End If
                If Not String.IsNullOrEmpty(Me.Address2) Then
                    block.AppendFormat(breakline, Me.Address2)
                End If
                If Not String.IsNullOrEmpty(Me.City) Then
                    block.AppendFormat("{0}, ", Me.City)
                End If
                If Not String.IsNullOrEmpty(Me.State) Then
                    block.AppendFormat("{0} ", Me.State)
                End If
                If Not String.IsNullOrEmpty(Me.CountyProvince) Then
                    block.AppendFormat("{0} ", Me.CountyProvince)
                End If
                If Not String.IsNullOrEmpty(Me.PostalCode) Then
                    block.AppendFormat(breakline, Me.PostalCode)
                End If
                If Not String.IsNullOrEmpty(Me.CountryName) Then
                    block.AppendFormat(breakline, Me.CountryName)
                   
                End If
                If Not String.IsNullOrEmpty(Me.PhonePrimary) Then 'Newchange
                    block.Append(Me.PhonePrimary)
                   
                End If
                Return block.ToString()
            End Get
        End Property

Public ReadOnly Property AddressBlockForHtml As String
            Get
                'Return Me.AddressBlock
                Return Me.AddressBlock.Replace(Environment.NewLine, "<br />").Replace("&", "&amp;") Environment.NewLine).Replace("&", "&amp;")
            End Get
        End Property
The above line break code is working fine RadGrid but not not showing in Excel file.
Can you please check my code and where I am doing mistake. Please let me know.[quote]Your quote goes here[/quote]

[shekher]

Please find the attached files for radgrid anf excel file

[Eyup]

Hello Shekher,

 

You can achieve this requirement using the following approach:

    protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
    {
        Telerik.Web.UI.ExportInfrastructure.Table table = e.ExportStructure.Tables[0];
        for (int col = 0; col < RadGrid1.Columns.Count; col++)
        {
            for (int row = 1; row <= table.Rows.Count; row++)
            {
                table.Cells[col + 1, row].TextWrap = true;
            }
        }
    }

I hope this will prove helpful.

 

Regards,
Eyup
Progress Telerik