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 haven't used his library, but work in Excel a lot. I don't know what he's doing with a datatype for a cell, since they don't work that way. Dates in Excel cells are all integers with Date Formatting.

I would try to put the date in as an integer, the trick is converting your string to the correct integer. See this link for information on Excel's Date as Numbers methodology. I would then set the WorksheetStyle.NumberFormat Property.

Answer 2

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");