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

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:

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

1. A cell into which I entered the string with an apostrophe in front
2. A cell that was formatted with the "@" (="text") format
3. 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.