tdf#92256: Improved interop of INDIRECT function

This is a combination of 12 commits from master branch:

tdf#92256: ODF save/load syntax for string reference

Related tdf#92256: map CONV_OOO to listbox item no.1

tdf#92256: Introducing CONV_A1_XL_A1 address pseudoconvention

tdf#92256: OOXML save/load syntax for string reference

add unhandled case in switch

that comment is not correct anymore

don't generate invalid XLSX files

tdf#92256: Handle case when string ref syntax is unknown

tdf#92256: Make OOXML filter CONV_A1_XL_A1 aware too

tdf#92256: Make sure ref syntax of Excel docs gets saved

tdf#92256: Save ref syntax when different from native one

tdf#92256: Don't force CalcA1 syntax on all !Microsoft xlsx docs

Change-Id: I226d5644ce729f1311aefc9a8998b3a75633c334
Reviewed-on: https://gerrit.libreoffice.org/17837
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Thorsten Behrens <Thorsten.Behrens@CIB.de>

21 files changed, 300 insertions, 32 deletions

diff --git a/include/formula/grammar.hxx b/include/formula/grammar.hxx
index 4f6a2bcdf2c3..618db98c1143 100644
--- a/include/formula/grammar.hxx
+++ b/include/formula/grammar.hxx
@@ -43,7 +43,11 @@ public:
 
         CONV_LOTUS_A1,      /* external? 3d? A1.B2 <placeholder/> */
 
-        CONV_LAST   /* for loops, must always be last */
+        CONV_LAST,   /* for loops, must always be last */
+
+        // not a real address convention, a special case for INDIRECT function interpretation
+        // only -> try using CONV_OOO, failing that CONV_XL_A1
+        CONV_A1_XL_A1
     };
 
     //! CONV_UNSPECIFIED is a negative value!
diff --git a/oox/source/core/xmlfilterbase.cxx b/oox/source/core/xmlfilterbase.cxx
index 4ef16d0c6bce..13deec20eeaa 100644
--- a/oox/source/core/xmlfilterbase.cxx
+++ b/oox/source/core/xmlfilterbase.cxx
@@ -118,7 +118,8 @@ struct NamespaceIds: public rtl::StaticWithInit<
             "http://schemas.openxmlformats.org/markup-compatibility/2006",
             "http://schemas.openxmlformats.org/spreadsheetml/2006/main/v2",
             "http://schemas.microsoft.com/office/drawing/2008/diagram",
-            "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
+            "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main",
+            "http://schemas.libreoffice.org/"
         };
 
         static const sal_Int32 namespaceIds[] = {
@@ -145,7 +146,8 @@ struct NamespaceIds: public rtl::StaticWithInit<
             NMSP_mce,
             NMSP_mceTest,
             NMSP_dsp,
-            NMSP_xls14Lst
+            NMSP_xls14Lst,
+            NMSP_loext
         };
 
         Sequence< beans::Pair< OUString, sal_Int32 > > aRet(SAL_N_ELEMENTS(namespaceIds));
diff --git a/oox/source/token/namespaces-strict.txt b/oox/source/token/namespaces-strict.txt
index 9359f8b48094..026fcfe8ded9 100644
--- a/oox/source/token/namespaces-strict.txt
+++ b/oox/source/token/namespaces-strict.txt
@@ -80,3 +80,6 @@ a14                     http://schemas.microsoft.com/office/drawingml/2010/main
 
 # xls14Lst for features introduced by excel 2010
 xls14Lst               http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
+
+# LibreOffice's own extensions
+loext                  http://schemas.libreoffice.org/
diff --git a/oox/source/token/namespaces.hxx.tail b/oox/source/token/namespaces.hxx.tail
index de5cc21d86a6..24de6457698a 100644
--- a/oox/source/token/namespaces.hxx.tail
+++ b/oox/source/token/namespaces.hxx.tail
@@ -55,6 +55,7 @@ inline sal_Int32 getNamespace( sal_Int32 nToken ) { return nToken & NMSP_MASK; }
 #define WPS_TOKEN( token )      OOX_TOKEN( wps, token )
 #define WPG_TOKEN( token )      OOX_TOKEN( wpg, token )
 #define W_TOKEN( token )        OOX_TOKEN( doc, token )
+#define LOEXT_TOKEN( token )    OOX_TOKEN( loext, token )
 
 
 
diff --git a/oox/source/token/namespaces.txt b/oox/source/token/namespaces.txt
index face9d62db10..2c61d606ff9d 100644
--- a/oox/source/token/namespaces.txt
+++ b/oox/source/token/namespaces.txt
@@ -80,3 +80,6 @@ a14                     http://schemas.microsoft.com/office/drawing/2010/main
 
 # xls14Lst for features introduced by excel 2010
 xls14Lst               http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
+
+# LibreOffice's own extensions
+loext                  http://schemas.libreoffice.org/
diff --git a/oox/source/token/tokens.txt b/oox/source/token/tokens.txt
index c26e957f6d6b..c5843faffdd8 100644
--- a/oox/source/token/tokens.txt
+++ b/oox/source/token/tokens.txt
@@ -69,6 +69,8 @@ BroadcastTitle
 Broadcaster
 Button
 CF
+CalcA1
+CalcA1ExcelA1
 Camera
 Cancel
 Caption
@@ -141,6 +143,8 @@ Editor
 ElectronicSource
 Embed
 EnhancedMetaFile
+ExcelA1
+ExcelR1C1
 Extend
 Extension
 External
@@ -392,6 +396,7 @@ True
 Type
 Types
 UIObj
+Unspecified
 URI
 URL
 UpdateMode
@@ -2137,6 +2142,7 @@ exp
 explosion
 expression
 ext
+extCalcPr
 extLst
 extend
 extendable
@@ -3165,6 +3171,7 @@ lockWindows
 locked
 lockedCanvas
 lockrotationcenter
+loext
 log
 logBase
 lon
@@ -4954,6 +4961,7 @@ strikeH
 strikeTLBR
 strikeV
 string
+stringRefSyntax
 stringValue1
 stringValue2
 stripedRightArrow
diff --git a/sc/inc/calcconfig.hxx b/sc/inc/calcconfig.hxx
index d10154c426f4..18aab2166c0d 100644
--- a/sc/inc/calcconfig.hxx
+++ b/sc/inc/calcconfig.hxx
@@ -43,6 +43,7 @@ struct SC_DLLPUBLIC ScCalcConfig
     formula::FormulaGrammar::AddressConvention meStringRefAddressSyntax;
     StringConversion meStringConversion;
     bool mbEmptyStringAsZero:1;
+    bool mbHasStringRefSyntax:1;
 
     bool mbOpenCLSubsetOnly:1;
     bool mbOpenCLAutoSelect:1;
@@ -59,6 +60,7 @@ struct SC_DLLPUBLIC ScCalcConfig
 
     void reset();
     void MergeDocumentSpecific( const ScCalcConfig& r );
+    void SetStringRefSyntax( formula::FormulaGrammar::AddressConvention eConv );
 
     bool operator== (const ScCalcConfig& r) const;
     bool operator!= (const ScCalcConfig& r) const;
diff --git a/sc/inc/unonames.hxx b/sc/inc/unonames.hxx
index d937775769c0..b064995a1468 100644
--- a/sc/inc/unonames.hxx
+++ b/sc/inc/unonames.hxx
@@ -511,6 +511,7 @@
 #define SC_UNO_INTEROPGRABBAG           "InteropGrabBag"
 #define SC_UNO_RECORDCHANGES            "RecordChanges"
 #define SC_UNO_ISRECORDCHANGESPROTECTED "IsRecordChangesProtected"
+#define SC_UNO_SYNTAXSTRINGREF          "SyntaxStringRef"
 
 
 //  document properties from FormModel
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 08026cbc581a..5a84e567e262 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -4232,9 +4232,10 @@ void Test::testFuncINDIRECT()
 
     m_pDoc->CalcAll();
     {
-        // Default is to use the current formula syntax, which is Calc A1.
+        // Default is to use compatibility mode, accept both Calc A1 and
+        // Excel A1 syntax
         const OUString* aChecks[] = {
-            &aTest, &aRefErr, &aRefErr, &aTest
+            &aTest, &aTest, &aRefErr, &aTest
         };
 
         for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
@@ -4245,7 +4246,7 @@ void Test::testFuncINDIRECT()
     }
 
     ScCalcConfig aConfig;
-    aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
+    aConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_OOO );
     m_pDoc->SetCalcConfig(aConfig);
     m_pDoc->CalcAll();
     {
@@ -4261,7 +4262,7 @@ void Test::testFuncINDIRECT()
         }
     }
 
-    aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
+    aConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_XL_A1 );
     m_pDoc->SetCalcConfig(aConfig);
     m_pDoc->CalcAll();
     {
@@ -4277,7 +4278,7 @@ void Test::testFuncINDIRECT()
         }
     }
 
-    aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
+    aConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_XL_R1C1 );
     m_pDoc->SetCalcConfig(aConfig);
     m_pDoc->CalcAll();
     {
diff --git a/sc/source/core/tool/calcconfig.cxx b/sc/source/core/tool/calcconfig.cxx
index 6fd43458ef88..cc52ad7b3de7 100644
--- a/sc/source/core/tool/calcconfig.cxx
+++ b/sc/source/core/tool/calcconfig.cxx
@@ -23,7 +23,8 @@
 ScCalcConfig::ScCalcConfig() :
     meStringRefAddressSyntax(formula::FormulaGrammar::CONV_UNSPECIFIED),
     meStringConversion(StringConversion::LOCALE),     // old LibreOffice behavior
-    mbEmptyStringAsZero(false)
+    mbEmptyStringAsZero(false),
+    mbHasStringRefSyntax(false)
 {
     setOpenCLConfigToDefault();
 
@@ -85,6 +86,13 @@ void ScCalcConfig::MergeDocumentSpecific( const ScCalcConfig& r )
     mbEmptyStringAsZero      = r.mbEmptyStringAsZero;
     // INDIRECT ref syntax is per document.
     meStringRefAddressSyntax = r.meStringRefAddressSyntax;
+    mbHasStringRefSyntax      = r.mbHasStringRefSyntax;
+}
+
+void ScCalcConfig::SetStringRefSyntax( formula::FormulaGrammar::AddressConvention eConv )
+{
+    meStringRefAddressSyntax = eConv;
+    mbHasStringRefSyntax = true;
 }
 
 bool ScCalcConfig::operator== (const ScCalcConfig& r) const
@@ -92,6 +100,7 @@ bool ScCalcConfig::operator== (const ScCalcConfig& r) const
     return meStringRefAddressSyntax == r.meStringRefAddressSyntax &&
            meStringConversion == r.meStringConversion &&
            mbEmptyStringAsZero == r.mbEmptyStringAsZero &&
+           mbHasStringRefSyntax == r.mbHasStringRefSyntax &&
            mbOpenCLSubsetOnly == r.mbOpenCLSubsetOnly &&
            mbOpenCLAutoSelect == r.mbOpenCLAutoSelect &&
            maOpenCLDevice == r.maOpenCLDevice &&
@@ -127,6 +136,7 @@ std::ostream& operator<<(std::ostream& rStream, const ScCalcConfig& rConfig)
         "StringRefAddressSyntax=" << rConfig.meStringRefAddressSyntax << ","
         "StringConversion=" << StringConversionToString(rConfig.meStringConversion) << ","
         "EmptyStringAsZero=" << (rConfig.mbEmptyStringAsZero?"Y":"N") << ","
+        "HasStringRefSyntax=" << (rConfig.mbHasStringRefSyntax?"Y":"N") << ","
         "OpenCLSubsetOnly=" << (rConfig.mbOpenCLSubsetOnly?"Y":"N") << ","
         "OpenCLAutoSelect=" << (rConfig.mbOpenCLAutoSelect?"Y":"N") << ","
         "OpenCLDevice='" << rConfig.maOpenCLDevice << "',"
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 5ef68937f92e..ff8a990afff1 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -7032,17 +7032,27 @@ 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);
+
         if (nParamCount == 2 && 0.0 == ::rtl::math::approxFloor( GetDouble()))
         {
             // Overwrite the config and try Excel R1C1.
             eConv = FormulaGrammar::CONV_XL_R1C1;
+            bTryXlA1 = false;
         }
-        const ScAddress::Details aDetails( eConv, aPos );
+
+        const ScAddress::Details aDetails( bTryXlA1 ? FormulaGrammar::CONV_OOO : eConv, aPos );
+        const ScAddress::Details aDetailsXlA1( FormulaGrammar::CONV_XL_A1, aPos );
         SCTAB nTab = aPos.Tab();
         OUString sRefStr = GetString().getString();
         ScRefAddress aRefAd, aRefAd2;
         ScAddress::ExternalInfo aExtInfo;
-        if (ConvertDoubleRef(pDok, sRefStr, nTab, aRefAd, aRefAd2, aDetails, &aExtInfo))
+        if ( ConvertDoubleRef(pDok, sRefStr, nTab, aRefAd, aRefAd2, aDetails, &aExtInfo) ||
+             ( bTryXlA1 && ConvertDoubleRef(pDok, sRefStr, nTab, aRefAd,
+                                            aRefAd2, aDetailsXlA1, &aExtInfo) ) )
         {
             if (aExtInfo.mbExternal)
             {
@@ -7054,7 +7064,9 @@ void ScInterpreter::ScIndirect()
             else
                 PushDoubleRef( aRefAd, aRefAd2);
         }
-        else if (ConvertSingleRef(pDok, sRefStr, nTab, aRefAd, aDetails, &aExtInfo))
+        else if ( ConvertSingleRef(pDok, sRefStr, nTab, aRefAd, aDetails, &aExtInfo) ||
+                  ( bTryXlA1 && ConvertSingleRef (pDok, sRefStr, nTab, aRefAd,
+                                                  aDetailsXlA1, &aExtInfo) ) )
         {
             if (aExtInfo.mbExternal)
             {
diff --git a/sc/source/filter/excel/excdoc.cxx b/sc/source/filter/excel/excdoc.cxx
index b9788d7f8154..98fc175cff4d 100644
--- a/sc/source/filter/excel/excdoc.cxx
+++ b/sc/source/filter/excel/excdoc.cxx
@@ -881,6 +881,19 @@ void ExcDocument::WriteXml( XclExpXmlStream& rStrm )
     if (rCaches.HasCaches())
         rCaches.SaveXml(rStrm);
 
+    const ScCalcConfig& rCalcConfig = GetDoc().GetCalcConfig();
+
+    // write if it has been read|imported or explicitly changed
+    // or if ref syntax isn't what would be native for our file format
+    // i.e. ExcelA1 in this case
+    if ( rCalcConfig.mbHasStringRefSyntax ||
+         (rCalcConfig.meStringRefAddressSyntax != formula::FormulaGrammar::CONV_XL_A1) )
+    {
+        XclExtLstRef xExtLst( new XclExtLst( GetRoot()  ) );
+        xExtLst->AddRecord( XclExpExtRef( new XclExpExtCalcPr( GetRoot(), rCalcConfig.meStringRefAddressSyntax ))  );
+        xExtLst->SaveXml(rStrm);
+    }
+
     rWorkbook->endElement( XML_workbook );
     rWorkbook.reset();
 }
diff --git a/sc/source/filter/excel/xeextlst.cxx b/sc/source/filter/excel/xeextlst.cxx
index 2639bec35fef..b6c781891dda 100644
--- a/sc/source/filter/excel/xeextlst.cxx
+++ b/sc/source/filter/excel/xeextlst.cxx
@@ -361,6 +361,51 @@ void XclExpExtConditionalFormatting::SaveXml( XclExpXmlStream& rStrm )
     rWorksheet->endElementNS( XML_x14, XML_conditionalFormatting );
 }
 
+XclExpExtCalcPr::XclExpExtCalcPr( const XclExpRoot& rRoot, formula::FormulaGrammar::AddressConvention eConv ):
+    XclExpExt( rRoot ),
+    meConv( eConv )
+{
+    maURI = OString("{7626C862-2A13-11E5-B345-FEFF819CDC9F}");
+
+    switch (meConv)
+    {
+        case formula::FormulaGrammar::CONV_OOO:
+            maSyntax = OString("CalcA1");
+            break;
+        case formula::FormulaGrammar::CONV_XL_A1:
+            maSyntax = OString("ExcelA1");
+            break;
+        case formula::FormulaGrammar::CONV_XL_R1C1:
+            maSyntax = OString("ExcelR1C1");
+            break;
+        case formula::FormulaGrammar::CONV_A1_XL_A1:
+            maSyntax = OString("CalcA1ExcelA1");
+            break;
+        case formula::FormulaGrammar::CONV_UNSPECIFIED:
+        case formula::FormulaGrammar::CONV_ODF:
+        case formula::FormulaGrammar::CONV_XL_OOX:
+        case formula::FormulaGrammar::CONV_LOTUS_A1:
+        case formula::FormulaGrammar::CONV_LAST:
+            maSyntax = OString("Unspecified");
+            break;
+    }
+}
+
+void XclExpExtCalcPr::SaveXml( XclExpXmlStream& rStrm )
+{
+    sax_fastparser::FSHelperPtr& rWorksheet = rStrm.GetCurrentStream();
+    rWorksheet->startElement( XML_ext,
+                                FSNS( XML_xmlns, XML_loext ), "http://schemas.libreoffice.org/",
+                                XML_uri, maURI.getStr(),
+                                FSEND );
+
+    rWorksheet->singleElementNS( XML_loext, XML_extCalcPr,
+                                 XML_stringRefSyntax, maSyntax.getStr(),
+                                 FSEND );
+
+    rWorksheet->endElement( XML_ext );
+}
+
 XclExpExtCondFormat::XclExpExtCondFormat( const XclExpRoot& rRoot ):
     XclExpExt( rRoot )
 {
diff --git a/sc/source/filter/inc/extlstcontext.hxx b/sc/source/filter/inc/extlstcontext.hxx
index 598adbd5f6e3..d77d51ae50d0 100644
--- a/sc/source/filter/inc/extlstcontext.hxx
+++ b/sc/source/filter/inc/extlstcontext.hxx
@@ -12,6 +12,7 @@
 
 #include "excelhandlers.hxx"
 #include "worksheetfragment.hxx"
+#include "workbookfragment.hxx"
 
 #include <boost/ptr_container/ptr_vector.hpp>
 
@@ -103,6 +104,27 @@ protected:
     virtual ::oox::core::ContextHandlerRef onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs ) SAL_OVERRIDE;
 };
 
+class ExtGlobalWorkbookContext : public WorkbookContextBase
+{
+public:
+    explicit ExtGlobalWorkbookContext( WorkbookContextBase& rFragment );
+
+protected:
+    virtual ::oox::core::ContextHandlerRef onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs ) SAL_OVERRIDE;
+    virtual void        onStartElement( const AttributeList& rAttribs ) SAL_OVERRIDE;
+
+private:
+};
+
+class ExtLstGlobalWorkbookContext : public WorkbookContextBase
+{
+public:
+    explicit ExtLstGlobalWorkbookContext( WorkbookFragment& rFragment );
+
+protected:
+    virtual ::oox::core::ContextHandlerRef onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs ) SAL_OVERRIDE;
+};
+
 } //namespace xls
 } //namespace oox
 
diff --git a/sc/source/filter/inc/xeextlst.hxx b/sc/source/filter/inc/xeextlst.hxx
index f148f836186e..fcb9a1d8a7ba 100644
--- a/sc/source/filter/inc/xeextlst.hxx
+++ b/sc/source/filter/inc/xeextlst.hxx
@@ -14,12 +14,14 @@
 #include "xeroot.hxx"
 
 #include "colorscale.hxx"
+#include "formulaopt.hxx"
 
 #include <memory>
 
 enum XclExpExtType
 {
-    XclExpExtDataBarType
+    XclExpExtDataBarType,
+    XclExpExtDataFooType
 };
 
 struct XclExpExtCondFormatData
@@ -32,8 +34,6 @@ struct XclExpExtCondFormatData
 
 /**
  * Base class for ext entries. Extend this class to provide the needed functionality
- *
- * Right now the only supported subclass is XclExpExtCondFormat
  */
 class XclExpExt : public XclExpRecordBase, public XclExpRoot
 {
@@ -167,6 +167,19 @@ private:
     XclExpRecordList< XclExpExtConditionalFormatting > maCF;
 };
 
+class XclExpExtCalcPr : public XclExpExt
+{
+public:
+    XclExpExtCalcPr( const XclExpRoot& rRoot, formula::FormulaGrammar::AddressConvention eConv );
+    virtual void SaveXml( XclExpXmlStream& rStrm ) SAL_OVERRIDE;
+
+    virtual XclExpExtType GetType() SAL_OVERRIDE { return XclExpExtDataFooType; }
+
+private:
+    formula::FormulaGrammar::AddressConvention meConv;
+    OString maSyntax;
+};
+
 class XclExtLst : public XclExpRecordBase, public XclExpRoot
 {
 public:
diff --git a/sc/source/filter/oox/extlstcontext.cxx b/sc/source/filter/oox/extlstcontext.cxx
index 7371f99574ba..f0980d54ffb5 100644
--- a/sc/source/filter/oox/extlstcontext.cxx
+++ b/sc/source/filter/oox/extlstcontext.cxx
@@ -249,6 +249,60 @@ ContextHandlerRef ExtLstGlobalContext::onCreateContext( sal_Int32 nElement, cons
     return this;
 }
 
+ExtGlobalWorkbookContext::ExtGlobalWorkbookContext( WorkbookContextBase& rFragment ):
+    WorkbookContextBase(rFragment)
+{
+}
+
+ContextHandlerRef ExtGlobalWorkbookContext::onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs )
+{
+    if (nElement == LOEXT_TOKEN(extCalcPr))
+    {
+        ScDocument* pDoc = &getScDocument();
+        sal_Int32 nToken = rAttribs.getToken( XML_stringRefSyntax, XML_CalcA1 );
+        ScCalcConfig aCalcConfig = pDoc->GetCalcConfig();
+
+        switch( nToken )
+        {
+             case XML_CalcA1:
+                aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_OOO );
+                break;
+             case XML_ExcelA1:
+                aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_XL_A1 );
+                break;
+             case XML_ExcelR1C1:
+                aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_XL_R1C1 );
+                break;
+             case XML_CalcA1ExcelA1:
+                aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_A1_XL_A1 );
+                break;
+             default:
+                aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_UNSPECIFIED );
+               break;
+        }
+        pDoc->SetCalcConfig(aCalcConfig);
+    }
+
+    return this;
+}
+
+void ExtGlobalWorkbookContext::onStartElement( const AttributeList& /*rAttribs*/ )
+{
+}
+
+ExtLstGlobalWorkbookContext::ExtLstGlobalWorkbookContext( WorkbookFragment& rFragment ):
+    WorkbookContextBase(rFragment)
+{
+}
+
+ContextHandlerRef ExtLstGlobalWorkbookContext::onCreateContext( sal_Int32 nElement, const AttributeList& )
+{
+    if (nElement == XLS_TOKEN( ext ))
+        return new ExtGlobalWorkbookContext( *this );
+
+    return this;
+}
+
 } //namespace oox
 } //namespace xls
 
diff --git a/sc/source/filter/oox/workbookfragment.cxx b/sc/source/filter/oox/workbookfragment.cxx
index 4b37d0f20c79..06881c02c5cf 100644
--- a/sc/source/filter/oox/workbookfragment.cxx
+++ b/sc/source/filter/oox/workbookfragment.cxx
@@ -46,6 +46,7 @@
 #include "worksheethelper.hxx"
 #include "worksheetfragment.hxx"
 #include "sheetdatacontext.hxx"
+#include "extlstcontext.hxx"
 #include "officecfg/Office/Common.hxx"
 
 #include "document.hxx"
@@ -110,6 +111,8 @@ ContextHandlerRef WorkbookFragment::onCreateContext( sal_Int32 nElement, const A
                 case XLS_TOKEN( workbookPr ):           getWorkbookSettings().importWorkbookPr( rAttribs );     break;
                 case XLS_TOKEN( calcPr ):               getWorkbookSettings().importCalcPr( rAttribs );         break;
                 case XLS_TOKEN( oleSize ):              getViewSettings().importOleSize( rAttribs );            break;
+
+                case XLS_TOKEN( extLst ):               return new ExtLstGlobalWorkbookContext( *this );
             }
         break;
 
diff --git a/sc/source/filter/oox/workbookhelper.cxx b/sc/source/filter/oox/workbookhelper.cxx
index d264e5621d6c..fc080cc0f8bf 100644
--- a/sc/source/filter/oox/workbookhelper.cxx
+++ b/sc/source/filter/oox/workbookhelper.cxx
@@ -533,18 +533,6 @@ void WorkbookGlobals::useInternalChartDataTable( bool bInternal )
 
 // private --------------------------------------------------------------------
 
-namespace {
-
-formula::FormulaGrammar::AddressConvention getConvention(css::uno::Reference<XDocumentProperties> xDocProps)
-{
-    if (xDocProps->getGenerator().startsWithIgnoreAsciiCase("Microsoft"))
-        return formula::FormulaGrammar::CONV_XL_A1;
-
-    return formula::FormulaGrammar::CONV_OOO;
-}
-
-}
-
 void WorkbookGlobals::initialize( bool bWorkbookFile )
 {
     maCellStyles = "CellStyles";
@@ -574,9 +562,13 @@ void WorkbookGlobals::initialize( bool bWorkbookFile )
 
     Reference< XDocumentPropertiesSupplier > xPropSupplier( mxDoc, UNO_QUERY);
     Reference< XDocumentProperties > xDocProps = xPropSupplier->getDocumentProperties();
-    ScCalcConfig aCalcConfig = mpDoc->GetCalcConfig();
-    aCalcConfig.meStringRefAddressSyntax = getConvention(xDocProps);
-    mpDoc->SetCalcConfig(aCalcConfig);
+
+    if (xDocProps->getGenerator().startsWithIgnoreAsciiCase("Microsoft"))
+    {
+        ScCalcConfig aCalcConfig = mpDoc->GetCalcConfig();
+        aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_XL_A1 ) ;
+        mpDoc->SetCalcConfig(aCalcConfig);
+    }
 
     mxDocImport.reset(new ScDocumentImport(*mpDoc));
 
diff --git a/sc/source/ui/optdlg/calcoptionsdlg.cxx b/sc/source/ui/optdlg/calcoptionsdlg.cxx
index 2b82a92bb0d8..13e948722a07 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.cxx
+++ b/sc/source/ui/optdlg/calcoptionsdlg.cxx
@@ -46,6 +46,8 @@ formula::FormulaGrammar::AddressConvention toAddressConvention(sal_Int32 nPos)
             return formula::FormulaGrammar::CONV_XL_A1;
         case 3:
             return formula::FormulaGrammar::CONV_XL_R1C1;
+        case 4:
+            return formula::FormulaGrammar::CONV_A1_XL_A1;
         case 0:
         default:
             ;
@@ -54,6 +56,24 @@ formula::FormulaGrammar::AddressConvention toAddressConvention(sal_Int32 nPos)
     return formula::FormulaGrammar::CONV_UNSPECIFIED;
 }
 
+sal_Int32 toSelectedItem( formula::FormulaGrammar::AddressConvention eConv )
+{
+    switch (eConv)
+    {
+        case formula::FormulaGrammar::CONV_OOO:
+            return 1;
+        case formula::FormulaGrammar::CONV_XL_A1:
+            return 2;
+        case formula::FormulaGrammar::CONV_XL_R1C1:
+            return 3;
+        case formula::FormulaGrammar::CONV_A1_XL_A1:
+            return 4;
+        default:
+            ;
+    }
+    return 0;
+}
+
 }
 
 ScCalcOptionsDialog::ScCalcOptionsDialog(vcl::Window* pParent, const ScCalcConfig& rConfig)
@@ -79,7 +99,7 @@ ScCalcOptionsDialog::ScCalcOptionsDialog(vcl::Window* pParent, const ScCalcConfi
     mpEmptyAsZero->SetClickHdl(LINK(this, ScCalcOptionsDialog, AsZeroModifiedHdl));
 
     get(mpSyntax,"comboSyntaxRef");
-    mpSyntax->SelectEntryPos(rConfig.meStringRefAddressSyntax);
+    mpSyntax->SelectEntryPos( toSelectedItem(rConfig.meStringRefAddressSyntax) );
     mpSyntax->SetSelectHdl(LINK(this, ScCalcOptionsDialog, SyntaxModifiedHdl));
 
     get(mpUseOpenCL,"CBUseOpenCL");
@@ -208,7 +228,7 @@ IMPL_LINK(ScCalcOptionsDialog, ConversionModifiedHdl, ListBox*, pConv )
 
 IMPL_LINK(ScCalcOptionsDialog, SyntaxModifiedHdl, ListBox*, pSyntax)
 {
-    maConfig.meStringRefAddressSyntax = toAddressConvention(pSyntax->GetSelectEntryPos());
+    maConfig.SetStringRefSyntax(toAddressConvention(pSyntax->GetSelectEntryPos()));
     return 0;
 }
 
diff --git a/sc/source/ui/unoobj/confuno.cxx b/sc/source/ui/unoobj/confuno.cxx
index a56c36f8293f..0ddaaa114f35 100644
--- a/sc/source/ui/unoobj/confuno.cxx
+++ b/sc/source/ui/unoobj/confuno.cxx
@@ -31,6 +31,7 @@
 
 #include <com/sun/star/beans/PropertyAttribute.hpp>
 #include <cppuhelper/supportsservice.hxx>
+#include <formula/grammar.hxx>
 #include <sfx2/printer.hxx>
 #include <xmloff/xmluconv.hxx>
 #include <rtl/ustrbuf.hxx>
@@ -75,6 +76,7 @@ static const SfxItemPropertyMapEntry* lcl_GetConfigPropertyMap()
         {OUString(SC_UNO_SHAREDOC),     0,  cppu::UnoType<bool>::get(),              0, 0},
         {OUString(SC_UNO_MODIFYPASSWORDINFO), 0,  cppu::UnoType<uno::Sequence< beans::PropertyValue >>::get(),              0, 0},
         {OUString(SC_UNO_EMBED_FONTS), 0,  cppu::UnoType<bool>::get(),              0, 0},
+        {OUString(SC_UNO_SYNTAXSTRINGREF), 0,  cppu::UnoType<sal_Int16>::get(),     0, 0},
         { OUString(), 0, css::uno::Type(), 0, 0 }
     };
     return aConfigPropertyMap_Impl;
@@ -297,6 +299,29 @@ void SAL_CALL ScDocumentConfiguration::setPropertyValue(
                 rDoc.SetIsUsingEmbededFonts(bVal);
             }
         }
+        else if ( aPropertyName == SC_UNO_SYNTAXSTRINGREF )
+        {
+            ScCalcConfig aCalcConfig = rDoc.GetCalcConfig();
+            sal_Int16 nUno = 0;
+
+            if( aValue >>= nUno )
+            {
+                switch (nUno)
+                {
+                    case 0: // CONV_OOO
+                    case 2: // CONV_XL_A1
+                    case 3: // CONV_XL_R1C1
+                    case 7: // CONV_A1_XL_A1
+                        aCalcConfig.SetStringRefSyntax( static_cast<formula::FormulaGrammar::AddressConvention>( nUno ) );
+                        break;
+                    default:
+                        aCalcConfig.SetStringRefSyntax( formula::FormulaGrammar::CONV_UNSPECIFIED );
+                        break;
+
+                }
+                rDoc.SetCalcConfig( aCalcConfig );
+            }
+        }
 
         else
         {
@@ -433,6 +458,39 @@ uno::Any SAL_CALL ScDocumentConfiguration::getPropertyValue( const OUString& aPr
         {
             aRet <<= rDoc.IsUsingEmbededFonts();
         }
+        else if ( aPropertyName == SC_UNO_SYNTAXSTRINGREF )
+        {
+            ScCalcConfig aCalcConfig = rDoc.GetCalcConfig();
+
+            // write if it has been read|imported or explicitly changed
+            // or if ref syntax isn't what would be native for our file format
+            // i.e. CalcA1 in this case
+            if ( aCalcConfig.mbHasStringRefSyntax ||
+                 (aCalcConfig.meStringRefAddressSyntax != formula::FormulaGrammar::CONV_OOO) )
+            {
+                formula::FormulaGrammar::AddressConvention aConv = aCalcConfig.meStringRefAddressSyntax;
+
+                switch (aConv)
+                {
+                    case formula::FormulaGrammar::CONV_OOO:
+                    case formula::FormulaGrammar::CONV_XL_A1:
+                    case formula::FormulaGrammar::CONV_XL_R1C1:
+                    case formula::FormulaGrammar::CONV_A1_XL_A1:
+                         aRet <<= static_cast<sal_Int16>( aConv );
+                         break;
+
+                    case formula::FormulaGrammar::CONV_UNSPECIFIED:
+                    case formula::FormulaGrammar::CONV_ODF:
+                    case formula::FormulaGrammar::CONV_XL_OOX:
+                    case formula::FormulaGrammar::CONV_LOTUS_A1:
+                    case formula::FormulaGrammar::CONV_LAST:
+                    {
+                        aRet <<= sal_Int16(9999);
+                        break;
+                    }
+                 }
+             }
+        }
 
         else
         {
diff --git a/sc/uiconfig/scalc/ui/formulacalculationoptions.ui b/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
index 20cfab0c7554..59641e639197 100644
--- a/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
+++ b/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
@@ -86,6 +86,7 @@
                           <item id="1">Calc A1</item>
                           <item id="2">Excel A1</item>
                           <item id="3">Excel R1C1</item>
+                          <item id="4">Calc A1 | Excel A1</item>
                         </items>
                       </object>
                       <packing>