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?
Both setting the NumberFormat = "@" and prefixing the value with an apostrophe retained leading zeros for me. When using the NumberFormat method, to retain right alignment in the column, you can simply add the following: rng. NumberFormat = "@"; rng. HorizontalAlignment = Excel.
If you want to take text from multiple columns and merge it into one, you can easily do it in Excel. There are a few ways to do it, using an ampersand (&), the CONCAT function, or VBA.
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
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(); } }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With