Hello,
I am using Cells.ImportDataTable() to import data from a database, which works fine. For columns containing date/time values, I would like to set an appropriate cell format, using Cells.Columns[3].Style.Custom="MM/dd/yyyy". Again, doing so is not a problem, as long as the worksheet was added to the workbook using Worksheets.Add(). However, when using a designer template file (Worksheets.Open("template.xls")), the formatting of the date/time columns shows absolutely no effect: instead of date/time values, Excel displays the corresponding numeric values, e.g. 38685 instead of 11/29/2005.
Here is some sample code to verify the problem; see the attachment for the designer file being used.
//generate some sample data
DataTable dt = new DataTable();
dt.Columns.Add("DateValue", typeof(DateTime));
dt.Rows.Add(new object[] {DateTime.Now});
dt.Rows.Add(new object[] {DateTime.Now.AddDays(1)});
dt.Rows.Add(new object[] {DateTime.Now.AddDays(2)});
Excel xls = new Excel();
xls.Open("template.xls"); //open designer file; if you comment this out, everything will work perfectly.
xls.Worksheets[0].Cells.ImportDataTable(dt, true, 0, 0); //import data
xls.Worksheets[0].Cells.Columns[0].Style.Custom = "MM/dd/yyyy"; //set date/time format; this will not show any effect
xls.Worksheets[0].Cells[0,10].PutValue(DateTime.Now); //import data
xls.Worksheets[0].Cells.Columns[10].Style.Custom = "MM/dd/yyyy"; //set date/time format; this is going to work, because PutValue was used instead of ImportDataTable
Thank you in advance!
Stefan Raffeiner