This is a migrated thread and some comments may be shown as answers.

Export to CSV - Leading Zeros

11 Answers 325 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Jon
Top achievements
Rank 1
Jon asked on 12 Aug 2010, 07:44 PM
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

11 Answers, 1 is accepted

Sort by
0
Accepted
Daniel
Telerik team
answered on 18 Aug 2010, 11:14 AM
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
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 Public Issue Tracking system and vote to affect the priority of the items
0
Jon
Top achievements
Rank 1
answered on 18 Aug 2010, 12:55 PM
Daniel,

Your code works great!
Thanks for the tip.

Jon
0
Jeff Reinhardt
Top achievements
Rank 1
answered on 18 Aug 2010, 01:56 PM
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.

0
Daniel
Telerik team
answered on 18 Aug 2010, 02:28 PM
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
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 Public Issue Tracking system and vote to affect the priority of the items
0
Jeff Reinhardt
Top achievements
Rank 1
answered on 18 Aug 2010, 03:05 PM
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;
            }
        }


0
Kellen
Top achievements
Rank 1
answered on 18 Aug 2010, 10:36 PM
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...
0
Jeff Reinhardt
Top achievements
Rank 1
answered on 19 Aug 2010, 12:54 AM
How old of a version are you using?
0
Kellen
Top achievements
Rank 1
answered on 19 Aug 2010, 04:24 PM
RadGrid version 5.1.6.0
0
Daniel
Telerik team
answered on 19 Aug 2010, 08:53 PM
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
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 Public Issue Tracking system and vote to affect the priority of the items
0
Daniel
Telerik team
answered on 19 Aug 2010, 09:01 PM
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
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 Public Issue Tracking system and vote to affect the priority of the items
0
Kellen
Top achievements
Rank 1
answered on 19 Aug 2010, 10:11 PM
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!
Tags
Grid
Asked by
Jon
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Jon
Top achievements
Rank 1
Jeff Reinhardt
Top achievements
Rank 1
Kellen
Top achievements
Rank 1
Share this question
or