Oct 22, 2012 at 5:26 PM
Edited Oct 22, 2012 at 5:26 PM
Run the following against any excel file:
XLWorkbook wb = new ClosedXML.Excel.XLWorkbook(@"c:\temp\test.xlsx");
IXLWorksheet ws = wb.Worksheet(1);
ws.Cell(1, 1).DataType = XLCellValues.Text;
//The following will switch the DataType to DateTime and "corrupt" the value.
ws.Cell(1, 1).Value = "008117AM5";
I looked into the source code and line 1382 of XLCell.cs is doing a DateTime.TryParse and then changing the DataType of the Cell if it succeeds.
This has been causing sporadic havoc with some of the Excel files we generate.
My opinion is that setting the value of a cell really shouldn't cause a change in the DataType. An error for an invalid value should be thrown if it doesn't match the DataType, but the DataType should never change unless specifically set.
I don't know if it is functionality that is set in stone at this point as others may actually rely on the way Closed XML currently works. If so, An alternative I would recommend would be a flag at the workbook level. Maybe something like "AutoDataTypeChecking".
If there is some setting I am missing, let me know. Thanks for a great library.