This project has moved. For the latest updates, please go here.

Cell DataType Changes without Notice

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.

-Mike

Coordinator
Oct 22, 2012 at 5:29 PM

That's on the queue. In the meantime use cell.SetValue(...) instead.

Oct 22, 2012 at 6:15 PM
Edited Oct 22, 2012 at 6:19 PM

It is SetValue(...) that is the culprit however.

I rewrote it to be the following:

        private void SetValue(object value)
        {
            FormulaA1 = String.Empty;
            var val = value == null ? String.Empty : value.ToString();
            _richText = null;
            if (val.Length != 0)
            {
                //Check the DataType to make sure it matches the new value.
                DataType = _dataType;
            }
            if (val.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters.");
            _cellValue = val;
        }

I also had to comment out

//if (_dataType == value) return;

In the "DataType" property.

This seems to do what we need for now.  DataType doesn't change when a value is set and an error is thrown if we put a value in that conflicts with the Datatype.

I bet there are side effects I have introduced so I will be looking for updates that might resolve this.  Is there a specific issue number I can refer to to see status?

Thanks.

-Mike

Oct 22, 2012 at 6:23 PM

Oh, I see the alternate method:

SetValue<T>(T value)

I will look and see what we would have to do to use that.