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

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