diff --git a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs index 028d5c7..18c2c9d 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs @@ -91,7 +91,26 @@ var month = (int)p[1]; var day = (int)p[2]; - return (int)Math.Floor(new DateTime(year, month, day).ToOADate()); + // Excel allows months and days outside the normal range, and adjusts the date accordingly + if (month > 12 || month < 1) + { + year += (int)Math.Floor((double)(month - 1d) / 12.0); + month -= (int)Math.Floor((double)(month - 1d) / 12.0) * 12; + } + + int daysAdjustment = 0; + if (day > DateTime.DaysInMonth(year, month)) + { + daysAdjustment = day - DateTime.DaysInMonth(year, month); + day = DateTime.DaysInMonth(year, month); + } + else if (day < 1) + { + daysAdjustment = day - 1; + day = 1; + } + + return (int)Math.Floor(new DateTime(year, month, day).AddDays(daysAdjustment).ToOADate()); } private static object Datevalue(List p) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 943fab0..56b6849 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1009,7 +1009,7 @@ { if (workbook.DefinedNames == null) return; - foreach (DefinedName definedName in workbook.DefinedNames) + foreach (var definedName in workbook.DefinedNames.OfType()) { var name = definedName.Name; var visible = true; @@ -1021,7 +1021,6 @@ { if (area.Contains("[")) { - String tableName = area.Substring(0, area.IndexOf("[")); var ws = Worksheets.FirstOrDefault(w => (w as XLWorksheet).SheetId == definedName.LocalSheetId + 1); if (ws != null) { @@ -1094,7 +1093,8 @@ var areas = validateDefinedNames(definedName.Text.Split(',')); foreach (var item in areas) { - SetColumnsOrRowsToRepeat(item); + if (this.Range(item) != null) + SetColumnsOrRowsToRepeat(item); } } diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index e06cc44..982cdf2 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -1,288 +1,289 @@ - - - - Debug - AnyCPU - - - 2.0 - {09B066ED-E4A7-4545-A1A4-FF03DD524BDF} - Library - Properties - ClosedXML_Tests - ClosedXML_Tests - v4.5.2 - 512 - {3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC} - ..\ - true - - - true - full - false - bin\Debug\ - DEBUG;TRACE;$(AppVeyor) - prompt - 4 - false - - - pdbonly - true - bin\Release\ - TRACE;$(AppVeyor) - prompt - 4 - false - - - true - - - ClosedXML.snk - - - - ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll - True - - - - ..\packages\NUnit.3.4.1\lib\net45\nunit.framework.dll - True - - - - - 3.5 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - {BD5E6BFE-E837-4A35-BCA9-39667D873A20} - ClosedXML - - - {03A518D0-1CB7-488E-861C-C4E782B27A46} - ClosedXML_Examples - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - .editorconfig - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + Debug + AnyCPU + + + 2.0 + {09B066ED-E4A7-4545-A1A4-FF03DD524BDF} + Library + Properties + ClosedXML_Tests + ClosedXML_Tests + v4.5.2 + 512 + {3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC} + ..\ + true + + + true + full + false + bin\Debug\ + DEBUG;TRACE;$(AppVeyor) + prompt + 4 + false + + + pdbonly + true + bin\Release\ + TRACE;$(AppVeyor) + prompt + 4 + false + + + true + + + ClosedXML.snk + + + + ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll + True + + + + ..\packages\NUnit.3.4.1\lib\net45\nunit.framework.dll + True + + + + + 3.5 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + {BD5E6BFE-E837-4A35-BCA9-39667D873A20} + ClosedXML + + + {03A518D0-1CB7-488E-861C-C4E782B27A46} + ClosedXML_Examples + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .editorconfig + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs index e6ed681..1d4c5d4 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs @@ -21,8 +21,25 @@ [Test] public void Date() { - Object actual = XLWorkbook.EvaluateExpr("Date(2008, 1, 1)"); + Object actual; + + actual = XLWorkbook.EvaluateExpr("Date(2008, 1, 1)"); Assert.AreEqual(39448, actual); + + actual = XLWorkbook.EvaluateExpr("Date(2008, 15, 1)"); + Assert.AreEqual(39873, actual); + + actual = XLWorkbook.EvaluateExpr("Date(2008, -50, 1)"); + Assert.AreEqual(37895, actual); + + actual = XLWorkbook.EvaluateExpr("Date(2008, 5, 63)"); + Assert.AreEqual(39631, actual); + + actual = XLWorkbook.EvaluateExpr("Date(2008, 13, 63)"); + Assert.AreEqual(39876, actual); + + actual = XLWorkbook.EvaluateExpr("Date(2008, 15, -120)"); + Assert.AreEqual(39752, actual); } [Test] diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 8746443..14c588e 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -23,6 +23,7 @@ @"Misc\LoadPivotTables.xlsx", @"Misc\LoadFileWithCustomSheetViews.xlsx", @"Misc\LoadSheetsWithCommas.xlsx", + @"Misc\InvalidPrintTitles.xlsx", @"Misc\ExcelProducedWorkbookWithImages.xlsx" }; @@ -135,8 +136,8 @@ var ws2 = wb.Worksheets.Skip(1).First(); Assert.AreEqual(1, ws2.Pictures.Count); Assert.AreEqual(XLPicturePlacement.MoveAndSize, ws2.Pictures.First().Placement); - } - } + } +} [Test] public void CanLoadFileWithImagesWithCorrectImageType() diff --git a/ClosedXML_Tests/Resource/Misc/InvalidPrintTitles.xlsx b/ClosedXML_Tests/Resource/Misc/InvalidPrintTitles.xlsx new file mode 100644 index 0000000..86bef09 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/InvalidPrintTitles.xlsx Binary files differ