Fix duration handling on .NET Core.
AppSheet's version of EPPlus added a hack for OLE Automation dates for
values that use specific formatting styles (20, 21, 45, 46, 47) which
we consider "durations".
See: https://github.com/closedxml/closedxml/wiki/
NumberFormatId-Lookup-Table
For these "duration" date fields, we don't use the .NET built-in
DateTime.FromOADate(double) method because we believed it returned the
"wrong" value. Specifically, we treat the double value as an absolute
offset from 1899-12-30 00:00. For dates after 1899-12-30 00:00, this is
close enough (though see notes about rounding), but for dates before
1899-12-30 00:00 AppSheet's behavior is wrong. Specifically, on an OLE
Automation date, the negative sign *only* affects the integer portion
of the double, *not* the fractional portion. So, for example, -0.5 and
0.5 are the same OLE Automation date, and -1.5 means "0.5 days after 1
day before 1899-12-30 00:00", *not* "1.5 days before 1899-12-30 00:00".
Unfortunately, this busted behavior is baked into the AppSheet codebase,
and it's been this way for a long time. "Fixing" this behavior would
break existing apps that use Excel and have dates before 1899-12-30 or
negative durations.
The current implementation has another unfortunate problem - it only has
millisecond precision because of the way that TimeSpan.FromDays works.
In .NET Framework, this doesn't seem to matter because DateTime has
limited precision anyway, but in .NET Core it results in weird rounding
issues.
This change attempts to work around the rounding issues while preserving
the existing (broken) AppSheet behavior. It also adds tests for both
.NET Framework and .NET Core to verify the "correct" functionality.
Change-Id: Iefda8bd95583b28aae49e0fb97ab4716b155acf8
10 files changed
