[Libreoffice-commits] core.git: sc/qa sc/source
Justin Luth
justin_luth at sil.org
Mon Jun 11 21:18:15 UTC 2018
sc/qa/unit/data/ods/tdf113991_relativeNamedRanges.ods |binary
sc/qa/unit/subsequent_export-test.cxx | 22 ++++++
sc/source/filter/excel/xename.cxx | 63 ++++++++++++++++--
3 files changed, 80 insertions(+), 5 deletions(-)
New commits:
commit b7a30d4bfbcf6b4c4fd773e8e1a436928e1224e3
Author: Justin Luth <justin_luth at sil.org>
Date: Sat May 5 19:39:59 2018 +0300
tdf#113991 xls/xlsx export: no relative sheet in named ranges
MSO apparently requires absolute sheet references
in named ranges.
XLS and XLSX compose the range in entirely different ways.
XLS doesn't use the sSymbol result at all, while XLSX is
entirely dependent upon it.
XLS doesn't require the range to be 3D in order to be absolute,
but XLSX and ODS do require that, so it only makes sense to ensure
that 3D is always enabled when the range changes to absolute
in order to enhance inter-operability.
GLOBAL names will be handled in a followup commit.
Change-Id: Idd2bd841264fdbb30e861da8956da3d28158dfa3
Reviewed-on: https://gerrit.libreoffice.org/54739
Tested-by: Jenkins <ci at libreoffice.org>
Reviewed-by: Eike Rathke <erack at redhat.com>
diff --git a/sc/qa/unit/data/ods/tdf113991_relativeNamedRanges.ods b/sc/qa/unit/data/ods/tdf113991_relativeNamedRanges.ods
new file mode 100644
index 000000000000..ed5c6ce3bc09
Binary files /dev/null and b/sc/qa/unit/data/ods/tdf113991_relativeNamedRanges.ods differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 3ff3c4301e3d..5eda7f6ef609 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -179,6 +179,7 @@ public:
#endif
void testAbsNamedRangeHTML();
void testSheetLocalRangeNameXLS();
+ void testRelativeNamedExpressionsXLS();
void testSheetTextBoxHyperlinkXLSX();
void testFontSizeXLSX();
void testSheetCharacterKerningSpaceXLSX();
@@ -286,6 +287,7 @@ public:
CPPUNIT_TEST(testPreserveTextWhitespace2XLSX);
CPPUNIT_TEST(testAbsNamedRangeHTML);
CPPUNIT_TEST(testSheetLocalRangeNameXLS);
+ CPPUNIT_TEST(testRelativeNamedExpressionsXLS);
CPPUNIT_TEST(testSheetTextBoxHyperlinkXLSX);
CPPUNIT_TEST(testFontSizeXLSX);
CPPUNIT_TEST(testSheetCharacterKerningSpaceXLSX);
@@ -3364,6 +3366,26 @@ void ScExportTest::testSheetLocalRangeNameXLS()
xDocSh2->DoClose();
}
+void ScExportTest::testRelativeNamedExpressionsXLS()
+{
+ ScDocShellRef xDocSh = loadDoc("tdf113991_relativeNamedRanges.", FORMAT_ODS);
+ xDocSh->DoHardRecalc();
+ ScDocShellRef xDocSh2 = saveAndReload(xDocSh.get(), FORMAT_XLS);
+ xDocSh->DoClose();
+ xDocSh2->DoHardRecalc();
+ ScDocument& rDoc = xDocSh2->GetDocument();
+
+ // Sheet1:G3
+ ScAddress aPos(6,2,0);
+ CPPUNIT_ASSERT_EQUAL(1.0, rDoc.GetValue(aPos));
+ ASSERT_FORMULA_EQUAL(rDoc, aPos, "single_cell_A3", nullptr);
+ // Sheet2:F6
+ aPos = ScAddress(5,5,1);
+ CPPUNIT_ASSERT_EQUAL(18.0, rDoc.GetValue(aPos));
+ ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(test_conflict)", nullptr);
+ xDocSh2->DoClose();
+}
+
void ScExportTest::testSheetTextBoxHyperlinkXLSX()
{
ScDocShellRef xShell = loadDoc("textbox-hyperlink.", FORMAT_XLSX);
diff --git a/sc/source/filter/excel/xename.cxx b/sc/source/filter/excel/xename.cxx
index 84ab353ee2d8..c55840e2532e 100644
--- a/sc/source/filter/excel/xename.cxx
+++ b/sc/source/filter/excel/xename.cxx
@@ -23,6 +23,7 @@
#include <document.hxx>
#include <rangenam.hxx>
+#include <tokenarray.hxx>
#include <dbdata.hxx>
#include <xehelper.hxx>
#include <xelink.hxx>
@@ -334,6 +335,41 @@ void XclExpName::WriteBody( XclExpStream& rStrm )
mxTokArr->WriteArray( rStrm ); // token array without size
}
+void lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok )
+{
+ if ( !pTok || ( pTok->GetType() != formula::svSingleRef && pTok->GetType() != formula::svDoubleRef ) )
+ return;
+
+ ScSingleRefData* pRef1 = pTok->GetSingleRef();
+ if ( !pRef1 )
+ return;
+
+ ScSingleRefData* pRef2 = nullptr;
+ if ( pTok->GetType() == formula::svDoubleRef )
+ pRef2 = pTok->GetSingleRef2();
+
+ if ( pRef1->IsTabRel() || !pRef1->IsFlag3D() )
+ {
+ if ( pRef1->IsTabRel() && nTab != SCTAB_GLOBAL )
+ pRef1->SetAbsTab( nTab + pRef1->Tab() ); //XLS requirement
+ if ( !pRef1->IsTabRel() )
+ {
+ pRef1->SetFlag3D( true ); //XLSX requirement
+ if ( pRef2 && !pRef2->IsTabRel() )
+ pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
+ }
+ }
+
+ if ( pRef2 && pRef2->IsTabRel() && !pRef1->IsTabRel() )
+ {
+ if ( nTab != SCTAB_GLOBAL )
+ {
+ pRef2->SetAbsTab( nTab + pRef2->Tab() );
+ pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
+ }
+ }
+}
+
XclExpNameManagerImpl::XclExpNameManagerImpl( const XclExpRoot& rRoot ) :
XclExpRoot( rRoot ),
mnFirstUserIdx( 0 )
@@ -543,12 +579,29 @@ sal_uInt16 XclExpNameManagerImpl::CreateName( SCTAB nTab, const ScRangeData& rRa
This may cause recursive creation of other defined names. */
if( const ScTokenArray* pScTokArr = const_cast< ScRangeData& >( rRangeData ).GetCode() )
{
- XclTokenArrayRef xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pScTokArr );
- xName->SetTokenArray( xTokArr );
-
+ XclTokenArrayRef xTokArr;
OUString sSymbol;
- rRangeData.GetSymbol( sSymbol, ((GetOutput() == EXC_OUTPUT_BINARY) ?
- formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 : formula::FormulaGrammar::GRAM_OOXML));
+ // MSO requires named ranges to have absolute sheet references
+ if ( rRangeData.HasType( ScRangeData::Type::AbsPos ) || rRangeData.HasType( ScRangeData::Type::AbsArea ) )
+ {
+ // Don't modify the actual document; use a temporary copy to create the export formulas.
+ std::unique_ptr<ScTokenArray> pTokenCopy( pScTokArr->Clone() );
+ lcl_EnsureAbs3DToken( nTab, pTokenCopy.get()->FirstToken() );
+
+ xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pTokenCopy.get() );
+ if ( GetOutput() != EXC_OUTPUT_BINARY )
+ {
+ ScCompiler aComp( &GetDocRef(), rRangeData.GetPos(), *pTokenCopy.get(), formula::FormulaGrammar::GRAM_OOXML );
+ aComp.CreateStringFromTokenArray( sSymbol );
+ }
+ }
+ else
+ {
+ xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pScTokArr );
+ rRangeData.GetSymbol( sSymbol, ((GetOutput() == EXC_OUTPUT_BINARY) ?
+ formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 : formula::FormulaGrammar::GRAM_OOXML));
+ }
+ xName->SetTokenArray( xTokArr );
xName->SetSymbol( sSymbol );
/* Try to replace by existing built-in name - complete token array is
More information about the Libreoffice-commits
mailing list