include/test/unoapi_test.hxx | 2 + sc/qa/unit/data/xlsx/tdf165180_date1904.xlsx |binary sc/qa/unit/subsequent_export_test2.cxx | 36 +++++++++++++++++++++++++++ sc/source/filter/excel/excrecds.cxx | 12 +++------ sc/source/filter/oox/workbooksettings.cxx | 20 +++++++-------- 5 files changed, 52 insertions(+), 18 deletions(-)
New commits: commit 0c8944956f2d7bde41412668dca0e727de25f9f3 Author: Justin Luth <[email protected]> AuthorDate: Thu Dec 4 08:46:35 2025 -0500 Commit: Justin Luth <[email protected]> CommitDate: Fri Dec 5 21:44:22 2025 +0100 related tdf#165180 sc:don't export dateCompatibility - strict-xml only dateCompatibility is ignored by ECMA 1st edition (Excel 2007) and ISOIEC29500 (Excel 2010+). This effectively reverts 25.8.4 commit 990075b66846e78257df7aaa43a23cecf4b7ec9d make CppunitTest_sc_subsequent_export_test2 \ CPPUNIT_TEST_NAME=testTdf165180_date1904 Change-Id: I1d3beb2d3d14d983ba83edb8b63d05f127a0eaf4 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/194970 Tested-by: Jenkins Reviewed-by: Justin Luth <[email protected]> diff --git a/include/test/unoapi_test.hxx b/include/test/unoapi_test.hxx index f00cfa5308ac..dbf046cb4abb 100644 --- a/include/test/unoapi_test.hxx +++ b/include/test/unoapi_test.hxx @@ -66,6 +66,7 @@ enum class TestFilter XLSM, XLST, XLSX, + XLSX_2007, }; const std::unordered_map<TestFilter, OUString> TestFilterNames{ @@ -110,6 +111,7 @@ const std::unordered_map<TestFilter, OUString> TestFilterNames{ { TestFilter::XLSM, u"Calc MS Excel 2007 VBA XML"_ustr }, { TestFilter::XLST, u"Calc MS Excel 2007 XML Template"_ustr }, { TestFilter::XLSX, u"Calc Office Open XML"_ustr }, + { TestFilter::XLSX_2007, u"Calc MS Excel 2007 XML"_ustr }, }; // basic uno api test class diff --git a/sc/qa/unit/data/xlsx/tdf165180_date1904.xlsx b/sc/qa/unit/data/xlsx/tdf165180_date1904.xlsx new file mode 100644 index 000000000000..4242fe2b9606 Binary files /dev/null and b/sc/qa/unit/data/xlsx/tdf165180_date1904.xlsx differ diff --git a/sc/qa/unit/subsequent_export_test2.cxx b/sc/qa/unit/subsequent_export_test2.cxx index 4aabf03cbcdc..637fac6fbde6 100644 --- a/sc/qa/unit/subsequent_export_test2.cxx +++ b/sc/qa/unit/subsequent_export_test2.cxx @@ -1005,6 +1005,42 @@ CPPUNIT_TEST_FIXTURE(ScExportTest2, testXltxExport) u"application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml"); } +CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf165180_date1904) +{ + // given a hand-modified document (which added dateCompatibility="0") + // with an earliest date of 1904 (Excel-for-mac null-date) + + // ensure en-US locale for expected date formatting + SvtSysLocaleOptions aOptions; + OUString sLocaleConfigString = aOptions.GetLanguageTag().getBcp47(); + aOptions.SetLocaleConfigString(u"en-US"_ustr); + aOptions.Commit(); + comphelper::ScopeGuard g([&aOptions, &sLocaleConfigString] { + aOptions.SetLocaleConfigString(sLocaleConfigString); + aOptions.Commit(); + }); + + createScDoc("xlsx/tdf165180_date1904.xlsx"); + saveAndReload(TestFilter::XLSX_2007); + + ScDocument* pDoc = getScDoc(); + CPPUNIT_ASSERT_EQUAL(u"Tuesday, March 1, 1904"_ustr, pDoc->GetString(0, 0, 0)); + + xmlDocUniquePtr pWorkbook = parseExport(u"xl/workbook.xml"_ustr); + // dateCompatibility is ignored: make sure that date1904=true is round-tripped + assertXPath(pWorkbook, "/x:workbook/x:workbookPr", "date1904", u"true"); + + createScDoc("xlsx/tdf165180_date1904.xlsx"); + saveAndReload(TestFilter::XLSX); + + pDoc = getScDoc(); + CPPUNIT_ASSERT_EQUAL(u"Tuesday, March 1, 1904"_ustr, pDoc->GetString(0, 0, 0)); + + pWorkbook = parseExport(u"xl/workbook.xml"_ustr); + // dateCompatibility is ignored: make sure that date1904=true is round-tripped + assertXPath(pWorkbook, "/x:workbook/x:workbookPr", "date1904", u"true"); +} + CPPUNIT_TEST_FIXTURE(ScExportTest2, testPivotCacheAfterExportXLSX) { createScDoc("ods/numgroup_example.ods"); diff --git a/sc/source/filter/excel/excrecds.cxx b/sc/source/filter/excel/excrecds.cxx index 4373c6c2ea97..def9da8bdeca 100644 --- a/sc/source/filter/excel/excrecds.cxx +++ b/sc/source/filter/excel/excrecds.cxx @@ -267,14 +267,10 @@ sal_uInt16 Exc1904::GetNum() const void Exc1904::SaveXml( XclExpXmlStream& rStrm ) { - if( rStrm.getVersion() == oox::core::ECMA_376_1ST_EDITION ) // Word 2007 Compat - { - rStrm.WriteAttributes(XML_dateCompatibility, ToPsz(bDateCompatibility)); - } - else - { - rStrm.WriteAttributes(XML_date1904, ToPsz(bVal)); - } + // dateCompatibility appears to only apply to "strict xml" which we don't export to, + // (and seems to only determine whether date1904 is considered or not when in strict mode). + + rStrm.WriteAttributes(XML_date1904, ToPsz(bVal)); } //------------------------------------------------------ class ExcBundlesheet - commit 5f9d0cf0ce3a9b06138bbab62fcd3e6c89c9e339 Author: Justin Luth <[email protected]> AuthorDate: Wed Dec 3 16:14:52 2025 -0500 Commit: Justin Luth <[email protected]> CommitDate: Fri Dec 5 21:44:12 2025 +0100 related tdf#165180 oox: dateCompatibility not usable in LO This patch will be needed in order to round-trip the Excel 2010+ filter (ISOIEC_29500_2008). We can't use dateCompatibility because we don't have a "strict xml" filter. I've only triple checked this, so I can't be sure that I've got this right. WorkbookSettings::getNullDate is mostly dead code. getOoxFilter().getVersion() == oox::core::ISOIEC_29500_2008 can only be true when the import filter is Calc Office Open XML Spreadsheet but that never happens because filterdetect.cxx if( rContentType == u"...spreadsheetml.sheet.main+xml") returns "MS Excel 2007 XML"_ustr; and calc_MS_Excel_2007_XML.xcu doesn't define <prop oor:name="FileFormatVersion"/> so it is considered to be a zero, which means that mxImpl->meVersion = OoxmlVersion( 0 ); which is oox::core::ECMA_376_1ST_EDITION So getVersion for XLSX will never equal ISOIEC_29500_2008 and thus the code this patch deletes has never been executed. Plus, our code was wrong. dateCompatibility only briefly made it into the ISO transitional standards before it disappeared. > mstahl:6:35 AM we are puzzled by an attribute dateCompatibility > that is written by Calc since a commit > "Merged ooxml-sc-page-date.diff from ooo-build" > and was added to ECMA-376 3rd ed. > then removed (without explanation) from ECMA-376 4th ed. Excel only creates dateCompatibility when saving as "Strict Open XML", but we don't support OOXMLVariant::ISO_Strict. It seems to be ignored by Excel for non-strict formats. Plus, it wouldn't be needed by LO. Excel does not correctly display dates prior to Mar 1, 1900 because it uses the old Lotus logic that treats 1900 as a leap year, which it isn't. dateCompatibility="0" seems designed to tell Excel to consider Feb 29 1900 as an invalid date. LO never treats 1900 as a leap year (so no Feb 29, 1900), so our (accurate) dates do not match Excel. (That isn't a big deal because Excel starts at Jan 1 1900 and is only wrong for the first two months, so we only don't match for the first 2 months of 1900.) Change-Id: I19ca19334e1f13da010bf09d4c5d9a32aea3a091 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/194969 Reviewed-by: Justin Luth <[email protected]> Tested-by: Jenkins diff --git a/sc/source/filter/oox/workbooksettings.cxx b/sc/source/filter/oox/workbooksettings.cxx index bb7269d9702f..2308c644e6fa 100644 --- a/sc/source/filter/oox/workbooksettings.cxx +++ b/sc/source/filter/oox/workbooksettings.cxx @@ -269,18 +269,18 @@ sal_Int16 WorkbookSettings::getApiShowObjectMode() const css::util::Date const & WorkbookSettings::getNullDate() const { - static const css::util::Date saDate1900 ( 30, 12, 1899 ); - static const css::util::Date saDate1904 ( 1, 1, 1904 ); - static const css::util::Date saDateBackCompatibility1900( 31, 12, 1899 ); + // Excel (inacurrately) treats Feb 29, 1900 as a valid date (for compatibility with Lotus). + // AFAICS, only Strict .xlsx treats that date as invalid + // (regardless of whether dateCompatibility="0" or "1"). + // Thus the only purpose of dateCompatibility="1" is to determine the oldest recognizable date + // which will be Jan 1 1904 if date1904="true", otherwise it will be Jan 1 1900. - if( getOoxFilter().getVersion() == oox::core::ISOIEC_29500_2008 ) - { - if( !maBookSettings.mbDateCompatibility ) - return saDate1900; + // LO never treats 1900 as a leap year (so we never match Excel's first two months of 1900), + // and doesn't have an earliest recognizable date limitation. + // The day-early null date of Dec 30 1899 takes care of the extra leap-day that Excel inserts. - return maBookSettings.mbDateMode1904 ? saDate1904 : - saDateBackCompatibility1900; - } + static const css::util::Date saDate1900 ( 30, 12, 1899 ); + static const css::util::Date saDate1904 ( 1, 1, 1904 ); return maBookSettings.mbDateMode1904 ? saDate1904 : saDate1900; }
