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
diff --git a/EPPlus.sln b/EPPlus.sln
index 02fa821..f41280f 100644
--- a/EPPlus.sln
+++ b/EPPlus.sln
@@ -1,4 +1,4 @@
-
+
Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 2013
VisualStudioVersion = 12.0.30110.0
@@ -9,18 +9,12 @@
LocalTestRun.testrunconfig = LocalTestRun.testrunconfig
EndProjectSection
EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlus", "EPPlus\EPPlus.csproj", "{7B288026-5502-4A39-BF41-77E086F3E4A3}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlusSamples", "SampleApp\EPPlusSamples.csproj", "{06BF3C68-E7D4-4579-90BE-E36DACE564EF}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlusTest", "EPPlusTest\EPPlusTest.csproj", "{E7BCEDE0-EADD-437B-86D5-49D192216948}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlusWebSample", "EPPlusWebSample\EPPlusWebSample.csproj", "{1BF30A52-6149-432D-82F6-725250E5C662}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlusExcelCalculationDemo", "EPPlusExcelCalculationSample\EPPlusExcelCalculationDemo.csproj", "{94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}"
-EndProject
Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "EPPlusSDK", "EPPlus\EPPlusSDK.csproj", "{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}"
EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "NetCoreTests", "NetCoreTests\NetCoreTests.csproj", "{7D59FAA6-A53F-4672-8387-9281731509F2}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "NetFrameworkTests", "NetFrameworkTests\NetFrameworkTests.csproj", "{BD35C0D2-3A4C-41AB-8300-734F6CAB9733}"
+EndProject
Global
GlobalSection(SolutionConfigurationPlatforms) = preSolution
Debug|Any CPU = Debug|Any CPU
@@ -34,81 +28,6 @@
Release|x86 = Release|x86
EndGlobalSection
GlobalSection(ProjectConfigurationPlatforms) = postSolution
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Debug|x86.ActiveCfg = Debug|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release 4.0|Any CPU.ActiveCfg = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release 4.0|Any CPU.Build.0 = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release 4.0|Mixed Platforms.ActiveCfg = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release 4.0|Mixed Platforms.Build.0 = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release 4.0|x86.ActiveCfg = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release|Any CPU.Build.0 = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release|Mixed Platforms.Build.0 = Release|Any CPU
- {7B288026-5502-4A39-BF41-77E086F3E4A3}.Release|x86.ActiveCfg = Release|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Debug|x86.ActiveCfg = Debug|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release 4.0|Any CPU.ActiveCfg = Release 4.0|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release 4.0|Any CPU.Build.0 = Release 4.0|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release 4.0|Mixed Platforms.ActiveCfg = Release 4.0|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release 4.0|Mixed Platforms.Build.0 = Release 4.0|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release 4.0|x86.ActiveCfg = Release 4.0|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release|Any CPU.Build.0 = Release|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release|Mixed Platforms.Build.0 = Release|Any CPU
- {06BF3C68-E7D4-4579-90BE-E36DACE564EF}.Release|x86.ActiveCfg = Release|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Debug|x86.ActiveCfg = Debug|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release 4.0|Any CPU.ActiveCfg = Release 4.0|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release 4.0|Any CPU.Build.0 = Release 4.0|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release 4.0|Mixed Platforms.ActiveCfg = Release 4.0|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release 4.0|Mixed Platforms.Build.0 = Release 4.0|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release 4.0|x86.ActiveCfg = Release 4.0|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release|Any CPU.Build.0 = Release|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release|Mixed Platforms.Build.0 = Release|Any CPU
- {E7BCEDE0-EADD-437B-86D5-49D192216948}.Release|x86.ActiveCfg = Release|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Debug|x86.ActiveCfg = Debug|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release 4.0|Any CPU.ActiveCfg = Release 4.0|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release 4.0|Any CPU.Build.0 = Release 4.0|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release 4.0|Mixed Platforms.ActiveCfg = Release 4.0|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release 4.0|Mixed Platforms.Build.0 = Release 4.0|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release 4.0|x86.ActiveCfg = Release 4.0|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release|Any CPU.Build.0 = Release|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release|Mixed Platforms.Build.0 = Release|Any CPU
- {1BF30A52-6149-432D-82F6-725250E5C662}.Release|x86.ActiveCfg = Release|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Debug|x86.ActiveCfg = Debug|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release 4.0|Any CPU.ActiveCfg = Release 4.0|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release 4.0|Any CPU.Build.0 = Release 4.0|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release 4.0|Mixed Platforms.ActiveCfg = Release 4.0|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release 4.0|Mixed Platforms.Build.0 = Release 4.0|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release 4.0|x86.ActiveCfg = Release 4.0|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release|Any CPU.Build.0 = Release|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release|Mixed Platforms.Build.0 = Release|Any CPU
- {94CBCEFC-DDBF-4F54-8AEA-4C40BBD2881B}.Release|x86.ActiveCfg = Release|Any CPU
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Debug|Any CPU.Build.0 = Debug|Any CPU
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
@@ -127,6 +46,42 @@
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Release|Mixed Platforms.Build.0 = Release|Any CPU
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Release|x86.ActiveCfg = Release|Any CPU
{256597F1-67E5-478D-B5B8-3BBB47CB3D3F}.Release|x86.Build.0 = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|x86.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Debug|x86.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|Any CPU.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|Any CPU.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|Mixed Platforms.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|Mixed Platforms.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|x86.ActiveCfg = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release 4.0|x86.Build.0 = Debug|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|Any CPU.Build.0 = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|Mixed Platforms.Build.0 = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|x86.ActiveCfg = Release|Any CPU
+ {7D59FAA6-A53F-4672-8387-9281731509F2}.Release|x86.Build.0 = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|x86.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Debug|x86.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|Any CPU.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|Any CPU.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|Mixed Platforms.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|Mixed Platforms.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|x86.ActiveCfg = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release 4.0|x86.Build.0 = Debug|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|Any CPU.Build.0 = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|Mixed Platforms.Build.0 = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|x86.ActiveCfg = Release|Any CPU
+ {BD35C0D2-3A4C-41AB-8300-734F6CAB9733}.Release|x86.Build.0 = Release|Any CPU
EndGlobalSection
GlobalSection(SolutionProperties) = preSolution
HideSolutionNode = FALSE
diff --git a/EPPlus/EPPlusSDK.csproj b/EPPlus/EPPlusSDK.csproj
index 42fc4ae..4a28eeb 100644
--- a/EPPlus/EPPlusSDK.csproj
+++ b/EPPlus/EPPlusSDK.csproj
@@ -6,7 +6,7 @@
<SignAssembly>true</SignAssembly>
<AssemblyOriginatorKeyFile>OpenOfficeXml.snk</AssemblyOriginatorKeyFile>
<PackageId>Appsheet.EPPlus</PackageId>
- <Version>1.0.0</Version>
+ <Version>1.0.1</Version>
<DefaultItemExcludes>$(DefaultItemExcludes);Properties/AssemblyInfo.cs;FormulaParsing/LexicalAnalysis/TokenSeparatorHandlers/**;FormulaParsing/LexicalAnalysis/TokenHandler.cs;FormulaParsing/Excel/Functions/Math/Rank.cs</DefaultItemExcludes>
<DefineConstants>Core;STANDARD20</DefineConstants>
</PropertyGroup>
diff --git a/EPPlus/ExcelWorksheet.cs b/EPPlus/ExcelWorksheet.cs
index 2d1371f..c194e88 100644
--- a/EPPlus/ExcelWorksheet.cs
+++ b/EPPlus/ExcelWorksheet.cs
@@ -4,7 +4,7 @@
* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
* See http://www.codeplex.com/EPPlus for details.
*
- * Copyright (C) 2011 Jan Källman
+ * Copyright (C) 2011 Jan Källman
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
@@ -26,9 +26,9 @@
*
* Author Change Date
* ******************************************************************************
- * Jan Källman Initial Release 2011-11-02
- * Jan Källman Total rewrite 2010-03-01
- * Jan Källman License changed GPL-->LGPL 2011-12-27
+ * Jan Källman Initial Release 2011-11-02
+ * Jan Källman Total rewrite 2010-03-01
+ * Jan Källman License changed GPL-->LGPL 2011-12-27
*******************************************************************************/
using System;
using System.Xml;
@@ -1436,16 +1436,40 @@
};
}
- private DateTime DurationFromOADate(double value)
+ private static readonly DateTime _excelEpoch = new DateTime(1899, 12, 30);
+
+ public static DateTime IncorrectDurationFromOADate(double value)
{
- // Convert the Duration value into the corresponding DateTime value.
- // The Duration value is passed in as a fractional Office Automation double values where the
- // whole portion is the number of days and the fractional portion of the number is the hours.
- // For example, the Duration value -1.5 represents a negative duration of one day and 12 hours.
- // We use this technique rather than calling the 'DateTime.FromOADate()' method, because
- // this technique works for both positive and negative duration values. unlike 'DateTime.FromOADate()'
- // which yields the wrong result for negative Duration values.
- return new DateTime(1899, 12, 30) + TimeSpan.FromDays(value);
+ // This behavior is wrong. Real OADate values have a discontinuity on 30 December 1899.
+ // For real OADate values, the negative sign applies only to the integer portion of
+ // the float, *not* to the decimal portion. For example, -0.5 and 0.5 both refer to the
+ // same date, and -1.5 is actually 1899-12-29 12:00 (1 day before 1899-12-30 00:00
+ // plus 0.5 days), *not* 1899-12-28 12:00 (1.5 days before 1899-12-30 00:00).
+ //
+ // Unfortunately, AppSheet's duration-handling code gets this very wrong, and treats the
+ // duration as the offset from 1899-12-30 00:00. This is correct for positive durations,
+ // but it's wrong for negative durations. This code tries to fix the bug that exists in
+ // AppSheet's duration-handling code here, and it succeeds in some cases and fails in
+ // others.
+ //
+ // This code also breaks date/time handling for dates before 1899-12-30 00:00 in some
+ // cases. Specifically, dates end up being offset by one day.
+ //
+ // Regardless, changing this behavior is risky, so this code simply replicates the
+ // existing behavior for
+ if (value >= 0)
+ {
+ return DateTime.FromOADate(value);
+ }
+ else
+ {
+ // This looks like a very complicated way to call TimeSpan.FromDays(value), but
+ // TimeSpan.FromDays actually only guarantees millisecond precision, and critically
+ // rounding is different on .NET Core, resulting in values like (e.g.) 3:15:00 being
+ // incorrectly rounded.
+ var offset = DateTime.FromOADate(-value) - _excelEpoch;
+ return _excelEpoch - offset;
+ }
}
private void SetValueFromXml(XmlTextReader xr, string type, int styleID, int row, int col)
@@ -1500,7 +1524,7 @@
if (res >= -657435.0 && res < 2958465.9999999)
{
// Get the Duration value expressed as a DateTime.
- _values.SetValue(row, col, DurationFromOADate(res));
+ _values.SetValue(row, col, IncorrectDurationFromOADate(res));
}
else
{
diff --git a/EPPlusTest/EPPlusTest.csproj b/EPPlusTest/EPPlusTest.csproj
index 046e2db..4cc7ac5 100644
--- a/EPPlusTest/EPPlusTest.csproj
+++ b/EPPlusTest/EPPlusTest.csproj
@@ -1,4 +1,4 @@
-<?xml version="1.0" encoding="utf-8"?>
+<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
@@ -10,7 +10,7 @@
<AppDesignerFolder>Properties</AppDesignerFolder>
<RootNamespace>EPPlusTest</RootNamespace>
<AssemblyName>EPPlusTest</AssemblyName>
- <TargetFrameworkVersion>v4.5</TargetFrameworkVersion>
+ <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
<FileAlignment>512</FileAlignment>
<ProjectTypeGuids>{3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
<FileUpgradeFlags>
diff --git a/NetCoreTests/NetCoreTests.csproj b/NetCoreTests/NetCoreTests.csproj
new file mode 100644
index 0000000..a8daf6d
--- /dev/null
+++ b/NetCoreTests/NetCoreTests.csproj
@@ -0,0 +1,18 @@
+<Project Sdk="Microsoft.NET.Sdk">
+ <PropertyGroup>
+ <TargetFramework>net6.0</TargetFramework>
+ <Nullable>enable</Nullable>
+ <LangVersion>10.0</LangVersion>
+ </PropertyGroup>
+ <ItemGroup>
+ <PackageReference Include="FluentAssertions" Version="6.6.0" />
+ <PackageReference Include="MSTest.TestAdapter" Version="2.2.8"/>
+ <PackageReference Include="MSTest.TestFramework" Version="2.2.8" />
+ <PackageReference Include="Microsoft.NET.Test.SDK" Version="17.1.0"/>
+ <PackageReference Include="System.Text.Encoding.CodePages" Version="4.3.0" />
+
+ <ProjectReference Include="..\EPPlus\EPPlusSDK.csproj" />
+
+ <None Include="TestWorkbooks/**" CopyToOutputDirectory="PreserveNewest"/>
+ </ItemGroup>
+</Project>
diff --git a/NetCoreTests/TestWorkbooks/Times.xlsx b/NetCoreTests/TestWorkbooks/Times.xlsx
new file mode 100644
index 0000000..fc4a71c
--- /dev/null
+++ b/NetCoreTests/TestWorkbooks/Times.xlsx
Binary files differ
diff --git a/NetCoreTests/TimeTest.cs b/NetCoreTests/TimeTest.cs
new file mode 100644
index 0000000..39cc038
--- /dev/null
+++ b/NetCoreTests/TimeTest.cs
@@ -0,0 +1,65 @@
+using System;
+using System.IO;
+using System.Linq;
+using System.Text;
+using FluentAssertions;
+using Microsoft.VisualStudio.TestTools.UnitTesting;
+using OfficeOpenXml;
+
+namespace NetCoreTests;
+
+[TestClass]
+public class TimeTest
+{
+ static TimeTest()
+ {
+ Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
+ }
+
+ [TestMethod]
+ public void TestIncorrectDurationFromOADate()
+ {
+ ExcelWorksheet.IncorrectDurationFromOADate(2.75).Should().Be(new(1900, 1, 1, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(1.75).Should().Be(new(1899, 12, 31, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.75).Should().Be(new(1899, 12, 30, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.5).Should().Be(new(1899, 12, 30, 12, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.25).Should().Be(new(1899, 12, 30, 6, 0, 0));
+
+ ExcelWorksheet.IncorrectDurationFromOADate(0).Should().Be(new(1899, 12, 30, 0, 0, 0));
+
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.25).Should().Be(new(1899, 12, 29, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.5).Should().Be(new(1899, 12, 29, 12, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.75).Should().Be(new(1899, 12, 29, 6, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-1.75).Should().Be(new(1899, 12, 28, 6, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-2.75).Should().Be(new(1899, 12, 27, 6, 0, 0));
+ }
+
+ [TestMethod]
+ public void TestDateParse()
+ {
+ using var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("Times.xlsx")));
+ var sheet = package.Workbook.Worksheets.First();
+
+ sheet.Cells["B3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+ sheet.Cells["C3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 0));
+ sheet.Cells["D3"].Value.Should().Be(0.13541666666666666);
+ sheet.Cells["E3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+ sheet.Cells["F3"].Value.Should().Be(0.13576388888888888);
+ sheet.Cells["G3"].Value.Should().Be(0.13541666666666666);
+ sheet.Cells["H3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+
+ sheet.Cells["B7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ sheet.Cells["C7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 0));
+ sheet.Cells["D7"].Value.Should().Be(0.9993055555555556);
+ sheet.Cells["E7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ sheet.Cells["F7"].Value.Should().Be(0.999988425925926);
+ sheet.Cells["G7"].Value.Should().Be(0.9993055555555556);
+ sheet.Cells["H7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ }
+
+ private static string GetTestWorkbookPath(string filename)
+ {
+ var assemblyPath = Path.GetDirectoryName(typeof(TimeTest).Assembly.Location)!;
+ return Path.Combine(assemblyPath, "TestWorkbooks", filename);
+ }
+}
diff --git a/NetFrameworkTests/NetFrameworkTests.csproj b/NetFrameworkTests/NetFrameworkTests.csproj
new file mode 100644
index 0000000..242fcf5
--- /dev/null
+++ b/NetFrameworkTests/NetFrameworkTests.csproj
@@ -0,0 +1,18 @@
+<Project Sdk="Microsoft.NET.Sdk">
+ <PropertyGroup>
+ <TargetFramework>net472</TargetFramework>
+ <Nullable>enable</Nullable>
+ <LangVersion>10.0</LangVersion>
+ </PropertyGroup>
+ <ItemGroup>
+ <PackageReference Include="FluentAssertions" Version="6.6.0" />
+ <PackageReference Include="MSTest.TestAdapter" Version="2.2.8"/>
+ <PackageReference Include="MSTest.TestFramework" Version="2.2.8" />
+ <PackageReference Include="Microsoft.NET.Test.SDK" Version="17.1.0"/>
+ <PackageReference Include="System.Text.Encoding.CodePages" Version="4.3.0" />
+
+ <ProjectReference Include="..\EPPlus\EPPlusSDK.csproj" />
+
+ <None Include="TestWorkbooks/**" CopyToOutputDirectory="PreserveNewest"/>
+ </ItemGroup>
+</Project>
diff --git a/NetFrameworkTests/TestWorkbooks/Times.xlsx b/NetFrameworkTests/TestWorkbooks/Times.xlsx
new file mode 100644
index 0000000..fc4a71c
--- /dev/null
+++ b/NetFrameworkTests/TestWorkbooks/Times.xlsx
Binary files differ
diff --git a/NetFrameworkTests/TimeTest.cs b/NetFrameworkTests/TimeTest.cs
new file mode 100644
index 0000000..1ddddbe
--- /dev/null
+++ b/NetFrameworkTests/TimeTest.cs
@@ -0,0 +1,65 @@
+using System;
+using System.IO;
+using System.Linq;
+using System.Text;
+using FluentAssertions;
+using Microsoft.VisualStudio.TestTools.UnitTesting;
+using OfficeOpenXml;
+
+namespace NetFrameworkTests;
+
+[TestClass]
+public class TimeTest
+{
+ static TimeTest()
+ {
+ Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
+ }
+
+ [TestMethod]
+ public void TestIncorrectDurationFromOADate()
+ {
+ ExcelWorksheet.IncorrectDurationFromOADate(2.75).Should().Be(new(1900, 1, 1, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(1.75).Should().Be(new(1899, 12, 31, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.75).Should().Be(new(1899, 12, 30, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.5).Should().Be(new(1899, 12, 30, 12, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(0.25).Should().Be(new(1899, 12, 30, 6, 0, 0));
+
+ ExcelWorksheet.IncorrectDurationFromOADate(0).Should().Be(new(1899, 12, 30, 0, 0, 0));
+
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.25).Should().Be(new(1899, 12, 29, 18, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.5).Should().Be(new(1899, 12, 29, 12, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-0.75).Should().Be(new(1899, 12, 29, 6, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-1.75).Should().Be(new(1899, 12, 28, 6, 0, 0));
+ ExcelWorksheet.IncorrectDurationFromOADate(-2.75).Should().Be(new(1899, 12, 27, 6, 0, 0));
+ }
+
+ [TestMethod]
+ public void TestDateParse()
+ {
+ using var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("Times.xlsx")));
+ var sheet = package.Workbook.Worksheets.First();
+
+ sheet.Cells["B3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+ sheet.Cells["C3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 0));
+ sheet.Cells["D3"].Value.Should().Be(0.13541666666666666);
+ sheet.Cells["E3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+ sheet.Cells["F3"].Value.Should().Be(0.13576388888888888);
+ sheet.Cells["G3"].Value.Should().Be(0.13541666666666666);
+ sheet.Cells["H3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
+
+ sheet.Cells["B7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ sheet.Cells["C7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 0));
+ sheet.Cells["D7"].Value.Should().Be(0.9993055555555556);
+ sheet.Cells["E7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ sheet.Cells["F7"].Value.Should().Be(0.999988425925926);
+ sheet.Cells["G7"].Value.Should().Be(0.9993055555555556);
+ sheet.Cells["H7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
+ }
+
+ private static string GetTestWorkbookPath(string filename)
+ {
+ var assemblyPath = Path.GetDirectoryName(typeof(TimeTest).Assembly.Location)!;
+ return Path.Combine(assemblyPath, "TestWorkbooks", filename);
+ }
+}
