diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 0e29be5..75f9cee 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -95,7 +95,7 @@ } var wbProps = dSpreadsheet.WorkbookPart.Workbook.WorkbookProperties; - Use1904DateSystem = wbProps != null && wbProps.Date1904 != null && wbProps.Date1904.Value; + Use1904DateSystem = wbProps?.Date1904?.Value ?? false; var wbProtection = dSpreadsheet.WorkbookPart.Workbook.WorkbookProtection; if (wbProtection != null) @@ -1392,6 +1392,13 @@ xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } + + if (Use1904DateSystem && xlCell.DataType == XLDataType.DateTime) + { + // Internally ClosedXML stores cells as standard 1900-based style + // so if a workbook is in 1904-format, we do that adjustment here and when saving. + xlCell.SetValue(xlCell.GetDateTime().AddDays(1462)); + } } /// diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 473956c..a0e3fdc 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -5346,7 +5346,16 @@ if (!String.IsNullOrWhiteSpace(xlCell.InnerText)) { var cellValue = new CellValue(); - cellValue.Text = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); + var d = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture); + + if (xlCell.Worksheet.Workbook.Use1904DateSystem && xlCell.DataType == XLDataType.DateTime) + { + // Internally ClosedXML stores cells as standard 1900-based style + // so if a workbook is in 1904-format, we do that adjustment here and when loading. + d -= 1462; + } + + cellValue.Text = d.ToInvariantString(); openXmlCell.CellValue = cellValue; } } diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 1365f8a..cf928a7 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -290,6 +290,7 @@ + diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 87d5960..64ab73c 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -33,7 +33,8 @@ @"Misc\TableHeadersWithLineBreaks.xlsx", @"Misc\TableWithNameNull.xlsx", @"Misc\DuplicateImageNames.xlsx", - @"Misc\InvalidPrintArea.xlsx" + @"Misc\InvalidPrintArea.xlsx", + @"Misc\Date1904System.xlsx" }; foreach (var file in files) @@ -55,6 +56,35 @@ } [Test] + public void CanLoadDate1904SystemCorrectly() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\Date1904System.xlsx"))) + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + var c = ws.Cell("A2"); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 27, 21, 0, 0), c.GetDateTime()); + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + var c = ws.Cell("A2"); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 27, 21, 0, 0), c.GetDateTime()); + wb.SaveAs(ms); + } + + } + } + + [Test] public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() { // This file's workbook.xml contains: @@ -94,7 +124,7 @@ /// /// For non-English locales, the default style ("Normal" in English) can be - /// another piece of text (e.g. Обычный in Russian). + /// another piece of text (e.g. ??????? in Russian). /// This test ensures that the default style is correctly detected and /// no style conflicts occur on save. /// diff --git a/ClosedXML_Tests/Resource/Misc/Date1904System.xlsx b/ClosedXML_Tests/Resource/Misc/Date1904System.xlsx new file mode 100644 index 0000000..8a7da48 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/Date1904System.xlsx Binary files differ