This project has moved. For the latest updates, please go here.

How to set Column Type to Date

Mar 25, 2012 at 11:25 AM

Hey guys,

I'm populating ExcelWorkSheet by using LoadFromDataTable() method, and passing to it DataTable. In DataTable create method I'm setting one of it's column's type to DateTime. However when Excel gets populated, Date column cell's type is Number, if I manually change it to Date, it shows correct data. So the question is, can I programmatically set type of COLUMN to particular type, and if I can, how to do that ?

Thanks in advance.

Mar 25, 2012 at 12:25 PM

This worked for me where ExpiryDate is of type DateTime:

worksheet_1.Cells[row, 3].Style.Numberformat.Format = "dd-mmm-yy";
worksheet_1.Cells[row, 3].Value = ExpiryDate;

Mar 25, 2012 at 2:04 PM

Thanks for response. And yes your code works, but can't we set Cell's type to Date from code ?

Jan 4, 2013 at 3:27 PM
Edited Jan 4, 2013 at 3:31 PM

Hi,

I had the same issue, as the generated cell format is "Custom" not "Date".
But I finally found a way to generate a proper "Date" format:

worksheet_1.Cells[row, 3].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;