eCellType.Date and eCellType.DateTime

Jul 13, 2010 at 3:22 PM

First of all I have to say thank you for doing all of the hard work in building this fantastic library, I have spent some time looking for a library with this exact functionality!  Please keep up the great work!

That said, I am having an issue with the "eCellType.Date" and "eCellType.DateTime" enumerators.  The format for these is hard coded, so a date will always be in day/month/year, would it be possible to extend this to allow the format to be passed through and override the default?

Jul 13, 2010 at 4:11 PM
Edited Jul 13, 2010 at 4:12 PM
If you want date or any number in one of Office's formats or even you custom format - just create empty worksheet in Excel set format for cell, save document (as .xml table) and copy value from quotes. Something like:

<Style ss:ID="s74">
<NumberFormat ss:Format="yyyy\,\ mmmm;@"/>

And you need to copy: "yyyy\,\ mmmm;@"

Then, you must create your custom style and use it when you need value in such format:

ExcelRow row = table.AddRow();
ExcelCellStyle style = ExcelCellStyle.AddCellStyle("Custom_Date", "Date");
style.numberFormat=@"yyyy\,\ mmmm;@";
row.AddCell(eDateType.Date, DateTime.Now).cellStyle = "Custom_Date";

P.S.: Thank you for using my library. If you need any new functionality - just ask :)
Jul 14, 2010 at 8:18 AM
Hello Fahrain, thanks for the information, I never thought to look for the numberFormat property - worked like a charm :) I did find one other small issue though that needed me to change the code, the date format was being added to the XML document in this format on my computer: yyyy-MM-ddTHH:mm:ss And Excel loved it - however on the web server the date came through as: yyyy-MM-ddThh:mm:ss AM/PM Excel threw a mental and refused to load the document. I had to change the current threads locale to en-GB in order for it to work. I can't see why Excel should not be able to understand a DateTime that is in 12 hour format, but it seems Microsoft decided on a very specific DateTime parsing format. Seeing as I am in the UK I cannot test if this is also the case with US or mainland European date formats.
Jul 14, 2010 at 9:54 AM

There is very strange number and date/time formats inside Excel .xml... It seems that Excel always used hardcoded custom DateTime format and only after loading converts value into user format. Same problem with float/double values: decimal separator inside .xml must always be '.'