Export GridTemplateColumn to Excel as string

[Richard]

Good evening,

I'm trying to export a RadGrid to xlsx using RadGrid_ItemCommand and RadGrid.ExportToExcelCommandName.

I have a template column:

<telerik:GridTemplateColumn HeaderText="Column1" SortExpression="Column1" DataField="Column1" UniqueName="Column1" ColumnGroupName="Column1Data" AllowFiltering="false">
<EditItemTemplate>
<telerik:RadTextBox ID="txtColumn1" runat="server" RenderMode="Lightweight" MaxLength="6" Width="200px" Text='<%# Bind("Column1") %>' />
<asp:RequiredFieldValidator ID="reqColumn1" runat="server" ControlToValidate="txtColumn1" CssClass="standard-text" ErrorMessage="RequiredFieldValidator" Text="This field is required" />
</EditItemTemplate>
<ItemTemplate>
 <%#DataBinder.Eval(Container.DataItem, "Column1", "{0:@}")%>
</ItemTemplate>
</telerik:GridTemplateColumn>

It contains up to 6 alphanumeric characters e.g.:

When the value 07E3 is exported to xlsx (and there are other similar values that give this behaviour) it is exporting as Scientific format:

What can be done to make sure it is displayed as Text format in Excel, without having to use an Excel template file?

Kind regards,

Richard

[Vasko]

Hello Richard, 

You can achieve the desired result by utilizing the ExportCellFormatting event and using the mso-number-formatting CSS attribute, you can style them: 

<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" Width="300px" OnExportCellFormatting="RadGrid1_ExportCellFormatting"  OnNeedDataSource="RadGrid1_NeedDataSource">
    <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <telerik:GridTemplateColumn HeaderText="Column1" SortExpression="Column1" DataField="Column1" UniqueName="Column1" ColumnGroupName="Column1Data" AllowFiltering="false">
                <EditItemTemplate>
                    <telerik:RadTextBox ID="txtColumn1" runat="server" RenderMode="Lightweight" MaxLength="6" Width="200px" Text='<%# Bind("Column1") %>' />
                    <asp:RequiredFieldValidator ID="reqColumn1" runat="server" ControlToValidate="txtColumn1" CssClass="standard-text" ErrorMessage="RequiredFieldValidator" Text="This field is required" />
                </EditItemTemplate>
                <ItemTemplate>
                    <%#DataBinder.Eval(Container.DataItem, "Column1", "{0:@}")%>
                </ItemTemplate>
            </telerik:GridTemplateColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

protected void RadGrid1_ExportCellFormatting(object sender, Telerik.Web.UI.ExportCellFormattingEventArgs e)
{
    if (e.FormattedColumn.UniqueName == "Column1")
    {
        e.Cell.Style["mso-number-format"] = @"\@"; // This forces Excel to treat the cell as text
    }
}

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    (sender as RadGrid).DataSource = OrdersTable(); 
}
private DataTable OrdersTable()
{
    DataTable dt = new DataTable();

    dt.Columns.Add(new DataColumn("OrderID", typeof(int)));
    dt.Columns.Add(new DataColumn("Column1", typeof(string)));

    dt.PrimaryKey = new DataColumn[] { dt.Columns["OrderID"] };

    for (int i = 0; i < 3; i++)
    {
        int index = i + 1;

        DataRow row = dt.NewRow();

        row["OrderID"] = index;
        row["Column1"] = "07E" + index;

        dt.Rows.Add(row);
    }

    return dt;
}

Regards,
Vasko
Progress Telerik

[Richard]

Hi Vasko,

Many thanks for your really quick reply!

I had been working on something very similar to your answer, but couldn't get it to work.  It wasn't hitting RadGrid1_ExportCellFormatting at all, so not applying the formatting.

It seems the problem is this setting:

ExportSettings-Excel-Format="Xlsx"

If I remove that, and export it as xls the formatting works.

Is there any way to make it work as xlsx?

Kind regards,

Richard

 

[Vasko]

Hi Richard,

Due to this being the Xlsx export, the given solution will in fact not work, due to Excel treating it as a "scientific notation". That said, the default Excel export of the Grid will work with the given suggestion, but the Xlsx won't, due to differences in the 2 formats.

I'm afraid that if you wish to export the desired column, Xlsx is not the proper format for this scenario. Additionally, you can also look at the Set Excel export cell format for more information regarding the topic.

Regards,
Vasko
Progress Telerik

[Richard]

Hi Vasko,

Many thanks for your reply.  I had a look at Set Excel export cell format, and using RadGrid1_InfrastructureExporting I've been able to set the cell format as Text and use Xlsx.

This seems to do the trick:

        // loop through the cells to set format
        foreach (Telerik.Web.UI.ExportInfrastructure.Cell cell in col.Cells)
        {
            cell.Format = "@";// Text Excel cell format
        }

The rest of the settings I have under RadGrid.ExportToExcelCommandName are:

    RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.ExportSettings.ExportOnlyData = true;
    RadGrid1.ExportSettings.OpenInNewWindow = true;
    RadGrid1.ExportSettings.HideStructureColumns = true;

Does that work for you?

Kind regards,

Richard

[Vasko]

Hi Richard,

Yes, you are correct, setting the format does indeed set the value as text in the Excel sheet.

Regards,
Author nickname
Progress Telerik