diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 2d1368d..d4fc266 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -4,6 +4,7 @@ using System.Collections; using System.Collections.Generic; using System.Linq; +using System.Text; namespace ClosedXML.Excel.CalcEngine { @@ -25,6 +26,9 @@ ce.RegisterFunction("COMBIN", 2, Combin); ce.RegisterFunction("COS", 1, Cos); ce.RegisterFunction("COSH", 1, Cosh); + ce.RegisterFunction("COT", 1, Cot); + ce.RegisterFunction("CSCH", 1, Csch); + ce.RegisterFunction("DECIMAL", 2, MathTrig.Decimal); ce.RegisterFunction("DEGREES", 1, Degrees); ce.RegisterFunction("EVEN", 1, Even); ce.RegisterFunction("EXP", 1, Exp); @@ -115,6 +119,58 @@ return Math.Cosh(p[0]); } + private static object Cot(List p) + { + var tan = (double)Math.Tan(p[0]); + + if (tan == 0) + throw new DivisionByZeroException(); + + return 1 / tan; + } + + private static object Csch(List p) + { + if (Math.Abs((double)p[0].Evaluate()) < Double.Epsilon) + throw new DivisionByZeroException(); + + return 1 / Math.Sinh(p[0]); + } + + private static object Decimal(List p) + { + string source = p[0]; + double radix = p[1]; + + if (radix < 2 || radix > 36) + throw new NumberException(); + + var asciiValues = Encoding.ASCII.GetBytes(source.ToUpperInvariant()); + + double result = 0; + int i = 0; + + foreach (byte digit in asciiValues) + { + if (digit > 90) + { + throw new NumberException(); + } + + int digitNumber = digit >= 48 && digit < 58 + ? digit - 48 + : digit - 55; + + if (digitNumber > radix - 1) + throw new NumberException(); + + result = result * radix + digitNumber; + i++; + } + + return result; + } + private static object Exp(List p) { return Math.Exp(p[0]); @@ -159,7 +215,7 @@ private static object Int(List p) { - return (int)((double)p[0]); + return Math.Floor(p[0]); } private static object Ln(List p) diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 4b004d3..b1173de 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -401,16 +401,9 @@ public Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet) { - if (Worksheets.Any(w => string.Equals(w.Name, XLWorksheets.TrimSheetName(name), StringComparison.OrdinalIgnoreCase))) - { - worksheet = Worksheet(name); - return true; + return Worksheets.TryGetWorksheet(name, out worksheet); } - worksheet = null; - return false; - } - public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws) { ws = null; @@ -522,6 +515,7 @@ _loadSource = XLLoadSource.File; _originalFile = file; + _originalStream = null; } private static SpreadsheetDocumentType GetSpreadsheetDocumentType(string filePath) @@ -622,6 +616,7 @@ _loadSource = XLLoadSource.Stream; _originalStream = stream; + _originalFile = null; } internal static void CopyStream(Stream input, Stream output) @@ -988,5 +983,10 @@ { Protect(false, false, workbookPassword); } + + public override string ToString() + { + return _originalFile ?? String.Format("XLWorkbook({0})", _originalStream.ToString()); } } +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 4ab4125..436e1c1 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -727,8 +727,10 @@ private static IXLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) { + var row = Math.Max(1, Convert.ToInt32(marker.RowId.InnerText) + 1); + var column = Math.Min(XLHelper.MaxColumnNumber, Convert.ToInt32(marker.ColumnId.InnerText) + 1); return new XLMarker( - ws.Cell(Convert.ToInt32(marker.RowId.InnerText) + 1, Convert.ToInt32(marker.ColumnId.InnerText) + 1).Address, + ws.Cell(row, column).Address, new Point( ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.ColumnOffset.InnerText), GraphicsUtils.Graphics.DpiX), ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.RowOffset.InnerText), GraphicsUtils.Graphics.DpiY) diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 7a82720..eeab82f 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -154,19 +154,18 @@ string sheetName = worksheet.Name; // Get the pivot Table Parts - IEnumerable pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; - Dictionary pvtTableCacheDefinationPart = new Dictionary(); + var pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; + var pvtTableCacheDefinitionPart = new Dictionary(); foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts) { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete - var pvtCache = pvtCacheDef.Descendants().Where(s => s.WorksheetSource.Sheet == sheetName); - if (pvtCache.Any()) + if (pvtCacheDef.Descendants().Any(s => s.WorksheetSource != null && s.WorksheetSource.Sheet == sheetName)) { - pvtTableCacheDefinationPart.Add(Item, Item.ToString()); + pvtTableCacheDefinitionPart.Add(Item, Item.ToString()); } } - foreach (var Item in pvtTableCacheDefinationPart) + foreach (var Item in pvtTableCacheDefinitionPart) { wbPart.DeletePart(Item.Key); } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index f6304c1..23ff835 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -651,7 +651,8 @@ } if (AutoFilter.Enabled) - targetSheet.Range(AutoFilter.Range.RangeAddress).SetAutoFilter(); + using (var range = targetSheet.Range(AutoFilter.Range.RangeAddress.FirstAddress.RowNumber, AutoFilter.Range.RangeAddress.FirstAddress.ColumnNumber, AutoFilter.Range.RangeAddress.LastAddress.RowNumber, AutoFilter.Range.RangeAddress.LastAddress.ColumnNumber)) + range.SetAutoFilter(); return targetSheet; } diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 8f68e66..1a36a5a 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -47,7 +47,7 @@ public bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet) { XLWorksheet w; - if (_worksheets.TryGetValue(sheetName, out w)) + if (_worksheets.TryGetValue(TrimSheetName(sheetName).ToLowerInvariant(), out w)) { worksheet = w; return true; @@ -70,7 +70,7 @@ XLWorksheet w; - if (_worksheets.TryGetValue(sheetName, out w)) + if (_worksheets.TryGetValue(sheetName.ToLowerInvariant(), out w)) return w; var wss = _worksheets.Where(ws => string.Equals(ws.Key, sheetName, StringComparison.OrdinalIgnoreCase)); @@ -118,12 +118,12 @@ if (_worksheets.Any(ws => ws.Key.Equals(sheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", sheetName), nameof(sheetName)); - _worksheets.Add(sheetName, sheet); + _worksheets.Add(sheetName.ToLowerInvariant(), sheet); } public void Delete(String sheetName) { - Delete(_worksheets[sheetName].Position); + Delete(_worksheets[sheetName.ToLowerInvariant()].Position); } public void Delete(Int32 position) @@ -178,14 +178,14 @@ public void Rename(String oldSheetName, String newSheetName) { - if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; + if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName.ToLowerInvariant())) return; if (!oldSheetName.Equals(newSheetName, StringComparison.OrdinalIgnoreCase) && _worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName)); - var ws = _worksheets[oldSheetName]; - _worksheets.Remove(oldSheetName); + var ws = _worksheets[oldSheetName.ToLowerInvariant()]; + _worksheets.Remove(oldSheetName.ToLowerInvariant()); Add(newSheetName, ws); } } diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index da8bde2..9a4356a 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -1,8 +1,9 @@ -using System; -using System.Collections.Generic; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; namespace ClosedXML_Tests { @@ -94,5 +95,35 @@ Assert.That(!ws.AutoFilter.Enabled); } } + + [Test] + public void CanCopyAutoFilterToNewSheetOnNewWorkbook() + { + using (var ms1 = new MemoryStream()) + using (var ms2 = new MemoryStream()) + { + using (var wb1 = new XLWorkbook()) + using (var wb2 = new XLWorkbook()) + { + var ws = wb1.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Names"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.RangeUsed().SetAutoFilter(); + + wb1.SaveAs(ms1); + + ws.CopyTo(wb2, ws.Name); + wb2.SaveAs(ms2); + } + + using (var wb2 = new XLWorkbook(ms2)) + { + Assert.IsTrue(wb2.Worksheets.First().AutoFilter.Enabled); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index b9003d2..dd2eeef 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -2,6 +2,7 @@ using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; +using System.Globalization; using System.Linq; namespace ClosedXML_Tests.Excel.CalcEngine @@ -11,6 +12,60 @@ { private readonly double tolerance = 1e-10; + [TestCase(1, 0.642092616)] + [TestCase(2, -0.457657554)] + [TestCase(3, -7.015252551)] + [TestCase(4, 0.863691154)] + [TestCase(5, -0.295812916)] + [TestCase(6, -3.436353004)] + [TestCase(7, 1.147515422)] + [TestCase(8, -0.147065064)] + [TestCase(9, -2.210845411)] + [TestCase(10, 1.542351045)] + [TestCase(11, -0.004425741)] + [TestCase(Math.PI * 0.5, 0)] + [TestCase(45, 0.617369624)] + [TestCase(-2, 0.457657554)] + [TestCase(-3, 7.015252551)] + public void Cot(double input, double expected) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"COT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual, tolerance * 10.0); + } + + [Test] + public void Cot_Input0() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("COT(0)")); + } + + [TestCase("FF", 16, 255)] + [TestCase("111", 2, 7)] + [TestCase("zap", 36, 45745)] + public void Decimal(string inputString, int radix, int expectedResult) + { + var actualResult = XLWorkbook.EvaluateExpr($"DECIMAL(\"{inputString}\", {radix})"); + Assert.AreEqual(expectedResult, actualResult); + } + + [Test] + public void Decimal_ZeroIsZeroInAnyRadix([Range(2, 36)] int radix) + { + Assert.AreEqual(0, XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + + [Theory] + public void Decimal_ReturnsErrorForRadiansGreater36([Range(37, 255)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + + [Theory] + public void Decimal_ReturnsErrorForRadiansSmaller2([Range(-5, 1)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + [Test] public void Floor() { @@ -139,5 +194,36 @@ Assert.Throws(() => ws.Evaluate("SUMPRODUCT(A1:A10, B1:B5)")); } } + + [TestCase(1, 0.850918128)] + [TestCase(2, 0.275720565)] + [TestCase(3, 0.09982157)] + [TestCase(4, 0.03664357)] + [TestCase(5, 0.013476506)] + [TestCase(6, 0.004957535)] + [TestCase(7, 0.001823765)] + [TestCase(8, 0.000670925)] + [TestCase(9, 0.00024682)] + [TestCase(10, 0.000090799859712122200000)] + [TestCase(11, 0.0000334034)] + public void CSch_CalculatesCorrectValues(double input, double expectedOutput) + { + Assert.AreEqual(expectedOutput, (double)XLWorkbook.EvaluateExpr($@"CSCH({input})"), 0.000000001); + } + + [Test] + public void Csch_ReturnsDivisionByZeroErrorOnInput0() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("CSCH(0)")); + } + + [TestCase(8.9, 8)] + [TestCase(-8.9, -9)] + public void Int(double input, double expected) + { + var actual = XLWorkbook.EvaluateExpr(string.Format(@"INT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual); + + } } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 76f612e..33fafed 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -161,6 +161,27 @@ } [Test] + public void TryGetWorksheet() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + IXLWorksheet ws; + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("Sheet1", out ws)); + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("sheet1", out ws)); + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("sHEeT1", out ws)); + Assert.IsFalse(wb.Worksheets.TryGetWorksheet("Sheeeet2", out ws)); + + Assert.IsTrue(wb.TryGetWorksheet("Sheet1", out ws)); + Assert.IsTrue(wb.TryGetWorksheet("sheet1", out ws)); + Assert.IsTrue(wb.TryGetWorksheet("sHEeT1", out ws)); + Assert.IsFalse(wb.TryGetWorksheet("Sheeeet2", out ws)); + } + } + + [Test] public void HideWorksheet() { using (var ms = new MemoryStream())