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)

Change-Id: I1355031cdd63e2a5c50064531011be71ae7f7b8f
Reviewed-on: https://gerrit.libreoffice.org/18923
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Katarina Behrens <Katarina.Behrens@cib.de>

5 files changed, 35 insertions, 10 deletions

diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 26d759588216..7fe0a69b6d8f 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -2927,6 +2927,7 @@ void ScExportTest::testRefStringXLSX()
 
 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());
 
@@ -2938,13 +2939,18 @@ void ScExportTest::testRefStringConfigXLSX()
     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());
 
-    //set ref syntax to something else than ExcelA1 (native to xlsx format) ...
     ScDocument& rDoc2 = xDocSh->GetDocument();
     aConfig = rDoc2.GetCalcConfig();
-    aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_A1_XL_A1;
+    // 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);
@@ -2953,7 +2959,7 @@ void ScExportTest::testRefStringConfigXLSX()
     // ... 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_A1_XL_A1,
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match", formula::FormulaGrammar::CONV_XL_R1C1,
                             aConfig.meStringRefAddressSyntax);
 
     xDocSh->DoClose();
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index c77173097b6f..791fe6563f3d 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -4487,10 +4487,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 365812d8f813..5d423ce21bfc 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -7081,10 +7081,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 250a129ae623..a4cb2f63ab90 100644
--- a/sc/source/filter/oox/workbookhelper.cxx
+++ b/sc/source/filter/oox/workbookhelper.cxx
@@ -796,6 +796,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/ui/unoobj/confuno.cxx b/sc/source/ui/unoobj/confuno.cxx
index 3c9277c1f3fa..8b9d4a789772 100644
--- a/sc/source/ui/unoobj/confuno.cxx
+++ b/sc/source/ui/unoobj/confuno.cxx
@@ -355,6 +355,16 @@ void SAL_CALL ScDocumentConfiguration::setPropertyValue(
                     pDocShell->PostPaint(ScRange(0, 0, nTab, MAXCOL, MAXROW, nTab), PAINT_GRID);
             pDocShell->SetDocumentModified();
         }
+
+        ScCalcConfig aCalcConfig = rDoc.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;
+            rDoc.SetCalcConfig(aCalcConfig);
+        }
     }
     else
         throw uno::RuntimeException();