Click here to Skip to main content
15,943,008 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
See more:
When I export the contents of a datatable to excel, the leading 0s of the
first column are truncated. Is there a way to keep this from happening?
Posted
Updated 14-Jan-14 21:48pm
v2
Comments
Karthik_Mahalingam 15-Jan-14 3:44am    
post your code

While exporting to excel, adding "\t" before the value being inserted
for example:
C#
string test = "000456";
string insertValueAs = "\t" + test;

the string test would then be considered as a string value and not an integer value. Thus, it would retain the leading zeros.
Let me know if it helps or not.
 
Share this answer
 
Comments
ars88 5-Apr-14 1:31am    
Thnx. It works great
The best you could do is to convert the column format to Text
That can be done by

In the following I am converting the format of the C1 column to Text
C#
range["C1"].NumberFormat="@";
range["C1"].Value="00129";/*Then you have to specify the value*/  


the above will give the exact value you assigned to the column.

You can also look at the other formats like Pincode, Creditcard number format in the following
http://office.microsoft.com/en-sg/excel-help/keep-leading-zeros-in-number-codes-HA010342581.aspx[^]
 
Share this answer
 
v2
The best way to do it is to format the column as a number, with the appropriate number of leading zeros - that way you can still use it as a numeric value.
This creates a new file, with the first column formatted to show two digits, with a leading zero:
C#
Excel.Application excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook wb = excel.Workbooks.Add(1);
Excel.Worksheet ws = (Excel.Worksheet) wb.Sheets[1];
ws.Cells[1, 1].EntireColumn.NumberFormat = "00";
wb.SaveAs(@"D:\Temp\aaexcel.xlsx");
But you can open an existing file instead and apply the formatting in exactly the same way.
 
Share this answer
 
Comments
Shivam Sharma 25-Aug-20 13:23pm    
Excel.Application is using which library references ?
OriginalGriff 25-Aug-20 16:32pm    
It's a lot, lot quicker to look at the documentation:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.application?view=excel-pia
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "@";
xlWorkSheet.Cells[1, 1] = "098";

Full Source : C# Excel Formatting

Justin
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900