[gnumeric] xlsx: improve excel-specific validation import.

[Morten Welinder <mortenw src gnome org>]

commit 65fb13b91e48737e9178e0a35ba5f869fd0b0380
Author: Morten Welinder <terra gnome org>
Date:   Sun May 30 18:42:37 2021 -0400

    xlsx: improve excel-specific validation import.
    
    MS, in its wisdom, decided not to use the standard file format for
    validations, but invent its own under "ext".  Sigh.
    
    Also fixed the sense of showing the dropdown which is inverted relative
    to the spec.

 NEWS                       |  1 +
 plugins/excel/xlsx-read.c  | 46 ++++++++++++++++++++++++++++++++++++----------
 plugins/excel/xlsx-utils.h |  1 +
 plugins/excel/xlsx-write.c |  5 +++--
 4 files changed, 41 insertions(+), 12 deletions(-)
---
diff --git a/NEWS b/NEWS
index faad6163d..30cf5ce89 100644
--- a/NEWS
+++ b/NEWS
@@ -11,6 +11,7 @@ Morten:
        * Improve CELL("format",...)  [#576]
        * wk4 import improvements.  (For no good reason.)
        * Fix some -fsanitize=undefined problems.
+       * Improve excel-specific xlsx import of validation.
 
 --------------------------------------------------------------------------
 Gnumeric 1.12.49
diff --git a/plugins/excel/xlsx-read.c b/plugins/excel/xlsx-read.c
index 6bb8eb52d..a5bdce919 100644
--- a/plugins/excel/xlsx-read.c
+++ b/plugins/excel/xlsx-read.c
@@ -287,6 +287,8 @@ static GsfXMLInNS const xlsx_ns[] = {
        GSF_XML_IN_NS (XL_NS_SS,        "http://schemas.openxmlformats.org/spreadsheetml/2006/7/main";),       
    /* Office 12 BETA-2 Technical Refresh */
        GSF_XML_IN_NS (XL_NS_SS,        "http://schemas.openxmlformats.org/spreadsheetml/2006/5/main";),       
    /* Office 12 BETA-2 */
        GSF_XML_IN_NS (XL_NS_SS,        "http://schemas.microsoft.com/office/excel/2006/2";),                  
    /* Office 12 BETA-1 Technical Refresh */
+       GSF_XML_IN_NS (XL_NS_MSSS,      "http://schemas.microsoft.com/office/excel/2006/main";),               
    /* Office 14??? */
+       GSF_XML_IN_NS (XL_NS_SS,        "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main";),     
    /* Office 14??? */
        GSF_XML_IN_NS (XL_NS_SS_DRAW,   
"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing";),   /* Office 12 BETA-2 */
        GSF_XML_IN_NS (XL_NS_SS_DRAW,   
"http://schemas.openxmlformats.org/drawingml/2006/3/spreadsheetDrawing";), /* Office 12 BETA-2 Technical 
Refresh */
        GSF_XML_IN_NS (XL_NS_CHART,     "http://schemas.openxmlformats.org/drawingml/2006/3/chart";),          
    /* Office 12 BETA-2 */
@@ -2239,17 +2241,20 @@ xlsx_CT_DataValidation_begin (GsfXMLIn *xin, xmlChar const **attrs)
        state->validation_regions = g_slist_reverse (
                xlsx_parse_sqref (xin, refs));
 
-       if (NULL == state->validation_regions)
-               return;
-
-       if (showErrorMessage) {
+       if (state->validation_regions) {
                GnmRange const *r = state->validation_regions->data;
                state->pos = r->start;
+       } else {
+               state->pos.col = state->pos.row = 0;
+       }
+
+       if (showErrorMessage) {
                state->validation = gnm_validation_new
                        (val_style, val_type, val_op,
                         state->sheet,
                         errorTitle, error,
-                        NULL, NULL, allowBlank, showDropDown);
+                        NULL, NULL, allowBlank, !showDropDown);
+               // Note: inverted sense of showDropDown
        }
 
        if (showInputMessage && (NULL != promptTitle || NULL != prompt))
@@ -2299,12 +2304,25 @@ xlsx_CT_DataValidation_end (GsfXMLIn *xin, G_GNUC_UNUSED GsfXMLBlob *blob)
        state->pos.col = state->pos.row = -1;
 }
 
+static void
+xlsx_CT_DataValidation_sqref_end (GsfXMLIn *xin, G_GNUC_UNUSED GsfXMLBlob *blob)
+{
+       XLSXReadState *state = (XLSXReadState *)xin->user_state;
+       const char *s = xin->content->str;
+
+       state->validation_regions =
+               g_slist_concat (
+                       g_slist_reverse (xlsx_parse_sqref (xin, s)),
+                       state->validation_regions);
+}
+
 static void
 xlsx_validation_expr (GsfXMLIn *xin, G_GNUC_UNUSED GsfXMLBlob *blob)
 {
        XLSXReadState *state = (XLSXReadState *)xin->user_state;
        GnmParsePos pp;
        GnmExprTop const *texpr;
+       int i = xin->node->user_data.v_int;
 
        if (state->validation == NULL)
                return;
@@ -2314,8 +2332,7 @@ xlsx_validation_expr (GsfXMLIn *xin, G_GNUC_UNUSED GsfXMLBlob *blob)
                state->pos.col, state->pos.row);
        texpr = xlsx_parse_expr (xin, xin->content->str, &pp);
        if (NULL != texpr) {
-               gnm_validation_set_expr (state->validation, texpr,
-                       xin->node->user_data.v_int);
+               gnm_validation_set_expr (state->validation, texpr, i);
                gnm_expr_top_unref (texpr);
        }
 }
@@ -3254,7 +3271,8 @@ xlsx_ext_begin (GsfXMLIn *xin, xmlChar const **attrs)
        if (!warned)
                xlsx_warning (xin,
                              _("Encountered uninterpretable \"ext\" extension with missing namespace"));
-       gsf_xml_in_set_silent_unknowns (xin, TRUE);
+       if (!gnm_debug_flag ("xlsxext"))
+               gsf_xml_in_set_silent_unknowns (xin, TRUE);
 }
 
 static void
@@ -3459,9 +3477,17 @@ xlsx_rich_text (GsfXMLIn *xin, G_GNUC_UNUSED GsfXMLBlob *blob)
 static GsfXMLInNode const xlsx_sheet_dtd[] = {
 GSF_XML_IN_NODE_FULL (START, START, -1, NULL, GSF_XML_NO_CONTENT, FALSE, TRUE, NULL, NULL, 0),
 GSF_XML_IN_NODE_FULL (START, SHEET, XL_NS_SS, "worksheet", GSF_XML_NO_CONTENT, FALSE, TRUE, NULL, 
&xlsx_CT_worksheet, 0),
-  GSF_XML_IN_NODE (SHEET, EXTLST, XL_NS_SS, "extLst", GSF_XML_NO_CONTENT, NULL, NULL),
-    GSF_XML_IN_NODE (EXTLST, EXTITEM, XL_NS_SS, "ext", GSF_XML_NO_CONTENT, &xlsx_ext_begin, &xlsx_ext_end),
+  GSF_XML_IN_NODE_FULL (SHEET, EXTLST, XL_NS_SS, "extLst", GSF_XML_NO_CONTENT, FALSE, TRUE, NULL, NULL, 0),
+    GSF_XML_IN_NODE_FULL (EXTLST, EXTITEM, XL_NS_SS, "ext", GSF_XML_NO_CONTENT, FALSE, TRUE, 
&xlsx_ext_begin, &xlsx_ext_end, 0),
       GSF_XML_IN_NODE (EXTITEM, EXT_TABTEXTCOLOR, XL_NS_GNM_EXT, "tabTextColor", GSF_XML_NO_CONTENT, 
&xlsx_ext_tabtextcolor, NULL),
+      GSF_XML_IN_NODE_FULL (EXTITEM, EXT_DataValidations, XL_NS_SS, "dataValidations", GSF_XML_NO_CONTENT, 
FALSE, TRUE, NULL, NULL, 0),
+       GSF_XML_IN_NODE (EXT_DataValidations, EXT_DataValidation, XL_NS_SS, "dataValidation", 
GSF_XML_NO_CONTENT,
+                        &xlsx_CT_DataValidation_begin, &xlsx_CT_DataValidation_end),
+         GSF_XML_IN_NODE_FULL (EXT_DataValidation, DATAVAL_FORMULA1, XL_NS_SS, "formula1", 
GSF_XML_NO_CONTENT, FALSE, FALSE, NULL, NULL, 0),
+            GSF_XML_IN_NODE_FULL (DATAVAL_FORMULA1, DATAVAL_F1, XL_NS_MSSS, "f", GSF_XML_CONTENT, FALSE, 
FALSE, NULL, &xlsx_validation_expr, 0),
+         GSF_XML_IN_NODE_FULL (EXT_DataValidation, DATAVAL_FORMULA2, XL_NS_SS, "formula2", 
GSF_XML_NO_CONTENT, FALSE, FALSE, NULL, NULL, 1),
+            GSF_XML_IN_NODE_FULL (DATAVAL_FORMULA2, DATAVAL_F2, XL_NS_MSSS, "f", GSF_XML_CONTENT, FALSE, 
FALSE, NULL, &xlsx_validation_expr, 1),
+         GSF_XML_IN_NODE (EXT_DataValidation, DATAVAL_SQREF, XL_NS_MSSS, "sqref", GSF_XML_CONTENT, NULL, 
xlsx_CT_DataValidation_sqref_end),
   GSF_XML_IN_NODE (SHEET, PROPS, XL_NS_SS, "sheetPr", GSF_XML_NO_CONTENT, &xlsx_CT_SheetPr, NULL),
     GSF_XML_IN_NODE (PROPS, OUTLINE_PROPS, XL_NS_SS, "outlinePr", GSF_XML_NO_CONTENT, NULL, NULL),
     GSF_XML_IN_NODE (PROPS, TAB_COLOR, XL_NS_SS, "tabColor", GSF_XML_NO_CONTENT, &xlsx_sheet_tabcolor, NULL),
diff --git a/plugins/excel/xlsx-utils.h b/plugins/excel/xlsx-utils.h
index 3188dd26b..cdb2a5330 100644
--- a/plugins/excel/xlsx-utils.h
+++ b/plugins/excel/xlsx-utils.h
@@ -29,6 +29,7 @@
 enum {
        XL_NS_SS,
        XL_NS_SS_DRAW,
+       XL_NS_MSSS,
        XL_NS_CHART,
        XL_NS_CHART_DRAW,
        XL_NS_DRAW,
diff --git a/plugins/excel/xlsx-write.c b/plugins/excel/xlsx-write.c
index 7337564d9..e3071e518 100644
--- a/plugins/excel/xlsx-write.c
+++ b/plugins/excel/xlsx-write.c
@@ -2016,8 +2016,9 @@ xlsx_write_validation (XLValInputPair const *vip, G_GNUC_UNUSED gpointer dummy,
 
                if (vip->v->allow_blank)
                        xlsx_add_bool (info->xml, "allowBlank", TRUE);
-               if (vip->v->use_dropdown)
-                       xlsx_add_bool (info->xml, "showDropDown", TRUE);
+
+               // Inverted sense
+               xlsx_add_bool (info->xml, "showDropDown", !vip->v->use_dropdown);
 
                if (NULL != vip->v->title)
                        gsf_xml_out_add_cstr (info->xml, "errorTitle", vip->v->title->str);