Problem with dates using CarlosAg.ExcelXmlWriter

Question

I am using the CarlosAg.ExcelXmlWriter library to generate an Excel file in C#. If I treat all data as strings everything works fine, but I need some cells to be recognized by Excel as date fields. When I try to set the data type accordingly, the resulting Excel file fails to open in Excel 2003 (or Excel 2007 for that matter).

In Excel 2003, I get the following error on load:

Problems came up in the following area during load: Table

I am using the following code to generate the DateTime cells that are causing the problem:

string val = DateTime.Now.ToString("MM/dd/yyyy");     
row.Cells.Add(new WorksheetCell(val, DataType.DateTime));

Thanks.

Answer 1

I eventually figured this out. Thanks to Lance Roberts for nudging me in the right direction.

First, define a WorksheetStyle called dateStyle and set its NumberFormat property to a valid Excel date format:

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("myWorksheet");

WorksheetStyle dateStyle = book.Styles.Add("dateStyle");
dateStyle.NumberFormat = "Dd Mmm Yy";

WorksheetRow row = book.Worksheets[0].Table.Rows.Add();

Then, export the date using the .NET "s" date format and add the cell:

string val = DateTime.Now.ToString("s");
row.Cells.Add(val, DataType.DateTime, "dateStyle");