diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 815da32..36cfadd 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -25,6 +25,7 @@ ce.RegisterFunction("ATAN", 1, Atan); ce.RegisterFunction("ATAN2", 2, Atan2); ce.RegisterFunction("ATANH", 1, Atanh); + ce.RegisterFunction("BASE", 2, 3, Base); ce.RegisterFunction("CEILING", 1, Ceiling); ce.RegisterFunction("COMBIN", 2, Combin); ce.RegisterFunction("COMBINA", 2, CombinA); @@ -565,6 +566,39 @@ return XLMath.ATanh(p[0]); } + private static object Base(List p) + { + long number; + int radix; + int minLength = 0; + + var rawNumber = p[0].Evaluate(); + if (rawNumber is long || rawNumber is int || rawNumber is byte || rawNumber is double || rawNumber is float) + number = Convert.ToInt64(rawNumber); + else + throw new CellValueException(); + + var rawRadix = p[1].Evaluate(); + if (rawRadix is long || rawRadix is int || rawRadix is byte || rawRadix is double || rawRadix is float) + radix = Convert.ToInt32(rawRadix); + else + throw new CellValueException(); + + if (p.Count > 2) + { + var rawMinLength = p[2].Evaluate(); + if (rawMinLength is long || rawMinLength is int || rawMinLength is byte || rawMinLength is double || rawMinLength is float) + minLength = Convert.ToInt32(rawMinLength); + else + throw new CellValueException(); + } + + if (number < 0 || radix < 2 || radix > 36) + throw new NumberException(); + + return XLMath.ChangeBase(number, radix).PadLeft(minLength, '0'); + } + private static object Combin(List p) { Int32 n = (int)p[0]; diff --git a/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs b/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs index 85f5fe2..4354e04 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs @@ -151,5 +151,36 @@ throw new ArgumentOutOfRangeException("text is not a valid roman number"); } + + public static string ChangeBase(long number, int radix) + { + if (number < 0) + throw new ArgumentOutOfRangeException("number must be greater or equal to 0"); + if (radix < 2) + throw new ArgumentOutOfRangeException("radix must be greater or equal to 2"); + if (radix > 36) + throw new ArgumentOutOfRangeException("radix must be smaller than or equal to 36"); + + StringBuilder sb = new StringBuilder(); + long remaining = number; + + if (remaining == 0) + { + sb.Insert(0, '0'); + } + + while (remaining > 0) + { + var nextDigitDecimal = remaining % radix; + remaining = remaining / radix; + + if (nextDigitDecimal < 10) + sb.Insert(0, nextDigitDecimal); + else + sb.Insert(0, (char)(nextDigitDecimal + 55)); + } + + return sb.ToString(); + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 98de92a..a609646 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -361,6 +361,92 @@ Assert.AreEqual(expectedResult, actual, tolerance * 10); } + [Theory] + public void Base_ThrowsNumberExceptionOnBaseSmallerThan2([Range(-2, 1)] int theBase) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE(0, {0})", theBase.ToString(CultureInfo.InvariantCulture)))); + } + + [Theory] + public void Base_ThrowsNumberExceptionOnInputSmallerThan0([Range(-5, -1)] int input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, 2)", input.ToString(CultureInfo.InvariantCulture)))); + } + + [Theory] + public void Base_ThrowsNumberExceptionOnRadixGreaterThan36([Range(37, 40)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE(1, {0})", radix.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase("x", "2", "2")] + [TestCase("0", "x", "2")] + [TestCase("0", "2", "x")] + public void Base_ThrowsValueExceptionOnAnyInputNotANumber(string input, string theBase, string minLength) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"BASE({0}, {1}, {2})", + input, + theBase, + minLength))); + } + + [TestCase(0, 36, "0")] + [TestCase(1, 36, "1")] + [TestCase(2, 36, "2")] + [TestCase(3, 36, "3")] + [TestCase(4, 36, "4")] + [TestCase(5, 36, "5")] + [TestCase(6, 36, "6")] + [TestCase(7, 36, "7")] + [TestCase(8, 36, "8")] + [TestCase(9, 36, "9")] + [TestCase(10, 36, "A")] + [TestCase(11, 36, "B")] + [TestCase(12, 36, "C")] + [TestCase(13, 36, "D")] + [TestCase(14, 36, "E")] + [TestCase(15, 36, "F")] + [TestCase(16, 36, "G")] + [TestCase(17, 36, "H")] + [TestCase(18, 36, "I")] + [TestCase(19, 36, "J")] + [TestCase(20, 36, "K")] + [TestCase(21, 36, "L")] + [TestCase(22, 36, "M")] + [TestCase(23, 36, "N")] + [TestCase(24, 36, "O")] + [TestCase(25, 36, "P")] + [TestCase(26, 36, "Q")] + [TestCase(27, 36, "R")] + [TestCase(28, 36, "S")] + [TestCase(29, 36, "T")] + [TestCase(30, 36, "U")] + [TestCase(31, 36, "V")] + [TestCase(32, 36, "W")] + [TestCase(33, 36, "X")] + [TestCase(34, 36, "Y")] + [TestCase(35, 36, "Z")] + [TestCase(36, 36, "10")] + [TestCase(255, 29, "8N")] + [TestCase(255, 2, "11111111")] + public void Base_ReturnsCorrectResultOnInput(int input, int theBase, string expectedResult) + { + var actual = (string)XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, {1})", input, theBase)); + Assert.AreEqual(expectedResult, actual); + } + + [TestCase(255, 2, 3, "11111111")] + [TestCase(255, 2, 8, "11111111")] + [TestCase(255, 2, 10, "0011111111")] + [TestCase(10, 3, 4, "0101")] + public void Base_ReturnsCorrectResultOnInputWithMinimalLength(int input, int theBase, int minLength, string expectedResult) + { + var actual = (string)XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, {1}, {2})", input, theBase, minLength)); + Assert.AreEqual(expectedResult, actual); + } + [TestCase(4, 3, 20)] [TestCase(10, 3, 220)] [TestCase(0, 0, 1)]