tdf#104310: Accept x12ac lists and fallbacks in dataValidations

Change-Id: I42cf20fcfe3ec03ebd09923be509a9d11e0b40da
Reviewed-on: https://gerrit.libreoffice.org/31516
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Kohei Yoshida <libreoffice@kohei.us>
(cherry picked from commit ce17ebb69500530c978767b1389c9e8341acb9bf)
Reviewed-on: https://gerrit.libreoffice.org/31529
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>
Tested-by: Mike Kaganski <mike.kaganski@collabora.com>

10 files changed, 155 insertions, 23 deletions

diff --git a/oox/source/core/fragmenthandler2.cxx b/oox/source/core/fragmenthandler2.cxx
index 9a708d569d56..ba3f880cde60 100644
--- a/oox/source/core/fragmenthandler2.cxx
+++ b/oox/source/core/fragmenthandler2.cxx
@@ -76,6 +76,7 @@ bool FragmentHandler2::prepareMceContext( sal_Int32 nElement, const AttributeLis
                 {
                     "p14",
                     "p15",
+                    "x12ac",
                 };
 
                 if (std::find(aSupportedNS.begin(), aSupportedNS.end(), aRequires) != aSupportedNS.end())
diff --git a/oox/source/core/xmlfilterbase.cxx b/oox/source/core/xmlfilterbase.cxx
index fae720f25da7..952bf5ead0e2 100644
--- a/oox/source/core/xmlfilterbase.cxx
+++ b/oox/source/core/xmlfilterbase.cxx
@@ -146,7 +146,10 @@ struct NamespaceIds: public rtl::StaticWithInit<
             {"http://schemas.microsoft.com/office/powerpoint/2010/main",
              NMSP_p14},
             {"http://schemas.microsoft.com/office/powerpoint/2012/main",
-             NMSP_p15}};
+             NMSP_p15},
+            {"http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac",
+             NMSP_x12ac},
+        };
     }
 };
 
diff --git a/oox/source/token/namespaces-strict.txt b/oox/source/token/namespaces-strict.txt
index f9a4633488c7..0f606f775213 100644
--- a/oox/source/token/namespaces-strict.txt
+++ b/oox/source/token/namespaces-strict.txt
@@ -83,6 +83,7 @@ p14                     http://schemas.microsoft.com/office/powerpoint/2010/main
 # MSO 2012/2013 extensions ---------------------------------------------------------
 
 p15                     http://schemas.microsoft.com/office/powerpoint/2012/main
+x12ac                   http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac
 
 # extlst namespaces
 
diff --git a/oox/source/token/namespaces.hxx.tail b/oox/source/token/namespaces.hxx.tail
index 89f8c1c4d10b..17770dcbbdae 100644
--- a/oox/source/token/namespaces.hxx.tail
+++ b/oox/source/token/namespaces.hxx.tail
@@ -46,6 +46,7 @@ inline sal_Int32 getNamespace( sal_Int32 nToken ) { return nToken & NMSP_MASK; }
 #define R_TOKEN( token )        OOX_TOKEN( officeRel, token )
 #define VML_TOKEN( token )      OOX_TOKEN( vml, token )
 #define VMLX_TOKEN( token )     OOX_TOKEN( vmlExcel, token )
+#define X12AC_TOKEN( token )    OOX_TOKEN( x12ac, token )
 #define XDR_TOKEN( token )      OOX_TOKEN( dmlSpreadDr, token )
 #define XLS_TOKEN( token )      OOX_TOKEN( xls, token )
 #define XLS14_TOKEN( token )    OOX_TOKEN( xls14Lst, token )
diff --git a/oox/source/token/namespaces.txt b/oox/source/token/namespaces.txt
index 792057256e81..4b6f49a56ef8 100644
--- a/oox/source/token/namespaces.txt
+++ b/oox/source/token/namespaces.txt
@@ -83,6 +83,7 @@ p14                     http://schemas.microsoft.com/office/powerpoint/2010/main
 # MSO 2012/2013 extensions ---------------------------------------------------------
 
 p15                     http://schemas.microsoft.com/office/powerpoint/2012/main
+x12ac                   http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac
 
 # extlst namespaces
 
diff --git a/oox/source/token/tokens.txt b/oox/source/token/tokens.txt
index b113c84ee209..6d4fcb8a47f5 100644
--- a/oox/source/token/tokens.txt
+++ b/oox/source/token/tokens.txt
@@ -5781,6 +5781,7 @@ writeProtection
 wsDr
 wsp
 x
+x12ac
 x14
 xAlign
 xIllusions
diff --git a/sc/qa/unit/bugfix-test.cxx b/sc/qa/unit/bugfix-test.cxx
index 9b7f2b772ded..34991092ebba 100644
--- a/sc/qa/unit/bugfix-test.cxx
+++ b/sc/qa/unit/bugfix-test.cxx
@@ -242,20 +242,42 @@ void ScFiltersTest::testRhbz1390776()
 
 void ScFiltersTest::testTdf104310()
 {
-    ScDocShellRef xDocSh = loadDoc("tdf104310.", FORMAT_XLSX);
-    ScDocument& rDoc = xDocSh->GetDocument();
-
-    const ScValidationData* pData = rDoc.GetValidationEntry(1);
-    CPPUNIT_ASSERT(pData);
-
-    // Make sure the list is correct.
-    std::vector<ScTypedStrData> aList;
-    pData->FillSelectionList(aList, ScAddress(0, 1, 0));
-    CPPUNIT_ASSERT_EQUAL(size_t(5), aList.size());
-    for (size_t i = 0; i < 5; ++i)
-        CPPUNIT_ASSERT_DOUBLES_EQUAL(double(i+1), aList[i].GetValue(), 1e-8);
-
-    xDocSh->DoClose();
+    // 1. Test x14 extension
+    {
+        ScDocShellRef xDocSh = loadDoc("tdf104310.", FORMAT_XLSX);
+        ScDocument& rDoc = xDocSh->GetDocument();
+
+        const ScValidationData* pData = rDoc.GetValidationEntry(1);
+        CPPUNIT_ASSERT(pData);
+
+        // Make sure the list is correct.
+        std::vector<ScTypedStrData> aList;
+        pData->FillSelectionList(aList, ScAddress(0, 1, 0));
+        CPPUNIT_ASSERT_EQUAL(size_t(5), aList.size());
+        for (size_t i = 0; i < 5; ++i)
+            CPPUNIT_ASSERT_DOUBLES_EQUAL(double(i + 1), aList[i].GetValue(), 1e-8);
+
+        xDocSh->DoClose();
+    }
+
+    // 2. Test x12ac extension
+    {
+        ScDocShellRef xDocSh = loadDoc("tdf104310-2.", FORMAT_XLSX);
+        ScDocument& rDoc = xDocSh->GetDocument();
+
+        const ScValidationData* pData = rDoc.GetValidationEntry(1);
+        CPPUNIT_ASSERT(pData);
+
+        // Make sure the list is correct.
+        std::vector<ScTypedStrData> aList;
+        pData->FillSelectionList(aList, ScAddress(0, 1, 0));
+        CPPUNIT_ASSERT_EQUAL(size_t(3), aList.size());
+        CPPUNIT_ASSERT_EQUAL(OUString("1"),   aList[0].GetString());
+        CPPUNIT_ASSERT_EQUAL(OUString("2,3"), aList[1].GetString());
+        CPPUNIT_ASSERT_EQUAL(OUString("4"),   aList[2].GetString());
+
+        xDocSh->DoClose();
+    }
 }
 
 ScFiltersTest::ScFiltersTest()
diff --git a/sc/qa/unit/data/xlsx/tdf104310-2.xlsx b/sc/qa/unit/data/xlsx/tdf104310-2.xlsx
new file mode 100644
index 000000000000..dc5e9ac36bb5
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf104310-2.xlsx
diff --git a/sc/source/filter/inc/worksheetfragment.hxx b/sc/source/filter/inc/worksheetfragment.hxx
index 2fb52cbf42ee..9fc348bf73a8 100644
--- a/sc/source/filter/inc/worksheetfragment.hxx
+++ b/sc/source/filter/inc/worksheetfragment.hxx
@@ -36,6 +36,8 @@ public:
     void                           importDataValidation(const AttributeList& rAttribs);
     /** Imports the DATAVALIDATION record containing data validation settings. */
     static void                    importDataValidation(SequenceInputStream& rStrm, ::oox::xls::WorksheetHelper& rTarget);
+    bool                           isFormula1Set() const { return !mFormula1.isEmpty(); }
+    bool                           isFormula2Set() const { return !mFormula2.isEmpty(); }
 private:
     ::std::unique_ptr< ValidationModel > mxValModel;
     OUString                             mSqref;
@@ -43,6 +45,30 @@ private:
     OUString                             mFormula2;
 };
 
+// For following types of validations:
+//
+//  <dataValidations count="1">
+//    <dataValidation allowBlank="true" operator="equal" showDropDown="false" showErrorMessage="true" showInputMessage="false" sqref="C1:C5" type="list">
+//      <formula1>Sheet1!$A$1:$A$5</formula1>
+//      <formula2>0</formula2>
+//    </dataValidation>
+//  </dataValidations>
+//
+// or
+//
+//  <dataValidations count="1">
+//    <dataValidation type="list" operator="equal" allowBlank="1" showErrorMessage="1" sqref="A1">
+//      <mc:AlternateContent xmlns:x12ac="http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
+//        <mc:Choice Requires="x12ac">
+//          <x12ac:list>1,"2,3",4</x12ac:list>
+//        </mc:Choice>
+//        <mc:Fallback>
+//          <formula1>"1,2,3,4"</formula1>
+//        </mc:Fallback>
+//      </mc:AlternateContent>
+//    </dataValidation>
+//  </dataValidations>
+
 class DataValidationsContext : public WorksheetContextBase, private DataValidationsContext_Base
 {
 public:
@@ -56,6 +82,21 @@ protected:
     virtual ::oox::core::ContextHandlerRef onCreateRecordContext( sal_Int32 nRecId, SequenceInputStream& rStrm ) override;
 };
 
+// For following types of validations:
+//
+//  <extLst>
+//    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
+//      <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
+//        <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
+//          <x14:formula1>
+//            <xm:f>Sheet1!$A$2:$A$272</xm:f>
+//          </x14:formula1>
+//          <xm:sqref>A6:A22</xm:sqref>
+//        </x14:dataValidation>
+//      </x14:dataValidations>
+//    </ext>
+//  </extLst>
+
 class ExtDataValidationsContext : public WorksheetContextBase, private DataValidationsContext_Base
 {
 public:
diff --git a/sc/source/filter/oox/worksheetfragment.cxx b/sc/source/filter/oox/worksheetfragment.cxx
index 30872d8f02b9..8a2f1e07c156 100644
--- a/sc/source/filter/oox/worksheetfragment.cxx
+++ b/sc/source/filter/oox/worksheetfragment.cxx
@@ -97,12 +97,9 @@ void DataValidationsContext_Base::SetValidation(::oox::xls::WorksheetHelper& rTa
 void DataValidationsContext_Base::importDataValidation(const AttributeList& rAttribs)
 {
     mxValModel.reset(new ValidationModel);
-    OUString aSqref = rAttribs.getString(XML_sqref, OUString());
-    // Only set mSqref if it is set in attributes, to avoid owerwriting already set using SetSqref
-    if (!aSqref.isEmpty())
-    {
-        mSqref = aSqref;
-    }
+    mFormula1.clear();
+    mFormula2.clear();
+    mSqref = rAttribs.getString(XML_sqref, OUString());
     mxValModel->maInputTitle = rAttribs.getXString(XML_promptTitle, OUString());
     mxValModel->maInputMessage = rAttribs.getXString(XML_prompt, OUString());
     mxValModel->maErrorTitle = rAttribs.getXString(XML_errorTitle, OUString());
@@ -160,7 +157,7 @@ DataValidationsContext::DataValidationsContext( WorksheetFragmentBase& rFragment
 
 ContextHandlerRef DataValidationsContext::onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs )
 {
-    switch( getCurrentElement() )
+    switch( getCurrentElementWithMce() )
     {
         case XLS_TOKEN( dataValidations ):
             if( nElement == XLS_TOKEN( dataValidation ) )
@@ -172,15 +169,76 @@ ContextHandlerRef DataValidationsContext::onCreateContext( sal_Int32 nElement, c
         case XLS_TOKEN( dataValidation ):
             switch( nElement )
             {
+                case MCE_TOKEN( AlternateContent ):
                 case XLS_TOKEN( formula1 ):
                 case XLS_TOKEN( formula2 ):
                     return this;    // collect formulas in onCharacters()
             }
         break;
+        case MCE_TOKEN( AlternateContent ):
+            switch( nElement )
+            {
+                case MCE_TOKEN( Choice ):
+                case MCE_TOKEN( Fallback ):
+                    return this;
+            }
+        break;
+        case MCE_TOKEN( Choice ):
+            switch( nElement )
+            {
+                case X12AC_TOKEN( list ):
+                    return this;
+            }
+        break;
+        case MCE_TOKEN( Fallback ):
+            switch( nElement )
+            {
+                case XLS_TOKEN( formula1 ):
+                    if (!isFormula1Set()) // only if more preferable choice was not used
+                        return this;      // collect formulas in onCharacters()
+                break;
+                case XLS_TOKEN( formula2 ):
+                    if (!isFormula2Set()) // only if more preferable choice was not used
+                        return this;      // collect formulas in onCharacters()
+                break;
+            }
+        break;
     }
     return nullptr;
 }
 
+namespace {
+// Convert strings like 1,"2,3",4 to form "1","2,3","4"
+OUString NormalizeOoxList(const OUString& aList)
+{
+    OUStringBuffer aResult("\"");
+    bool bInsideQuotes = false;
+    const sal_Int32 nLen = aList.getLength();
+    for (sal_Int32 i = 0; i < nLen; ++i)
+    {
+        sal_Unicode ch = aList[i];
+
+        switch (ch)
+        {
+            case L'"':
+                bInsideQuotes = !bInsideQuotes;
+                break;
+            case L',':
+                if (!bInsideQuotes)
+                {
+                    aResult.append("\",\"");
+                    break;
+                }
+                SAL_FALLTHROUGH;
+            default:
+                aResult.append(ch);
+                break;
+        }
+    }
+    return aResult.append('"').makeStringAndClear();
+}
+}
+
 void DataValidationsContext::onCharacters( const OUString& rChars )
 {
     switch( getCurrentElement() )
@@ -191,12 +249,15 @@ void DataValidationsContext::onCharacters( const OUString& rChars )
         case XLS_TOKEN( formula2 ):
             SetFormula2( rChars );
         break;
+        case X12AC_TOKEN( list ):
+            SetFormula1( NormalizeOoxList( rChars ) );
+        break;
     }
 }
 
 void DataValidationsContext::onEndElement()
 {
-    if( isCurrentElement( XLS_TOKEN( dataValidation ) ) )
+    if( getCurrentElementWithMce() == XLS_TOKEN( dataValidation ) )
     {
         SetValidation( *this );
     }