How to set Excel export cell format

RELATED TO:

AUTHOR: Marin Bratanov

DATE POSTED: April 17, 2018

HOW TO

Set cell format for Excel export.

This can be necessary when you have large numbers (e.g., serial numbers) that Excel can show in scientific (exponential) notation. While the end user can select the Format Cells option and choose the cell format, you want them not to have to do that. 

SOLUTION

To do this, you need to use the InfrastructureExporting event of the grid and the underlying ExportInfrastructure, then find the column that you wish to change (for example, via its header text), then loop through its cells and set their Excel format via their Format property. Here is an example:

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    int colIndex = -1;//you can make this a list and store more indexes so you can loop through them to format more columns
 
    //get the first row where the headers are so you can find the desired column
    Telerik.Web.UI.ExportInfrastructure.CellCollection cells = e.ExportStructure.Tables[0].Rows[1].Cells;
    foreach (Telerik.Web.UI.ExportInfrastructure.Cell item in cells)
    {
        if (item.Text == "large numbers")
        {
            colIndex = item.ColIndex;
        }
    }
 
    if (colIndex < 0) return;//no column found, avoid error
 
    Telerik.Web.UI.ExportInfrastructure.Column col = e.ExportStructure.Tables[0].Columns[colIndex];
    //loop through the cells to set format
    foreach (Telerik.Web.UI.ExportInfrastructure.Cell cell in col.Cells)
    {
        cell.Format = "##############";//or use another Excel format, this is just a series of digits for number representation
    }
    col.Width = 200;//enlarge the column to accommodate long numbers
}
 
protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    RadGrid1.DataSource = GetDummyData();
}
 
protected DataTable GetDummyData()
{
    DataTable dt = new DataTable();
 
    dt.Columns.Add("numbers", typeof(decimal));
    dt.Columns.Add("someField", typeof(int));
    dt.Columns.Add("someDate", typeof(DateTime));
 
    dt.Rows.Add(1, 2, new DateTime(2011, 06, 12));
    dt.Rows.Add(200011150032, 3, new DateTime(2011, 12, 12));
    dt.Rows.Add(3, 6, new DateTime(2012, 06, 17));
    dt.Rows.Add(4, 4, new DateTime(2012, 09, 18));
    dt.Rows.Add(5, 7, new DateTime(2013, 03, 18));
 
    return dt;
}

and some sample markup
<telerik:RadGrid runat="server" ID="RadGrid1" OnInfrastructureExporting="RadGrid1_InfrastructureExporting" OnNeedDataSource="RadGrid1_NeedDataSource" AutoGenerateColumns="false">
    <ExportSettings Excel-Format="Xlsx" IgnorePaging="true" ExportOnlyData="true" OpenInNewWindow="true">
    </ExportSettings>
    <MasterTableView CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <telerik:GridBoundColumn DataField="numbers" UniqueName="numbers" HeaderText="large numbers" DataType="System.Decimal"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="someField" UniqueName="someColumn" HeaderText="another column" DataType="System.Decimal"></telerik:GridBoundColumn>
            <telerik:GridDateTimeColumn DataField="someDate" UniqueName="dateColumn" HeaderText="the date"></telerik:GridDateTimeColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

More Resources

  • Forums

    Find answers to thousands of questions or reach out to our active community.

  • Demos

    Browse 1,200+ demos with source code covering basic and advanced scenarios.

  • Sample Applications

    Learn how to integrate the controls in real-world examples.

  • Feedback Portal

    Vote and follow the features you want to see added to Telerik UI for ASP.NET AJAX.

  • User Voice

    Get answers directly from the people who build the product.

  • Documentation

    Benefit from detailed help articles and API references for all controls.

  • Knowledge Base

    Learn how to achieve common scenarios from a multitude of tutorials and working examples.

  • Code Library

    Browse sample projects submitted by Telerik staff and community members.

  • Blog

    Stay up-to-date with the latest functionality, how-to articles and industry trends.

  • Release History

    Up-to-date release notes for all new features and improvements.