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.

Answer 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