Format an Excel column (or cell) as Text in C#?

Question

I am losing the leading zeros when I copy values from a datatable to an Excel sheet. That's because probably Excel treats the values as a number instead of text.

I created the worksheet in C# and I am copying the values like so:

myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();

How do I format a whole column or each cell as Text? A related question, how to cast myWorksheet.Cells[i + 2, j] to show a style property in Intellisense?

Answer 1

   if (dtCustomers.Columns[j - 1].DataType != typeof(decimal) && dtCustomers.Columns[j - 1].DataType != typeof(int))
   {
      myWorksheet.Cells[i + 2, j].NumberFormat = "@";
   }

Answer 2

I've recently battled with this problem as well, and I've learned two things about the above suggestions.

1. Setting the numberFormatting to @ causes Excel to left-align the value, and read it as if it were text, however, it still truncates the leading zero.
2. Adding an apostrophe at the beginning results in Excel treating it as text and retains the zero, and then applies the default text format, solving both problems.

The misleading aspect of this is that you now have a different value in the cell. Fortuately, when you copy/paste or export to CSV, the apostrophe is not included.

Conclusion: use the apostrophe, not the numberFormatting in order to retain the leading zeros.

Answer 3

Use your WorkSheet.Columns.NumberFormat, and set it to string "@", here is the sample:

Excel._Worksheet workSheet = (Excel._Worksheet)_Excel.Worksheets.Add();
//set columns format to text format
workSheet.Columns.NumberFormat = "@";

Note: this text format will apply for your hole excel sheet!

If you want a particular column to apply the text format, for example, the first column, you can do this:

workSheet.Columns[0].NumberFormat = "@";

or this will apply the specified range of woorkSheet to text format:

workSheet.get_Range("A1", "D1").NumberFormat = "@";