Export Excel : Avoid stripping the leading zeros

Question

I am Export a data to Excel Sheet in C#.Net. There i am having column which has the data like "00123450098". The data is exported without the first zero's. I want to show the data as it is.

Here is my export excel code.

 string style = @"<style> .text { mso-number-format:\@; } </style> ";
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
    HttpContext.Current.Response.ContentType = "application/ms-excel";
    HtmlForm frm = new HtmlForm();
    ...................
    ...................
     table.RenderControl(htw);
            HttpContext.Current.Response.Write(style);
            //render the htmlwriter into the response
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();

Answer 1

While exporting to excel, adding \t before the value being inserted will solve the problem. Eg:

string test = "000456";
string insertValueAs = "\t" + test;

The string test would then be considered as a string value and not an integer value. Thus, it would retain the leading zeros.

I have faced the same issue, and above solution worked for me. Hope this post helps!

Answer 2

If exporting to CSV / TSV, put this into each cell containing a textual "number" with leading 0s or (especially) 16+ digits:

="0012345"

..where 0012345 is the number you want to export to that cell.

I wish I could remember where I saw that.