diff --git a/.gitattributes b/.gitattributes
new file mode 100644
index 0000000..68cc179
--- /dev/null
+++ b/.gitattributes
@@ -0,0 +1,20 @@
+# Source https://help.github.com/articles/dealing-with-line-endings
+# Set default behaviour, in case users don't have core.autocrlf set.
+* text=auto
+
+# Explicitly declare text files we want to always be normalized and converted
+# to native line endings on checkout.
+*.cs text diff=csharp eol=crlf
+
+# Declare files that will always have CRLF line endings on checkout.
+*.sln text eol=crlf
+
+# Denote all files that are truly binary and should not be modified.
+*.png binary
+*.jpg binary
+*.dll binary
+*.gif binary
+*.ico binary
+*.xlsx binary
+
+core.autocrlf=true
diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE.md
index cfa55f4..e5313db 100644
--- a/.github/ISSUE_TEMPLATE.md
+++ b/.github/ISSUE_TEMPLATE.md
@@ -12,3 +12,17 @@
**Did this work in previous versions of our tool? Which versions?**
- [ ] I attached a sample spreadsheet. (You can drag files on to this issue)
+
+**Code to reproduce problem:**
+```c#
+public void Main()
+{
+ // Where possible, post full code to reproduce your issue that adheres to:
+ // - Fully runnable. I should be able to copy and paste this code into a
+ // console application and run it without having to edit it much.
+ // - Declare all your variables (this follows from the previous point)
+ // - The code should be a minimal code sample to illustrate issue. The code
+ // samples on the wiki are good examples of the terseness that I want. Don't
+ // post your full application.
+}
+```
diff --git a/.github/PULL_REQUEST_TEMPLATE.md b/.github/PULL_REQUEST_TEMPLATE.md
index 891ee85..6856874 100644
--- a/.github/PULL_REQUEST_TEMPLATE.md
+++ b/.github/PULL_REQUEST_TEMPLATE.md
@@ -1,13 +1,14 @@
-Fixes # .
-Changes proposed in this pull request:
- -
- -
- -
-
-How did I test this code:
- -
- -
- -
-
-- [ ] I attached a sample spreadsheet. (You can drag files on to this pull request)
+#### What's this PR do?
+#### Where should the reviewer start?
+#### How should this be manually tested?
+#### Any background context you want to provide?
+
+#### Screenshots (if appropriate)
+#### Questions:
+- Is there a blog post?
+- Does the knowledge base need an update?
+- Does this add new (C#) dependencies?
+
+- [ ] C# Code Review: @csreviewer
+- [ ] Test Automation Review: @csreviewer
\ No newline at end of file
diff --git a/.vs/config/applicationhost.config b/.vs/config/applicationhost.config
new file mode 100644
index 0000000..113af3d
--- /dev/null
+++ b/.vs/config/applicationhost.config
@@ -0,0 +1,1047 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/CONTRIBUTING.md b/CONTRIBUTING.md
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/CONTRIBUTING.md
diff --git a/ClosedXML.sln b/ClosedXML.sln
index 4104ff4..b58a074 100644
--- a/ClosedXML.sln
+++ b/ClosedXML.sln
@@ -1,60 +1,55 @@
-
-Microsoft Visual Studio Solution File, Format Version 12.00
-# Visual Studio 2013
-VisualStudioVersion = 12.0.31101.0
-MinimumVisualStudioVersion = 10.0.40219.1
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Sandbox", "ClosedXML_Sandbox\ClosedXML_Sandbox.csproj", "{38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}"
-EndProject
-Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{5C94E22C-85AA-48FD-B082-CF929FFC6C31}"
- ProjectSection(SolutionItems) = preProject
- ClosedXML.vsmdi = ClosedXML.vsmdi
- EndProjectSection
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Examples", "ClosedXML_Examples\ClosedXML_Examples.csproj", "{03A518D0-1CB7-488E-861C-C4E782B27A46}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Net3.5", "ClosedXML_Net3.5\ClosedXML_Net3.5.csproj", "{5F43B12B-A900-40C6-9924-A0C0B032F791}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML", "ClosedXML\ClosedXML.csproj", "{BD5E6BFE-E837-4A35-BCA9-39667D873A20}"
-EndProject
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Tests", "ClosedXML_Tests\ClosedXML_Tests.csproj", "{09B066ED-E4A7-4545-A1A4-FF03DD524BDF}"
-EndProject
-Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = ".github", ".github", "{073CFB1C-43DC-4ADC-9D12-BB8D7B10C099}"
- ProjectSection(SolutionItems) = preProject
- .github\ISSUE_TEMPLATE.md = .github\ISSUE_TEMPLATE.md
- .github\PULL_REQUEST_TEMPLATE.md = .github\PULL_REQUEST_TEMPLATE.md
- EndProjectSection
-EndProject
-Global
- GlobalSection(SolutionConfigurationPlatforms) = preSolution
- Debug|Any CPU = Debug|Any CPU
- Release|Any CPU = Release|Any CPU
- EndGlobalSection
- GlobalSection(ProjectConfigurationPlatforms) = postSolution
- {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.Build.0 = Release|Any CPU
- {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.Build.0 = Release|Any CPU
- {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.Build.0 = Release|Any CPU
- {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.Build.0 = Release|Any CPU
- {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Release|Any CPU.Build.0 = Release|Any CPU
- EndGlobalSection
- GlobalSection(SolutionProperties) = preSolution
- HideSolutionNode = FALSE
- EndGlobalSection
- GlobalSection(TestCaseManagementSettings) = postSolution
- CategoryFile = ClosedXML.vsmdi
- EndGlobalSection
-EndGlobal
+
+Microsoft Visual Studio Solution File, Format Version 12.00
+# Visual Studio 14
+VisualStudioVersion = 14.0.25420.1
+MinimumVisualStudioVersion = 10.0.40219.1
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Sandbox", "ClosedXML_Sandbox\ClosedXML_Sandbox.csproj", "{38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}"
+EndProject
+Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{5C94E22C-85AA-48FD-B082-CF929FFC6C31}"
+ ProjectSection(SolutionItems) = preProject
+ appveyor.yml = appveyor.yml
+ ClosedXML.vsmdi = ClosedXML.vsmdi
+ EndProjectSection
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Examples", "ClosedXML_Examples\ClosedXML_Examples.csproj", "{03A518D0-1CB7-488E-861C-C4E782B27A46}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML", "ClosedXML\ClosedXML.csproj", "{BD5E6BFE-E837-4A35-BCA9-39667D873A20}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Tests", "ClosedXML_Tests\ClosedXML_Tests.csproj", "{09B066ED-E4A7-4545-A1A4-FF03DD524BDF}"
+EndProject
+Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = ".github", ".github", "{073CFB1C-43DC-4ADC-9D12-BB8D7B10C099}"
+ ProjectSection(SolutionItems) = preProject
+ .github\ISSUE_TEMPLATE.md = .github\ISSUE_TEMPLATE.md
+ .github\PULL_REQUEST_TEMPLATE.md = .github\PULL_REQUEST_TEMPLATE.md
+ EndProjectSection
+EndProject
+Global
+ GlobalSection(SolutionConfigurationPlatforms) = preSolution
+ Debug|Any CPU = Debug|Any CPU
+ Release|Any CPU = Release|Any CPU
+ EndGlobalSection
+ GlobalSection(ProjectConfigurationPlatforms) = postSolution
+ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.Build.0 = Release|Any CPU
+ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.Build.0 = Release|Any CPU
+ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.Build.0 = Release|Any CPU
+ {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {09B066ED-E4A7-4545-A1A4-FF03DD524BDF}.Release|Any CPU.Build.0 = Release|Any CPU
+ EndGlobalSection
+ GlobalSection(SolutionProperties) = preSolution
+ HideSolutionNode = FALSE
+ EndGlobalSection
+ GlobalSection(TestCaseManagementSettings) = postSolution
+ CategoryFile = ClosedXML.vsmdi
+ EndGlobalSection
+EndGlobal
diff --git a/ClosedXML/AttributeExtensions.cs b/ClosedXML/AttributeExtensions.cs
new file mode 100644
index 0000000..be35455
--- /dev/null
+++ b/ClosedXML/AttributeExtensions.cs
@@ -0,0 +1,36 @@
+using System;
+using System.Linq;
+using System.Linq.Expressions;
+using System.Reflection;
+
+namespace ClosedXML
+{
+ public static class AttributeExtensions
+ {
+ public static TAttribute[] GetAttributes(
+ this MemberInfo member)
+ where TAttribute : Attribute
+ {
+ var attributes = member.GetCustomAttributes(typeof(TAttribute), true);
+
+ return (TAttribute[])attributes;
+ }
+
+ public static MethodInfo GetMethod(this T instance, Expression> methodSelector)
+ {
+ return ((MethodCallExpression)methodSelector.Body).Method;
+ }
+
+ public static MethodInfo GetMethod(this T instance, Expression> methodSelector)
+ {
+ return ((MethodCallExpression)methodSelector.Body).Method;
+ }
+
+ public static bool HasAttribute(
+ this MemberInfo member)
+ where TAttribute : Attribute
+ {
+ return GetAttributes(member).Any();
+ }
+ }
+}
diff --git a/ClosedXML/Attributes/ColumnOrderAttribute.cs b/ClosedXML/Attributes/ColumnOrderAttribute.cs
deleted file mode 100644
index b85c541..0000000
--- a/ClosedXML/Attributes/ColumnOrderAttribute.cs
+++ /dev/null
@@ -1,15 +0,0 @@
-using System;
-
-namespace ClosedXML.Attributes
-{
- [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false, Inherited = false)]
- public class ColumnOrderAttribute : Attribute
- {
- public ColumnOrderAttribute(long order)
- {
- this.Order = order;
- }
-
- public long Order { get; private set; }
- }
-}
diff --git a/ClosedXML/Attributes/XLColumnAttribute.cs b/ClosedXML/Attributes/XLColumnAttribute.cs
new file mode 100644
index 0000000..41b541b
--- /dev/null
+++ b/ClosedXML/Attributes/XLColumnAttribute.cs
@@ -0,0 +1,43 @@
+using System;
+using System.Linq;
+using System.Reflection;
+using ClosedXML;
+using ClosedXML.Excel;
+
+namespace ClosedXML.Attributes
+{
+ [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false, Inherited = false)]
+ public class XLColumnAttribute : Attribute
+ {
+ public String Header { get; set; }
+ public Boolean Ignore { get; set; }
+ public Int32 Order { get; set; }
+
+ private static XLColumnAttribute GetXLColumnAttribute(MemberInfo mi)
+ {
+ if (!mi.HasAttribute()) return null;
+ return mi.GetAttributes().First();
+ }
+
+ internal static String GetHeader(MemberInfo mi)
+ {
+ var attribute = GetXLColumnAttribute(mi);
+ if (attribute == null) return null;
+ return XLHelper.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header;
+ }
+
+ internal static Int32 GetOrder(MemberInfo mi)
+ {
+ var attribute = GetXLColumnAttribute(mi);
+ if (attribute == null) return Int32.MaxValue;
+ return attribute.Order;
+ }
+
+ internal static Boolean IgnoreMember(MemberInfo mi)
+ {
+ var attribute = GetXLColumnAttribute(mi);
+ if (attribute == null) return false;
+ return attribute.Ignore;
+ }
+ }
+}
diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj
index 5960577..7de9988 100644
--- a/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML.csproj
@@ -10,9 +10,8 @@
Properties
ClosedXML
ClosedXML
- v4.0
+ v4.5.2
512
- Client
..\
true
@@ -26,6 +25,7 @@
prompt
4
1591
+ false
pdbonly
@@ -37,6 +37,7 @@
4
bin\Release\ClosedXML.xml
1591
+ false
true
@@ -45,13 +46,16 @@
ClosedXML.snk
-
+
+ ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll
True
- ..\packages\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll
+
+
+ ..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll
+ True
-
@@ -61,16 +65,26 @@
-
+
+
+
+
+
+
+
+
+
+
+
@@ -328,9 +342,10 @@
.editorconfig
-
+
+ Designer
+
-
+
diff --git a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs
index 8859848..e1a87a7 100644
--- a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs
@@ -1,16 +1,16 @@
-using System;
-namespace ClosedXML.Excel
-{
- using System.Collections.Generic;
-
- public interface IXLAutoFilter: IDisposable
- {
- IXLFilterColumn Column(String column);
- IXLFilterColumn Column(Int32 column);
-
- IXLAutoFilter Sort(Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true);
- Boolean Sorted { get; set; }
- XLSortOrder SortOrder { get; set; }
- Int32 SortColumn { get; set; }
- }
+using System;
+namespace ClosedXML.Excel
+{
+ using System.Collections.Generic;
+
+ public interface IXLAutoFilter: IDisposable
+ {
+ IXLFilterColumn Column(String column);
+ IXLFilterColumn Column(Int32 column);
+
+ IXLAutoFilter Sort(Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true);
+ Boolean Sorted { get; set; }
+ XLSortOrder SortOrder { get; set; }
+ Int32 SortColumn { get; set; }
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs
index b3f8d66..88ca907 100644
--- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs
@@ -1,25 +1,25 @@
-using System;
-namespace ClosedXML.Excel
-{
- using System.Collections.Generic;
- public enum XLFilterType { Regular, Custom, TopBottom, Dynamic }
- public enum XLFilterDynamicType { AboveAverage, BelowAverage }
- public enum XLTopBottomPart { Top, Bottom}
- public interface IXLBaseAutoFilter
- {
- Boolean Enabled { get; set; }
- IXLRange Range { get; set; }
- IXLBaseAutoFilter Set(IXLRangeBase range);
- IXLBaseAutoFilter Clear();
-
- IXLFilterColumn Column(String column);
- IXLFilterColumn Column(Int32 column);
-
- IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true);
- Boolean Sorted { get; set; }
- XLSortOrder SortOrder { get; set; }
- Int32 SortColumn { get; set; }
-
-
- }
+using System;
+namespace ClosedXML.Excel
+{
+ using System.Collections.Generic;
+ public enum XLFilterType { Regular, Custom, TopBottom, Dynamic }
+ public enum XLFilterDynamicType { AboveAverage, BelowAverage }
+ public enum XLTopBottomPart { Top, Bottom}
+ public interface IXLBaseAutoFilter
+ {
+ Boolean Enabled { get; set; }
+ IXLRange Range { get; set; }
+ IXLBaseAutoFilter Set(IXLRangeBase range);
+ IXLBaseAutoFilter Clear();
+
+ IXLFilterColumn Column(String column);
+ IXLFilterColumn Column(Int32 column);
+
+ IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true);
+ Boolean Sorted { get; set; }
+ XLSortOrder SortOrder { get; set; }
+ Int32 SortColumn { get; set; }
+
+
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs
index 85cec03..37c6a17 100644
--- a/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs
@@ -1,19 +1,19 @@
-using System;
-namespace ClosedXML.Excel
-{
- public interface IXLCustomFilteredColumn
- {
- void EqualTo(T value) where T : IComparable;
- void NotEqualTo(T value) where T : IComparable;
- void GreaterThan(T value) where T : IComparable;
- void LessThan(T value) where T : IComparable;
- void EqualOrGreaterThan(T value) where T : IComparable;
- void EqualOrLessThan(T value) where T : IComparable;
- void BeginsWith(String value);
- void NotBeginsWith(String value);
- void EndsWith(String value);
- void NotEndsWith(String value);
- void Contains(String value);
- void NotContains(String value);
- }
+using System;
+namespace ClosedXML.Excel
+{
+ public interface IXLCustomFilteredColumn
+ {
+ void EqualTo(T value) where T : IComparable;
+ void NotEqualTo(T value) where T : IComparable;
+ void GreaterThan(T value) where T : IComparable;
+ void LessThan(T value) where T : IComparable;
+ void EqualOrGreaterThan(T value) where T : IComparable;
+ void EqualOrLessThan(T value) where T : IComparable;
+ void BeginsWith(String value);
+ void NotBeginsWith(String value);
+ void EndsWith(String value);
+ void NotEndsWith(String value);
+ void Contains(String value);
+ void NotContains(String value);
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs
index 159faf9..41376c8 100644
--- a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs
@@ -1,46 +1,46 @@
-using System;
-namespace ClosedXML.Excel
-{
- public enum XLTopBottomType { Items, Percent }
- public interface IXLFilterColumn
- {
- void Clear();
-
- IXLFilteredColumn AddFilter(T value) where T : IComparable;
-
- void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items);
- void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items);
- void AboveAverage();
- void BelowAverage();
-
- IXLFilterConnector EqualTo(T value) where T : IComparable;
- IXLFilterConnector NotEqualTo(T value) where T : IComparable;
- IXLFilterConnector GreaterThan(T value) where T : IComparable;
- IXLFilterConnector LessThan(T value) where T : IComparable;
- IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable;
- IXLFilterConnector EqualOrLessThan(T value) where T : IComparable;
- void Between(T minValue, T maxValue) where T : IComparable;
- void NotBetween(T minValue, T maxValue) where T : IComparable;
- IXLFilterConnector BeginsWith(String value);
- IXLFilterConnector NotBeginsWith(String value);
- IXLFilterConnector EndsWith(String value);
- IXLFilterConnector NotEndsWith(String value);
- IXLFilterConnector Contains(String value);
- IXLFilterConnector NotContains(String value);
-
- XLFilterType FilterType { get; set; }
- Int32 TopBottomValue { get; set; }
- XLTopBottomType TopBottomType { get; set; }
- XLTopBottomPart TopBottomPart { get; set; }
- XLFilterDynamicType DynamicType { get; set; }
- Double DynamicValue { get; set; }
-
- IXLFilterColumn SetFilterType(XLFilterType value);
- IXLFilterColumn SetTopBottomValue(Int32 value);
- IXLFilterColumn SetTopBottomType(XLTopBottomType value);
- IXLFilterColumn SetTopBottomPart(XLTopBottomPart value);
- IXLFilterColumn SetDynamicType(XLFilterDynamicType value);
- IXLFilterColumn SetDynamicValue(Double value);
-
- }
+using System;
+namespace ClosedXML.Excel
+{
+ public enum XLTopBottomType { Items, Percent }
+ public interface IXLFilterColumn
+ {
+ void Clear();
+
+ IXLFilteredColumn AddFilter(T value) where T : IComparable;
+
+ void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items);
+ void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items);
+ void AboveAverage();
+ void BelowAverage();
+
+ IXLFilterConnector EqualTo(T value) where T : IComparable;
+ IXLFilterConnector NotEqualTo(T value) where T : IComparable;
+ IXLFilterConnector GreaterThan(T value) where T : IComparable;
+ IXLFilterConnector LessThan(T value) where T : IComparable;
+ IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable;
+ IXLFilterConnector EqualOrLessThan(T value) where T : IComparable;
+ void Between(T minValue, T maxValue) where T : IComparable;
+ void NotBetween(T minValue, T maxValue) where T : IComparable;
+ IXLFilterConnector BeginsWith(String value);
+ IXLFilterConnector NotBeginsWith(String value);
+ IXLFilterConnector EndsWith(String value);
+ IXLFilterConnector NotEndsWith(String value);
+ IXLFilterConnector Contains(String value);
+ IXLFilterConnector NotContains(String value);
+
+ XLFilterType FilterType { get; set; }
+ Int32 TopBottomValue { get; set; }
+ XLTopBottomType TopBottomType { get; set; }
+ XLTopBottomPart TopBottomPart { get; set; }
+ XLFilterDynamicType DynamicType { get; set; }
+ Double DynamicValue { get; set; }
+
+ IXLFilterColumn SetFilterType(XLFilterType value);
+ IXLFilterColumn SetTopBottomValue(Int32 value);
+ IXLFilterColumn SetTopBottomType(XLTopBottomType value);
+ IXLFilterColumn SetTopBottomPart(XLTopBottomPart value);
+ IXLFilterColumn SetDynamicType(XLFilterDynamicType value);
+ IXLFilterColumn SetDynamicValue(Double value);
+
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs b/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs
index 7955869..d5d52be 100644
--- a/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs
@@ -1,10 +1,10 @@
-using System;
-
-namespace ClosedXML.Excel
-{
- public interface IXLFilterConnector
- {
- IXLCustomFilteredColumn And { get; }
- IXLCustomFilteredColumn Or { get; }
- }
-}
+using System;
+
+namespace ClosedXML.Excel
+{
+ public interface IXLFilterConnector
+ {
+ IXLCustomFilteredColumn And { get; }
+ IXLCustomFilteredColumn Or { get; }
+ }
+}
diff --git a/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs
index 6491f55..86db9db 100644
--- a/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs
@@ -1,8 +1,8 @@
-using System;
-namespace ClosedXML.Excel
-{
- public interface IXLFilteredColumn
- {
- IXLFilteredColumn AddFilter(T value) where T : IComparable;
- }
+using System;
+namespace ClosedXML.Excel
+{
+ public interface IXLFilteredColumn
+ {
+ IXLFilteredColumn AddFilter(T value) where T : IComparable;
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs
index 28f4209..dcf4b76 100644
--- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs
+++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs
@@ -1,165 +1,165 @@
-using System;
-using System.Linq;
-
-namespace ClosedXML.Excel
-{
- using System.Collections.Generic;
-
- internal class XLAutoFilter : IXLBaseAutoFilter, IXLAutoFilter
- {
- private readonly Dictionary _columns = new Dictionary();
-
- public XLAutoFilter()
- {
- Filters = new Dictionary>();
- }
-
- public Dictionary> Filters { get; private set; }
-
- #region IXLAutoFilter Members
-
- IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase,
- Boolean ignoreBlanks)
- {
- return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks);
- }
-
- public void Dispose()
- {
- if (Range != null)
- Range.Dispose();
- }
-
- #endregion
-
- #region IXLBaseAutoFilter Members
-
- public Boolean Enabled { get; set; }
- public IXLRange Range { get; set; }
-
- IXLBaseAutoFilter IXLBaseAutoFilter.Clear()
- {
- return Clear();
- }
-
- IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range)
- {
- return Set(range);
- }
-
- IXLBaseAutoFilter IXLBaseAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase,
- Boolean ignoreBlanks)
- {
- return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks);
- }
-
- public Boolean Sorted { get; set; }
- public XLSortOrder SortOrder { get; set; }
- public Int32 SortColumn { get; set; }
-
- public IXLFilterColumn Column(String column)
- {
- return Column(XLHelper.GetColumnNumberFromLetter(column));
- }
-
- public IXLFilterColumn Column(Int32 column)
- {
- XLFilterColumn filterColumn;
- if (!_columns.TryGetValue(column, out filterColumn))
- {
- filterColumn = new XLFilterColumn(this, column);
- _columns.Add(column, filterColumn);
- }
-
- return filterColumn;
- }
-
- #endregion
-
- public XLAutoFilter Set(IXLRangeBase range)
- {
- Range = range.AsRange();
- Enabled = true;
- return this;
- }
-
- public XLAutoFilter Clear()
- {
- if (!Enabled) return this;
-
- Enabled = false;
- Filters.Clear();
- foreach (IXLRangeRow row in Range.Rows().Where(r => r.RowNumber() > 1))
- row.WorksheetRow().Unhide();
- return this;
- }
-
- public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks)
- {
- if (!Enabled)
- throw new ApplicationException("Filter has not been enabled.");
-
- var ws = Range.Worksheet as XLWorksheet;
- ws.SuspendEvents();
- Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase,
- ignoreBlanks);
-
- Sorted = true;
- SortOrder = sortOrder;
- SortColumn = columnToSortBy;
-
- if (Enabled)
- {
- using (var rows = Range.Rows(2, Range.RowCount()))
- {
- foreach (IXLRangeRow row in rows)
- row.WorksheetRow().Unhide();
- }
-
- foreach (KeyValuePair> kp in Filters)
- {
- Boolean firstFilter = true;
- foreach (XLFilter filter in kp.Value)
- {
- Boolean isText = filter.Value is String;
- using (var rows = Range.Rows(2, Range.RowCount()))
- {
- foreach (IXLRangeRow row in rows)
- {
- Boolean match = isText
- ? filter.Condition(row.Cell(kp.Key).GetString())
- : row.Cell(kp.Key).DataType == XLCellValues.Number &&
- filter.Condition(row.Cell(kp.Key).GetDouble());
- if (firstFilter)
- {
- if (match)
- row.WorksheetRow().Unhide();
- else
- row.WorksheetRow().Hide();
- }
- else
- {
- if (filter.Connector == XLConnector.And)
- {
- if (!row.WorksheetRow().IsHidden)
- {
- if (match)
- row.WorksheetRow().Unhide();
- else
- row.WorksheetRow().Hide();
- }
- }
- else if (match)
- row.WorksheetRow().Unhide();
- }
- }
- firstFilter = false;
- }
- }
- }
- }
- ws.ResumeEvents();
- return this;
- }
- }
+using System;
+using System.Linq;
+
+namespace ClosedXML.Excel
+{
+ using System.Collections.Generic;
+
+ internal class XLAutoFilter : IXLBaseAutoFilter, IXLAutoFilter
+ {
+ private readonly Dictionary _columns = new Dictionary();
+
+ public XLAutoFilter()
+ {
+ Filters = new Dictionary>();
+ }
+
+ public Dictionary> Filters { get; private set; }
+
+ #region IXLAutoFilter Members
+
+ IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase,
+ Boolean ignoreBlanks)
+ {
+ return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks);
+ }
+
+ public void Dispose()
+ {
+ if (Range != null)
+ Range.Dispose();
+ }
+
+ #endregion
+
+ #region IXLBaseAutoFilter Members
+
+ public Boolean Enabled { get; set; }
+ public IXLRange Range { get; set; }
+
+ IXLBaseAutoFilter IXLBaseAutoFilter.Clear()
+ {
+ return Clear();
+ }
+
+ IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range)
+ {
+ return Set(range);
+ }
+
+ IXLBaseAutoFilter IXLBaseAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase,
+ Boolean ignoreBlanks)
+ {
+ return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks);
+ }
+
+ public Boolean Sorted { get; set; }
+ public XLSortOrder SortOrder { get; set; }
+ public Int32 SortColumn { get; set; }
+
+ public IXLFilterColumn Column(String column)
+ {
+ return Column(XLHelper.GetColumnNumberFromLetter(column));
+ }
+
+ public IXLFilterColumn Column(Int32 column)
+ {
+ XLFilterColumn filterColumn;
+ if (!_columns.TryGetValue(column, out filterColumn))
+ {
+ filterColumn = new XLFilterColumn(this, column);
+ _columns.Add(column, filterColumn);
+ }
+
+ return filterColumn;
+ }
+
+ #endregion
+
+ public XLAutoFilter Set(IXLRangeBase range)
+ {
+ Range = range.AsRange();
+ Enabled = true;
+ return this;
+ }
+
+ public XLAutoFilter Clear()
+ {
+ if (!Enabled) return this;
+
+ Enabled = false;
+ Filters.Clear();
+ foreach (IXLRangeRow row in Range.Rows().Where(r => r.RowNumber() > 1))
+ row.WorksheetRow().Unhide();
+ return this;
+ }
+
+ public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks)
+ {
+ if (!Enabled)
+ throw new ApplicationException("Filter has not been enabled.");
+
+ var ws = Range.Worksheet as XLWorksheet;
+ ws.SuspendEvents();
+ Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase,
+ ignoreBlanks);
+
+ Sorted = true;
+ SortOrder = sortOrder;
+ SortColumn = columnToSortBy;
+
+ if (Enabled)
+ {
+ using (var rows = Range.Rows(2, Range.RowCount()))
+ {
+ foreach (IXLRangeRow row in rows)
+ row.WorksheetRow().Unhide();
+ }
+
+ foreach (KeyValuePair> kp in Filters)
+ {
+ Boolean firstFilter = true;
+ foreach (XLFilter filter in kp.Value)
+ {
+ Boolean isText = filter.Value is String;
+ using (var rows = Range.Rows(2, Range.RowCount()))
+ {
+ foreach (IXLRangeRow row in rows)
+ {
+ Boolean match = isText
+ ? filter.Condition(row.Cell(kp.Key).GetString())
+ : row.Cell(kp.Key).DataType == XLCellValues.Number &&
+ filter.Condition(row.Cell(kp.Key).GetDouble());
+ if (firstFilter)
+ {
+ if (match)
+ row.WorksheetRow().Unhide();
+ else
+ row.WorksheetRow().Hide();
+ }
+ else
+ {
+ if (filter.Connector == XLConnector.And)
+ {
+ if (!row.WorksheetRow().IsHidden)
+ {
+ if (match)
+ row.WorksheetRow().Unhide();
+ else
+ row.WorksheetRow().Hide();
+ }
+ }
+ else if (match)
+ row.WorksheetRow().Unhide();
+ }
+ }
+ firstFilter = false;
+ }
+ }
+ }
+ }
+ ws.ResumeEvents();
+ return this;
+ }
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs
index 26fd585..d2cb44d 100644
--- a/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs
@@ -1,143 +1,143 @@
-using System;
-using System.Linq;
-
-namespace ClosedXML.Excel
-{
- internal class XLCustomFilteredColumn : IXLCustomFilteredColumn
- {
- private readonly XLAutoFilter _autoFilter;
- private readonly Int32 _column;
- private readonly XLConnector _connector;
-
- public XLCustomFilteredColumn(XLAutoFilter autoFilter, Int32 column, XLConnector connector)
- {
- _autoFilter = autoFilter;
- _column = column;
- _connector = connector;
- }
-
- #region IXLCustomFilteredColumn Members
-
- public void EqualTo(T value) where T: IComparable
- {
- if (typeof(T) == typeof(String))
- {
- ApplyCustomFilter(value, XLFilterOperator.Equal,
- v =>
- v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
- }
- else
- {
- ApplyCustomFilter(value, XLFilterOperator.Equal,
- v => v.CastTo().CompareTo(value) == 0);
- }
- }
-
- public void NotEqualTo(T value) where T: IComparable
- {
- if (typeof(T) == typeof(String))
- {
- ApplyCustomFilter(value, XLFilterOperator.NotEqual,
- v =>
- !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
- }
- else
- {
- ApplyCustomFilter(value, XLFilterOperator.NotEqual,
- v => v.CastTo().CompareTo(value) != 0);
- }
- }
-
- public void GreaterThan(T value) where T: IComparable
- {
- ApplyCustomFilter(value, XLFilterOperator.GreaterThan,
- v => v.CastTo().CompareTo(value) > 0);
- }
-
- public void LessThan(T value) where T: IComparable
- {
- ApplyCustomFilter(value, XLFilterOperator.LessThan, v => v.CastTo().CompareTo(value) < 0);
- }
-
- public void EqualOrGreaterThan(T value) where T: IComparable
- {
- ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan,
- v => v.CastTo().CompareTo(value) >= 0);
- }
-
- public void EqualOrLessThan(T value) where T: IComparable
- {
- ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan,
- v => v.CastTo().CompareTo(value) <= 0);
- }
-
- public void BeginsWith(String value)
- {
- ApplyCustomFilter(value + "*", XLFilterOperator.Equal,
- s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public void NotBeginsWith(String value)
- {
- ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual,
- s =>
- !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public void EndsWith(String value)
- {
- ApplyCustomFilter("*" + value, XLFilterOperator.Equal,
- s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public void NotEndsWith(String value)
- {
- ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual,
- s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public void Contains(String value)
- {
- ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
- s => ((string)s).ToLower().Contains(value.ToLower()));
- }
-
- public void NotContains(String value)
- {
- ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
- s => !((string)s).ToLower().Contains(value.ToLower()));
- }
-
- #endregion
-
- private void ApplyCustomFilter(T value, XLFilterOperator op, Func condition)
- where T: IComparable
- {
- _autoFilter.Filters[_column].Add(new XLFilter
- {
- Value = value,
- Operator = op,
- Connector = _connector,
- Condition = condition
- });
- using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()))
- {
- foreach (IXLRangeRow row in rows)
- {
- if (_connector == XLConnector.And)
- {
- if (!row.WorksheetRow().IsHidden)
- {
- if (condition(row.Cell(_column).GetValue()))
- row.WorksheetRow().Unhide().Dispose();
- else
- row.WorksheetRow().Hide().Dispose();
- }
- }
- else if (condition(row.Cell(_column).GetValue()))
- row.WorksheetRow().Unhide().Dispose();
- }
- }
- }
- }
+using System;
+using System.Linq;
+
+namespace ClosedXML.Excel
+{
+ internal class XLCustomFilteredColumn : IXLCustomFilteredColumn
+ {
+ private readonly XLAutoFilter _autoFilter;
+ private readonly Int32 _column;
+ private readonly XLConnector _connector;
+
+ public XLCustomFilteredColumn(XLAutoFilter autoFilter, Int32 column, XLConnector connector)
+ {
+ _autoFilter = autoFilter;
+ _column = column;
+ _connector = connector;
+ }
+
+ #region IXLCustomFilteredColumn Members
+
+ public void EqualTo(T value) where T: IComparable
+ {
+ if (typeof(T) == typeof(String))
+ {
+ ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v =>
+ v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
+ }
+ else
+ {
+ ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v => v.CastTo().CompareTo(value) == 0);
+ }
+ }
+
+ public void NotEqualTo(T value) where T: IComparable
+ {
+ if (typeof(T) == typeof(String))
+ {
+ ApplyCustomFilter(value, XLFilterOperator.NotEqual,
+ v =>
+ !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
+ }
+ else
+ {
+ ApplyCustomFilter(value, XLFilterOperator.NotEqual,
+ v => v.CastTo().CompareTo(value) != 0);
+ }
+ }
+
+ public void GreaterThan(T value) where T: IComparable
+ {
+ ApplyCustomFilter(value, XLFilterOperator.GreaterThan,
+ v => v.CastTo().CompareTo(value) > 0);
+ }
+
+ public void LessThan(T value) where T: IComparable
+ {
+ ApplyCustomFilter(value, XLFilterOperator.LessThan, v => v.CastTo().CompareTo(value) < 0);
+ }
+
+ public void EqualOrGreaterThan(T value) where T: IComparable
+ {
+ ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan,
+ v => v.CastTo().CompareTo(value) >= 0);
+ }
+
+ public void EqualOrLessThan(T value) where T: IComparable
+ {
+ ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan,
+ v => v.CastTo().CompareTo(value) <= 0);
+ }
+
+ public void BeginsWith(String value)
+ {
+ ApplyCustomFilter(value + "*", XLFilterOperator.Equal,
+ s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public void NotBeginsWith(String value)
+ {
+ ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual,
+ s =>
+ !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public void EndsWith(String value)
+ {
+ ApplyCustomFilter("*" + value, XLFilterOperator.Equal,
+ s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public void NotEndsWith(String value)
+ {
+ ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual,
+ s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public void Contains(String value)
+ {
+ ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
+ s => ((string)s).ToLower().Contains(value.ToLower()));
+ }
+
+ public void NotContains(String value)
+ {
+ ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
+ s => !((string)s).ToLower().Contains(value.ToLower()));
+ }
+
+ #endregion
+
+ private void ApplyCustomFilter(T value, XLFilterOperator op, Func condition)
+ where T: IComparable
+ {
+ _autoFilter.Filters[_column].Add(new XLFilter
+ {
+ Value = value,
+ Operator = op,
+ Connector = _connector,
+ Condition = condition
+ });
+ using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()))
+ {
+ foreach (IXLRangeRow row in rows)
+ {
+ if (_connector == XLConnector.And)
+ {
+ if (!row.WorksheetRow().IsHidden)
+ {
+ if (condition(row.Cell(_column).GetValue()))
+ row.WorksheetRow().Unhide().Dispose();
+ else
+ row.WorksheetRow().Hide().Dispose();
+ }
+ }
+ else if (condition(row.Cell(_column).GetValue()))
+ row.WorksheetRow().Unhide().Dispose();
+ }
+ }
+ }
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/XLFilter.cs b/ClosedXML/Excel/AutoFilters/XLFilter.cs
index cf3d8b6..f91231e 100644
--- a/ClosedXML/Excel/AutoFilters/XLFilter.cs
+++ b/ClosedXML/Excel/AutoFilters/XLFilter.cs
@@ -1,22 +1,22 @@
-using System;
-using System.Collections.Generic;
-using System.Linq;
-using System.Text;
-
-namespace ClosedXML.Excel
-{
- public enum XLConnector { And, Or }
- public enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan }
- internal class XLFilter
- {
- public XLFilter(XLFilterOperator op = XLFilterOperator.Equal)
- {
- Operator = op;
- }
-
- public XLFilterOperator Operator { get; set; }
- public Object Value { get; set; }
- public XLConnector Connector { get; set; }
- public Func Condition { get; set; }
- }
-}
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ public enum XLConnector { And, Or }
+ public enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan }
+ internal class XLFilter
+ {
+ public XLFilter(XLFilterOperator op = XLFilterOperator.Equal)
+ {
+ Operator = op;
+ }
+
+ public XLFilterOperator Operator { get; set; }
+ public Object Value { get; set; }
+ public XLConnector Connector { get; set; }
+ public Func Condition { get; set; }
+ }
+}
diff --git a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs
index 11eb172..a045910 100644
--- a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs
@@ -1,393 +1,393 @@
-using System;
-using System.Linq;
-
-namespace ClosedXML.Excel
-{
- using System.Collections.Generic;
-
- internal class XLFilterColumn : IXLFilterColumn
- {
- private readonly XLAutoFilter _autoFilter;
- private readonly Int32 _column;
-
- public XLFilterColumn(XLAutoFilter autoFilter, Int32 column)
- {
- _autoFilter = autoFilter;
- _column = column;
- }
-
- #region IXLFilterColumn Members
-
- public void Clear()
- {
- if (_autoFilter.Filters.ContainsKey(_column))
- _autoFilter.Filters.Remove(_column);
- }
-
- public IXLFilteredColumn AddFilter(T value) where T: IComparable
- {
- if (typeof(T) == typeof(String))
- {
- ApplyCustomFilter(value, XLFilterOperator.Equal,
- v =>
- v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase),
- XLFilterType.Regular);
- }
- else
- {
- ApplyCustomFilter(value, XLFilterOperator.Equal,
- v => v.CastTo().CompareTo(value) == 0, XLFilterType.Regular);
- }
- return new XLFilteredColumn(_autoFilter, _column);
- }
-
- public void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items)
- {
- _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top;
- SetTopBottom(value, type);
- }
-
- public void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items)
- {
- _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Bottom;
- SetTopBottom(value, type, false);
- }
-
- public void AboveAverage()
- {
- ShowAverage(true);
- }
-
- public void BelowAverage()
- {
- ShowAverage(false);
- }
-
- public IXLFilterConnector EqualTo(T value) where T: IComparable
- {
- if (typeof(T) == typeof(String))
- {
- return ApplyCustomFilter(value, XLFilterOperator.Equal,
- v =>
- v.ToString().Equals(value.ToString(),
- StringComparison.InvariantCultureIgnoreCase));
- }
-
- return ApplyCustomFilter(value, XLFilterOperator.Equal,
- v => v.CastTo().CompareTo(value) == 0);
- }
-
- public IXLFilterConnector NotEqualTo(T value) where T: IComparable
- {
- if (typeof(T) == typeof(String))
- {
- return ApplyCustomFilter(value, XLFilterOperator.NotEqual,
- v =>
- !v.ToString().Equals(value.ToString(),
- StringComparison.InvariantCultureIgnoreCase));
- }
-
- return ApplyCustomFilter(value, XLFilterOperator.NotEqual,
- v => v.CastTo().CompareTo(value) != 0);
- }
-
- public IXLFilterConnector GreaterThan(T value) where T: IComparable
- {
- return ApplyCustomFilter(value, XLFilterOperator.GreaterThan,
- v => v.CastTo().CompareTo(value) > 0);
- }
-
- public IXLFilterConnector LessThan(T value) where T: IComparable
- {
- return ApplyCustomFilter(value, XLFilterOperator.LessThan,
- v => v.CastTo().CompareTo(value) < 0);
- }
-
- public IXLFilterConnector EqualOrGreaterThan(T value) where T: IComparable
- {
- return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan,
- v => v.CastTo().CompareTo(value) >= 0);
- }
-
- public IXLFilterConnector EqualOrLessThan(T value) where T: IComparable
- {
- return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan,
- v => v.CastTo().CompareTo(value) <= 0);
- }
-
- public void Between(T minValue, T maxValue) where T: IComparable
- {
- EqualOrGreaterThan(minValue).And.EqualOrLessThan(maxValue);
- }
-
- public void NotBetween(T minValue, T maxValue) where T: IComparable
- {
- LessThan(minValue).Or.GreaterThan(maxValue);
- }
-
- public IXLFilterConnector BeginsWith(String value)
- {
- return ApplyCustomFilter(value + "*", XLFilterOperator.Equal,
- s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public IXLFilterConnector NotBeginsWith(String value)
- {
- return ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual,
- s => !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public IXLFilterConnector EndsWith(String value)
- {
- return ApplyCustomFilter("*" + value, XLFilterOperator.Equal,
- s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public IXLFilterConnector NotEndsWith(String value)
- {
- return ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual,
- s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
- }
-
- public IXLFilterConnector Contains(String value)
- {
- return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
- s => ((string)s).ToLower().Contains(value.ToLower()));
- }
-
- public IXLFilterConnector NotContains(String value)
- {
- return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
- s => !((string)s).ToLower().Contains(value.ToLower()));
- }
-
- public XLFilterType FilterType { get; set; }
-
- public Int32 TopBottomValue { get; set; }
- public XLTopBottomType TopBottomType { get; set; }
- public XLTopBottomPart TopBottomPart { get; set; }
-
- public XLFilterDynamicType DynamicType { get; set; }
- public Double DynamicValue { get; set; }
-
- #endregion
-
- private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true)
- {
- _autoFilter.Enabled = true;
- _autoFilter.Column(_column).SetFilterType(XLFilterType.TopBottom)
- .SetTopBottomValue(value)
- .SetTopBottomType(type);
-
- var values = GetValues(value, type, takeTop);
-
- Clear();
- _autoFilter.Filters.Add(_column, new List());
-
- Boolean addToList = true;
- var ws = _autoFilter.Range.Worksheet as XLWorksheet;
- ws.SuspendEvents();
- var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
- foreach (IXLRangeRow row in rows)
- {
- Boolean foundOne = false;
- foreach (double val in values)
- {
- Func condition = v => (v as IComparable).CompareTo(val) == 0;
- if (addToList)
- {
- _autoFilter.Filters[_column].Add(new XLFilter
- {
- Value = val,
- Operator = XLFilterOperator.Equal,
- Connector = XLConnector.Or,
- Condition = condition
- });
- }
-
- var cell = row.Cell(_column);
- if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue;
- row.WorksheetRow().Unhide();
- foundOne = true;
- }
- if (!foundOne)
- row.WorksheetRow().Hide();
-
- addToList = false;
- }
- ws.ResumeEvents();
- }
-
- private IEnumerable GetValues(int value, XLTopBottomType type, bool takeTop)
- {
- using (var column = _autoFilter.Range.Column(_column))
- {
- using (var subColumn = column.Column(2, column.CellCount()))
- {
- var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number);
- if (takeTop)
- {
- if (type == XLTopBottomType.Items)
- {
- return cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct();
- }
- var numerics1 = cellsUsed.Select(c => c.GetDouble());
- Int32 valsToTake1 = numerics1.Count() * value / 100;
- return numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct();
- }
-
- if (type == XLTopBottomType.Items)
- {
- return cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct();
- }
-
- var numerics = cellsUsed.Select(c => c.GetDouble());
- Int32 valsToTake = numerics.Count() * value / 100;
- return numerics.OrderBy(d => d).Take(valsToTake).Distinct();
- }
- }
- }
-
- private void ShowAverage(Boolean aboveAverage)
- {
- _autoFilter.Enabled = true;
- _autoFilter.Column(_column).SetFilterType(XLFilterType.Dynamic)
- .SetDynamicType(aboveAverage
- ? XLFilterDynamicType.AboveAverage
- : XLFilterDynamicType.BelowAverage);
- var values = GetAverageValues(aboveAverage);
-
-
- Clear();
- _autoFilter.Filters.Add(_column, new List());
-
- Boolean addToList = true;
- var ws = _autoFilter.Range.Worksheet as XLWorksheet;
- ws.SuspendEvents();
- var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
-
- foreach (IXLRangeRow row in rows)
- {
- Boolean foundOne = false;
- foreach (double val in values)
- {
- Func condition = v => (v as IComparable).CompareTo(val) == 0;
- if (addToList)
- {
- _autoFilter.Filters[_column].Add(new XLFilter
- {
- Value = val,
- Operator = XLFilterOperator.Equal,
- Connector = XLConnector.Or,
- Condition = condition
- });
- }
-
- var cell = row.Cell(_column);
- if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue;
- row.WorksheetRow().Unhide();
- foundOne = true;
- }
-
- if (!foundOne)
- row.WorksheetRow().Hide();
-
- addToList = false;
- }
-
- ws.ResumeEvents();
- }
-
- private IEnumerable GetAverageValues(bool aboveAverage)
- {
- using (var column = _autoFilter.Range.Column(_column))
- {
- using (var subColumn = column.Column(2, column.CellCount()))
- {
- Double average = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Average();
-
- if (aboveAverage)
- {
- return
- subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).
- Select(c => c.GetDouble()).Where(c => c > average).Distinct();
- }
-
- return
- subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).
- Select(c => c.GetDouble()).Where(c => c < average).Distinct();
-
- }
- }
- }
-
- private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition,
- XLFilterType filterType = XLFilterType.Custom)
- where T: IComparable
- {
- _autoFilter.Enabled = true;
- if (filterType == XLFilterType.Custom)
- {
- Clear();
- _autoFilter.Filters.Add(_column,
- new List
- {
- new XLFilter
- {
- Value = value,
- Operator = op,
- Connector = XLConnector.Or,
- Condition = condition
- }
- });
- }
- else
- {
- List filterList;
- if (_autoFilter.Filters.TryGetValue(_column, out filterList))
- filterList.Add(new XLFilter
- {Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition});
- else
- {
- _autoFilter.Filters.Add(_column,
- new List
- {
- new XLFilter
- {
- Value = value,
- Operator = op,
- Connector = XLConnector.Or,
- Condition = condition
- }
- });
- }
- }
- _autoFilter.Column(_column).FilterType = filterType;
- Boolean isText = typeof(T) == typeof(String);
- var ws = _autoFilter.Range.Worksheet as XLWorksheet;
- ws.SuspendEvents();
- var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
- foreach (IXLRangeRow row in rows)
- {
- Boolean match = isText
- ? condition(row.Cell(_column).GetString())
- : row.Cell(_column).DataType == XLCellValues.Number &&
- condition(row.Cell(_column).GetDouble());
- if (match)
- row.WorksheetRow().Unhide();
- else
- row.WorksheetRow().Hide();
- }
- ws.ResumeEvents();
- return new XLFilterConnector(_autoFilter, _column);
- }
-
- public IXLFilterColumn SetFilterType(XLFilterType value) { FilterType = value; return this; }
- public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; }
- public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; }
- public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; }
- public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; }
- public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; }
-
- }
+using System;
+using System.Linq;
+
+namespace ClosedXML.Excel
+{
+ using System.Collections.Generic;
+
+ internal class XLFilterColumn : IXLFilterColumn
+ {
+ private readonly XLAutoFilter _autoFilter;
+ private readonly Int32 _column;
+
+ public XLFilterColumn(XLAutoFilter autoFilter, Int32 column)
+ {
+ _autoFilter = autoFilter;
+ _column = column;
+ }
+
+ #region IXLFilterColumn Members
+
+ public void Clear()
+ {
+ if (_autoFilter.Filters.ContainsKey(_column))
+ _autoFilter.Filters.Remove(_column);
+ }
+
+ public IXLFilteredColumn AddFilter(T value) where T: IComparable
+ {
+ if (typeof(T) == typeof(String))
+ {
+ ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v =>
+ v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase),
+ XLFilterType.Regular);
+ }
+ else
+ {
+ ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v => v.CastTo().CompareTo(value) == 0, XLFilterType.Regular);
+ }
+ return new XLFilteredColumn(_autoFilter, _column);
+ }
+
+ public void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items)
+ {
+ _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top;
+ SetTopBottom(value, type);
+ }
+
+ public void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items)
+ {
+ _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Bottom;
+ SetTopBottom(value, type, false);
+ }
+
+ public void AboveAverage()
+ {
+ ShowAverage(true);
+ }
+
+ public void BelowAverage()
+ {
+ ShowAverage(false);
+ }
+
+ public IXLFilterConnector EqualTo(T value) where T: IComparable
+ {
+ if (typeof(T) == typeof(String))
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v =>
+ v.ToString().Equals(value.ToString(),
+ StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ return ApplyCustomFilter(value, XLFilterOperator.Equal,
+ v => v.CastTo().CompareTo(value) == 0);
+ }
+
+ public IXLFilterConnector NotEqualTo(T value) where T: IComparable
+ {
+ if (typeof(T) == typeof(String))
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.NotEqual,
+ v =>
+ !v.ToString().Equals(value.ToString(),
+ StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ return ApplyCustomFilter(value, XLFilterOperator.NotEqual,
+ v => v.CastTo().CompareTo(value) != 0);
+ }
+
+ public IXLFilterConnector GreaterThan(T value) where T: IComparable
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.GreaterThan,
+ v => v.CastTo().CompareTo(value) > 0);
+ }
+
+ public IXLFilterConnector LessThan(T value) where T: IComparable
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.LessThan,
+ v => v.CastTo().CompareTo(value) < 0);
+ }
+
+ public IXLFilterConnector EqualOrGreaterThan(T value) where T: IComparable
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan,
+ v => v.CastTo().CompareTo(value) >= 0);
+ }
+
+ public IXLFilterConnector EqualOrLessThan(T value) where T: IComparable
+ {
+ return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan,
+ v => v.CastTo().CompareTo(value) <= 0);
+ }
+
+ public void Between(T minValue, T maxValue) where T: IComparable
+ {
+ EqualOrGreaterThan(minValue).And.EqualOrLessThan(maxValue);
+ }
+
+ public void NotBetween(T minValue, T maxValue) where T: IComparable
+ {
+ LessThan(minValue).Or.GreaterThan(maxValue);
+ }
+
+ public IXLFilterConnector BeginsWith(String value)
+ {
+ return ApplyCustomFilter(value + "*", XLFilterOperator.Equal,
+ s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public IXLFilterConnector NotBeginsWith(String value)
+ {
+ return ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual,
+ s => !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public IXLFilterConnector EndsWith(String value)
+ {
+ return ApplyCustomFilter("*" + value, XLFilterOperator.Equal,
+ s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public IXLFilterConnector NotEndsWith(String value)
+ {
+ return ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual,
+ s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase));
+ }
+
+ public IXLFilterConnector Contains(String value)
+ {
+ return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
+ s => ((string)s).ToLower().Contains(value.ToLower()));
+ }
+
+ public IXLFilterConnector NotContains(String value)
+ {
+ return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal,
+ s => !((string)s).ToLower().Contains(value.ToLower()));
+ }
+
+ public XLFilterType FilterType { get; set; }
+
+ public Int32 TopBottomValue { get; set; }
+ public XLTopBottomType TopBottomType { get; set; }
+ public XLTopBottomPart TopBottomPart { get; set; }
+
+ public XLFilterDynamicType DynamicType { get; set; }
+ public Double DynamicValue { get; set; }
+
+ #endregion
+
+ private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true)
+ {
+ _autoFilter.Enabled = true;
+ _autoFilter.Column(_column).SetFilterType(XLFilterType.TopBottom)
+ .SetTopBottomValue(value)
+ .SetTopBottomType(type);
+
+ var values = GetValues(value, type, takeTop);
+
+ Clear();
+ _autoFilter.Filters.Add(_column, new List());
+
+ Boolean addToList = true;
+ var ws = _autoFilter.Range.Worksheet as XLWorksheet;
+ ws.SuspendEvents();
+ var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
+ foreach (IXLRangeRow row in rows)
+ {
+ Boolean foundOne = false;
+ foreach (double val in values)
+ {
+ Func condition = v => (v as IComparable).CompareTo(val) == 0;
+ if (addToList)
+ {
+ _autoFilter.Filters[_column].Add(new XLFilter
+ {
+ Value = val,
+ Operator = XLFilterOperator.Equal,
+ Connector = XLConnector.Or,
+ Condition = condition
+ });
+ }
+
+ var cell = row.Cell(_column);
+ if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue;
+ row.WorksheetRow().Unhide();
+ foundOne = true;
+ }
+ if (!foundOne)
+ row.WorksheetRow().Hide();
+
+ addToList = false;
+ }
+ ws.ResumeEvents();
+ }
+
+ private IEnumerable GetValues(int value, XLTopBottomType type, bool takeTop)
+ {
+ using (var column = _autoFilter.Range.Column(_column))
+ {
+ using (var subColumn = column.Column(2, column.CellCount()))
+ {
+ var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number);
+ if (takeTop)
+ {
+ if (type == XLTopBottomType.Items)
+ {
+ return cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct();
+ }
+ var numerics1 = cellsUsed.Select(c => c.GetDouble());
+ Int32 valsToTake1 = numerics1.Count() * value / 100;
+ return numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct();
+ }
+
+ if (type == XLTopBottomType.Items)
+ {
+ return cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct();
+ }
+
+ var numerics = cellsUsed.Select(c => c.GetDouble());
+ Int32 valsToTake = numerics.Count() * value / 100;
+ return numerics.OrderBy(d => d).Take(valsToTake).Distinct();
+ }
+ }
+ }
+
+ private void ShowAverage(Boolean aboveAverage)
+ {
+ _autoFilter.Enabled = true;
+ _autoFilter.Column(_column).SetFilterType(XLFilterType.Dynamic)
+ .SetDynamicType(aboveAverage
+ ? XLFilterDynamicType.AboveAverage
+ : XLFilterDynamicType.BelowAverage);
+ var values = GetAverageValues(aboveAverage);
+
+
+ Clear();
+ _autoFilter.Filters.Add(_column, new List());
+
+ Boolean addToList = true;
+ var ws = _autoFilter.Range.Worksheet as XLWorksheet;
+ ws.SuspendEvents();
+ var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
+
+ foreach (IXLRangeRow row in rows)
+ {
+ Boolean foundOne = false;
+ foreach (double val in values)
+ {
+ Func condition = v => (v as IComparable).CompareTo(val) == 0;
+ if (addToList)
+ {
+ _autoFilter.Filters[_column].Add(new XLFilter
+ {
+ Value = val,
+ Operator = XLFilterOperator.Equal,
+ Connector = XLConnector.Or,
+ Condition = condition
+ });
+ }
+
+ var cell = row.Cell(_column);
+ if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue;
+ row.WorksheetRow().Unhide();
+ foundOne = true;
+ }
+
+ if (!foundOne)
+ row.WorksheetRow().Hide();
+
+ addToList = false;
+ }
+
+ ws.ResumeEvents();
+ }
+
+ private IEnumerable GetAverageValues(bool aboveAverage)
+ {
+ using (var column = _autoFilter.Range.Column(_column))
+ {
+ using (var subColumn = column.Column(2, column.CellCount()))
+ {
+ Double average = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Average();
+
+ if (aboveAverage)
+ {
+ return
+ subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).
+ Select(c => c.GetDouble()).Where(c => c > average).Distinct();
+ }
+
+ return
+ subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).
+ Select(c => c.GetDouble()).Where(c => c < average).Distinct();
+
+ }
+ }
+ }
+
+ private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition,
+ XLFilterType filterType = XLFilterType.Custom)
+ where T: IComparable
+ {
+ _autoFilter.Enabled = true;
+ if (filterType == XLFilterType.Custom)
+ {
+ Clear();
+ _autoFilter.Filters.Add(_column,
+ new List
+ {
+ new XLFilter
+ {
+ Value = value,
+ Operator = op,
+ Connector = XLConnector.Or,
+ Condition = condition
+ }
+ });
+ }
+ else
+ {
+ List filterList;
+ if (_autoFilter.Filters.TryGetValue(_column, out filterList))
+ filterList.Add(new XLFilter
+ {Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition});
+ else
+ {
+ _autoFilter.Filters.Add(_column,
+ new List
+ {
+ new XLFilter
+ {
+ Value = value,
+ Operator = op,
+ Connector = XLConnector.Or,
+ Condition = condition
+ }
+ });
+ }
+ }
+ _autoFilter.Column(_column).FilterType = filterType;
+ Boolean isText = typeof(T) == typeof(String);
+ var ws = _autoFilter.Range.Worksheet as XLWorksheet;
+ ws.SuspendEvents();
+ var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount());
+ foreach (IXLRangeRow row in rows)
+ {
+ Boolean match = isText
+ ? condition(row.Cell(_column).GetString())
+ : row.Cell(_column).DataType == XLCellValues.Number &&
+ condition(row.Cell(_column).GetDouble());
+ if (match)
+ row.WorksheetRow().Unhide();
+ else
+ row.WorksheetRow().Hide();
+ }
+ ws.ResumeEvents();
+ return new XLFilterConnector(_autoFilter, _column);
+ }
+
+ public IXLFilterColumn SetFilterType(XLFilterType value) { FilterType = value; return this; }
+ public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; }
+ public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; }
+ public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; }
+ public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; }
+ public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; }
+
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs b/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs
index b563fd5..3aac0ec 100644
--- a/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs
+++ b/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs
@@ -1,33 +1,33 @@
-using System;
-using System.Collections.Generic;
-using System.Linq;
-using System.Text;
-
-namespace ClosedXML.Excel
-{
- internal class XLFilterConnector : IXLFilterConnector
- {
- private readonly XLAutoFilter _autoFilter;
- private readonly Int32 _column;
-
- public XLFilterConnector(XLAutoFilter autoFilter, Int32 column)
- {
- _autoFilter = autoFilter;
- _column = column;
- }
-
- #region IXLFilterConnector Members
-
- public IXLCustomFilteredColumn And
- {
- get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.And); }
- }
-
- public IXLCustomFilteredColumn Or
- {
- get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.Or); }
- }
-
- #endregion
- }
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ internal class XLFilterConnector : IXLFilterConnector
+ {
+ private readonly XLAutoFilter _autoFilter;
+ private readonly Int32 _column;
+
+ public XLFilterConnector(XLAutoFilter autoFilter, Int32 column)
+ {
+ _autoFilter = autoFilter;
+ _column = column;
+ }
+
+ #region IXLFilterConnector Members
+
+ public IXLCustomFilteredColumn And
+ {
+ get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.And); }
+ }
+
+ public IXLCustomFilteredColumn Or
+ {
+ get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.Or); }
+ }
+
+ #endregion
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs
index 72479d0..f9d2bd3 100644
--- a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs
+++ b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs
@@ -1,61 +1,61 @@
-using System;
-using System.Linq;
-
-namespace ClosedXML.Excel
-{
- internal class XLFilteredColumn : IXLFilteredColumn
- {
- private readonly XLAutoFilter _autoFilter;
- private readonly Int32 _column;
-
- public XLFilteredColumn(XLAutoFilter autoFilter, Int32 column)
- {
- _autoFilter = autoFilter;
- _column = column;
- }
-
- #region IXLFilteredColumn Members
-
- public IXLFilteredColumn AddFilter(T value) where T: IComparable
- {
- Func condition;
- Boolean isText;
- if (typeof(T) == typeof(String))
- {
- condition = v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase);
- isText = true;
- }
- else
- {
- condition = v => v.CastTo().CompareTo(value) == 0;
- isText = false;
- }
-
- _autoFilter.Filters[_column].Add(new XLFilter
- {
- Value = value,
- Condition = condition,
- Operator = XLFilterOperator.Equal,
- Connector = XLConnector.Or
- });
-
- using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()))
- {
- foreach (IXLRangeRow row in rows)
- {
- if ((isText && condition(row.Cell(_column).GetString())) || (
- !isText &&
- row.Cell(_column).DataType ==
- XLCellValues.Number &&
- condition(
- row.Cell(_column).GetValue()))
- )
- row.WorksheetRow().Unhide().Dispose();
- }
- }
- return this;
- }
-
- #endregion
- }
+using System;
+using System.Linq;
+
+namespace ClosedXML.Excel
+{
+ internal class XLFilteredColumn : IXLFilteredColumn
+ {
+ private readonly XLAutoFilter _autoFilter;
+ private readonly Int32 _column;
+
+ public XLFilteredColumn(XLAutoFilter autoFilter, Int32 column)
+ {
+ _autoFilter = autoFilter;
+ _column = column;
+ }
+
+ #region IXLFilteredColumn Members
+
+ public IXLFilteredColumn AddFilter(T value) where T: IComparable
+ {
+ Func condition;
+ Boolean isText;
+ if (typeof(T) == typeof(String))
+ {
+ condition = v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase);
+ isText = true;
+ }
+ else
+ {
+ condition = v => v.CastTo().CompareTo(value) == 0;
+ isText = false;
+ }
+
+ _autoFilter.Filters[_column].Add(new XLFilter
+ {
+ Value = value,
+ Condition = condition,
+ Operator = XLFilterOperator.Equal,
+ Connector = XLConnector.Or
+ });
+
+ using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()))
+ {
+ foreach (IXLRangeRow row in rows)
+ {
+ if ((isText && condition(row.Cell(_column).GetString())) || (
+ !isText &&
+ row.Cell(_column).DataType ==
+ XLCellValues.Number &&
+ condition(
+ row.Cell(_column).GetValue()))
+ )
+ row.WorksheetRow().Unhide().Dispose();
+ }
+ }
+ return this;
+ }
+
+ #endregion
+ }
}
\ No newline at end of file
diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs
index 9ba60b2..39af87c 100644
--- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs
+++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs
@@ -1,694 +1,831 @@
-using System;
-using System.Reflection;
-using System.Collections.Generic;
-using System.ComponentModel;
-using System.Globalization;
-using System.Diagnostics;
-using System.Text;
-using System.Text.RegularExpressions;
-using ClosedXML.Excel.CalcEngine;
-using ClosedXML.Excel.CalcEngine.Functions;
-
-namespace ClosedXML.Excel.CalcEngine {
- ///
- /// CalcEngine parses strings and returns Expression objects that can
- /// be evaluated.
- ///
- ///
- /// This class has three extensibility points:
- /// Use the DataContext property to add an object's properties to the engine scope.
- /// Use the RegisterFunction method to define custom functions.
- /// Override the GetExternalObject method to add arbitrary variables to the engine scope.
- ///
- internal class CalcEngine {
- //---------------------------------------------------------------------------
- #region ** fields
-
- // members
- string _expr; // expression being parsed
- int _len; // length of the expression being parsed
- int _ptr; // current pointer into expression
- string _idChars; // valid characters in identifiers (besides alpha and digits)
- Token _token; // current token being parsed
- Dictionary _tkTbl; // table with tokens (+, -, etc)
- Dictionary _fnTbl; // table with constants and functions (pi, sin, etc)
- Dictionary _vars; // table with variables
- object _dataContext; // object with properties
- bool _optimize; // optimize expressions when parsing
- ExpressionCache _cache; // cache with parsed expressions
- CultureInfo _ci; // culture info used to parse numbers/dates
- char _decimal, _listSep, _percent; // localized decimal separator, list separator, percent sign
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** ctor
-
- public CalcEngine() {
- CultureInfo = CultureInfo.InvariantCulture;
- _tkTbl = GetSymbolTable();
- _fnTbl = GetFunctionTable();
- _vars = new Dictionary(StringComparer.OrdinalIgnoreCase);
- _cache = new ExpressionCache(this);
- _optimize = true;
-#if DEBUG
- //this.Test();
-#endif
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** object model
-
- ///
- /// Parses a string into an .
- ///
- /// String to parse.
- /// An object that can be evaluated.
- public Expression Parse(string expression) {
- // initialize
- _expr = expression;
- _len = _expr.Length;
- _ptr = 0;
-
- // skip leading equals sign
- if (_len > 0 && _expr[0] == '=') {
- _ptr++;
- }
-
- // parse the expression
- var expr = ParseExpression();
-
- // check for errors
- if (_token.ID != TKID.END) {
- Throw();
- }
-
- // optimize expression
- if (_optimize) {
- expr = expr.Optimize();
- }
-
- // done
- return expr;
- }
- ///
- /// Evaluates a string.
- ///
- /// Expression to evaluate.
- /// The value of the expression.
- ///
- /// If you are going to evaluate the same expression several times,
- /// it is more efficient to parse it only once using the
- /// method and then using the Expression.Evaluate method to evaluate
- /// the parsed expression.
- ///
- public object Evaluate(string expression) {
- var x = //Parse(expression);
- _cache != null
- ? _cache[expression]
- : Parse(expression);
- return x.Evaluate();
- }
- ///
- /// Gets or sets whether the calc engine should keep a cache with parsed
- /// expressions.
- ///
- public bool CacheExpressions {
- get { return _cache != null; }
- set {
- if (value != CacheExpressions) {
- _cache = value
- ? new ExpressionCache(this)
- : null;
- }
- }
- }
- ///
- /// Gets or sets whether the calc engine should optimize expressions when
- /// they are parsed.
- ///
- public bool OptimizeExpressions {
- get { return _optimize; }
- set { _optimize = value; }
- }
- ///
- /// Gets or sets a string that specifies special characters that are valid for identifiers.
- ///
- ///
- /// Identifiers must start with a letter or an underscore, which may be followed by
- /// additional letters, underscores, or digits. This string allows you to specify
- /// additional valid characters such as ':' or '!' (used in Excel range references
- /// for example).
- ///
- public string IdentifierChars {
- get { return _idChars; }
- set { _idChars = value; }
- }
- ///
- /// Registers a function that can be evaluated by this .
- ///
- /// Function name.
- /// Minimum parameter count.
- /// Maximum parameter count.
- /// Delegate that evaluates the function.
- public void RegisterFunction(string functionName, int parmMin, int parmMax, CalcEngineFunction fn) {
- _fnTbl.Add(functionName, new FunctionDefinition(parmMin, parmMax, fn));
- }
- ///
- /// Registers a function that can be evaluated by this .
- ///
- /// Function name.
- /// Parameter count.
- /// Delegate that evaluates the function.
- public void RegisterFunction(string functionName, int parmCount, CalcEngineFunction fn) {
- RegisterFunction(functionName, parmCount, parmCount, fn);
- }
- ///
- /// Gets an external object based on an identifier.
- ///
- ///
- /// This method is useful when the engine needs to create objects dynamically.
- /// For example, a spreadsheet calc engine would use this method to dynamically create cell
- /// range objects based on identifiers that cannot be enumerated at design time
- /// (such as "AB12", "A1:AB12", etc.)
- ///
- public virtual object GetExternalObject(string identifier) {
- return null;
- }
- ///
- /// Gets or sets the DataContext for this .
- ///
- ///
- /// Once a DataContext is set, all public properties of the object become available
- /// to the CalcEngine, including sub-properties such as "Address.Street". These may
- /// be used with expressions just like any other constant.
- ///
- public virtual object DataContext {
- get { return _dataContext; }
- set { _dataContext = value; }
- }
- ///
- /// Gets the dictionary that contains function definitions.
- ///
- public Dictionary Functions {
- get { return _fnTbl; }
- }
- ///
- /// Gets the dictionary that contains simple variables (not in the DataContext).
- ///
- public Dictionary Variables {
- get { return _vars; }
- }
- ///
- /// Gets or sets the to use when parsing numbers and dates.
- ///
- public CultureInfo CultureInfo {
- get { return _ci; }
- set {
- _ci = value;
- var nf = _ci.NumberFormat;
- _decimal = nf.NumberDecimalSeparator[0];
- _percent = nf.PercentSymbol[0];
- _listSep = _ci.TextInfo.ListSeparator[0];
- }
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** token/keyword tables
-
- // build/get static token table
- Dictionary GetSymbolTable() {
- if (_tkTbl == null) {
- _tkTbl = new Dictionary();
- AddToken('&', TKID.CONCAT, TKTYPE.ADDSUB);
- AddToken('+', TKID.ADD, TKTYPE.ADDSUB);
- AddToken('-', TKID.SUB, TKTYPE.ADDSUB);
- AddToken('(', TKID.OPEN, TKTYPE.GROUP);
- AddToken(')', TKID.CLOSE, TKTYPE.GROUP);
- AddToken('*', TKID.MUL, TKTYPE.MULDIV);
- AddToken('.', TKID.PERIOD, TKTYPE.GROUP);
- AddToken('/', TKID.DIV, TKTYPE.MULDIV);
- AddToken('\\', TKID.DIVINT, TKTYPE.MULDIV);
- AddToken('=', TKID.EQ, TKTYPE.COMPARE);
- AddToken('>', TKID.GT, TKTYPE.COMPARE);
- AddToken('<', TKID.LT, TKTYPE.COMPARE);
- AddToken('^', TKID.POWER, TKTYPE.POWER);
- AddToken("<>", TKID.NE, TKTYPE.COMPARE);
- AddToken(">=", TKID.GE, TKTYPE.COMPARE);
- AddToken("<=", TKID.LE, TKTYPE.COMPARE);
-
- // list separator is localized, not necessarily a comma
- // so it can't be on the static table
- //AddToken(',', TKID.COMMA, TKTYPE.GROUP);
- }
- return _tkTbl;
- }
- void AddToken(object symbol, TKID id, TKTYPE type) {
- var token = new Token(symbol, id, type);
- _tkTbl.Add(symbol, token);
- }
-
- // build/get static keyword table
- Dictionary GetFunctionTable() {
- if (_fnTbl == null) {
- // create table
- _fnTbl = new Dictionary(StringComparer.InvariantCultureIgnoreCase);
-
- // register built-in functions (and constants)
- Information.Register(this);
- Logical.Register(this);
- Lookup.Register(this);
- MathTrig.Register(this);
- Text.Register(this);
- Statistical.Register(this);
- DateAndTime.Register(this);
- }
- return _fnTbl;
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** private stuff
-
- Expression ParseExpression() {
- GetToken();
- return ParseCompare();
- }
- Expression ParseCompare() {
- var x = ParseAddSub();
- while (_token.Type == TKTYPE.COMPARE) {
- var t = _token;
- GetToken();
- var exprArg = ParseAddSub();
- x = new BinaryExpression(t, x, exprArg);
- }
- return x;
- }
- Expression ParseAddSub() {
- var x = ParseMulDiv();
- while (_token.Type == TKTYPE.ADDSUB) {
- var t = _token;
- GetToken();
- var exprArg = ParseMulDiv();
- x = new BinaryExpression(t, x, exprArg);
- }
- return x;
- }
- Expression ParseMulDiv() {
- var x = ParsePower();
- while (_token.Type == TKTYPE.MULDIV) {
- var t = _token;
- GetToken();
- var a = ParsePower();
- x = new BinaryExpression(t, x, a);
- }
- return x;
- }
- Expression ParsePower() {
- var x = ParseUnary();
- while (_token.Type == TKTYPE.POWER) {
- var t = _token;
- GetToken();
- var a = ParseUnary();
- x = new BinaryExpression(t, x, a);
- }
- return x;
- }
- Expression ParseUnary() {
- // unary plus and minus
- if (_token.ID == TKID.ADD || _token.ID == TKID.SUB) {
- var t = _token;
- GetToken();
- var a = ParseAtom();
- return new UnaryExpression(t, a);
- }
-
- // not unary, return atom
- return ParseAtom();
- }
- Expression ParseAtom() {
- string id;
- Expression x = null;
- FunctionDefinition fnDef = null;
-
- switch (_token.Type) {
- // literals
- case TKTYPE.LITERAL:
- x = new Expression(_token);
- break;
-
- // identifiers
- case TKTYPE.IDENTIFIER:
-
- // get identifier
- id = (string)_token.Value;
-
- // look for functions
- if (_fnTbl.TryGetValue(id, out fnDef)) {
- var p = GetParameters();
- var pCnt = p == null ? 0 : p.Count;
- if (fnDef.ParmMin != -1 && pCnt < fnDef.ParmMin) {
- Throw("Too few parameters.");
- }
- if (fnDef.ParmMax != -1 && pCnt > fnDef.ParmMax) {
- Throw("Too many parameters.");
- }
- x = new FunctionExpression(fnDef, p);
- break;
- }
-
- // look for simple variables (much faster than binding!)
- if (_vars.ContainsKey(id)) {
- x = new VariableExpression(_vars, id);
- break;
- }
-
- // look for external objects
- var xObj = GetExternalObject(id);
- if (xObj != null) {
- x = new XObjectExpression(xObj);
- break;
- }
-
- // look for bindings
- if (DataContext != null) {
- var list = new List();
- for (var t = _token; t != null; t = GetMember()) {
- list.Add(new BindingInfo((string)t.Value, GetParameters()));
- }
- x = new BindingExpression(this, list, _ci);
- break;
- }
- Throw("Unexpected identifier");
- break;
-
- // sub-expressions
- case TKTYPE.GROUP:
-
- // anything other than opening parenthesis is illegal here
- if (_token.ID != TKID.OPEN) {
- Throw("Expression expected.");
- }
-
- // get expression
- GetToken();
- x = ParseCompare();
-
- // check that the parenthesis was closed
- if (_token.ID != TKID.CLOSE) {
- Throw("Unbalanced parenthesis.");
- }
-
- break;
- }
-
- // make sure we got something...
- if (x == null) {
- Throw();
- }
-
- // done
- GetToken();
- return x;
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** parser
-
- void GetToken() {
- // eat white space
- while (_ptr < _len && _expr[_ptr] <= ' ') {
- _ptr++;
- }
-
- // are we done?
- if (_ptr >= _len) {
- _token = new Token(null, TKID.END, TKTYPE.GROUP);
- return;
- }
-
- // prepare to parse
- int i;
- var c = _expr[_ptr];
-
- // operators
- // this gets called a lot, so it's pretty optimized.
- // note that operators must start with non-letter/digit characters.
- var isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');
- var isDigit = c >= '0' && c <= '9';
- if (!isLetter && !isDigit) {
- // if this is a number starting with a decimal, don't parse as operator
- var nxt = _ptr + 1 < _len ? _expr[_ptr + 1] : 0;
- bool isNumber = c == _decimal && nxt >= '0' && nxt <= '9';
- if (!isNumber) {
- // look up localized list separator
- if (c == _listSep) {
- _token = new Token(c, TKID.COMMA, TKTYPE.GROUP);
- _ptr++;
- return;
- }
-
- // look up single-char tokens on table
- Token tk;
- if (_tkTbl.TryGetValue(c, out tk)) {
- // save token we found
- _token = tk;
- _ptr++;
-
- // look for double-char tokens (special case)
- if (_ptr < _len && (c == '>' || c == '<')) {
- if (_tkTbl.TryGetValue(_expr.Substring(_ptr - 1, 2), out tk)) {
- _token = tk;
- _ptr++;
- }
- }
-
- // found token on the table
- return;
- }
- }
- }
-
- // parse numbers
- if (isDigit || c == _decimal) {
- var sci = false;
- var pct = false;
- var div = -1.0; // use double, not int (this may get really big)
- var val = 0.0;
- for (i = 0; i + _ptr < _len; i++) {
- c = _expr[_ptr + i];
-
- // digits always OK
- if (c >= '0' && c <= '9') {
- val = val * 10 + (c - '0');
- if (div > -1) {
- div *= 10;
- }
- continue;
- }
-
- // one decimal is OK
- if (c == _decimal && div < 0) {
- div = 1;
- continue;
- }
-
- // scientific notation?
- if ((c == 'E' || c == 'e') && !sci) {
- sci = true;
- c = _expr[_ptr + i + 1];
- if (c == '+' || c == '-') i++;
- continue;
- }
-
- // percentage?
- if (c == _percent) {
- pct = true;
- i++;
- break;
- }
-
- // end of literal
- break;
- }
-
- // end of number, get value
- if (!sci) {
- // much faster than ParseDouble
- if (div > 1) {
- val /= div;
- }
- if (pct) {
- val /= 100.0;
- }
- } else {
- var lit = _expr.Substring(_ptr, i);
- val = ParseDouble(lit, _ci);
- }
-
- // build token
- _token = new Token(val, TKID.ATOM, TKTYPE.LITERAL);
-
- // advance pointer and return
- _ptr += i;
- return;
- }
-
- // parse strings
- if (c == '\"') {
- // look for end quote, skip double quotes
- for (i = 1; i + _ptr < _len; i++) {
- c = _expr[_ptr + i];
- if (c != '\"') continue;
- char cNext = i + _ptr < _len - 1 ? _expr[_ptr + i + 1] : ' ';
- if (cNext != '\"') break;
- i++;
- }
-
- // check that we got the end of the string
- if (c != '\"') {
- Throw("Can't find final quote.");
- }
-
- // end of string
- var lit = _expr.Substring(_ptr + 1, i - 1);
- _ptr += i + 1;
- _token = new Token(lit.Replace("\"\"", "\""), TKID.ATOM, TKTYPE.LITERAL);
- return;
- }
-
- // parse dates (review)
- if (c == '#') {
- // look for end #
- for (i = 1; i + _ptr < _len; i++) {
- c = _expr[_ptr + i];
- if (c == '#') break;
- }
-
- // check that we got the end of the date
- if (c != '#') {
- Throw("Can't find final date delimiter ('#').");
- }
-
- // end of date
- var lit = _expr.Substring(_ptr + 1, i - 1);
- _ptr += i + 1;
- _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL);
- return;
- }
-
- // identifiers (functions, objects) must start with alpha or underscore
- if (!isLetter && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) {
- Throw("Identifier expected.");
- }
-
- // and must contain only letters/digits/_idChars
- for (i = 1; i + _ptr < _len; i++) {
- c = _expr[_ptr + i];
- isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');
- isDigit = c >= '0' && c <= '9';
- if (!isLetter && !isDigit && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) {
- break;
- }
- }
-
- // got identifier
- var id = _expr.Substring(_ptr, i);
- _ptr += i;
- _token = new Token(id, TKID.ATOM, TKTYPE.IDENTIFIER);
- }
- static double ParseDouble(string str, CultureInfo ci) {
- if (str.Length > 0 && str[str.Length - 1] == ci.NumberFormat.PercentSymbol[0]) {
- str = str.Substring(0, str.Length - 1);
- return double.Parse(str, NumberStyles.Any, ci) / 100.0;
- }
- return double.Parse(str, NumberStyles.Any, ci);
- }
- List GetParameters() // e.g. myfun(a, b, c+2)
- {
- // check whether next token is a (,
- // restore state and bail if it's not
- var pos = _ptr;
- var tk = _token;
- GetToken();
- if (_token.ID != TKID.OPEN) {
- _ptr = pos;
- _token = tk;
- return null;
- }
-
- // check for empty Parameter list
- pos = _ptr;
- GetToken();
- if (_token.ID == TKID.CLOSE) {
- return null;
- }
- _ptr = pos;
-
- // get Parameters until we reach the end of the list
- var parms = new List();
- var expr = ParseExpression();
- parms.Add(expr);
- while (_token.ID == TKID.COMMA) {
- expr = ParseExpression();
- parms.Add(expr);
- }
-
- // make sure the list was closed correctly
- if (_token.ID != TKID.CLOSE) {
- Throw();
- }
-
- // done
- return parms;
- }
- Token GetMember() {
- // check whether next token is a MEMBER token ('.'),
- // restore state and bail if it's not
- var pos = _ptr;
- var tk = _token;
- GetToken();
- if (_token.ID != TKID.PERIOD) {
- _ptr = pos;
- _token = tk;
- return null;
- }
-
- // skip member token
- GetToken();
- if (_token.Type != TKTYPE.IDENTIFIER) {
- Throw("Identifier expected");
- }
- return _token;
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** static helpers
-
- static void Throw() {
- Throw("Syntax error.");
- }
- static void Throw(string msg) {
- throw new Exception(msg);
- }
-
- #endregion
- }
-
- ///
- /// Delegate that represents CalcEngine functions.
- ///
- /// List of objects that represent the
- /// parameters to be used in the function call.
- /// The function result.
- internal delegate object CalcEngineFunction(List parms);
-}
+using ClosedXML.Excel.CalcEngine.Functions;
+using System;
+using System.Collections.Generic;
+using System.Globalization;
+using System.Linq;
+
+namespace ClosedXML.Excel.CalcEngine
+{
+ ///
+ /// CalcEngine parses strings and returns Expression objects that can
+ /// be evaluated.
+ ///
+ ///
+ /// This class has three extensibility points:
+ /// Use the DataContext property to add an object's properties to the engine scope.
+ /// Use the RegisterFunction method to define custom functions.
+ /// Override the GetExternalObject method to add arbitrary variables to the engine scope.
+ ///
+ internal class CalcEngine
+ {
+ //---------------------------------------------------------------------------
+
+ #region ** fields
+
+ // members
+ private string _expr; // expression being parsed
+
+ private int _len; // length of the expression being parsed
+ private int _ptr; // current pointer into expression
+ private char[] _idChars; // valid characters in identifiers (besides alpha and digits)
+ private Token _token; // current token being parsed
+ private Dictionary _tkTbl; // table with tokens (+, -, etc)
+ private Dictionary _fnTbl; // table with constants and functions (pi, sin, etc)
+ private Dictionary _vars; // table with variables
+ private object _dataContext; // object with properties
+ private bool _optimize; // optimize expressions when parsing
+ private ExpressionCache _cache; // cache with parsed expressions
+ private CultureInfo _ci; // culture info used to parse numbers/dates
+ private char _decimal, _listSep, _percent; // localized decimal separator, list separator, percent sign
+
+ #endregion ** fields
+
+ //---------------------------------------------------------------------------
+
+ #region ** ctor
+
+ public CalcEngine()
+ {
+ CultureInfo = CultureInfo.InvariantCulture;
+ _tkTbl = GetSymbolTable();
+ _fnTbl = GetFunctionTable();
+ _vars = new Dictionary(StringComparer.OrdinalIgnoreCase);
+ _cache = new ExpressionCache(this);
+ _optimize = true;
+#if DEBUG
+ //this.Test();
+#endif
+ }
+
+ #endregion ** ctor
+
+ //---------------------------------------------------------------------------
+
+ #region ** object model
+
+ ///
+ /// Parses a string into an .
+ ///
+ /// String to parse.
+ /// An object that can be evaluated.
+ public Expression Parse(string expression)
+ {
+ // initialize
+ _expr = expression;
+ _len = _expr.Length;
+ _ptr = 0;
+
+ // skip leading equals sign
+ if (_len > 0 && _expr[0] == '=')
+ {
+ _ptr++;
+ }
+
+ // parse the expression
+ var expr = ParseExpression();
+
+ // check for errors
+ if (_token.ID != TKID.END)
+ {
+ Throw();
+ }
+
+ // optimize expression
+ if (_optimize)
+ {
+ expr = expr.Optimize();
+ }
+
+ // done
+ return expr;
+ }
+
+ ///
+ /// Evaluates a string.
+ ///
+ /// Expression to evaluate.
+ /// The value of the expression.
+ ///
+ /// If you are going to evaluate the same expression several times,
+ /// it is more efficient to parse it only once using the
+ /// method and then using the Expression.Evaluate method to evaluate
+ /// the parsed expression.
+ ///
+ public object Evaluate(string expression)
+ {
+ var x = //Parse(expression);
+ _cache != null
+ ? _cache[expression]
+ : Parse(expression);
+ return x.Evaluate();
+ }
+
+ ///
+ /// Gets or sets whether the calc engine should keep a cache with parsed
+ /// expressions.
+ ///
+ public bool CacheExpressions
+ {
+ get { return _cache != null; }
+ set
+ {
+ if (value != CacheExpressions)
+ {
+ _cache = value
+ ? new ExpressionCache(this)
+ : null;
+ }
+ }
+ }
+
+ ///
+ /// Gets or sets whether the calc engine should optimize expressions when
+ /// they are parsed.
+ ///
+ public bool OptimizeExpressions
+ {
+ get { return _optimize; }
+ set { _optimize = value; }
+ }
+
+ ///
+ /// Gets or sets a string that specifies special characters that are valid for identifiers.
+ ///
+ ///
+ /// Identifiers must start with a letter or an underscore, which may be followed by
+ /// additional letters, underscores, or digits. This string allows you to specify
+ /// additional valid characters such as ':' or '!' (used in Excel range references
+ /// for example).
+ ///
+ public char[] IdentifierChars
+ {
+ get { return _idChars; }
+ set { _idChars = value; }
+ }
+
+ ///
+ /// Registers a function that can be evaluated by this .
+ ///
+ /// Function name.
+ /// Minimum parameter count.
+ /// Maximum parameter count.
+ /// Delegate that evaluates the function.
+ public void RegisterFunction(string functionName, int parmMin, int parmMax, CalcEngineFunction fn)
+ {
+ _fnTbl.Add(functionName, new FunctionDefinition(parmMin, parmMax, fn));
+ }
+
+ ///
+ /// Registers a function that can be evaluated by this .
+ ///
+ /// Function name.
+ /// Parameter count.
+ /// Delegate that evaluates the function.
+ public void RegisterFunction(string functionName, int parmCount, CalcEngineFunction fn)
+ {
+ RegisterFunction(functionName, parmCount, parmCount, fn);
+ }
+
+ ///
+ /// Gets an external object based on an identifier.
+ ///
+ ///
+ /// This method is useful when the engine needs to create objects dynamically.
+ /// For example, a spreadsheet calc engine would use this method to dynamically create cell
+ /// range objects based on identifiers that cannot be enumerated at design time
+ /// (such as "AB12", "A1:AB12", etc.)
+ ///
+ public virtual object GetExternalObject(string identifier)
+ {
+ return null;
+ }
+
+ ///
+ /// Gets or sets the DataContext for this .
+ ///
+ ///
+ /// Once a DataContext is set, all public properties of the object become available
+ /// to the CalcEngine, including sub-properties such as "Address.Street". These may
+ /// be used with expressions just like any other constant.
+ ///
+ public virtual object DataContext
+ {
+ get { return _dataContext; }
+ set { _dataContext = value; }
+ }
+
+ ///
+ /// Gets the dictionary that contains function definitions.
+ ///
+ public Dictionary Functions
+ {
+ get { return _fnTbl; }
+ }
+
+ ///
+ /// Gets the dictionary that contains simple variables (not in the DataContext).
+ ///
+ public Dictionary Variables
+ {
+ get { return _vars; }
+ }
+
+ ///
+ /// Gets or sets the to use when parsing numbers and dates.
+ ///
+ public CultureInfo CultureInfo
+ {
+ get { return _ci; }
+ set
+ {
+ _ci = value;
+ var nf = _ci.NumberFormat;
+ _decimal = nf.NumberDecimalSeparator[0];
+ _percent = nf.PercentSymbol[0];
+ _listSep = _ci.TextInfo.ListSeparator[0];
+ }
+ }
+
+ #endregion ** object model
+
+ //---------------------------------------------------------------------------
+
+ #region ** token/keyword tables
+
+ // build/get static token table
+ private Dictionary GetSymbolTable()
+ {
+ if (_tkTbl == null)
+ {
+ _tkTbl = new Dictionary();
+ AddToken('&', TKID.CONCAT, TKTYPE.ADDSUB);
+ AddToken('+', TKID.ADD, TKTYPE.ADDSUB);
+ AddToken('-', TKID.SUB, TKTYPE.ADDSUB);
+ AddToken('(', TKID.OPEN, TKTYPE.GROUP);
+ AddToken(')', TKID.CLOSE, TKTYPE.GROUP);
+ AddToken('*', TKID.MUL, TKTYPE.MULDIV);
+ AddToken('.', TKID.PERIOD, TKTYPE.GROUP);
+ AddToken('/', TKID.DIV, TKTYPE.MULDIV);
+ AddToken('\\', TKID.DIVINT, TKTYPE.MULDIV);
+ AddToken('=', TKID.EQ, TKTYPE.COMPARE);
+ AddToken('>', TKID.GT, TKTYPE.COMPARE);
+ AddToken('<', TKID.LT, TKTYPE.COMPARE);
+ AddToken('^', TKID.POWER, TKTYPE.POWER);
+ AddToken("<>", TKID.NE, TKTYPE.COMPARE);
+ AddToken(">=", TKID.GE, TKTYPE.COMPARE);
+ AddToken("<=", TKID.LE, TKTYPE.COMPARE);
+
+ // list separator is localized, not necessarily a comma
+ // so it can't be on the static table
+ //AddToken(',', TKID.COMMA, TKTYPE.GROUP);
+ }
+ return _tkTbl;
+ }
+
+ private void AddToken(object symbol, TKID id, TKTYPE type)
+ {
+ var token = new Token(symbol, id, type);
+ _tkTbl.Add(symbol, token);
+ }
+
+ // build/get static keyword table
+ private Dictionary GetFunctionTable()
+ {
+ if (_fnTbl == null)
+ {
+ // create table
+ _fnTbl = new Dictionary(StringComparer.InvariantCultureIgnoreCase);
+
+ // register built-in functions (and constants)
+ Information.Register(this);
+ Logical.Register(this);
+ Lookup.Register(this);
+ MathTrig.Register(this);
+ Text.Register(this);
+ Statistical.Register(this);
+ DateAndTime.Register(this);
+ }
+ return _fnTbl;
+ }
+
+ #endregion ** token/keyword tables
+
+ //---------------------------------------------------------------------------
+
+ #region ** private stuff
+
+ private Expression ParseExpression()
+ {
+ GetToken();
+ return ParseCompare();
+ }
+
+ private Expression ParseCompare()
+ {
+ var x = ParseAddSub();
+ while (_token.Type == TKTYPE.COMPARE)
+ {
+ var t = _token;
+ GetToken();
+ var exprArg = ParseAddSub();
+ x = new BinaryExpression(t, x, exprArg);
+ }
+ return x;
+ }
+
+ private Expression ParseAddSub()
+ {
+ var x = ParseMulDiv();
+ while (_token.Type == TKTYPE.ADDSUB)
+ {
+ var t = _token;
+ GetToken();
+ var exprArg = ParseMulDiv();
+ x = new BinaryExpression(t, x, exprArg);
+ }
+ return x;
+ }
+
+ private Expression ParseMulDiv()
+ {
+ var x = ParsePower();
+ while (_token.Type == TKTYPE.MULDIV)
+ {
+ var t = _token;
+ GetToken();
+ var a = ParsePower();
+ x = new BinaryExpression(t, x, a);
+ }
+ return x;
+ }
+
+ private Expression ParsePower()
+ {
+ var x = ParseUnary();
+ while (_token.Type == TKTYPE.POWER)
+ {
+ var t = _token;
+ GetToken();
+ var a = ParseUnary();
+ x = new BinaryExpression(t, x, a);
+ }
+ return x;
+ }
+
+ private Expression ParseUnary()
+ {
+ // unary plus and minus
+ if (_token.ID == TKID.ADD || _token.ID == TKID.SUB)
+ {
+ var t = _token;
+ GetToken();
+ var a = ParseAtom();
+ return new UnaryExpression(t, a);
+ }
+
+ // not unary, return atom
+ return ParseAtom();
+ }
+
+ private Expression ParseAtom()
+ {
+ string id;
+ Expression x = null;
+ FunctionDefinition fnDef = null;
+
+ switch (_token.Type)
+ {
+ // literals
+ case TKTYPE.LITERAL:
+ x = new Expression(_token);
+ break;
+
+ // identifiers
+ case TKTYPE.IDENTIFIER:
+
+ // get identifier
+ id = (string)_token.Value;
+
+ // look for functions
+ if (_fnTbl.TryGetValue(id, out fnDef))
+ {
+ var p = GetParameters();
+ var pCnt = p == null ? 0 : p.Count;
+ if (fnDef.ParmMin != -1 && pCnt < fnDef.ParmMin)
+ {
+ Throw("Too few parameters.");
+ }
+ if (fnDef.ParmMax != -1 && pCnt > fnDef.ParmMax)
+ {
+ Throw("Too many parameters.");
+ }
+ x = new FunctionExpression(fnDef, p);
+ break;
+ }
+
+ // look for simple variables (much faster than binding!)
+ if (_vars.ContainsKey(id))
+ {
+ x = new VariableExpression(_vars, id);
+ break;
+ }
+
+ // look for external objects
+ var xObj = GetExternalObject(id);
+ if (xObj != null)
+ {
+ x = new XObjectExpression(xObj);
+ break;
+ }
+
+ Throw("Unexpected identifier");
+ break;
+
+ // sub-expressions
+ case TKTYPE.GROUP:
+
+ // anything other than opening parenthesis is illegal here
+ if (_token.ID != TKID.OPEN)
+ {
+ Throw("Expression expected.");
+ }
+
+ // get expression
+ GetToken();
+ x = ParseCompare();
+
+ // check that the parenthesis was closed
+ if (_token.ID != TKID.CLOSE)
+ {
+ Throw("Unbalanced parenthesis.");
+ }
+
+ break;
+ }
+
+ // make sure we got something...
+ if (x == null)
+ {
+ Throw();
+ }
+
+ // done
+ GetToken();
+ return x;
+ }
+
+ #endregion ** private stuff
+
+ //---------------------------------------------------------------------------
+
+ #region ** parser
+
+ private static IDictionary matchingClosingSymbols = new Dictionary()
+ {
+ { '\'', '\'' },
+ { '[', ']' }
+ };
+
+ private void GetToken()
+ {
+ // eat white space
+ while (_ptr < _len && _expr[_ptr] <= ' ')
+ {
+ _ptr++;
+ }
+
+ // are we done?
+ if (_ptr >= _len)
+ {
+ _token = new Token(null, TKID.END, TKTYPE.GROUP);
+ return;
+ }
+
+ // prepare to parse
+ int i;
+ var c = _expr[_ptr];
+
+ // operators
+ // this gets called a lot, so it's pretty optimized.
+ // note that operators must start with non-letter/digit characters.
+ var isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');
+ var isDigit = c >= '0' && c <= '9';
+
+ var isEnclosed = matchingClosingSymbols.Keys.Contains(c);
+ char matchingClosingSymbol = '\0';
+ if (isEnclosed)
+ matchingClosingSymbol = matchingClosingSymbols[c];
+
+ if (!isLetter && !isDigit && !isEnclosed)
+ {
+ // if this is a number starting with a decimal, don't parse as operator
+ var nxt = _ptr + 1 < _len ? _expr[_ptr + 1] : 0;
+ bool isNumber = c == _decimal && nxt >= '0' && nxt <= '9';
+ if (!isNumber)
+ {
+ // look up localized list separator
+ if (c == _listSep)
+ {
+ _token = new Token(c, TKID.COMMA, TKTYPE.GROUP);
+ _ptr++;
+ return;
+ }
+
+ // look up single-char tokens on table
+ Token tk;
+ if (_tkTbl.TryGetValue(c, out tk))
+ {
+ // save token we found
+ _token = tk;
+ _ptr++;
+
+ // look for double-char tokens (special case)
+ if (_ptr < _len && (c == '>' || c == '<'))
+ {
+ if (_tkTbl.TryGetValue(_expr.Substring(_ptr - 1, 2), out tk))
+ {
+ _token = tk;
+ _ptr++;
+ }
+ }
+
+ // found token on the table
+ return;
+ }
+ }
+ }
+
+ // parse numbers
+ if (isDigit || c == _decimal)
+ {
+ var sci = false;
+ var pct = false;
+ var div = -1.0; // use double, not int (this may get really big)
+ var val = 0.0;
+ for (i = 0; i + _ptr < _len; i++)
+ {
+ c = _expr[_ptr + i];
+
+ // digits always OK
+ if (c >= '0' && c <= '9')
+ {
+ val = val * 10 + (c - '0');
+ if (div > -1)
+ {
+ div *= 10;
+ }
+ continue;
+ }
+
+ // one decimal is OK
+ if (c == _decimal && div < 0)
+ {
+ div = 1;
+ continue;
+ }
+
+ // scientific notation?
+ if ((c == 'E' || c == 'e') && !sci)
+ {
+ sci = true;
+ c = _expr[_ptr + i + 1];
+ if (c == '+' || c == '-') i++;
+ continue;
+ }
+
+ // percentage?
+ if (c == _percent)
+ {
+ pct = true;
+ i++;
+ break;
+ }
+
+ // end of literal
+ break;
+ }
+
+ // end of number, get value
+ if (!sci)
+ {
+ // much faster than ParseDouble
+ if (div > 1)
+ {
+ val /= div;
+ }
+ if (pct)
+ {
+ val /= 100.0;
+ }
+ }
+ else
+ {
+ var lit = _expr.Substring(_ptr, i);
+ val = ParseDouble(lit, _ci);
+ }
+
+ if (c != ':')
+ {
+ // build token
+ _token = new Token(val, TKID.ATOM, TKTYPE.LITERAL);
+
+ // advance pointer and return
+ _ptr += i;
+ return;
+ }
+ }
+
+ // parse strings
+ if (c == '\"')
+ {
+ // look for end quote, skip double quotes
+ for (i = 1; i + _ptr < _len; i++)
+ {
+ c = _expr[_ptr + i];
+ if (c != '\"') continue;
+ char cNext = i + _ptr < _len - 1 ? _expr[_ptr + i + 1] : ' ';
+ if (cNext != '\"') break;
+ i++;
+ }
+
+ // check that we got the end of the string
+ if (c != '\"')
+ {
+ Throw("Can't find final quote.");
+ }
+
+ // end of string
+ var lit = _expr.Substring(_ptr + 1, i - 1);
+ _ptr += i + 1;
+ _token = new Token(lit.Replace("\"\"", "\""), TKID.ATOM, TKTYPE.LITERAL);
+ return;
+ }
+
+ // parse dates (review)
+ if (c == '#')
+ {
+ // look for end #
+ for (i = 1; i + _ptr < _len; i++)
+ {
+ c = _expr[_ptr + i];
+ if (c == '#') break;
+ }
+
+ // check that we got the end of the date
+ if (c != '#')
+ {
+ Throw("Can't find final date delimiter ('#').");
+ }
+
+ // end of date
+ var lit = _expr.Substring(_ptr + 1, i - 1);
+ _ptr += i + 1;
+ _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL);
+ return;
+ }
+
+ // identifiers (functions, objects) must start with alpha or underscore
+ if (!isEnclosed && !isLetter && c != '_' && (_idChars == null || !_idChars.Contains(c)))
+ {
+ Throw("Identifier expected.");
+ }
+
+ // and must contain only letters/digits/_idChars
+ for (i = 1; i + _ptr < _len; i++)
+ {
+ c = _expr[_ptr + i];
+ isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');
+ isDigit = c >= '0' && c <= '9';
+
+ if (isEnclosed && c == matchingClosingSymbol)
+ {
+ isEnclosed = false;
+ matchingClosingSymbol = '\0';
+
+ i++;
+ c = _expr[_ptr + i];
+ isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');
+ isDigit = c >= '0' && c <= '9';
+ }
+
+ var disallowedSymbols = new List() { '\\', '/', '*', '[', ':', '?' };
+ if (isEnclosed && disallowedSymbols.Contains(c))
+ break;
+
+ var allowedSymbols = new List() { '_' };
+
+ if (!isLetter && !isDigit
+ && !(isEnclosed || allowedSymbols.Contains(c))
+ && (_idChars == null || !_idChars.Contains(c)))
+ break;
+ }
+
+ // got identifier
+ var id = _expr.Substring(_ptr, i);
+ _ptr += i;
+ _token = new Token(id, TKID.ATOM, TKTYPE.IDENTIFIER);
+ }
+
+ private static double ParseDouble(string str, CultureInfo ci)
+ {
+ if (str.Length > 0 && str[str.Length - 1] == ci.NumberFormat.PercentSymbol[0])
+ {
+ str = str.Substring(0, str.Length - 1);
+ return double.Parse(str, NumberStyles.Any, ci) / 100.0;
+ }
+ return double.Parse(str, NumberStyles.Any, ci);
+ }
+
+ private List GetParameters() // e.g. myfun(a, b, c+2)
+ {
+ // check whether next token is a (,
+ // restore state and bail if it's not
+ var pos = _ptr;
+ var tk = _token;
+ GetToken();
+ if (_token.ID != TKID.OPEN)
+ {
+ _ptr = pos;
+ _token = tk;
+ return null;
+ }
+
+ // check for empty Parameter list
+ pos = _ptr;
+ GetToken();
+ if (_token.ID == TKID.CLOSE)
+ {
+ return null;
+ }
+ _ptr = pos;
+
+ // get Parameters until we reach the end of the list
+ var parms = new List();
+ var expr = ParseExpression();
+ parms.Add(expr);
+ while (_token.ID == TKID.COMMA)
+ {
+ expr = ParseExpression();
+ parms.Add(expr);
+ }
+
+ // make sure the list was closed correctly
+ if (_token.ID != TKID.CLOSE)
+ {
+ Throw();
+ }
+
+ // done
+ return parms;
+ }
+
+ private Token GetMember()
+ {
+ // check whether next token is a MEMBER token ('.'),
+ // restore state and bail if it's not
+ var pos = _ptr;
+ var tk = _token;
+ GetToken();
+ if (_token.ID != TKID.PERIOD)
+ {
+ _ptr = pos;
+ _token = tk;
+ return null;
+ }
+
+ // skip member token
+ GetToken();
+ if (_token.Type != TKTYPE.IDENTIFIER)
+ {
+ Throw("Identifier expected");
+ }
+ return _token;
+ }
+
+ #endregion ** parser
+
+ //---------------------------------------------------------------------------
+
+ #region ** static helpers
+
+ private static void Throw()
+ {
+ Throw("Syntax error.");
+ }
+
+ private static void Throw(string msg)
+ {
+ throw new Exception(msg);
+ }
+
+ #endregion ** static helpers
+ }
+
+ ///
+ /// Delegate that represents CalcEngine functions.
+ ///
+ /// List of objects that represent the
+ /// parameters to be used in the function call.
+ /// The function result.
+ internal delegate object CalcEngineFunction(List parms);
+}
diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs
index b5853b8..46e96e1 100644
--- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs
+++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs
@@ -1,74 +1,74 @@
-using System;
-using System.Diagnostics;
-using System.Text.RegularExpressions;
-
-namespace ClosedXML.Excel.CalcEngine
-{
- internal class CalcEngineHelpers
- {
- internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce)
- {
- // safety...
- if (value == null)
- {
- return false;
- }
-
- // if criteria is a number, straight comparison
- if (criteria is double)
- {
- if (value is Double)
- return (double)value == (double)criteria;
- Double dValue;
- return Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria;
- }
-
- // convert criteria to string
- var cs = criteria as string;
- if (!string.IsNullOrEmpty(cs))
- {
- // if criteria is an expression (e.g. ">20"), use calc engine
- if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>')
- {
- // build expression
- var expression = string.Format("{0}{1}", value, cs);
-
- // add quotes if necessary
- var pattern = @"(\w+)(\W+)(\w+)";
- var m = Regex.Match(expression, pattern);
- if (m.Groups.Count == 4)
- {
- double d;
- if (!double.TryParse(m.Groups[1].Value, out d) ||
- !double.TryParse(m.Groups[3].Value, out d))
- {
- expression = string.Format("\"{0}\"{1}\"{2}\"",
- m.Groups[1].Value,
- m.Groups[2].Value,
- m.Groups[3].Value);
- }
- }
-
- // evaluate
- return (bool)ce.Evaluate(expression);
- }
-
- // if criteria is a regular expression, use regex
- if (cs.IndexOf('*') > -1)
- {
- var pattern = cs.Replace(@"\", @"\\");
- pattern = pattern.Replace(".", @"\");
- pattern = pattern.Replace("*", ".*");
- return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase);
- }
-
- // straight string comparison
- return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase);
- }
-
- // should never get here?
- Debug.Assert(false, "failed to evaluate criteria in SumIf");
- return false;
- }
- }
-}
+using System;
+using System.Diagnostics;
+using System.Text.RegularExpressions;
+
+namespace ClosedXML.Excel.CalcEngine
+{
+ internal class CalcEngineHelpers
+ {
+ internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce)
+ {
+ // safety...
+ if (value == null)
+ {
+ return false;
+ }
+
+ // if criteria is a number, straight comparison
+ if (criteria is double)
+ {
+ if (value is Double)
+ return (double)value == (double)criteria;
+ Double dValue;
+ return Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria;
+ }
+
+ // convert criteria to string
+ var cs = criteria as string;
+ if (!string.IsNullOrEmpty(cs))
+ {
+ // if criteria is an expression (e.g. ">20"), use calc engine
+ if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>')
+ {
+ // build expression
+ var expression = string.Format("{0}{1}", value, cs);
+
+ // add quotes if necessary
+ var pattern = @"(\w+)(\W+)(\w+)";
+ var m = Regex.Match(expression, pattern);
+ if (m.Groups.Count == 4)
+ {
+ double d;
+ if (!double.TryParse(m.Groups[1].Value, out d) ||
+ !double.TryParse(m.Groups[3].Value, out d))
+ {
+ expression = string.Format("\"{0}\"{1}\"{2}\"",
+ m.Groups[1].Value,
+ m.Groups[2].Value,
+ m.Groups[3].Value);
+ }
+ }
+
+ // evaluate
+ return (bool)ce.Evaluate(expression);
+ }
+
+ // if criteria is a regular expression, use regex
+ if (cs.IndexOf('*') > -1)
+ {
+ var pattern = cs.Replace(@"\", @"\\");
+ pattern = pattern.Replace(".", @"\");
+ pattern = pattern.Replace("*", ".*");
+ return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase);
+ }
+
+ // straight string comparison
+ return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase);
+ }
+
+ // should never get here?
+ Debug.Assert(false, "failed to evaluate criteria in SumIf");
+ return false;
+ }
+ }
+}
diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs
index 1b97924..1f11c8b 100644
--- a/ClosedXML/Excel/CalcEngine/Expression.cs
+++ b/ClosedXML/Excel/CalcEngine/Expression.cs
@@ -1,519 +1,415 @@
-using System;
-using System.Text;
-using System.Threading;
-using System.Collections;
-using System.Collections.Generic;
-using System.Diagnostics;
-using System.Globalization;
-using System.Reflection;
-using ClosedXML.Excel.CalcEngine;
-
-namespace ClosedXML.Excel.CalcEngine
-{
- ///
- /// Base class that represents parsed expressions.
- ///
- ///
- /// For example:
- ///
- /// Expression expr = scriptEngine.Parse(strExpression);
- /// object val = expr.Evaluate();
- ///
- ///
- internal class Expression : IComparable
- {
- //---------------------------------------------------------------------------
- #region ** fields
-
- internal Token _token;
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** ctors
-
- internal Expression()
- {
- _token = new Token(null, TKID.ATOM, TKTYPE.IDENTIFIER);
- }
- internal Expression(object value)
- {
- _token = new Token(value, TKID.ATOM, TKTYPE.LITERAL);
- }
- internal Expression(Token tk)
- {
- _token = tk;
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** object model
-
- public virtual object Evaluate()
- {
- if (_token.Type != TKTYPE.LITERAL)
- {
- throw new ArgumentException("Bad expression.");
- }
- return _token.Value;
- }
- public virtual Expression Optimize()
- {
- return this;
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** implicit converters
-
- public static implicit operator string(Expression x)
- {
- var v = x.Evaluate();
- return v == null ? string.Empty : v.ToString();
- }
- public static implicit operator double(Expression x)
- {
- // evaluate
- var v = x.Evaluate();
-
- // handle doubles
- if (v is double)
- {
- return (double)v;
- }
-
- // handle booleans
- if (v is bool)
- {
- return (bool)v ? 1 : 0;
- }
-
- // handle dates
- if (v is DateTime)
- {
- return ((DateTime)v).ToOADate();
- }
-
- // handle nulls
- if (v == null || v is string)
- {
- return 0;
- }
-
- // handle everything else
- CultureInfo _ci = Thread.CurrentThread.CurrentCulture;
- return (double)Convert.ChangeType(v, typeof(double), _ci);
- }
- public static implicit operator bool(Expression x)
- {
- // evaluate
- var v = x.Evaluate();
-
- // handle booleans
- if (v is bool)
- {
- return (bool)v;
- }
-
- // handle nulls
- if (v == null)
- {
- return false;
- }
-
- // handle doubles
- if (v is double)
- {
- return (double)v == 0 ? false : true;
- }
-
- // handle everything else
- return (double)x == 0 ? false : true;
- }
- public static implicit operator DateTime(Expression x)
- {
- // evaluate
- var v = x.Evaluate();
-
- // handle dates
- if (v is DateTime)
- {
- return (DateTime)v;
- }
-
- // handle doubles
- if (v is double || v is int)
- {
- return DateTime.FromOADate((double)x);
- }
-
- // handle everything else
- CultureInfo _ci = Thread.CurrentThread.CurrentCulture;
- return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci);
- }
-
- #endregion
-
- //---------------------------------------------------------------------------
- #region ** IComparable
-
- public int CompareTo(Expression other)
- {
- // get both values
- var c1 = this.Evaluate() as IComparable;
- var c2 = other.Evaluate() as IComparable;
-
- // handle nulls
- if (c1 == null && c2 == null)
- {
- return 0;
- }
- if (c2 == null)
- {
- return -1;
- }
- if (c1 == null)
- {
- return +1;
- }
-
- // make sure types are the same
- if (c1.GetType() != c2.GetType())
- {
- try
- {
- if (c1 is DateTime)
- c2 = ((DateTime)other);
- else if (c2 is DateTime)
- c1 = ((DateTime)this);
- else
- c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable;
- }
- catch (InvalidCastException) { return -1; }
- catch (FormatException) { return -1; }
- catch (OverflowException) { return -1; }
- catch (ArgumentNullException) { return -1; }
- }
-
- // compare
- return c1.CompareTo(c2);
- }
-
- #endregion
- }
- ///
- /// Unary expression, e.g. +123
- ///
- class UnaryExpression : Expression
- {
- // ** fields
- Expression _expr;
-
- // ** ctor
- public UnaryExpression(Token tk, Expression expr) : base(tk)
- {
- _expr = expr;
- }
-
- // ** object model
- override public object Evaluate()
- {
- switch (_token.ID)
- {
- case TKID.ADD:
- return +(double)_expr;
- case TKID.SUB:
- return -(double)_expr;
- }
- throw new ArgumentException("Bad expression.");
- }
- public override Expression Optimize()
- {
- _expr = _expr.Optimize();
- return _expr._token.Type == TKTYPE.LITERAL
- ? new Expression(this.Evaluate())
- : this;
- }
- }
- ///
- /// Binary expression, e.g. 1+2
- ///
- class BinaryExpression : Expression
- {
- // ** fields
- Expression _lft;
- Expression _rgt;
-
- // ** ctor
- public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk)
- {
- _lft = exprLeft;
- _rgt = exprRight;
- }
-
- // ** object model
- override public object Evaluate()
- {
- // handle comparisons
- if (_token.Type == TKTYPE.COMPARE)
- {
- var cmp = _lft.CompareTo(_rgt);
- switch (_token.ID)
- {
- case TKID.GT: return cmp > 0;
- case TKID.LT: return cmp < 0;
- case TKID.GE: return cmp >= 0;
- case TKID.LE: return cmp <= 0;
- case TKID.EQ: return cmp == 0;
- case TKID.NE: return cmp != 0;
- }
- }
-
- // handle everything else
- switch (_token.ID)
- {
- case TKID.CONCAT:
- return (string)_lft + (string)_rgt;
- case TKID.ADD:
- return (double)_lft + (double)_rgt;
- case TKID.SUB:
- return (double)_lft - (double)_rgt;
- case TKID.MUL:
- return (double)_lft * (double)_rgt;
- case TKID.DIV:
- return (double)_lft / (double)_rgt;
- case TKID.DIVINT:
- return (double)(int)((double)_lft / (double)_rgt);
- case TKID.MOD:
- return (double)(int)((double)_lft % (double)_rgt);
- case TKID.POWER:
- var a = (double)_lft;
- var b = (double)_rgt;
- if (b == 0.0) return 1.0;
- if (b == 0.5) return Math.Sqrt(a);
- if (b == 1.0) return a;
- if (b == 2.0) return a * a;
- if (b == 3.0) return a * a * a;
- if (b == 4.0) return a * a * a * a;
- return Math.Pow((double)_lft, (double)_rgt);
- }
- throw new ArgumentException("Bad expression.");
- }
- public override Expression Optimize()
- {
- _lft = _lft.Optimize();
- _rgt = _rgt.Optimize();
- return _lft._token.Type == TKTYPE.LITERAL && _rgt._token.Type == TKTYPE.LITERAL
- ? new Expression(this.Evaluate())
- : this;
- }
- }
- ///
- /// Function call expression, e.g. sin(0.5)
- ///
- class FunctionExpression : Expression
- {
- // ** fields
- FunctionDefinition _fn;
- List _parms;
-
- // ** ctor
- internal FunctionExpression()
- {
- }
- public FunctionExpression(FunctionDefinition function, List parms)
- {
- _fn = function;
- _parms = parms;
- }
-
- // ** object model
- override public object Evaluate()
- {
- return _fn.Function(_parms);
- }
- public override Expression Optimize()
- {
- bool allLits = true;
- if (_parms != null)
- {
- for (int i = 0; i < _parms.Count; i++)
- {
- var p = _parms[i].Optimize();
- _parms[i] = p;
- if (p._token.Type != TKTYPE.LITERAL)
- {
- allLits = false;
- }
- }
- }
- return allLits
- ? new Expression(this.Evaluate())
- : this;
- }
- }
- ///
- /// Simple variable reference.
- ///
- class VariableExpression : Expression
- {
- Dictionary _dct;
- string _name;
-
- public VariableExpression(Dictionary dct, string name)
- {
- _dct = dct;
- _name = name;
- }
- public override object Evaluate()
- {
- return _dct[_name];
- }
- }
- ///
- /// Expression based on an object's properties.
- ///
- class BindingExpression : Expression
- {
- CalcEngine _ce;
- CultureInfo _ci;
- List _bindingPath;
-
- // ** ctor
- internal BindingExpression(CalcEngine engine, List