tdf#93688: Set CalcA1|ExcelA1 syntax only for imported docs

those whose string ref syntax is unknown or can't be guessed i.e.
don't use it for new documents (prefer user settings in that case)

Conflicts:
	sc/qa/unit/subsequent_export-test.cxx

Change-Id: I1355031cdd63e2a5c50064531011be71ae7f7b8f
Reviewed-on: https://gerrit.libreoffice.org/19244
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

8 files changed, 95 insertions, 7 deletions

diff --git a/sc/qa/unit/data/xlsx/empty-noconf.xlsx b/sc/qa/unit/data/xlsx/empty-noconf.xlsx
new file mode 100644
index 000000000000..21f80b4cef61
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/empty-noconf.xlsx
diff --git a/sc/qa/unit/data/xlsx/empty.xlsx b/sc/qa/unit/data/xlsx/empty.xlsx
new file mode 100644
index 000000000000..c18648506653
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/empty.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index a8e0f305b1f6..b21b4aa9fa1f 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -148,6 +148,8 @@ public:
     void testMoveCellAnchoredShapes();
     void testHeaderImage();
     void testMatrixMultiplication();
+    void testRefStringXLSX();
+    void testRefStringConfigXLSX();
 
 
     CPPUNIT_TEST_SUITE(ScExportTest);
@@ -206,6 +208,8 @@ public:
     CPPUNIT_TEST(testMoveCellAnchoredShapes);
     CPPUNIT_TEST(testHeaderImage);
     CPPUNIT_TEST(testMatrixMultiplication);
+    CPPUNIT_TEST(testRefStringXLSX);
+    CPPUNIT_TEST(testRefStringConfigXLSX);
 
 
     CPPUNIT_TEST_SUITE_END();
@@ -2854,6 +2858,63 @@ void ScExportTest::testMatrixMultiplication()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testRefStringXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("ref_string.", XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to open doc", xDocSh.Is());
+
+    //make sure ref syntax gets saved for MSO-produced docs
+    xDocSh = saveAndReload( &(*xDocSh), XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to reload doc", xDocSh.Is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScCalcConfig aCalcConfig = rDoc.GetCalcConfig();
+    CPPUNIT_ASSERT_EQUAL(formula::FormulaGrammar::CONV_XL_A1, aCalcConfig.meStringRefAddressSyntax);
+
+    xDocSh->DoClose();
+}
+
+void ScExportTest::testRefStringConfigXLSX()
+{
+    // this doc is configured with CalcA1 ref syntax
+    ScDocShellRef xDocSh = loadDoc("empty.", XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to open doc", xDocSh.Is());
+
+    xDocSh = saveAndReload( &(*xDocSh), XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to reload doc", xDocSh.Is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScCalcConfig aConfig = rDoc.GetCalcConfig();
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match", formula::FormulaGrammar::CONV_OOO,
+                            aConfig.meStringRefAddressSyntax);
+
+    // this doc has no entry for ref syntax
+    xDocSh = loadDoc("empty-noconf.", XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to open 2nd doc", xDocSh.Is());
+
+    ScDocument& rDoc2 = xDocSh->GetDocument();
+    aConfig = rDoc2.GetCalcConfig();
+    // therefore after import, ref syntax should be set to CalcA1 | ExcelA1
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match", formula::FormulaGrammar::CONV_A1_XL_A1,
+                            aConfig.meStringRefAddressSyntax);
+
+    //set ref syntax to something else than ExcelA1 (native to xlsx format) ...
+    aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
+    rDoc2.SetCalcConfig( aConfig );
+
+    ScDocShellRef xNewDocSh = saveAndReload( &(*xDocSh), XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to reload 2nd doc", xNewDocSh.Is());
+
+    // ... and make sure it got saved
+    ScDocument& rDoc3 = xNewDocSh->GetDocument();
+    aConfig = rDoc3.GetCalcConfig();
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match", formula::FormulaGrammar::CONV_XL_R1C1,
+                            aConfig.meStringRefAddressSyntax);
+
+    xDocSh->DoClose();
+    xNewDocSh->DoClose();
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 5a84e567e262..6c593a5dfa4b 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -4232,10 +4232,10 @@ void Test::testFuncINDIRECT()
 
     m_pDoc->CalcAll();
     {
-        // Default is to use compatibility mode, accept both Calc A1 and
-        // Excel A1 syntax
+        // Default (for new documents) is to use current formula syntax
+        // which is Calc A1
         const OUString* aChecks[] = {
-            &aTest, &aTest, &aRefErr, &aTest
+            &aTest, &aRefErr, &aRefErr, &aTest
         };
 
         for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 68802aed005d..13e764d89436 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -7032,10 +7032,9 @@ void ScInterpreter::ScIndirect()
             // Use the current address syntax if unspecified.
             eConv = pDok->GetAddressConvention();
 
-        // either CONV_A1_XL_A1 was explicitly configured, or nothing at all
-        // was configured
-        bool bTryXlA1 = (eConv == FormulaGrammar::CONV_A1_XL_A1 ||
-                          !maCalcConfig.mbHasStringRefSyntax);
+        // either CONV_A1_XL_A1 was explicitly configured, or it wasn't possible
+        // to determine which syntax to use during doc import
+        bool bTryXlA1 = (eConv == FormulaGrammar::CONV_A1_XL_A1);
 
         if (nParamCount == 2 && 0.0 == ::rtl::math::approxFloor( GetDouble()))
         {
diff --git a/sc/source/filter/oox/workbookhelper.cxx b/sc/source/filter/oox/workbookhelper.cxx
index fc080cc0f8bf..2573a39f1d7c 100644
--- a/sc/source/filter/oox/workbookhelper.cxx
+++ b/sc/source/filter/oox/workbookhelper.cxx
@@ -797,6 +797,16 @@ void WorkbookHelper::finalizeWorkbookImport()
         sheets. Automatic numbering is set by passing the value 0. */
     PropertySet aDefPageStyle( getStyleObject( "Default", true ) );
     aDefPageStyle.setProperty< sal_Int16 >( PROP_FirstPageNumber, 0 );
+
+    // Has any string ref syntax been imported?
+    // If not, we need to take action
+    ScCalcConfig aCalcConfig = getScDocument().GetCalcConfig();
+
+    if ( !aCalcConfig.mbHasStringRefSyntax )
+    {
+        aCalcConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_A1_XL_A1;
+        getScDocument().SetCalcConfig(aCalcConfig);
+    }
 }
 
 // document model -------------------------------------------------------------
diff --git a/sc/source/filter/xml/xmlimprt.cxx b/sc/source/filter/xml/xmlimprt.cxx
index 226a27cb4681..d1bb29982160 100644
--- a/sc/source/filter/xml/xmlimprt.cxx
+++ b/sc/source/filter/xml/xmlimprt.cxx
@@ -3180,6 +3180,22 @@ void ScXMLImport::SetSheetNamedRanges()
     }
 }
 
+void ScXMLImport::SetStringRefSyntaxIfMissing()
+{
+    if (!pDoc)
+        return;
+
+    ScCalcConfig aCalcConfig = pDoc->GetCalcConfig();
+
+    // Has any string ref syntax been imported?
+    // If not, we need to take action
+    if ( !aCalcConfig.mbHasStringRefSyntax )
+    {
+        aCalcConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_A1_XL_A1;
+        pDoc->SetCalcConfig(aCalcConfig);
+    }
+}
+
 void SAL_CALL ScXMLImport::endDocument()
     throw(::com::sun::star::xml::sax::SAXException,
           ::com::sun::star::uno::RuntimeException,
@@ -3226,6 +3242,7 @@ void SAL_CALL ScXMLImport::endDocument()
             SetLabelRanges();
             SetNamedRanges();
             SetSheetNamedRanges();
+            SetStringRefSyntaxIfMissing();
             if (mpPivotSources)
                 // Process pivot table sources after the named ranges have been set.
                 mpPivotSources->process();
diff --git a/sc/source/filter/xml/xmlimprt.hxx b/sc/source/filter/xml/xmlimprt.hxx
index c26a71016a81..97291ae9503e 100644
--- a/sc/source/filter/xml/xmlimprt.hxx
+++ b/sc/source/filter/xml/xmlimprt.hxx
@@ -1219,6 +1219,7 @@ public:
     void SetSheetNamedRanges();
     void SetLabelRanges();
     void AddDefaultNote( const com::sun::star::table::CellAddress& aCell );
+    void SetStringRefSyntaxIfMissing();
 
     /** Extracts the formula string, the formula grammar namespace URL, and a
         grammar enum value from the passed formula attribute value.