poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1077878 - in /poi/trunk/src: documentation/content/xdocs/ ooxml/java/org/apache/poi/xssf/usermodel/ ooxml/java/org/apache/poi/xssf/usermodel/helpers/ testcases/org/apache/poi/ss/usermodel/
Date Fri, 04 Mar 2011 11:15:01 GMT
Author: yegor
Date: Fri Mar  4 11:15:01 2011
New Revision: 1077878

URL: http://svn.apache.org/viewvc?rev=1077878&view=rev
Log:
Change related formulas and named ranges when XSSFWorkbook.setSheetName is called, see Bugzilla
47100

Added:
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java
      - copied, changed from r1075959, poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
    poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1077878&r1=1077877&r2=1077878&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Mar  4 11:15:01 2011
@@ -33,7 +33,10 @@
     </developers>
 
     <changes>
-        <release version="3.8-beta1" date="2010-??-??">
+        <release version="3.8-beta2" date="2011-??-??">
+           <action dev="poi-developers" type="fix">47100 - Change related formulas
and named ranges when XSSFWorkbook.setSheetName is called</action>
+        </release>
+        <release version="3.8-beta1" date="2011-03-07">
            <action dev="poi-developers" type="add">50610 - Ant tasks for running POI
against a workbook</action>
            <action dev="poi-developers" type="add">32903 - Correct XBAT chaining explanation
in /poifs/fileformat.html</action>
            <action dev="poi-developers" type="add">50829 - Support for getting the
tables associated with a XSSFSheet</action>

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1077878&r1=1077877&r2=1077878&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Fri Mar  4 11:15:01
2011
@@ -46,7 +46,6 @@ import org.apache.poi.openxml4j.opc.Pack
 import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;
 import org.apache.poi.openxml4j.opc.PackagingURIHelper;
 import org.apache.poi.openxml4j.opc.TargetMode;
-import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
 import org.apache.poi.ss.formula.udf.UDFFinder;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
@@ -56,6 +55,7 @@ import org.apache.poi.ss.util.CellRefere
 import org.apache.poi.ss.util.WorkbookUtil;
 import org.apache.poi.util.*;
 import org.apache.poi.xssf.model.*;
+import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils;
 import org.apache.xmlbeans.XmlException;
 import org.apache.xmlbeans.XmlObject;
 import org.apache.xmlbeans.XmlOptions;
@@ -1109,14 +1109,18 @@ public class XSSFWorkbook extends POIXML
      * Will throw IllegalArgumentException if the name is greater than 31 chars
      * or contains /\?*[]
      *
-     * @param sheet number (0 based)
+     * @param sheetIndex number (0 based)
      */
-    public void setSheetName(int sheet, String name) {
-        validateSheetIndex(sheet);
+    public void setSheetName(int sheetIndex, String name) {
+        validateSheetIndex(sheetIndex);
         WorkbookUtil.validateSheetName(name);
-        if (containsSheet(name, sheet ))
+        if (containsSheet(name, sheetIndex ))
             throw new IllegalArgumentException( "The workbook already contains a sheet of
this name" );
-        workbook.getSheets().getSheetArray(sheet).setName(name);
+
+        XSSFFormulaUtils utils = new XSSFFormulaUtils(this);
+        utils.updateSheetName(sheetIndex, name);
+
+        workbook.getSheets().getSheetArray(sheetIndex).setName(name);
     }
 
     /**

Copied: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java
(from r1075959, poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java)
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java?p2=poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java&p1=poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java&r1=1075959&r2=1077878&rev=1077878&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java Fri
Mar  4 11:15:01 2011
@@ -1,190 +1,147 @@
-/* ====================================================================
-   Licensed to the Apache Software Foundation (ASF) under one or more
-   contributor license agreements.  See the NOTICE file distributed with
-   this work for additional information regarding copyright ownership.
-   The ASF licenses this file to You under the Apache License, Version 2.0
-   (the "License"); you may not use this file except in compliance with
-   the License.  You may obtain a copy of the License at
-
-       http://www.apache.org/licenses/LICENSE-2.0
-
-   Unless required by applicable law or agreed to in writing, software
-   distributed under the License is distributed on an "AS IS" BASIS,
-   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-   See the License for the specific language governing permissions and
-   limitations under the License.
-==================================================================== */
+/*
+ *  ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
 
 package org.apache.poi.xssf.usermodel.helpers;
 
-import org.apache.poi.xssf.usermodel.*;
-import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
 import org.apache.poi.ss.formula.FormulaParser;
-import org.apache.poi.ss.formula.FormulaType;
 import org.apache.poi.ss.formula.FormulaRenderer;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.formula.FormulaShifter;
+import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.NamePtg;
+import org.apache.poi.ss.formula.ptg.NameXPtg;
 import org.apache.poi.ss.formula.ptg.Ptg;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.xssf.usermodel.XSSFCell;
+import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFName;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
 
-import java.util.List;
-import java.util.ArrayList;
-
 /**
+ * Utility to update formulas and named ranges when a sheet name was changed
+ *
  * @author Yegor Kozlov
  */
-public final class XSSFRowShifter {
-    private final XSSFSheet sheet;
-
-    public XSSFRowShifter(XSSFSheet sh) {
-        sheet = sh;
+public final class XSSFFormulaUtils {
+    private final XSSFWorkbook _wb;
+    private final XSSFEvaluationWorkbook _fpwb;
+
+    public XSSFFormulaUtils(XSSFWorkbook wb) {
+        _wb = wb;
+        _fpwb = XSSFEvaluationWorkbook.create(_wb);
     }
 
     /**
-     * Shift merged regions
+     * Update sheet name in all formulas and named ranges.
+     * Called from {@link XSSFWorkbook#setSheetName(int, String)}
+     * <p/>
+     * <p>
+     * The idea is to parse every formula and render it back to string
+     * with the updated sheet name. The FormulaParsingWorkbook passed to the formula parser
+     * is constructed from the old workbook (sheet name is not yet updated) and
+     * the FormulaRenderingWorkbook passed to FormulaRenderer#toFormulaString is a custom
implementation that
+     * returns the new sheet name.
+     * </p>
      *
-     * @param startRow the row to start shifting
-     * @param endRow   the row to end shifting
-     * @param n        the number of rows to shift
-     * @return an array of affected cell regions
+     * @param sheetIndex the 0-based index of the sheet being changed
+     * @param name       the new sheet name
      */
-    public List<CellRangeAddress> shiftMerged(int startRow, int endRow, int n) {
-        List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>();
-        //move merged regions completely if they fall within the new region boundaries when
they are shifted
-        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
-            CellRangeAddress merged = sheet.getMergedRegion(i);
-
-            boolean inStart = (merged.getFirstRow() >= startRow || merged.getLastRow()
>= startRow);
-            boolean inEnd = (merged.getFirstRow() <= endRow || merged.getLastRow() <=
endRow);
-
-            //don't check if it's not within the shifted area
-            if (!inStart || !inEnd) {
-                continue;
-            }
-
-            //only shift if the region outside the shifted rows is not merged too
-            if (!containsCell(merged, startRow - 1, 0) && !containsCell(merged, endRow
+ 1, 0)) {
-                merged.setFirstRow(merged.getFirstRow() + n);
-                merged.setLastRow(merged.getLastRow() + n);
-                //have to remove/add it back
-                shiftedRegions.add(merged);
-                sheet.removeMergedRegion(i);
-                i = i - 1; // we have to back up now since we removed one
-            }
-        }
+    public void updateSheetName(final int sheetIndex, final String name) {
 
-        //read so it doesn't get shifted again
-        for (CellRangeAddress region : shiftedRegions) {
-            sheet.addMergedRegion(region);
-        }
-        return shiftedRegions;
-    }
+        /**
+         * An instance of FormulaRenderingWorkbook that returns
+         */
+        FormulaRenderingWorkbook frwb = new FormulaRenderingWorkbook() {
 
-    /**
-     * Check if the  row and column are in the specified cell range
-     *
-     * @param cr    the cell range to check in
-     * @param rowIx the row to check
-     * @param colIx the column to check
-     * @return true if the range contains the cell [rowIx,colIx]
-     */
-    private static boolean containsCell(CellRangeAddress cr, int rowIx, int colIx) {
-        if (cr.getFirstRow() <= rowIx && cr.getLastRow() >= rowIx
-                && cr.getFirstColumn() <= colIx && cr.getLastColumn()
>= colIx) {
-            return true;
-        }
-        return false;
-    }
-
-    /**
-     * Updated named ranges
-     */
-    public void updateNamedRanges(FormulaShifter shifter) {
-        XSSFWorkbook wb = sheet.getWorkbook();
-        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
-        for (int i = 0; i < wb.getNumberOfNames(); i++) {
-            XSSFName name = wb.getNameAt(i);
-            String formula = name.getRefersToFormula();
-            int sheetIndex = name.getSheetIndex();
+            public ExternalSheet getExternalSheet(int externSheetIndex) {
+                return _fpwb.getExternalSheet(externSheetIndex);
+            }
 
-            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex);
-            if (shifter.adjustFormula(ptgs, sheetIndex)) {
-                String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
-                name.setRefersToFormula(shiftedFmla);
+            public String getSheetNameByExternSheet(int externSheetIndex) {
+                if (externSheetIndex == sheetIndex) return name;
+                else return _fpwb.getSheetNameByExternSheet(externSheetIndex);
             }
 
-        }
-    }
+            public String resolveNameXText(NameXPtg nameXPtg) {
+                return _fpwb.resolveNameXText(nameXPtg);
+            }
 
-    /**
-     * Update formulas.
-     */
-    public void updateFormulas(FormulaShifter shifter) {
-        //update formulas on the parent sheet
-        updateSheetFormulas(sheet, shifter);
-
-        //update formulas on other sheets
-        XSSFWorkbook wb = sheet.getWorkbook();
-        for (XSSFSheet sh : wb) {
-            if (sheet == sh) continue;
-            updateSheetFormulas(sh, shifter);
-        }
-    }
+            public String getNameText(NamePtg namePtg) {
+                return _fpwb.getNameText(namePtg);
+            }
+        };
 
-    private void updateSheetFormulas(XSSFSheet sh, FormulaShifter shifter) {
-        for (Row r : sh) {
-            XSSFRow row = (XSSFRow) r;
-            updateRowFormulas(row, shifter);
+        // update named ranges
+        for (int i = 0; i < _wb.getNumberOfNames(); i++) {
+            XSSFName nm = _wb.getNameAt(i);
+            if (nm.getSheetIndex() == -1 || nm.getSheetIndex() == sheetIndex) {
+                updateName(nm, frwb);
+            }
         }
-    }
 
-    private void updateRowFormulas(XSSFRow row, FormulaShifter shifter) {
-        for (Cell c : row) {
-            XSSFCell cell = (XSSFCell) c;
-
-            CTCell ctCell = cell.getCTCell();
-            if (ctCell.isSetF()) {
-                CTCellFormula f = ctCell.getF();
-                String formula = f.getStringValue();
-                if (formula.length() > 0) {
-                    String shiftedFormula = shiftFormula(row, formula, shifter);
-                    if (shiftedFormula != null) {
-                        f.setStringValue(shiftedFormula);
+        // update formulas
+        for (Sheet sh : _wb) {
+            for (Row row : sh) {
+                for (Cell cell : row) {
+                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
+                        updateFormula((XSSFCell) cell, frwb);
                     }
                 }
-
-                if (f.isSetRef()) { //Range of cells which the formula applies to.
-                    String ref = f.getRef();
-                    String shiftedRef = shiftFormula(row, ref, shifter);
-                    if (shiftedRef != null) f.setRef(shiftedRef);
-                }
             }
-
         }
     }
 
     /**
-     * Shift a formula using the supplied FormulaShifter
+     * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
      *
-     * @param row     the row of the cell this formula belongs to. Used to get a reference
to the parent workbook.
-     * @param formula the formula to shift
-     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
-     * @return the shifted formula if the formula was changed,
-     *         <code>null</code> if the formula wasn't modified
+     * @param cell the cell to update
+     * @param frwb the formula rendering workbbok that returns new sheet name
      */
-    private static String shiftFormula(XSSFRow row, String formula, FormulaShifter shifter)
{
-        XSSFSheet sheet = row.getSheet();
-        XSSFWorkbook wb = sheet.getWorkbook();
-        int sheetIndex = wb.getSheetIndex(sheet);
-        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
-        Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
-        String shiftedFmla = null;
-        if (shifter.adjustFormula(ptgs, sheetIndex)) {
-            shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
+    private void updateFormula(XSSFCell cell, FormulaRenderingWorkbook frwb) {
+        CTCellFormula f = cell.getCTCell().getF();
+        if (f != null) {
+            String formula = f.getStringValue();
+            if (formula != null) {
+                int sheetIndex = _wb.getSheetIndex(cell.getSheet());
+                Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.CELL, sheetIndex);
+                String updatedFormula = FormulaRenderer.toFormulaString(frwb, ptgs);
+                if (!formula.equals(updatedFormula)) f.setStringValue(updatedFormula);
+            }
         }
-        return shiftedFmla;
     }
 
+    /**
+     * Parse formula in the named range and re-assemble it  back using the specified FormulaRenderingWorkbook.
+     *
+     * @param name the name to update
+     * @param frwb the formula rendering workbbok that returns new sheet name
+     */
+    private void updateName(XSSFName name, FormulaRenderingWorkbook frwb) {
+        String formula = name.getRefersToFormula();
+        if (formula != null) {
+            int sheetIndex = name.getSheetIndex();
+            Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.NAMEDRANGE, sheetIndex);
+            String updatedFormula = FormulaRenderer.toFormulaString(frwb, ptgs);
+            if (!formula.equals(updatedFormula)) name.setRefersToFormula(updatedFormula);
+        }
+    }
 }

Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java?rev=1077878&r1=1077877&r2=1077878&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java Fri Mar  4 11:15:01
2011
@@ -23,6 +23,9 @@ import junit.framework.TestCase;
 import org.apache.poi.ss.ITestDataProvider;
 import org.apache.poi.ss.util.CellRangeAddress;
 
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
+
 /**
  * @author Yegor Kozlov
  */
@@ -417,4 +420,130 @@ public abstract class BaseTestWorkbook e
         c3 = r.getCell(3);
         assertEquals(c3.getCellFormula(), formulaString);
     }
+
+    private Workbook newSetSheetNameTestingWorkbook() throws Exception {
+        Workbook wb = _testDataProvider.createWorkbook();
+        Sheet sh1 = wb.createSheet("Worksheet");
+        Sheet sh2 = wb.createSheet("Testing 47100");
+        Sheet sh3 = wb.createSheet("To be renamed");
+
+        Name name1 = wb.createName();
+        name1.setNameName("sale_1");
+        name1.setRefersToFormula("Worksheet!$A$1");
+
+        Name name2 = wb.createName();
+        name2.setNameName("sale_2");
+        name2.setRefersToFormula("'Testing 47100'!$A$1");
+
+        Name name3 = wb.createName();
+        name3.setNameName("sale_3");
+        name3.setRefersToFormula("'Testing 47100'!$B$1");
+
+        Name name4 = wb.createName();
+        name4.setNameName("sale_4");
+        name4.setRefersToFormula("'To be renamed'!$A$3");
+
+        sh1.createRow(0).createCell(0).setCellFormula("SUM('Testing 47100'!A1:C1)");
+        sh1.createRow(1).createCell(0).setCellFormula("SUM('Testing 47100'!A1:C1,'To be renamed'!A1:A5)");
+        sh1.createRow(2).createCell(0).setCellFormula("sale_2+sale_3+'Testing 47100'!C1");
+
+        sh2.createRow(0).createCell(0).setCellValue(1);
+        sh2.getRow(0).createCell(1).setCellValue(2);
+        sh2.getRow(0).createCell(2).setCellValue(3);
+
+        sh3.createRow(0).createCell(0).setCellValue(1);
+        sh3.createRow(1).createCell(0).setCellValue(2);
+        sh3.createRow(2).createCell(0).setCellValue(3);
+        sh3.createRow(3).createCell(0).setCellValue(4);
+        sh3.createRow(4).createCell(0).setCellValue(5);
+        sh3.createRow(5).createCell(0).setCellFormula("sale_3");
+        sh3.createRow(6).createCell(0).setCellFormula("'Testing 47100'!C1");
+
+        return wb;
+    }
+
+    /**
+     * Ensure that Workbook#setSheetName updates all dependent formulas and named ranges
+     *
+     * @see <a href="https://issues.apache.org/bugzilla/show_bug.cgi?id=47100">Bugzilla
47100</a>
+     */
+    public final void testSetSheetName() throws Exception {
+
+        Workbook wb = newSetSheetNameTestingWorkbook();
+
+        Sheet sh1 = wb.getSheetAt(0);
+
+        Name sale_2 = wb.getNameAt(1);
+        Name sale_3 = wb.getNameAt(2);
+        Name sale_4 = wb.getNameAt(3);
+
+        assertEquals("sale_2", sale_2.getNameName());
+        assertEquals("'Testing 47100'!$A$1", sale_2.getRefersToFormula());
+        assertEquals("sale_3", sale_3.getNameName());
+        assertEquals("'Testing 47100'!$B$1", sale_3.getRefersToFormula());
+        assertEquals("sale_4", sale_4.getNameName());
+        assertEquals("'To be renamed'!$A$3", sale_4.getRefersToFormula());
+
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+        Cell cell0 = sh1.getRow(0).getCell(0);
+        Cell cell1 = sh1.getRow(1).getCell(0);
+        Cell cell2 = sh1.getRow(2).getCell(0);
+
+        assertEquals("SUM('Testing 47100'!A1:C1)", cell0.getCellFormula());
+        assertEquals("SUM('Testing 47100'!A1:C1,'To be renamed'!A1:A5)", cell1.getCellFormula());
+        assertEquals("sale_2+sale_3+'Testing 47100'!C1", cell2.getCellFormula());
+
+        assertEquals(6.0, evaluator.evaluate(cell0).getNumberValue());
+        assertEquals(21.0, evaluator.evaluate(cell1).getNumberValue());
+        assertEquals(6.0, evaluator.evaluate(cell2).getNumberValue());
+
+        wb.setSheetName(1, "47100 - First");
+        wb.setSheetName(2, "47100 - Second");
+
+        assertEquals("sale_2", sale_2.getNameName());
+        assertEquals("'47100 - First'!$A$1", sale_2.getRefersToFormula());
+        assertEquals("sale_3", sale_3.getNameName());
+        assertEquals("'47100 - First'!$B$1", sale_3.getRefersToFormula());
+        assertEquals("sale_4", sale_4.getNameName());
+        assertEquals("'47100 - Second'!$A$3", sale_4.getRefersToFormula());
+
+        assertEquals("SUM('47100 - First'!A1:C1)", cell0.getCellFormula());
+        assertEquals("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.getCellFormula());
+        assertEquals("sale_2+sale_3+'47100 - First'!C1", cell2.getCellFormula());
+
+        evaluator.clearAllCachedResultValues();
+        assertEquals(6.0, evaluator.evaluate(cell0).getNumberValue());
+        assertEquals(21.0, evaluator.evaluate(cell1).getNumberValue());
+        assertEquals(6.0, evaluator.evaluate(cell2).getNumberValue());
+
+        wb = _testDataProvider.writeOutAndReadBack(wb);
+
+        sh1 = wb.getSheetAt(0);
+
+        sale_2 = wb.getNameAt(1);
+        sale_3 = wb.getNameAt(2);
+        sale_4 = wb.getNameAt(3);
+
+        cell0 = sh1.getRow(0).getCell(0);
+        cell1 = sh1.getRow(1).getCell(0);
+        cell2 = sh1.getRow(2).getCell(0);
+
+        assertEquals("sale_2", sale_2.getNameName());
+        assertEquals("'47100 - First'!$A$1", sale_2.getRefersToFormula());
+        assertEquals("sale_3", sale_3.getNameName());
+        assertEquals("'47100 - First'!$B$1", sale_3.getRefersToFormula());
+        assertEquals("sale_4", sale_4.getNameName());
+        assertEquals("'47100 - Second'!$A$3", sale_4.getRefersToFormula());
+
+        assertEquals("SUM('47100 - First'!A1:C1)", cell0.getCellFormula());
+        assertEquals("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.getCellFormula());
+        assertEquals("sale_2+sale_3+'47100 - First'!C1", cell2.getCellFormula());
+
+        evaluator = wb.getCreationHelper().createFormulaEvaluator();
+        assertEquals(6.0, evaluator.evaluate(cell0).getNumberValue());
+        assertEquals(21.0, evaluator.evaluate(cell1).getNumberValue());
+        assertEquals(6.0, evaluator.evaluate(cell2).getNumberValue());
+    }
+
 }



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message