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