Format Data when Exporting To Excel in Xlsx Format

5 posts, 0 answers
  1. Craig
    Craig avatar
    10 posts
    Member since:
    Jan 2015

    Posted 22 Oct 2015 Link to this post

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

    } 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  2. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2138 posts

    Posted 27 Oct 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Craig
    Craig avatar
    10 posts
    Member since:
    Jan 2015

    Posted 27 Oct 2015 in reply to Konstantin Dikov Link to this post

    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 = "@";
                }
    }

  4. Brandon
    Brandon avatar
    1 posts
    Member since:
    Jun 2014

    Posted 28 Nov in reply to Konstantin Dikov Link to this post

    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!

  5. Peter Milchev
    Admin
    Peter Milchev avatar
    300 posts

    Posted 01 Dec Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top