diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index 46e96e1..83d872a 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -1,11 +1,28 @@ using System; +using System.Collections.Generic; using System.Diagnostics; +using System.Linq; using System.Text.RegularExpressions; namespace ClosedXML.Excel.CalcEngine { internal class CalcEngineHelpers { + private static Lazy>> patternReplacements = + new Lazy>>(() => + { + var patternReplacements = new Dictionary>(); + // key: the literal string to match + // value: a tuple: first item: the search pattern, second item: the replacement + patternReplacements.Add(@"~~", new Tuple(@"~~", "~")); + patternReplacements.Add(@"~*", new Tuple(@"~\*", @"\*")); + patternReplacements.Add(@"~?", new Tuple(@"~\?", @"\?")); + patternReplacements.Add(@"?", new Tuple(@"\?", ".?")); + patternReplacements.Add(@"*", new Tuple(@"\*", ".*")); + + return patternReplacements; + }); + internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... @@ -25,8 +42,11 @@ // convert criteria to string var cs = criteria as string; - if (!string.IsNullOrEmpty(cs)) + if (cs != null) { + if (cs == "") + return cs.Equals(value); + // if criteria is an expression (e.g. ">20"), use calc engine if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') { @@ -54,11 +74,17 @@ } // if criteria is a regular expression, use regex - if (cs.IndexOf('*') > -1) + if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { - var pattern = cs.Replace(@"\", @"\\"); - pattern = pattern.Replace(".", @"\"); - pattern = pattern.Replace("*", ".*"); + var pattern = Regex.Replace( + cs, + "(" + String.Join( + "|", + patternReplacements.Value.Values.Select(t => t.Item1)) + + ")", + m => patternReplacements.Value[m.Value].Item2); + pattern = $"^{pattern}$"; + return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 160af64..188dbb4 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -151,11 +151,8 @@ var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { - if (!IsBlank(value)) - { - if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) - cnt++; - } + if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) + cnt++; } } return cnt; diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index c3cd7ce..ad9e5dc 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -217,6 +217,82 @@ Assert.AreEqual(0.7, actual, tolerance); } + /// + /// refers to Example 1 from the Excel documentation, + /// + /// + /// + /// + [TestCase(63000, "SUMIF(A1:A4,\">160000\", B1:B4)")] + [TestCase(900000, "SUMIF(A1:A4,\">160000\")")] + [TestCase(21000, "SUMIF(A1:A4, 300000, B1:B4)")] + [TestCase(28000, "SUMIF(A1:A4, \">\" &C1, B1:B4)")] + public void SumIf_ReturnsCorrectValues_ReferenceExample1FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = 100000; + ws.Cell(1, 2).Value = 7000; + ws.Cell(2, 1).Value = 200000; + ws.Cell(2, 2).Value = 14000; + ws.Cell(3, 1).Value = 300000; + ws.Cell(3, 2).Value = 21000; + ws.Cell(4, 1).Value = 400000; + ws.Cell(4, 2).Value = 28000; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to Example 2 from the Excel documentation, + /// + /// + /// + /// + [TestCase(2000, "SUMIF(A2:A7,\"Fruits\", C2:C7)")] + [TestCase(12000, "SUMIF(A2:A7,\"Vegetables\", C2:C7)")] + [TestCase(4300, "SUMIF(B2:B7, \"*es\", C2:C7)")] + [TestCase(400, "SUMIF(A2:A7, \"\", C2:C7)")] + public void SumIf_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(2, 1).Value = "Vegetables"; + ws.Cell(3, 1).Value = "Vegetables"; + ws.Cell(4, 1).Value = "Fruits"; + ws.Cell(5, 1).Value = ""; + ws.Cell(6, 1).Value = "Vegetables"; + ws.Cell(7, 1).Value = "Fruits"; + + ws.Cell(2, 2).Value = "Tomatoes"; + ws.Cell(3, 2).Value = "Celery"; + ws.Cell(4, 2).Value = "Oranges"; + ws.Cell(5, 2).Value = "Butter"; + ws.Cell(6, 2).Value = "Carrots"; + ws.Cell(7, 2).Value = "Apples"; + + ws.Cell(2, 3).Value = 2300; + ws.Cell(3, 3).Value = 5500; + ws.Cell(4, 3).Value = 800; + ws.Cell(5, 3).Value = 400; + ws.Cell(6, 3).Value = 4200; + ws.Cell(7, 3).Value = 1200; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + [Test] public void SumProduct() { diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs index 2550868..24931ed 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -91,6 +91,49 @@ Assert.AreEqual(24, value); } + [TestCase(@"=COUNTIF(Data!E:E, ""J*"")", 13)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i*"")", 21)] + [TestCase(@"=COUNTIF(Data!E:E, ""*in*"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i*l"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i?e*"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*o??s*"")", 10)] + [TestCase(@"=COUNTIF(Data!X1:X1000, """")", 1000)] + [TestCase(@"=COUNTIF(Data!E1:E44, """")", 1)] + public void CountIf_ConditionWithWildcards(string formula, int expectedResult) + { + var ws = workbook.Worksheets.First(); + + int value = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, value); + } + + [TestCase("x", @"=COUNTIF(A1:A1, ""?"")", 1)] + [TestCase("x", @"=COUNTIF(A1:A1, ""~?"")", 0)] + [TestCase("?", @"=COUNTIF(A1:A1, ""~?"")", 1)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~?"")", 0)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~~~?"")", 1)] + [TestCase("?", @"=COUNTIF(A1:A1, ""~~?"")", 0)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~~?"")", 1)] + [TestCase("~x", @"=COUNTIF(A1:A1, ""~~?"")", 1)] + [TestCase("*", @"=COUNTIF(A1:A1, ""~*"")", 1)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~*"")", 0)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~~~*"")", 1)] + [TestCase("*", @"=COUNTIF(A1:A1, ""~~*"")", 0)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + [TestCase("~x", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + [TestCase("~xyz", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + public void CountIf_MoreWildcards(string cellContent, string formula, int expectedResult) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = cellContent; + + Assert.AreEqual(expectedResult, (double)ws.Evaluate(formula)); + } + } + [OneTimeTearDown] public void Dispose() {