Unable to insert minimum datetime as cell value in ClosedXML

Question

ClosedXml does not allow to insert minimum datetime into cell. My datatable includes minimum datetime values that ClosedXml does not like.

using (var workbook = new XLWorkbook())
{
    var dataTable = GetDataTable();
    workbook.Worksheets.Add(dataTable, "myWorksheet"); // Exception thrown
    workbook.SaveAs(filePath);
}

Exception is thrown when adding a worksheet: 'Unable to set cell value to 01/01/0001 00:00:00'

How could I allow ClosedXml to insert this value without throwing exception? For example it could write empty values instead of throwing exception. Would it be possible to get around this without modifying the dataTable to clean up these minimum datetime values?

Answer 1

Excel itself doesn't allow you to set a cell value to such a low date value. Try it for yourself. Previously, one could set the value in ClosedXML, but Excel would then believe that the file is corrupt. The check was added to avoid the creation of a seemingly corrupt file.

Answer 2

Try to change your DateTime default value to UTC:

DateTime dt = new DateTime();
dt.ToUniversalTime()   

Answer 3

I have the same situation here. the solution is simple, to have the datatable field datetime with null value so ClosedXML can proses the null date and change it to empty

Answer 4

As others have mentioned, Excel will not allow dates before January 1st 1970 December 30th 1899.

I just wanted to add that you can use DateTime.UnixEpoch instead of DateTime.MinValue as your minimum date time. DateTime.UnixEpoch is defined as DaysTo1970 * TicksPerDay.

The value of this constant is equivalent to 00:00:00.0000000 UTC, January 1, 1970, in the Gregorian calendar.

Edit: Corrected minimum date, thx breeze. Personally I would still use DateTime.UnixEpoch as a minimum date, as it looks less specific, but whatever floats your boat.