How to leave cells empty in Date column instead of writing DateTime.Min in EPPlus

Question

I need to write a formatted date to Excel using EPPlus, leaving empty cells where there are no dates to write. I have tried the following:

1. Writing a date, and then formatting it. This works unless I have no date, in which case the minimum value is written:

2. Formatting the date as a string (passing an empty string when there is no date), then assigning a custom format. The problem with this is that Excel doesn't see the type as a date, therefore a downstream system cannot use it:

How can I write dates to Excel, using EPPlus, where the dates are recognised as date types (not strings), but where missing date values are not written at all?

Answer 1

Make sure the data you bind to the date column is of type DateTime? (nullable). Only if you provide a null value, an empty column will be rendered.

For example:

// Date format on first column
sheet.Column(1).Style.Numberformat.Format = "yyyy-mm-dd";

// Some date values
var columnValues = new List<DateTime?> {
    DateTime.Now,
    null,
    DateTime.Now.AddDays(1) };

// Bind values to column
sheet.Cells[1, 1].LoadFromArrays(columnValues.Select(v => new object[] { v }));

Result: