Export to CSV - Leading Zeros

[Jon]

Hello,

I am exporting data to CSV.
Some of my data contains leading zeros (U.S. postal codes for NJ and MA).
When opening the CSV file in excel, the leading zeros are dropped and thereby leaving an incorrect postal code.

Your documentation mentions the following:
Microsoft Excel parses the cell values automatically depending on the local settings. For example the string 19/05 might change to 19.May automatically. The only workaround would be to insert a sign of equality (=) before the relevant string. For example: "012"  should be modified as  ="012".

Is there a way to modify the string as you suggest in code when exporting the grid data to CSV?
Or is this a manual process the user must perform to the CSV file prior to opening in excel?

Thanks,
Jon

[Daniel]

Hello Jon,

You could insert the equality sign in the GridExporting event handler manually:

protected void RadGrid1_GridExporting(object source, GridExportingArgs e)
{
  e.ExportOutput = e.ExportOutput.Replace("\"0", "=\"0");
}

Please note that this is not an "official" approach but a workaround.

Regards,
Daniel
the Telerik team

[Jon]

Daniel,

Your code works great!
Thanks for the tip.

Jon

[Jeff Reinhardt]

I have used another workaround that helps if you have numeric text like a UPC code where it will not always be a leading zero but you do not want Excel to handle it as a number because it will cause issues formatting wise. 

I am using a Command button in the grid for Export to Excel, in the ItemCommand method I have this code:

if (e.CommandName == "Export")
{
    rgManage.ExportSettings.OpenInNewWindow = true;
    rgManage.ExportSettings.ExportOnlyData = true;
    rgManage.ExportSettings.IgnorePaging = true;
    rgManage.ExportSettings.FileName = "Some_Report";
    IsExport = true;
    rgManage.Rebind();
    rgManage.MasterTableView.ExportToExcel();
}

IsExport is simply a class level boolean.  After setting that to true I call rebind and in the ItemDataBound routine I do this:

if (IsExport)
{
      string UPC = gdi["UPC"].Text;
      gdi["UPC"].Text = "'" + UPC;
}

By adding the single TIC (') in front of the UPC text, when it is exported, Excel takes it in as a text field and does no formatting to it.

[Daniel]

Hello Jeff,

The same effect can be achieved using the mso-number-format style as explained in the documentation:
Word/Excel export (HTML-based)

...
e.Cell.Style["mso-number-format"] = @"\@";

Regards,
Daniel
the Telerik team

[Jeff Reinhardt]

This format kept Excel from formatting it but the leading zero was dropped. see attachments.

protected void rgManage_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
        {
            switch (e.FormattedColumn.UniqueName)
            {
                case "UPC":
                    e.Cell.Style["mso-number-format"] = @"\@";
                    break;
            }
        }

[Kellen]

I am having the same problem but none of these fixes have helped. I am using asp.net radgrid. I even went as far as adding the = or the ' directly into the database where I am getting the data from. They display in the grid (Which I wouldn't want) but even so when I export the leading zeros are still dropped. I was unable to try the solutions GridExporting event or ExcelExportCellFormatting because I didn't see those events in the radgrid properties...

[Jeff Reinhardt]

How old of a version are you using?

[Kellen]

RadGrid version 5.1.6.0

[Daniel]

Hello Jeff,

Please download the attached demo and test it on your end.

Another approach would be to define a custom number style:

e.Cell.Style["mso-number-format"] = @"00000000";

Let me know what your findings are.

Best regards,
Daniel
the Telerik team

[Daniel]

Hello Kellen,

For the classic RadGrid, I recommend that you use the following way:

<asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
<telerik:RadGrid ID="RadGrid1" GridLines="None" Width="600px" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource"
    OnItemDataBound="RadGrid1_ItemDataBound">
</telerik:RadGrid>

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    DataTable table = new DataTable();
    table.Columns.Add("Binary", typeof(string));
    table.Rows.Add("10001011");
    table.Rows.Add("00011001");
    table.Rows.Add("01001111");
    table.Rows.Add("01100100");
    table.Rows.Add("10011000");
    table.Rows.Add("00000011");
    (source as RadGrid).DataSource = table;
}

bool isExcelExport = false;
protected void Button1_Click(object sender, EventArgs e)
{
    isExcelExport = true;
    RadGrid1.ExportSettings.ExportOnlyData = true;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.MasterTableView.ExportToExcel();
}

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
{
    if (isExcelExport && e.Item is GridDataItem)
    {
        foreach (TableCell cell in e.Item.Cells)
        {
            //Approach 1
            cell.Style["mso-number-format"] = @"\@";

            //Approach 2
            //cell.Style["mso-number-format"] = @"00000000";

            //Approach 3
            //cell.Text = " " + cell.Text;
        }
    }
}

Best regards,
Daniel
the Telerik team

[Kellen]

Daniel, that did it. Thank you! Approach one did the trick. I didn't use the needdatasource code (as I had my own data) of course, and I am handling the export clientside so all I needed was the itemdatabound event. Thanks a million!