diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 2d579e8..af33d8b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -44,11 +44,11 @@ //ce.RegisterFunction("MMULT", MMult, 1); ce.RegisterFunction("MOD", 2, Mod); ce.RegisterFunction("MROUND", 2, MRound); - //ce.RegisterFunction("MULTINOMIAL", Multinomial, 1); - //ce.RegisterFunction("ODD", Odd, 1); + ce.RegisterFunction("MULTINOMIAL", 1, 255, Multinomial); + ce.RegisterFunction("ODD", 1, Odd); ce.RegisterFunction("PI", 0, Pi); ce.RegisterFunction("POWER", 2, Power); - //ce.RegisterFunction("PRODUCT", Product, 1); + ce.RegisterFunction("PRODUCT", 1, 255, Product); //ce.RegisterFunction("QUOTIENT", Quotient, 1); //ce.RegisterFunction("RADIANS", Radians, 1); ce.RegisterFunction("RAND", 0, Rand); @@ -370,11 +370,7 @@ return p[0] * (180.0 / Math.PI); } - private static object Even(List p) - { - var num = Math.Ceiling(p[0]); - return Math.Abs(num % 2) < XLHelper.Epsilon ? num : num + 1; - } + private static object Fact(List p) { @@ -430,25 +426,82 @@ private static object MRound(List p) { - Double number = p[0]; - Double roundingInterval = p[1]; - - if (roundingInterval == 0) { return 0; } + var n = (Decimal)(Double)p[0]; + var k = (Decimal)(Double)p[1]; - Double intv = Math.Abs(roundingInterval); - Double modulo = number % intv; - if ((intv - modulo) == modulo) + var mod = n % k; + var mult = Math.Floor(n / k); + var div = k / 2; + + if (Math.Abs(mod - div) <= (Decimal)XLHelper.Epsilon) return (k * mult) + k; + + return k * mult; + } + + private static object Multinomial(List p) + { + return Multinomial(p.Select(v => (double)v).ToList()); + } + + private static double Multinomial(List numbers) + { + double numbersSum = 0; + foreach (var number in numbers) + numbersSum += number; + + double maxNumber = numbers.Max(); + var denomFactorPowers = new double[(uint)numbers.Max() + 1]; + foreach (var number in numbers) + for (int i = 2; i <= number; i++) + denomFactorPowers[i]++; + for (int i = 2; i < denomFactorPowers.Length; i++) + denomFactorPowers[i]--; // reduce with nominator; + + int currentFactor = 2; + double currentPower = 1; + double result = 1; + for (double i = maxNumber + 1; i <= numbersSum; i++) { - var temp = (number - modulo).ToString("#.##################"); - if (temp.Length != 0 && temp[temp.Length - 1] % 2 == 0) modulo *= -1; + double tempDenom = 1; + while (tempDenom < result && currentFactor < denomFactorPowers.Length) + { + if (currentPower > denomFactorPowers[currentFactor]) + { + currentFactor++; + currentPower = 1; + } + else + { + tempDenom *= currentFactor; + currentPower++; + } + } + result = result / tempDenom * i; } - else if ((intv - modulo) < modulo) - modulo = (intv - modulo); - else - modulo *= -1; - return number + modulo; + return result; + } + private static object Odd(List p) + { + var num = (int)Math.Ceiling(p[0]); + var addValue = num >= 0 ? 1 : -1; + return XLMath.IsOdd(num) ? num : num + addValue; + } + + private static object Even(List p) + { + var num = (int)Math.Ceiling(p[0]); + var addValue = num >= 0 ? 1 : -1; + return XLMath.IsEven(num) ? num : num + addValue; + } + + private static object Product(List p) + { + if (p.Count == 0) return 0; + Double total = 1; + p.ForEach(v => total *= v); + return total; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs index b328d28..9a3856c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs @@ -88,5 +88,14 @@ if (k == 0) return 1; return n * Combin(n - 1, k - 1) / k; } + + public static Boolean IsEven(Int32 value) + { + return Math.Abs(value % 2) < XLHelper.Epsilon; + } + public static Boolean IsOdd(Int32 value) + { + return Math.Abs(value % 2) > XLHelper.Epsilon; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 84a9139..44771ba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -161,6 +161,8 @@ } return intToString[value]; } + + } public static class FontBaseExtensions diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index e5d6375..71c551e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -76,6 +76,7 @@ + @@ -84,7 +85,7 @@ - + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index ffb0143..f547b9f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -14,14 +14,6 @@ [TestClass] public class FunctionsTests { - [TestMethod] - public void Even() - { - var actual1 = XLWorkbook.EvaluateExpr("Even(1.5)"); - Assert.AreEqual(2.0, actual1); - var actual2 = XLWorkbook.EvaluateExpr("Even(2.01)"); - Assert.AreEqual(4.0, actual2); - } [TestMethod] public void Combin() @@ -107,14 +99,81 @@ [TestMethod] public void MRound() { - //var actual = XLWorkbook.EvaluateExpr("MRound(10, 3)"); - //Assert.AreEqual(9.0, actual); + var actual = XLWorkbook.EvaluateExpr("MRound(10, 3)"); + Assert.AreEqual(9m, actual); - //var actual1 = XLWorkbook.EvaluateExpr("MRound(-10, -10)"); - //Assert.AreEqual(-9.0, actual1); + var actual3 = XLWorkbook.EvaluateExpr("MRound(10.5, 3)"); + Assert.AreEqual(12m, actual3); - //var actual2 = XLWorkbook.EvaluateExpr("MRound(1.3, 0.2)"); - //Assert.AreEqual(1.4, actual2); + var actual4 = XLWorkbook.EvaluateExpr("MRound(10.4, 3)"); + Assert.AreEqual(9m, actual4); + + var actual1 = XLWorkbook.EvaluateExpr("MRound(-10, -3)"); + Assert.AreEqual(-9m, actual1); + + var actual2 = XLWorkbook.EvaluateExpr("MRound(1.3, 0.2)"); + Assert.AreEqual(1.4m, actual2); + } + + [TestMethod] + public void Multinomial() + { + var actual = XLWorkbook.EvaluateExpr("Multinomial(2,3,4)"); + Assert.AreEqual(1260.0, actual); + } + + [TestMethod] + public void Odd() + { + var actual = XLWorkbook.EvaluateExpr("Odd(1.5)"); + Assert.AreEqual(3, actual); + + var actual1 = XLWorkbook.EvaluateExpr("Odd(3)"); + Assert.AreEqual(3, actual1); + + var actual2 = XLWorkbook.EvaluateExpr("Odd(2)"); + Assert.AreEqual(3, actual2); + + var actual3 = XLWorkbook.EvaluateExpr("Odd(-1)"); + Assert.AreEqual(-1, actual3); + + var actual4 = XLWorkbook.EvaluateExpr("Odd(-2)"); + Assert.AreEqual(-3, actual4); + + actual = XLWorkbook.EvaluateExpr("Odd(0)"); + Assert.AreEqual(1, actual); + } + + [TestMethod] + public void Even() + { + var actual = XLWorkbook.EvaluateExpr("Even(3)"); + Assert.AreEqual(4, actual); + + actual = XLWorkbook.EvaluateExpr("Even(2)"); + Assert.AreEqual(2, actual); + + actual = XLWorkbook.EvaluateExpr("Even(-1)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr("Even(-2)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr("Even(0)"); + Assert.AreEqual(0, actual); + + actual = XLWorkbook.EvaluateExpr("Even(1.5)"); + Assert.AreEqual(2, actual); + + actual = XLWorkbook.EvaluateExpr("Even(2.01)"); + Assert.AreEqual(4, actual); + } + + [TestMethod] + public void Product() + { + var actual = XLWorkbook.EvaluateExpr("Product(2,3,4)"); + Assert.AreEqual(24.0, actual); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/XLMathTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/XLMathTests.cs new file mode 100644 index 0000000..0f6eb6c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/XLMathTests.cs @@ -0,0 +1,29 @@ +using System; +using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Functions; +using Microsoft.VisualStudio.TestTools.UnitTesting; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + /// + /// Summary description for UnitTest1 + /// + [TestClass] + public class Extensions + { + + [TestMethod] + public void IsEven() + { + Assert.IsTrue(XLMath.IsEven(2)); + Assert.IsFalse(XLMath.IsEven(3)); + } + + [TestMethod] + public void IsOdd() + { + Assert.IsTrue(XLMath.IsOdd(3)); + Assert.IsFalse(XLMath.IsOdd(2)); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/Extensions.cs deleted file mode 100644 index e336112..0000000 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/Extensions.cs +++ /dev/null @@ -1,28 +0,0 @@ -using System; -using System.Text; -using System.Collections.Generic; -using System.Linq; -using ClosedXML.Excel; -using Microsoft.VisualStudio.TestTools.UnitTesting; - -namespace ClosedXML_Tests.Excel -{ - /// - /// Summary description for UnitTest1 - /// - [TestClass] - public class Extensions - { - - [TestMethod] - public void FixNewLines() - { - Assert.AreEqual("\n".FixNewLines(), Environment.NewLine); - Assert.AreEqual("\r\n".FixNewLines(), Environment.NewLine); - Assert.AreEqual("\rS\n".FixNewLines(), "\rS" + Environment.NewLine); - Assert.AreEqual("\r\n\n".FixNewLines(), Environment.NewLine + Environment.NewLine); - } - - - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExtensionsTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExtensionsTests.cs new file mode 100644 index 0000000..2110151 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExtensionsTests.cs @@ -0,0 +1,28 @@ +using System; +using System.Text; +using System.Collections.Generic; +using System.Linq; +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; + +namespace ClosedXML_Tests.Excel +{ + /// + /// Summary description for UnitTest1 + /// + [TestClass] + public class Extensions + { + + [TestMethod] + public void FixNewLines() + { + Assert.AreEqual("\n".FixNewLines(), Environment.NewLine); + Assert.AreEqual("\r\n".FixNewLines(), Environment.NewLine); + Assert.AreEqual("\rS\n".FixNewLines(), "\rS" + Environment.NewLine); + Assert.AreEqual("\r\n\n".FixNewLines(), Environment.NewLine + Environment.NewLine); + } + + + } +}