[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); - } -}