sc/qa/unit/data/ods/tdf170515_invalid_parameters.ods |binary sc/qa/unit/subsequent_export_test2.cxx | 26 +++++++++++++++++++ sc/source/filter/excel/xestream.cxx | 5 ++- sc/source/filter/excel/xetable.cxx | 4 +- sc/source/filter/excel/xltools.cxx | 10 +++++++ sc/source/filter/inc/xestream.hxx | 3 +- sc/source/filter/inc/xltools.hxx | 2 + 7 files changed, 46 insertions(+), 4 deletions(-)
New commits: commit bebe7a0b270506b4d1a32bd99740865fbfbea3a3 Author: Aron Budea <[email protected]> AuthorDate: Fri Jan 30 14:47:44 2026 +1030 Commit: Aron Budea <[email protected]> CommitDate: Sat Jan 31 20:28:48 2026 +0100 tdf#170515 sc: formula with invalid parameters saved into XLSX Calc/ODS is more forgiving about accepting input, the bugdoc had this: =COUNTIF(BASE(1;14);"vv") Here the first parameter of COUNTIF isn't valid, Excel doesn't even allow entering it, and fails opening the XLSX with such formula. No problem in Calc, formula simply gives Err:504 result. Fix: don't export formula with Err:504 (IllegalParameter error) result to XLSX. Change-Id: Ic453440f385b665e56488a61a7c80bf458c4effd Reviewed-on: https://gerrit.libreoffice.org/c/core/+/198391 Tested-by: Jenkins Reviewed-by: Aron Budea <[email protected]> diff --git a/sc/qa/unit/data/ods/tdf170515_invalid_parameters.ods b/sc/qa/unit/data/ods/tdf170515_invalid_parameters.ods new file mode 100644 index 000000000000..35ac902111e6 Binary files /dev/null and b/sc/qa/unit/data/ods/tdf170515_invalid_parameters.ods differ diff --git a/sc/qa/unit/subsequent_export_test2.cxx b/sc/qa/unit/subsequent_export_test2.cxx index ff1dfda81a4f..91fc5214a534 100644 --- a/sc/qa/unit/subsequent_export_test2.cxx +++ b/sc/qa/unit/subsequent_export_test2.cxx @@ -1593,6 +1593,32 @@ CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf137543XLSX) u"_xlfn.LET(_xlpm.first,B5:E15,_xlfn.CHOOSEROWS(_xlpm.first, 1, 3, 5, 7, 9, 11))"); } +CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf170515_invalid_parameters) +{ + createScDoc("ods/tdf170515_invalid_parameters.ods"); + + save(TestFilter::XLSX); + xmlDocUniquePtr pSheet = parseExport(u"xl/worksheets/sheet1.xml"_ustr); + CPPUNIT_ASSERT(pSheet); + + // No function must be saved into the XLSX, the parameters are invalid (Err:504 in Calc), + // and Excel fails to open the result + // Function with invalid parameter: =COUNTIF(BASE(1;14);"VV") + CPPUNIT_ASSERT_EQUAL(0, + countXPathNodes(pSheet, "/x:worksheet/x:sheetData/x:row[1]/x:c[1]/x:f")); + // Function with invalid parameter: =COUNTIF("hello";"VV") + CPPUNIT_ASSERT_EQUAL(0, + countXPathNodes(pSheet, "/x:worksheet/x:sheetData/x:row[2]/x:c[1]/x:f")); + // Function with invalid parameter: =COUNTIF(1;"VV") + CPPUNIT_ASSERT_EQUAL(0, + countXPathNodes(pSheet, "/x:worksheet/x:sheetData/x:row[3]/x:c[1]/x:f")); + // Function with invalid parameter: =COUNTIF(TEXT(1;"#");"VV") + CPPUNIT_ASSERT_EQUAL(0, + countXPathNodes(pSheet, "/x:worksheet/x:sheetData/x:row[4]/x:c[1]/x:f")); + // Just check one of them that it has the right text + assertXPathContent(pSheet, "/x:worksheet/x:sheetData/x:row[1]/x:c[1]/x:v", u"#VALUE!"); +} + CPPUNIT_PLUGIN_IMPLEMENT(); /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/filter/excel/xestream.cxx b/sc/source/filter/excel/xestream.cxx index adc6a457fd3e..0037107780da 100644 --- a/sc/source/filter/excel/xestream.cxx +++ b/sc/source/filter/excel/xestream.cxx @@ -665,7 +665,8 @@ static const char* lcl_GetErrorString( FormulaError nScErrCode ) } } -void XclXmlUtils::GetFormulaTypeAndValue( ScFormulaCell& rCell, const char*& rsType, OUString& rsValue ) +void XclXmlUtils::GetFormulaTypeAndValue( ScFormulaCell& rCell, const char*& rsType, + OUString& rsValue, bool* pbWriteFormula ) { sc::FormulaResultValue aResValue = rCell.GetResult(); @@ -674,6 +675,8 @@ void XclXmlUtils::GetFormulaTypeAndValue( ScFormulaCell& rCell, const char*& rsT case sc::FormulaResultValue::Error: rsType = "e"; rsValue = ToOUString(lcl_GetErrorString(aResValue.mnError)); + if ( pbWriteFormula ) + *pbWriteFormula = XclTools::IsFormulaWithErrorValid(aResValue.mnError); break; case sc::FormulaResultValue::Value: rsType = rCell.GetFormatType() == SvNumFormatType::LOGICAL diff --git a/sc/source/filter/excel/xetable.cxx b/sc/source/filter/excel/xetable.cxx index e28f6b6e657f..680a390f3ca5 100644 --- a/sc/source/filter/excel/xetable.cxx +++ b/sc/source/filter/excel/xetable.cxx @@ -943,7 +943,8 @@ void XclExpFormulaCell::SaveXml( XclExpXmlStream& rStrm ) { const char* sType = nullptr; OUString sValue; - XclXmlUtils::GetFormulaTypeAndValue( mrScFmlaCell, sType, sValue ); + bool bWriteFormula = true; + XclXmlUtils::GetFormulaTypeAndValue( mrScFmlaCell, sType, sValue, &bWriteFormula ); sax_fastparser::FSHelperPtr& rWorksheet = rStrm.GetCurrentStream(); rWorksheet->startElement( XML_c, XML_r, XclXmlUtils::ToOString(rStrm.GetRoot().GetStringBuf(), GetXclPos()).getStr(), @@ -952,7 +953,6 @@ void XclExpFormulaCell::SaveXml( XclExpXmlStream& rStrm ) // OOXTODO: XML_cm, XML_vm, XML_ph ); - bool bWriteFormula = true; bool bTagStarted = false; ScAddress aScPos( static_cast< SCCOL >( GetXclPos().mnCol ), static_cast< SCROW >( GetXclPos().mnRow ), rStrm.GetRoot().GetCurrScTab() ); diff --git a/sc/source/filter/excel/xltools.cxx b/sc/source/filter/excel/xltools.cxx index c6ecb31ed6d5..84e637e034c8 100644 --- a/sc/source/filter/excel/xltools.cxx +++ b/sc/source/filter/excel/xltools.cxx @@ -218,6 +218,16 @@ sal_uInt8 XclTools::GetXclErrorCode( FormulaError nScError ) return EXC_ERR_NA; } +bool XclTools::IsFormulaWithErrorValid( FormulaError nScError ) +{ + switch ( nScError ) + { + // illegal parameter, eg. COUNTIF(1, "x") + case FormulaError::IllegalParameter: return false; + default: return true; + } +} + FormulaError XclTools::GetScErrorCode( sal_uInt8 nXclError ) { switch( nXclError ) diff --git a/sc/source/filter/inc/xestream.hxx b/sc/source/filter/inc/xestream.hxx index bd0129590244..e4ddf99e1faa 100644 --- a/sc/source/filter/inc/xestream.hxx +++ b/sc/source/filter/inc/xestream.hxx @@ -251,7 +251,8 @@ public: XclXmlUtils(const XclXmlUtils&) = delete; XclXmlUtils& operator=(const XclXmlUtils&) = delete; - static void GetFormulaTypeAndValue( ScFormulaCell& rCell, const char*& sType, OUString& rValue); + static void GetFormulaTypeAndValue( ScFormulaCell& rCell, const char*& sType, OUString& rValue, + bool* pbWriteFormula = nullptr ); static OUString GetStreamName( const char* sStreamDir, const char* sStream, sal_Int32 nId ); static OString ToOString( const Color& rColor ); diff --git a/sc/source/filter/inc/xltools.hxx b/sc/source/filter/inc/xltools.hxx index f67d896af949..057b1a684864 100644 --- a/sc/source/filter/inc/xltools.hxx +++ b/sc/source/filter/inc/xltools.hxx @@ -119,6 +119,8 @@ public: /** Converts a Calc error code to an Excel error code. */ static sal_uInt8 GetXclErrorCode( FormulaError nScError ); + /** Checks if erroneous formula is valid (file can be opened) in Excel */ + static bool IsFormulaWithErrorValid( FormulaError nScError ); /** Converts an Excel error code to a Calc error code. */ static FormulaError GetScErrorCode( sal_uInt8 nXclError );
