Click here to Skip to main content
15,743,427 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im having datatable to export it to Excel. In some versions the formatting is not correct so im getting my date format as some numbers. I need to format the some columns to datetime directly in coding. Is there any possibility.

What I have tried:

worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[rowsCount + 1, columnsCount])).Value = cells; // dataTable.AsEnumerable().Select(row => row.ItemArray).ToArray();
              foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.Columns)
                  worksheet.Columns[3].NumberFormat = "dd/MM/yyyy";
Updated 5-Jun-18 3:39am

1 solution

You have already established that there is a NumberFormat property and you know how to set it to a date, so instead of just setting it compare it against that format.

There is a worked example here C# : How to identify the cell format is Number or currency ot accounting or percentage in excel using Interop[^]
Unfortunately the link in the solution is broken, but I think this article will provide any extra information you need Excel custom number formats | Exceljet[^]
Alternatively you could format a variety of columns with the different formats you might come across and use the following to work out what the format strings look like (untested):
foreach (Microsoft.Office.Interop.Excel.Range col in worksheet.Columns)

As an aside, it is not good practice to use a variable name of row to hold a column as in
foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.Columns)
and there is no point in using a foreach if you are going to explicitly reference a single column as in
worksheet.Columns[3].NumberFormat = "dd/MM/yyyy";
See my code above for a better way of doing this
Share this answer

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

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