You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2011/03/04 12:15:01 UTC
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/
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