Excel not inserting leading zero

Question

I am using Office OpenXml to write to an Excel file. The file is a template so it already has all my headers and formatting for my columns. I am inserting numbers that have leading zeroes to a "special" column which is basically a 10 digit number. However in my code I can see that it is setting the value to for example 0000000004. The result in the sheet with a value of 4 in that cell and the actual cell showing 0000000004.

Here is my code to write to the cell.

  if (reader[2].ToString().Length < 9)
  {


        myworksheet.Cell(firstrow, 12).Value = reader[2].ToString(); //0045678945

  }
  else
  {
        myworksheet.Cell(firstrow, 12).Value = reader[2].ToString().Substring(0, 9); //0045678945

  }

when I open the excel sheet like I stated above my value is 45678945 instead of 0045678945

Any help would be appreciated.

Have you tried doing some validation on the value inside of Excel? Maybe write a macro that parses the value that's input to ensure it has the correct number of characters and add the leading zeros if it doesn't fit your criteria. Maybe even define a custom data type for that column. — sean_m, Feb 28 '13 at 21:23
Can you explain what your code does in the above to cause zeros to be prepended? It looks as though you will set the cell to the value of the string when the length is less than 9 - how do you make the zeros appear in that string? — Floris, Feb 28 '13 at 21:26

Floris · Answer 1 · 2013-02-28T21:19:20.220

The easiest way to get the result you want is to prepend an apostrophe to the value you put in the cell - this tells Excel it is a string:

Cell.Value= "'" & "000123"

Will show up as 000123

Here is a bit of code to show how things work (at least how they work for me, in Excel 2010):

Sub testFormat()
[A1].Value = "000123"
[A2].Value = "'000123"
[A3].Value = "000123"
[A3].NumberFormat = "@"
[A4].Value = "'000123"
[A4].NumberFormat = "@"
End Sub

The result of this is as follows:

formatting with leading zeros

As you can see, there are three cells that show the leading zeros:

A cell into which I entered the string with an apostrophe in front
A cell that was formatted with the "@" (="text") format
A cell that had the apostrophe AND the text formatting

I'm not sure what you did to make the apostrophe appear in your spreadsheet... But I'm hoping the above will inspire you to solve your problem.

Very strange - I have added some more examples of how this works for me. Hope it helps! — Floris, Feb 28 '13 at 21:19

score 1 · Accepted Answer · answered Feb 28 '13 at 19:19

1

If I understand correctly:

Excel shows numbers with leading zeroes, reading them from C# via OOXML you don't see the leading zeroes.

Chances are Excel is setting some formatting rules instead of storing the actual leading zeroes.

Several ways to counteract that. Here are the "cheapest" ones that come to mind, pick one:

format the column in Excel as text
in C# code don't expect the leading zeroes and instead add them

answered Feb 28 '13 at 19:19

Sten Petrov

10,943
1
41
61

Excel Does not show leading zero when sheet created with OOXML. – user541597 Feb 28 '13 at 20:24
@user541597 then, by reversing my answer, you can set the column type to text from OOXML so that Excel doesn't try to interpret it – Sten Petrov Feb 28 '13 at 20:43

score 0 · Answer 3 · answered Feb 28 '13 at 19:20

0

In order to get leading zeros to not be truncated from within Excel you need to set the number format for the cells to Text.

answered Feb 28 '13 at 19:20

GordonsBeard

636
4
14

already set the cells to text however it is still truncating the leading zero. – user541597 Feb 28 '13 at 20:23
Are the zeros being truncated before it gets put into Excel? You might need to change the C# code that gets the info in that case. – GordonsBeard Feb 28 '13 at 20:30
No I debugged the code that gets put into Excel and the zeroes are there. – user541597 Feb 28 '13 at 20:35

score 0 · Answer 4 · answered Oct 03 '13 at 14:05

I believe if you set the cell's DataType to either a String or SharedString the leading 0's will be preserved. Excel will see this as text and just treat the value literally and not try to apply any formatting rules.

cell.DataType = new EnumValue<CellValues>( CellValues.SharedString );

OR cell.DataType = new EnumValue( CellValues.String );

score 0 · Answer 5 · answered Jan 16 '14 at 06:15

If you are using a DataTable you need to Take another DataTable and then you need to iterate through entire cells in the datatable and prepend the cells text with space i.e with '&nbsp';.We cannot modify the rows in the same table because it will throw an exception saying "Collection was Modified".We have to take a new datatable.

Consider the following code.

    //To get the result with leading zero's in excel this code is written.
    DataTable dtUpdated=new DataTable();
    //This gives similar schema to the new datatable
    dtUpdated = dtReports.Clone();
        foreach (DataRow row in dtReports.Rows)
        {
            for (int i = 0; i < dtReports.Columns.Count; i++)
            {
                string oldVal = row[i].ToString();
                string newVal = "&nbsp;"+oldVal;
                row[i] = newVal;
            }
            dtUpdated.ImportRow(row); 
        }

We can bind this updated table to datagrid so that it can be useful for Excel Conversion.

Excel not inserting leading zero

5 Answers5

Linked