[NWD] Expose some EPPlus methods to improve performance.
These methods allow us to avoid using the .Cells collection in hot
codepaths, which is very useful because it can't be cached
(ExcelRange is mutable and "reading" actually mutates it). In addition,
the indexer on ExcelRange generates and parses strings, making it very
inefficient.
For the common cases, we can bypass all of this and get values by row
and column.
Also, remove obsolete .NET Framework code and target .NET 8.
Change-Id: I9d3938dd17c03bb36dcf836c1d46dbda47d5ddb2
Reviewed-on: https://gnocchi-internal-review.git.corp.google.com/c/third_party/epplus/+/206801
Reviewed-by: Greg Denton <gregdenton@google.com>
diff --git a/EPPlus.sln b/EPPlus.sln
index f41280f..a545934 100644
--- a/EPPlus.sln
+++ b/EPPlus.sln
@@ -13,8 +13,6 @@
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
@@ -64,24 +62,6 @@
{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 09bc049..53ab074 100644
--- a/EPPlus/EPPlusSDK.csproj
+++ b/EPPlus/EPPlusSDK.csproj
@@ -1,18 +1,13 @@
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
- <TargetFramework>netstandard2.0</TargetFramework>
+ <TargetFramework>net8.0</TargetFramework>
<RootNamespace>OfficeOpenXml</RootNamespace>
<AssemblyName>EPPlus</AssemblyName>
- <SignAssembly>true</SignAssembly>
- <AssemblyOriginatorKeyFile>OpenOfficeXml.snk</AssemblyOriginatorKeyFile>
<PackageId>Appsheet.EPPlus</PackageId>
- <Version>1.0.2</Version>
+ <Version>1.0.3</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>
- <ItemGroup Condition="'$(TargetFramework)' == 'net472'">
- <Reference Include="System.Web" />
- </ItemGroup>
<ItemGroup>
<PackageReference Include="IronSoftware.System.Drawing" Version="2024.3.4" />
<PackageReference Include="Microsoft.CSharp" Version="4.7.0" />
diff --git a/EPPlus/ExcelWorksheet.cs b/EPPlus/ExcelWorksheet.cs
index 97c5e58..67994dd 100644
--- a/EPPlus/ExcelWorksheet.cs
+++ b/EPPlus/ExcelWorksheet.cs
@@ -4233,7 +4233,7 @@
}
}
}
- internal string GetFormula(int row, int col)
+ public string GetFormula(int row, int col)
{
var v = _formulas.GetValue(row, col);
if (v is int)
@@ -4249,7 +4249,7 @@
return "";
}
}
- internal string GetFormulaR1C1(int row, int col)
+ public string GetFormulaR1C1(int row, int col)
{
var v = _formulas.GetValue(row, col);
if (v is int)
@@ -4267,7 +4267,7 @@
}
}
- internal string GetFormulaR1C1_V1(int row, int col)
+ public string GetFormulaR1C1_V1(int row, int col)
{
var v = _formulas.GetValue(row, col);
if (v is int)
@@ -4285,7 +4285,9 @@
}
}
- internal string GetArrayFormulaAddress(int row, int col)
+ public bool IsArrayFormula(int row, int col) => _flags.GetFlagValue(row, col, CellFlags.ArrayFormula);
+
+ public string GetArrayFormulaAddress(int row, int col)
{
var v = _formulas.GetValue(row, col);
if ((v is int) && (_sharedFormulas[(int)v].IsArray))
@@ -4297,6 +4299,14 @@
return "";
}
}
+
+ public int GetStyleId(int row, int col) {
+ int styleId = 0;
+ if (!_styles.Exists(row, col, ref styleId) && !_styles.Exists(row, 0, ref styleId)) {
+ styleId = _styles.GetValue(0, col);
+ }
+ return styleId;
+ }
private void DisposeInternal(IDisposable candidateDisposable)
{
diff --git a/NetCoreTests/NetCoreTests.csproj b/NetCoreTests/NetCoreTests.csproj
index ce61958..3f11e9f 100644
--- a/NetCoreTests/NetCoreTests.csproj
+++ b/NetCoreTests/NetCoreTests.csproj
@@ -1,8 +1,7 @@
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
- <TargetFramework>net6.0</TargetFramework>
+ <TargetFramework>net8.0</TargetFramework>
<Nullable>enable</Nullable>
- <LangVersion>10.0</LangVersion>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="FluentAssertions" Version="6.6.0" />
diff --git a/NetFrameworkTests/NetFrameworkTests.csproj b/NetFrameworkTests/NetFrameworkTests.csproj
deleted file mode 100644
index 0750c60..0000000
--- a/NetFrameworkTests/NetFrameworkTests.csproj
+++ /dev/null
@@ -1,18 +0,0 @@
-<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="5.0.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
deleted file mode 100644
index fc4a71c..0000000
--- a/NetFrameworkTests/TestWorkbooks/Times.xlsx
+++ /dev/null
Binary files differ
diff --git a/NetFrameworkTests/TimeTest.cs b/NetFrameworkTests/TimeTest.cs
deleted file mode 100644
index 1ddddbe..0000000
--- a/NetFrameworkTests/TimeTest.cs
+++ /dev/null
@@ -1,65 +0,0 @@
-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);
- }
-}
