EPPlusSoftware
DateOnly Not Fully Supported as a Proper Sortable Date
EPPlus usage
Noncommercial use
Environment
Windows 10+
Epplus version
7.2.0
Spreadsheet application
Microsoft Excel
Description
When I set the Value of a cell in code to a DateTime, then view it in Excel, I see something like "43980" unless I apply formatting, but if I set the Value of a cell to a DateOnly, then view it in Excel, I see something like "5/9/2020". The former can be formatted in Excel as a Date and sorted ASC/DSC properly; the latter is treated as text by Excel and will not allow me to format it or sort it properly.
I believe this is because under the hood EPPlus automatically converts the DateTime using ToOADate when setting the raw value in the XML, and I think to properly support DateOnly the same needs to be done for that type too.
I am not using TimeOnly in my code, but it's worth noting that the same issue probably exists for that type too.
If I'm right, then there really shouldn't be any further info required here; this would be a fairly quick change to your type conversion code, I hope. Please let me know if you cannot repro with this limited info.
This might be more correctly labeled as a feature request and not a bug. If so, I'm sorry for calling it a bug.
EPPlus currently do not handle DateOnly and TimeOnly, so as you say, they will be converted into string's. DateOnly and TimeOnly seem to be available only in .NET 6 and above and not in .NET Standard or .NET Framework. However, it should be possible to add functionality for this using compiler directive for .NET 6+, so I will relabel this issue as an enhancement and look into how it should be implemented. For now, convert these data types to DateTime to get the correct behaviour for dates.
Thanks. I don't care for DateOnly and TimeOnly as they are hard to use and lack good support move back and forth from DateTime, but the latest Entity Framework power tools scaffold the DB context using DateOnly where I use Date in my database, so I was kind of "forced" into it.
I mention this because it lead to a really poor UX when I had to track down why the rendered Date in Excel appeared fine (the format happened to be an exact match to the format string I was passing in), and it was only after a user (frustratingly, the CEO) couldn't sort the data in the column properly. I spent quite a while tracking this down.
Hopefully, if supporting it is a fairly quick change, this is good reason to prioritize it. And if it's not quick, then maybe some kind of change that by default kicks out an error or warning when the code passes in a DateOnly or TimeOnly. Food for thought only. Thank you again.
That's a breaking change, BTW. I was saving DateOnly to excel then reading it.
Now instead of strings I get a double, which I had to look hot to parse: DateOnly.FromDateTime(DateTime.FromOADate(d))
