Format Data when Exporting To Excel in Xlsx Format

[Craig]

Hi,

 I have a RadGrid. I would like to export it to excel and I would like to use the .xlsx format (ie: radGrid.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx). However, some columns have numerical names like 1234 or 01300 and I would like those particular columns to show up in the excel spreadsheet as a Text format - so 1.) 1234 would be left aligned in the cell just like any text and 2.) so that leading zeros aren't automatically removed. 

 I've tried many solutions from this forum - but all of them seem to only work in GridExcelExportFormat.Html format (like using the OnExporttoExcel event) or other grid formats like ExcelIML (ie. setting the DataFormatString attribute to contain a leading space = {0}).

 I want to export to .xlsx format to avoid warnings and because .xlsx has been around for a long time now, and I'd like my software to export to a modern commonly used format. 

Is there any solutions for this problem when using GridExcelExportFormat.Xlsx format?

See the attached images for the code.

 Here is the code:

<telerik:RadGrid AutoGenerateColumns="false" ID="rgrBoundaryDefinitionList"      OnNeedDataSource="rgrBoundaryDefinitionList_NeedDataSource"     AllowFilteringByColumn="True" AllowSorting="True" Width="100%"     ShowFooter="True" AllowPaging="True" PageSize="1000"      runat="server"      OnItemCommand="rgrBoundaryDefinitionList_ItemCommand"     OnPreRender="rgrBoundaryDefinitionList_OnPreRender"       EnableLinqExpressions="False">     <GroupingSettings CaseSensitive="false"></GroupingSettings>        <ClientSettings>           <Scrolling AllowScroll="True" UseStaticHeaders="True"/>         </ClientSettings>     <MasterTableView ShowHeadersWhenNoRecords="true" AutoGenerateColumns="false" AllowFilteringByColumn="True" ShowFooter="True"          DataKeyNames="ClientId, BoundarySetId, Name, ClientName, ClientType, KeyFileName" NoMasterRecordsText="No Results" EnableColumnsViewState="True">         <SortExpressions>             <telerik:GridSortExpression FieldName="Name" SortOrder="Ascending" />         </SortExpressions>         <Columns>             <telerik:GridTemplateColumn UniqueName="BoundarySetDetailsColumn" AllowFiltering="true"  HeaderStyle-Width="70px">                <FilterTemplate>                     <asp:ImageButton ID="btnClearFilters" runat="server" ImageUrl="~/Images/clearfilter.png" AlternateText="Clear Filters"                     ToolTip="Clear Filters" OnClick="btnClearFilters_Click" Style="vertical-align: middle; " />                </FilterTemplate>                 <ItemTemplate>                     <telerik:RadButton ID="btnBoundarySetDetails" runat="server" Text="Details" CommandName="Details" />                 </ItemTemplate>             </telerik:GridTemplateColumn>             <telerik:GridBoundColumn FilterControlWidth="120px" DataField="Name"  HeaderText="Boundary Name" DataFormatString=""                 AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false" HeaderStyle-Width="150px" />             <telerik:GridBoundColumn FilterControlWidth="150px" DataField="ClientName"  HeaderText="Organization Name"                 AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false" HeaderStyle-Width="150px" />             <telerik:GridBoundColumn DataField="ClientType"   HeaderText="Organization Type"                   AutoPostBackOnFilter="true" CurrentFilterFunction="EqualTo" ShowFilterIcon="false" HeaderStyle-Width="100px" />             <telerik:GridCheckBoxColumn DataField="IsUsingKeyFile" HeaderText="Keyfile" HeaderStyle-Width="50px"                 AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false" ConvertEmptyStringToNull="False" UniqueName="IsUsingKeyFile">                 <FilterTemplate>                     <telerik:RadComboBox ID="rcboFilterIsKey" runat="server" AllowCustomText="False "                         SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("IsUsingKeyFile").CurrentFilterValue %>'                         OnClientSelectedIndexChanged="IsKeyFileChanged" Width="40px" ViewStateMode="Disabled">                     <Items>                         <telerik:RadComboBoxItem Text="All" Value="" />                         <telerik:RadComboBoxItem Text="Yes" Value="True" />                         <telerik:RadComboBoxItem Text="No" Value="False" />                     </Items>                     </telerik:RadComboBox>                     <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">                     <script type="text/javascript">                         function IsKeyFileChanged(sender, args) {                             var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");                             var filterVal = args.get_item().get_value();                             if (filterVal==null  || filterVal == "") {                                 tableView.filter("IsUsingKeyFile", "", "NoFilter");                             }                             else  {                                 tableView.filter("IsUsingKeyFile", filterVal, "EqualTo");                             }                         }                     </script>                   </telerik:RadScriptBlock>                 </FilterTemplate>             </telerik:GridCheckBoxColumn>             <telerik:GridBoundColumn DataField="KeyFileName" Display="false" HeaderText="Keyfile Name" HeaderStyle-Width="200px" />             <telerik:GridBoundColumn DataField="Description" HeaderText="Description"                   AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false" HeaderStyle-Width="150px" />             <telerik:GridCheckBoxColumn DataField="ClientDefault" HeaderText="Default" HeaderStyle-Width="50px"                 AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false" ConvertEmptyStringToNull="False" UniqueName="ClientDefault">                 <FilterTemplate>                     <telerik:RadComboBox ID="rcboFilterDefault" runat="server" AllowCustomText="False "                         SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("ClientDefault").CurrentFilterValue %>'                         OnClientSelectedIndexChanged="IsDefaultChanged" Width="40px" ViewStateMode="Disabled">                     <Items>                         <telerik:RadComboBoxItem Text="All" Value="" />                         <telerik:RadComboBoxItem Text="Yes" Value="True" />                         <telerik:RadComboBoxItem Text="No" Value="False" />                     </Items>                     </telerik:RadComboBox>                     <telerik:RadScriptBlock ID="rsbDefaultScriptBlock" runat="server">                     <script type="text/javascript">                         function IsDefaultChanged(sender, args) {                             var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");                             var filterVal = args.get_item().get_value();                             if (filterVal == null || filterVal == "") {                                 tableView.filter("ClientDefault", "", "NoFilter");                             }                             else {                                 tableView.filter("ClientDefault", filterVal, "EqualTo");                             }                         }                     </script>                   </telerik:RadScriptBlock>                 </FilterTemplate>             </telerik:GridCheckBoxColumn>             <telerik:GridBoundColumn DataField="Status" HeaderText="Status" UniqueName="Status" DataType="System.String" HeaderStyle-Width="100px" FilterControlWidth="60px" />         </Columns>       </MasterTableView></telerik:RadGrid>

 

 

And the code from the code behind for the export button handler:

protected void rbtnExport_Click(object sender, EventArgs e) {     

rgrBoundaryDefinitionList.MasterTableView.GetColumn("KeyFileName").Display = true;

rgrBoundaryDefinitionList.MasterTableView.GetColumn("BoundarySetDetailsColumn").Display = false;    rgrBoundaryDefinitionList.MasterTableView.GetColumn("ClientType").HeaderStyle.Width = Unit.Pixel(200);    rgrBoundaryDefinitionList.MasterTableView.GetColumn("Description").HeaderStyle.Width = Unit.Pixel(500);   

rgrBoundaryDefinitionList.ExportSettings.OpenInNewWindow = true;

rgrBoundaryDefinitionList.ExportSettings.ExportOnlyData = false;

rgrBoundaryDefinitionList.ExportSettings.HideStructureColumns = true;

rgrBoundaryDefinitionList.ExportSettings.IgnorePaging = true;

rgrBoundaryDefinitionList.ExportSettings.FileName = fileName;

rgrBoundaryDefinitionList.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;

rgrBoundaryDefinitionList.MasterTableView.ExportToExcel();

} 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Konstantin Dikov]

Hi Craig,

The behavior that you are observing is related to the way that Excel handles those numeric value. In order to force Excel to display the numeric values as text you should handle the server-side OnInfrastructureExporting event of the grid, get reference to the cells in question and change their format:

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    var rows = e.ExportStructure.Tables[0].Rows;
    var rowCount = rows.Count;
    for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
    {
        for (int i = 1; i <= rows[rowIndex].Cells.Count; i++)
        {
            rows[rowIndex].Cells[i, rowIndex].Format = "@";
        }
    }
}

If you need to change the format only on particular columns you need to traverse change the Format property of those cells in the rows[rowIndex].Cells collection.

Hope this helps.


Regards,
Konstantin Dikov
Telerik

[Craig]

Hi Konstantin,

Thanks for the reply, this works perfectly. I implemented slightly differently, but this is definitely the working principle. 

Because I knew that I had a single column, in which every row needed to be formatted as text, i simply grabbed all the columns and then implemented your solution on all the rows. Thanks again for the great solution.

 

        protected void rgrBoundaryDefinitionList_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
        {
            var cols = e.ExportStructure.Tables[0].Columns;
            var columnCount = cols.Count;

            for (int i = 1; i <= cols[1].Cells.Count; i++)
            {
                cols[1].Cells[1, i].Format = "@";
            }
}

[Brandon]

Konstantin, 

Thanks for the solution. I am almost there with getting it to work on my issue. I have only one column (of 8 columns) that needs this formatting (a Parameter value that more often than not begins with zeroes). You mention the need to traverse change of those cells. Do you have an example of this, of how to format only one column in the table (column 6 of 8)? Thanks in advance!

[Peter Milchev]

Hello Brandon,

You can try to remove the inner loop and set the format only to the desired cell:

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    var rows = e.ExportStructure.Tables[0].Rows;
    var rowCount = rows.Count;
    for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
    {
        //for (int i = 1; i <= rows[rowIndex].Cells.Count; i++)
        // {
            //rows[rowIndex].Cells[i, rowIndex].Format = "@";
            rows[rowIndex].Cells[5, rowIndex].Format = "@";
        //}
    }
}

Regards,
Peter Milchev
Progress Telerik