diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index e1b6e51..c45be92 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -51,7 +51,9 @@ return cs.Equals(value); // if criteria is an expression (e.g. ">20"), use calc engine - if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') + if ((cs[0] == '=' && cs.IndexOfAny(new[] { '*', '?' }) < 0) + || cs[0] == '<' + || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); @@ -79,6 +81,8 @@ // if criteria is a regular expression, use regex if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { + if (cs[0] == '=') cs = cs.Substring(1); + var pattern = Regex.Replace( cs, "(" + String.Join( diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index dfd9d25..233a7c8 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -378,18 +378,18 @@ int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want. // prepare criteria-parameters: - var criteriaRanges = new Tuple>[numberOfCriteria]; - for(int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + var criteriaRanges = new Tuple>[numberOfCriteria]; + for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) { - var criterion = p[criteriaPair * 2 + 1].Evaluate(); - var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; - var criteriaRangeValues = new List(); - foreach (var value in criteriaRange) - { - criteriaRangeValues.Add(value); - } + var criteriaRange = p[criteriaPair * 2 + 1] as IEnumerable; - criteriaRanges[criteriaPair] = new Tuple>( + if (criteriaRange == null) + throw new CellReferenceException($"Expected parameter {criteriaPair * 2 + 2} to be a range"); + + var criterion = p[criteriaPair * 2 + 2].Evaluate(); + var criteriaRangeValues = criteriaRange.Cast().ToList(); + + criteriaRanges[criteriaPair] = new Tuple>( criterion, criteriaRangeValues); } @@ -398,7 +398,7 @@ { bool shouldUseValue = true; - foreach(var criteriaPair in criteriaRanges) + foreach (var criteriaPair in criteriaRanges) { if (!CalcEngineHelpers.ValueSatisfiesCriteria( criteriaPair.Item2[i], @@ -630,7 +630,6 @@ else throw new NumberException(); - n = (int)p[0]; k = (int)p[1]; @@ -670,11 +669,9 @@ if (!(input is long || input is int || input is byte || input is double || input is float)) throw new CellValueException(); - var num = Math.Floor((double)input); double fact = 1.0; - if (num < 0) throw new NumberException(); @@ -1031,4 +1028,4 @@ return m.Invert().mat; } } -} +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 2bdb25a..b611dec 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -702,7 +702,6 @@ Assert.Throws(() => XLWorkbook.EvaluateExpr(@"CSC(0)")); } - [TestCase("FF", 16, 255)] [TestCase("111", 2, 7)] [TestCase("zap", 36, 45745)] @@ -1152,7 +1151,6 @@ [Test] public void SumIf_ReturnsCorrectValues_WhenFormulaBelongToSameRange() { - using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Data"); @@ -1163,11 +1161,10 @@ new { Id = "C", Value = 2}, new { Id = "A", Value = 1}, new { Id = "B", Value = 4}, - }; ws.Cell("A1").InsertTable(data); ws.Cell("A7").SetValue("Sum A"); - // SUMIF formula + // SUMIF formula var formula = "=SUMIF(A:A,\"=A\",B:B)"; ws.Cell("B7").SetFormulaA1(formula); var value = ws.Cell("B7").Value; @@ -1175,17 +1172,16 @@ } } - /// /// refers to Example 1 to SumIf from the Excel documentation. - /// As SumIfs should behave the same if called with three parameters, we can take that example here again. + /// As SumIfs should behave the same if called with three parameters, but in a different order /// /// /// /// - [TestCase(63000, "SUMIFS(B1:B4, \">160000\", A1:A4)")] - [TestCase(21000, "SUMIFS(B1:B4, 300000, A1:A4)")] - [TestCase(28000, "SUMIFS(B1:B4, \">\" &C1, A1:A4)")] + [TestCase(63000, "SUMIFS(B1:B4, A1:A4, \">160000\")")] + [TestCase(21000, "SUMIFS(B1:B4, A1:A4, 300000)")] + [TestCase(28000, "SUMIFS(B1:B4, A1:A4, \">\" &C1)")] public void SumIfs_ReturnsCorrectValues_ReferenceExampleForSumIf1FromMicrosoft(int expectedOutcome, string formula) { using (var wb = new XLWorkbook()) @@ -1213,13 +1209,13 @@ /// As SumIfs should behave the same if called with three parameters, we can take that example here again. /// /// - /// + /// /// - [TestCase(2000, "SUMIFS(C2:C7, \"Fruits\", A2:A7)")] - [TestCase(12000, "SUMIFS(C2:C7, \"Vegetables\", A2:A7)")] - [TestCase(4300, "SUMIFS(C2:C7, \"*es\", B2:B7)")] - [TestCase(400, "SUMIFS(C2:C7, \"\", A2:A7)")] - public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + [TestCase(2000, "SUMIFS(C2:C7, A2:A7, \"Fruits\")")] + [TestCase(12000, "SUMIFS(C2:C7, A2:A7, \"Vegetables\")")] + [TestCase(4300, "SUMIFS(C2:C7, B2:B7, \"*es\")")] + [TestCase(400, "SUMIFS(C2:C7, A2:A7, \"\")")] + public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedResult, string formula) { using (var wb = new XLWorkbook()) { @@ -1249,7 +1245,8 @@ ws.Cell(1, 3).Value = 300000; - Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + var actualResult = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, actualResult); } } @@ -1260,7 +1257,7 @@ [TestCase(20, "=SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")")] [TestCase(30, "=SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")")] public void SumIfs_ReturnsCorrectValues_ReferenceExampleFromMicrosoft( - int result, + int expectedResult, string formula) { using (var wb = new XLWorkbook()) @@ -1268,41 +1265,53 @@ wb.ReferenceStyle = XLReferenceStyle.A1; var ws = wb.AddWorksheet("Sheet1"); - ws.Cell(1, 1).Value = 5; - ws.Cell(1, 2).Value = "Apples"; - ws.Cell(1, 3).Value = "Tom"; + var row = 2; - ws.Cell(2, 1).Value = 4; - ws.Cell(2, 2).Value = "Apples"; - ws.Cell(2, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 5; + ws.Cell(row, 2).Value = "Apples"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(3, 1).Value = 15; - ws.Cell(3, 2).Value = "Artichokes"; - ws.Cell(3, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 4; + ws.Cell(row, 2).Value = "Apples"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(4, 1).Value = 3; - ws.Cell(4, 2).Value = "Artichokes"; - ws.Cell(4, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 15; + ws.Cell(row, 2).Value = "Artichokes"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(5, 1).Value = 22; - ws.Cell(5, 2).Value = "Bananas"; - ws.Cell(5, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 3; + ws.Cell(row, 2).Value = "Artichokes"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(6, 1).Value = 12; - ws.Cell(6, 2).Value = "Bananas"; - ws.Cell(6, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 22; + ws.Cell(row, 2).Value = "Bananas"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(7, 1).Value = 10; - ws.Cell(7, 2).Value = "Carrots"; - ws.Cell(7, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 12; + ws.Cell(row, 2).Value = "Bananas"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(8, 1).Value = 33; - ws.Cell(8, 2).Value = "Carrots"; - ws.Cell(8, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 10; + ws.Cell(row, 2).Value = "Carrots"; + ws.Cell(row, 3).Value = "Tom"; + row++; + + ws.Cell(row, 1).Value = 33; + ws.Cell(row, 2).Value = "Carrots"; + ws.Cell(row, 3).Value = "Sarah"; + + var actualResult = ws.Evaluate(formula).CastTo(); + + Assert.AreEqual(expectedResult, actualResult, tolerance); } } - [Test] public void SumProduct() { @@ -1356,7 +1365,6 @@ { var actual = XLWorkbook.EvaluateExpr(string.Format(@"INT({0})", input.ToString(CultureInfo.InvariantCulture))); Assert.AreEqual(expected, actual); - } } }