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();

score 25 · Answer 1 · edited Sep 06 '16 at 09:27

25

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!

edited Sep 06 '16 at 09:27

slava

1,901
6
28
32

answered May 31 '13 at 12:13

mounisha nandella

251
3
3

Hey @viacheslav, appending "\t" to data is not working for me, it is still removing starting zero. – Dreamer Jan 06 '20 at 11:48
Hey, this works for me. The zeroes remain in cell. But it adds a tab space before the start of the string. Is there any alternative I could use ? – Jimesh Jul 29 '22 at 05:44

score 8 · Answer 2 · answered Dec 04 '12 at 17:37

8

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.

answered Dec 04 '12 at 17:37

Aaron West

187
2
5

score 6 · Accepted Answer · answered Jul 20 '12 at 07:36

6

In Excel file, Numbers cell always strips the leading zeros, you can set numbers with leading zeros by following a single quote. i.e.

00123450098 to '00123450098

but then, the format for that cell will changes to text. If your generated excel file, have any formula, which is include that cell reference as number then it will not work as expected.

answered Jul 20 '12 at 07:36

Yograj Gupta

9,811
3
29
48

1

this i checked..but the single quotes are also exported to excel...how to avoid that.. – RobinHood Jul 20 '12 at 07:39
Yes, this single quote will also export to display leading zeros, and if you don't want to place single quote, then you should provide a number format to the cell in your code. – Yograj Gupta Jul 20 '12 at 07:46

score 4 · Answer 4 · answered Jul 20 '15 at 19:14

I had this problem as well. The solution I came up with was to sneak the leading zeros in using excel's built in char() function. In excel, char() returns the value of the ASCII character code that is passed to it, so char(048) returns 0. Before exporting to excel, prepend your variable like so...

varName = "=CHAR(048)&" + varName;

score 2 · Answer 5 · answered Aug 29 '17 at 00:27

I found my answer for this using a combination of StackOverflow link and a blog. There are excel formatting styles that can be applied to the gridview on rowdatabound. I used those and now my export does not strip the leading zeros.

Below is an example of my code.

ExpenseResultsGrid.RowDataBound += new GridViewRowEventHandler(ExpenseResultsGrid_RowDataBound);

        protected void AllQuartersGrid_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {//add this style to prevent truncating leading zeros in fund code during export to excel
                e.Row.Cells[2].Attributes.CssStyle.Add("mso-number-format", "\\@");
            }
        }

score 0 · Answer 6 · edited Oct 11 '12 at 22:05

0

While exporting, just add an empty string like "" before the value that is inserted:

string x = "000123";
myWorksheet.Cells[1,1] = "" + x;

edited Oct 11 '12 at 22:05

the Tin Man

158,662
42
215
303

answered Oct 11 '12 at 21:54

Chandre Gowda

9
1

1

Didnt work. Outputed the same integer without leading zeroes. – Jānis Gruzis Jul 25 '13 at 11:12

Export Excel : Avoid stripping the leading zeros

6 Answers6

Linked

Related