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();
myWorksheet.Cells[i + 2, j]
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.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