tdf#64086 tdf#143623 tdf#66250 XLSX: fix named ranges in charts

tdf#64086: fix broken XLSX import of named ranges in charts

Do not create inner data table in Calc for charts, if the
data source contains named ranges. Use the named ranges
(local sheet names and global names) to find the data
source of charts, so it will be updated correctly if we
modify the values in cells.

Second part:

tdf#143623: chart OOXML, offapi: export the named ranges in charts

with the proper special (non-ooxml-standard) Excel syntax.
We need to add "[0]" characters before a global named range
for proper refreshing of chart data. Also we have to add the
sheet name to the local named ranges even if it's on the same
sheet, but only in case of charts. Because of this, add property
RefConventionChartOOXML to com::sun::star::sheet::FormulaParser,
which specifies that use special OOXML chart syntax in case of
OOXML reference convention, when parsing a formula string.

Third part:

tdf#66250 ODF chart: export the reference of named ranges

in case of charts, if the named range contains a valid reference.

Note: maybe export the name of named ranges would be a nicer
solution in the future.

Follow-up of commit 3c766512984feff739377d0f0af46558ee7139fd
"Related: tdf#64086 Add FormulaGrammar::isRefConventionOOXML()".

Thanks to Eike Rathke for his help.

Change-Id: I10d8563fb436092e833682f331c25b0c0829ef86
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/118862
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

2 files changed, 17 insertions, 0 deletions

diff --git a/chart2/qa/extras/chart2export2.cxx b/chart2/qa/extras/chart2export2.cxx
index e05d984f75cf..adde208d620c 100644
--- a/chart2/qa/extras/chart2export2.cxx
+++ b/chart2/qa/extras/chart2export2.cxx
@@ -97,6 +97,7 @@ public:
     void testTdf138181();
     void testCustomShapeText();
     void testuserShapesXLSX();
+    void testNameRangeXLSX();
 
     CPPUNIT_TEST_SUITE(Chart2ExportTest2);
     CPPUNIT_TEST(testSetSeriesToSecondaryAxisXLSX);
@@ -157,6 +158,7 @@ public:
     CPPUNIT_TEST(testTdf138181);
     CPPUNIT_TEST(testCustomShapeText);
     CPPUNIT_TEST(testuserShapesXLSX);
+    CPPUNIT_TEST(testNameRangeXLSX);
     CPPUNIT_TEST_SUITE_END();
 };
 
@@ -1420,6 +1422,21 @@ void Chart2ExportTest2::testuserShapesXLSX()
     CPPUNIT_ASSERT(!xRange->getString().isEmpty());
 }
 
+void Chart2ExportTest2::testNameRangeXLSX()
+{
+    load(u"/chart2/qa/extras/data/xlsx/", "chart_with_name_range.xlsx");
+    xmlDocUniquePtr pXmlDoc = parseExport("xl/charts/chart", "Calc Office Open XML");
+    CPPUNIT_ASSERT(pXmlDoc);
+    // test the syntax of local range name on the the local sheet.
+    assertXPathContent(pXmlDoc,
+                       "/c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser/c:cat/c:strRef/c:f",
+                       "Sheet1!local_name_range");
+    // test the syntax of a global range name.
+    assertXPathContent(pXmlDoc,
+                       "/c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser/c:val/c:numRef/c:f",
+                       "[0]!series1");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(Chart2ExportTest2);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/chart2/qa/extras/data/xlsx/chart_with_name_range.xlsx b/chart2/qa/extras/data/xlsx/chart_with_name_range.xlsx
new file mode 100644
index 000000000000..2f2b814011ff
--- /dev/null
+++ b/chart2/qa/extras/data/xlsx/chart_with_name_range.xlsx