diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index c4e86ca..5a25661 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,8 +1,8 @@ +using ClosedXML.Excel.CalcEngine.Functions; using System; using System.Collections; using System.Collections.Generic; using System.Linq; -using ClosedXML.Excel.CalcEngine.Functions; namespace ClosedXML.Excel.CalcEngine { @@ -29,7 +29,8 @@ ce.RegisterFunction("EXP", 1, Exp); ce.RegisterFunction("FACT", 1, Fact); ce.RegisterFunction("FACTDOUBLE", 1, FactDouble); - ce.RegisterFunction("FLOOR", 1, Floor); + ce.RegisterFunction("FLOOR", 1, 2, Floor); + //ce.RegisterFunction("FLOOR.MATH", 1, 3, FloorMath); ce.RegisterFunction("GCD", 1, 255, Gcd); ce.RegisterFunction("INT", 1, Int); ce.RegisterFunction("LCM", 1, 255, Lcm); @@ -120,12 +121,27 @@ private static object Floor(List p) { - return Math.Floor(p[0]); + double number = p[0]; + double significance = 1; + if (p.Count > 1) + significance = p[1]; + + if (significance < 0) + { + number = -number; + significance = -significance; + + return -Math.Floor(number / significance) * significance; + } + else if (significance == 1) + return Math.Floor(number); + else + return Math.Floor(number / significance) * significance; } private static object Int(List p) { - return (int) ((double) p[0]); + return (int)((double)p[0]); } private static object Ln(List p) @@ -135,7 +151,7 @@ private static object Log(List p) { - var lbase = p.Count > 1 ? (double) p[1] : 10; + var lbase = p.Count > 1 ? (double)p[1] : 10; return Math.Log(p[0], lbase); } @@ -161,7 +177,7 @@ private static object RandBetween(List p) { - return _rnd.Next((int) (double) p[0], (int) (double) p[1]); + return _rnd.Next((int)(double)p[0], (int)(double)p[1]); } private static object Sign(List p) @@ -240,42 +256,42 @@ private static object Trunc(List p) { - return (double) (int) ((double) p[0]); + return (double)(int)((double)p[0]); } public static double DegreesToRadians(double degrees) { - return (Math.PI/180.0)*degrees; + return (Math.PI / 180.0) * degrees; } public static double RadiansToDegrees(double radians) { - return (180.0/Math.PI)*radians; + return (180.0 / Math.PI) * radians; } public static double GradsToRadians(double grads) { - return (grads/200.0)*Math.PI; + return (grads / 200.0) * Math.PI; } public static double RadiansToGrads(double radians) { - return (radians/Math.PI)*200.0; + return (radians / Math.PI) * 200.0; } public static double DegreesToGrads(double degrees) { - return (degrees/9.0)*10.0; + return (degrees / 9.0) * 10.0; } public static double GradsToDegrees(double grads) { - return (grads/10.0)*9.0; + return (grads / 10.0) * 9.0; } public static double ASinh(double x) { - return (Math.Log(x + Math.Sqrt(x*x + 1.0))); + return (Math.Log(x + Math.Sqrt(x * x + 1.0))); } private static object Acosh(List p) @@ -295,8 +311,8 @@ private static object Combin(List p) { - Int32 n = (int) p[0]; - Int32 k = (int) p[1]; + Int32 n = (int)p[0]; + Int32 k = (int)p[1]; return XLMath.Combin(n, k); } @@ -305,8 +321,6 @@ return p[0] * (180.0 / Math.PI); } - - private static object Fact(List p) { var num = Math.Floor(p[0]); @@ -348,7 +362,7 @@ private static int Lcm(int a, int b) { if (a == 0 || b == 0) return 0; - return a * ( b / Gcd(a, b)); + return a * (b / Gcd(a, b)); } private static object Mod(List p) @@ -479,7 +493,6 @@ temp = Math.Round(temp, 0, MidpointRounding.AwayFromZero); return temp * Math.Pow(10, digits); } - } private static object RoundDown(List p) @@ -512,7 +525,7 @@ var obj = p[3] as XObjectExpression; if (obj == null) - return p[3] * Math.Pow(x , n); + return p[3] * Math.Pow(x, n); Double total = 0; Int32 i = 0; @@ -540,26 +553,37 @@ { case 1: return tally.Average(); + case 2: return tally.Count(true); + case 3: return tally.Count(false); + case 4: return tally.Max(); + case 5: return tally.Min(); + case 6: return tally.Product(); + case 7: return tally.Std(); + case 8: return tally.StdP(); + case 9: return tally.Sum(); + case 10: return tally.Var(); + case 11: return tally.VarP(); + default: throw new ArgumentException("Function not supported."); } @@ -591,19 +615,18 @@ } } - return C; } private static double[,] GetArray(Expression expression) { var oExp1 = expression as XObjectExpression; - if (oExp1 == null) return new [,]{{(Double)expression}}; + if (oExp1 == null) return new[,] { { (Double)expression } }; var range = (oExp1.Value as CellRangeReference).Range; var rowCount = range.RowCount(); var columnCount = range.ColumnCount(); - var arr = new double[rowCount,columnCount]; + var arr = new double[rowCount, columnCount]; for (int row = 0; row < rowCount; row++) { diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 487ea3a..9766852 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -80,6 +80,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs new file mode 100644 index 0000000..6d30f1f --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -0,0 +1,65 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class MathTrigTests + { + [Test] + public void Floor() + { + Object actual; + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.2)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.7)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-1.7)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.2, 1)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.7, 1)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-1.7, 1)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(0.4, 2)"); + Assert.AreEqual(0, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(2.7, 2)"); + Assert.AreEqual(2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(7.8, 2)"); + Assert.AreEqual(6, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-5.5, -2)"); + Assert.AreEqual(-4, actual); + } + + //[Test] + // Functions have to support a period first before we can implement this + public void FloorMath() + { + Object actual; + + actual = XLWorkbook.EvaluateExpr(@"FLOOR.MATH(24.3, 5)"); + Assert.AreEqual(20, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR.MATH(6.7)"); + Assert.AreEqual(6, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-8.1, 2)"); + Assert.AreEqual(-10, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, 2, -1)"); + Assert.AreEqual(-4, actual); + } + } +}