1

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:

enter image description here

  1. 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:

enter image description here

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?

Community
  • 1
  • 1
majjam
  • 1,286
  • 2
  • 15
  • 32
  • If you are doing a cast then you are probably entering 0's in the cells without dates. Post your code so other can take a look. – Ernie S Mar 05 '16 at 03:56

1 Answers1

3

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:

enter image description here

Philip Bijker
  • 4,955
  • 2
  • 36
  • 44