Add a test case for global and sheet-local named expressions.

Change-Id: I44247b55f3541214b515378744ee2c1982051e27
Reviewed-on: https://gerrit.libreoffice.org/49882
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Kohei Yoshida <libreoffice@kohei.us>

3 files changed, 268 insertions, 0 deletions

diff --git a/sc/qa/unit/data/xml/named-exp-global.xml b/sc/qa/unit/data/xml/named-exp-global.xml
new file mode 100644
index 000000000000..151ed7037e02
--- /dev/null
+++ b/sc/qa/unit/data/xml/named-exp-global.xml
@@ -0,0 +1,92 @@
+<?xml version="1.0"?>
+<?mso-application progid="Excel.Sheet"?>
+<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
+ xmlns:o="urn:schemas-microsoft-com:office:office"
+ xmlns:x="urn:schemas-microsoft-com:office:excel"
+ xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
+ xmlns:html="http://www.w3.org/TR/REC-html40">
+ <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
+  <Author>Kohei Yoshida</Author>
+  <LastAuthor>Kohei Yoshida</LastAuthor>
+  <Created>2016-12-17T20:46:03Z</Created>
+  <LastSaved>2016-12-17T20:49:13Z</LastSaved>
+  <Version>15.00</Version>
+ </DocumentProperties>
+ <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
+  <AllowPNG/>
+ </OfficeDocumentSettings>
+ <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
+  <WindowHeight>14385</WindowHeight>
+  <WindowWidth>24000</WindowWidth>
+  <WindowTopX>0</WindowTopX>
+  <WindowTopY>0</WindowTopY>
+  <RefModeR1C1/>
+  <ProtectStructure>False</ProtectStructure>
+  <ProtectWindows>False</ProtectWindows>
+ </ExcelWorkbook>
+ <Styles>
+  <Style ss:ID="Default" ss:Name="Normal">
+   <Alignment ss:Vertical="Bottom"/>
+   <Borders/>
+   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
+   <Interior/>
+   <NumberFormat/>
+   <Protection/>
+  </Style>
+ </Styles>
+ <Names>
+  <NamedRange ss:Name="MyRange" ss:RefersTo="=Sheet1!R1C1:R5C1"/>
+  <NamedRange ss:Name="MyRange2" ss:RefersTo="=Sheet1!R1C1:R5C2"/>
+ </Names>
+ <Worksheet ss:Name="Sheet1">
+  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="7" x:FullColumns="1"
+   x:FullRows="1" ss:DefaultRowHeight="15">
+   <Row>
+    <Cell><Data ss:Type="Number">1</Data><NamedCell ss:Name="MyRange"/><NamedCell
+      ss:Name="MyRange2"/></Cell>
+    <Cell><Data ss:Type="Number">6</Data><NamedCell ss:Name="MyRange2"/></Cell>
+   </Row>
+   <Row>
+    <Cell><Data ss:Type="Number">2</Data><NamedCell ss:Name="MyRange"/><NamedCell
+      ss:Name="MyRange2"/></Cell>
+    <Cell><Data ss:Type="Number">7</Data><NamedCell ss:Name="MyRange2"/></Cell>
+   </Row>
+   <Row>
+    <Cell><Data ss:Type="Number">3</Data><NamedCell ss:Name="MyRange"/><NamedCell
+      ss:Name="MyRange2"/></Cell>
+    <Cell><Data ss:Type="Number">8</Data><NamedCell ss:Name="MyRange2"/></Cell>
+   </Row>
+   <Row>
+    <Cell><Data ss:Type="Number">4</Data><NamedCell ss:Name="MyRange"/><NamedCell
+      ss:Name="MyRange2"/></Cell>
+    <Cell><Data ss:Type="Number">9</Data><NamedCell ss:Name="MyRange2"/></Cell>
+   </Row>
+   <Row>
+    <Cell><Data ss:Type="Number">5</Data><NamedCell ss:Name="MyRange"/><NamedCell
+      ss:Name="MyRange2"/></Cell>
+    <Cell><Data ss:Type="Number">10</Data><NamedCell ss:Name="MyRange2"/></Cell>
+   </Row>
+   <Row ss:Index="7">
+    <Cell ss:Formula="=SUM(MyRange)"><Data ss:Type="Number">15</Data></Cell>
+    <Cell ss:Formula="=SUM(MyRange2)"><Data ss:Type="Number">55</Data></Cell>
+   </Row>
+  </Table>
+  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
+   <PageSetup>
+    <Header x:Margin="0.3"/>
+    <Footer x:Margin="0.3"/>
+    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
+   </PageSetup>
+   <Selected/>
+   <Panes>
+    <Pane>
+     <Number>3</Number>
+     <ActiveRow>6</ActiveRow>
+     <ActiveCol>1</ActiveCol>
+    </Pane>
+   </Panes>
+   <ProtectObjects>False</ProtectObjects>
+   <ProtectScenarios>False</ProtectScenarios>
+  </WorksheetOptions>
+ </Worksheet>
+</Workbook>
diff --git a/sc/qa/unit/data/xml/named-exp-local.xml b/sc/qa/unit/data/xml/named-exp-local.xml
new file mode 100644
index 000000000000..46f110bf3da7
--- /dev/null
+++ b/sc/qa/unit/data/xml/named-exp-local.xml
@@ -0,0 +1,131 @@
+<?xml version="1.0"?>
+<?mso-application progid="Excel.Sheet"?>
+<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
+ xmlns:o="urn:schemas-microsoft-com:office:office"
+ xmlns:x="urn:schemas-microsoft-com:office:excel"
+ xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
+ xmlns:html="http://www.w3.org/TR/REC-html40">
+ <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
+  <Author>Kohei Yoshida</Author>
+  <LastAuthor>Kohei Yoshida</LastAuthor>
+  <Created>2017-02-08T02:38:34Z</Created>
+  <LastSaved>2017-02-08T02:41:56Z</LastSaved>
+  <Version>15.00</Version>
+ </DocumentProperties>
+ <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
+  <AllowPNG/>
+ </OfficeDocumentSettings>
+ <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
+  <WindowHeight>14385</WindowHeight>
+  <WindowWidth>24000</WindowWidth>
+  <WindowTopX>0</WindowTopX>
+  <WindowTopY>0</WindowTopY>
+  <ProtectStructure>False</ProtectStructure>
+  <ProtectWindows>False</ProtectWindows>
+ </ExcelWorkbook>
+ <Styles>
+  <Style ss:ID="Default" ss:Name="Normal">
+   <Alignment ss:Vertical="Bottom"/>
+   <Borders/>
+   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
+   <Interior/>
+   <NumberFormat/>
+   <Protection/>
+  </Style>
+ </Styles>
+ <Worksheet ss:Name="Sheet1">
+  <Names>
+   <NamedRange ss:Name="MyRange" ss:RefersTo="=Sheet1!R1C1:R3C2"/>
+  </Names>
+  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="7" x:FullColumns="1"
+   x:FullRows="1" ss:DefaultRowHeight="15">
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">1</Data><NamedCell ss:Name="MyRange"/></Cell>
+    <Cell><Data ss:Type="Number">6</Data><NamedCell ss:Name="MyRange"/></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">2</Data><NamedCell ss:Name="MyRange"/></Cell>
+    <Cell><Data ss:Type="Number">7</Data><NamedCell ss:Name="MyRange"/></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">3</Data><NamedCell ss:Name="MyRange"/></Cell>
+    <Cell><Data ss:Type="Number">8</Data><NamedCell ss:Name="MyRange"/></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">4</Data></Cell>
+    <Cell><Data ss:Type="Number">9</Data></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">5</Data></Cell>
+    <Cell><Data ss:Type="Number">10</Data></Cell>
+   </Row>
+   <Row ss:Index="7" ss:AutoFitHeight="0">
+    <Cell ss:Formula="=SUM(MyRange)"><Data ss:Type="Number">27</Data></Cell>
+   </Row>
+  </Table>
+  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
+   <PageSetup>
+    <Header x:Margin="0.3"/>
+    <Footer x:Margin="0.3"/>
+    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
+   </PageSetup>
+   <Unsynced/>
+   <Selected/>
+   <Panes>
+    <Pane>
+     <Number>3</Number>
+     <ActiveRow>6</ActiveRow>
+    </Pane>
+   </Panes>
+   <ProtectObjects>False</ProtectObjects>
+   <ProtectScenarios>False</ProtectScenarios>
+  </WorksheetOptions>
+ </Worksheet>
+ <Worksheet ss:Name="Sheet2">
+  <Names>
+   <NamedRange ss:Name="MyRange" ss:RefersTo="=Sheet2!R4C1:R5C2"/>
+  </Names>
+  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="7" x:FullColumns="1"
+   x:FullRows="1" ss:DefaultRowHeight="15">
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">10</Data></Cell>
+    <Cell><Data ss:Type="Number">20</Data></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">11</Data></Cell>
+    <Cell><Data ss:Type="Number">21</Data></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">12</Data></Cell>
+    <Cell><Data ss:Type="Number">22</Data></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">13</Data><NamedCell ss:Name="MyRange"/></Cell>
+    <Cell><Data ss:Type="Number">23</Data><NamedCell ss:Name="MyRange"/></Cell>
+   </Row>
+   <Row ss:AutoFitHeight="0">
+    <Cell><Data ss:Type="Number">14</Data><NamedCell ss:Name="MyRange"/></Cell>
+    <Cell><Data ss:Type="Number">24</Data><NamedCell ss:Name="MyRange"/></Cell>
+   </Row>
+   <Row ss:Index="7" ss:AutoFitHeight="0">
+    <Cell ss:Formula="=SUM(MyRange)"><Data ss:Type="Number">74</Data></Cell>
+   </Row>
+  </Table>
+  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
+   <PageSetup>
+    <Header x:Margin="0.3"/>
+    <Footer x:Margin="0.3"/>
+    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
+   </PageSetup>
+   <Unsynced/>
+   <Panes>
+    <Pane>
+     <Number>3</Number>
+     <ActiveRow>6</ActiveRow>
+    </Pane>
+   </Panes>
+   <ProtectObjects>False</ProtectObjects>
+   <ProtectScenarios>False</ProtectScenarios>
+  </WorksheetOptions>
+ </Worksheet>
+</Workbook>
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 5eb5303b397f..f93af35a0aab 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -237,6 +237,7 @@ public:
 
     void testMergedCellsXLSXML();
     void testBackgroundColorStandardXLSXML();
+    void testNamedExpressionsXLSXML();
 
     CPPUNIT_TEST_SUITE(ScFiltersTest);
     CPPUNIT_TEST(testBooleanFormatXLSX);
@@ -361,6 +362,7 @@ public:
 #endif
     CPPUNIT_TEST(testMergedCellsXLSXML);
     CPPUNIT_TEST(testBackgroundColorStandardXLSXML);
+    CPPUNIT_TEST(testNamedExpressionsXLSXML);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -3584,6 +3586,49 @@ void ScFiltersTest::testBackgroundColorStandardXLSXML()
     xDocSh->DoClose();
 }
 
+void ScFiltersTest::testNamedExpressionsXLSXML()
+{
+    {
+        // global named expressions
+
+        ScDocShellRef xDocSh = loadDoc("named-exp-global.", FORMAT_XLS_XML);
+        CPPUNIT_ASSERT_MESSAGE("Failed to load named-exp-global.xml", xDocSh.is());
+        ScDocument& rDoc = xDocSh->GetDocument();
+
+        // A7
+        ScAddress aPos(0,6,0);
+        CPPUNIT_ASSERT_EQUAL(15.0, rDoc.GetValue(aPos));
+        ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(MyRange)", nullptr);
+
+        // B7
+        aPos.IncCol();
+        CPPUNIT_ASSERT_EQUAL(55.0, rDoc.GetValue(aPos));
+        ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(MyRange2)", nullptr);
+
+        xDocSh->DoClose();
+    }
+
+    {
+        // sheet-local named expressions
+
+        ScDocShellRef xDocSh = loadDoc("named-exp-local.", FORMAT_XLS_XML);
+        CPPUNIT_ASSERT_MESSAGE("Failed to load named-exp-local.xml", xDocSh.is());
+        ScDocument& rDoc = xDocSh->GetDocument();
+
+        // A7 on Sheet1
+        ScAddress aPos(0,6,0);
+        CPPUNIT_ASSERT_EQUAL(27.0, rDoc.GetValue(aPos));
+        ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(MyRange)", nullptr);
+
+        // A7 on Sheet2
+        aPos.IncTab();
+        CPPUNIT_ASSERT_EQUAL(74.0, rDoc.GetValue(aPos));
+        ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(MyRange)", nullptr);
+
+        xDocSh->DoClose();
+    }
+}
+
 void ScFiltersTest::testCondFormatXLSB()
 {
     ScDocShellRef xDocSh = loadDoc("cond_format.", FORMAT_XLSB);