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

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 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?

Solution 1:

Below is some code to format columns A and C as text in SpreadsheetGear for .NET which has an API which is similar to Excel - except for the fact that SpreadsheetGear is frequently more strongly typed. It should not be too hard to figure out how to convert this to work with Excel / COM:

IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.Worksheets[0].Cells;
// Format column A as text.
cells["A:A"].NumberFormat = "@";
// Set A2 to text with a leading '0'.
cells["A2"].Value = "01234567890123456789";
// Format column C as text (SpreadsheetGear uses 0 based indexes - Excel uses 1 based indexes).
cells[0, 2].EntireColumn.NumberFormat = "@";
// Set C3 to text with a leading '0'.
cells[2, 2].Value = "01234567890123456789";
workbook.SaveAs(@"c:\tmp\TextFormat.xlsx", FileFormat.OpenXMLWorkbook);

Disclaimer: I own SpreadsheetGear LLC

Solution 2:

If you set the cell formatting to Text prior to adding a numeric value with a leading zero, the leading zero is retained without having to skew results by adding an apostrophe. If you try and manually add a leading zero value to a default sheet in Excel and then convert it to text, the leading zero is removed. If you convert the cell to Text first, then add your value, it is fine. Same principle applies when doing it programatically.

        // Pull in all the cells of the worksheet
        Range cells = xlWorkBook.Worksheets[1].Cells;
        // set each cell's format to Text
        cells.NumberFormat = "@";
        // reset horizontal alignment to the right
        cells.HorizontalAlignment = XlHAlign.xlHAlignRight;

        // now add values to the worksheet
        for (i = 0; i <= dataGridView1.RowCount - 1; i++)
        {
            for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
            {
                DataGridViewCell cell = dataGridView1[j, i];
                xlWorkSheet.Cells[i + 1, j + 1] = cell.Value.ToString();
            }
        }

Solution 3:

Solution that worked for me for Excel Interop:

myWorksheet.Columns[j].NumberFormat = "@";      // column as a text
myWorksheet.Cells[i + 2, j].NumberFormat = "@"; // cell as a text

This code should run before putting data to Excel. Column and row numbers are 1-based.

A bit more details. Whereas accepted response with reference for SpreadsheetGear looks almost correct, I had two concerns about it:

1. I am not using SpreadsheetGear. I was interested in regular Excel communication thru Excel interop without any 3rdparty libraries,
2. I was searching for the way to format column by number, not using ranges like "A:A".

Solution 4:

Before your write to Excel need to change the format:

xlApp = New Excel.Application
xlWorkSheet = xlWorkBook.Sheets("Sheet1")

Dim cells As Excel.Range = xlWorkSheet.Cells

'set each cell's format to Text
cells.NumberFormat = "@"

'reset horizontal alignment to the right
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

Solution 5:

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.