From 6269ee23127a05d094cd7f1c79b1f0b9c2c78f24 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Fri, 4 Mar 2011 11:15:01 +0000 Subject: [PATCH] Change related formulas and named ranges when XSSFWorkbook.setSheetName is called, see Bugzilla 47100 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1077878 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 5 +- .../poi/xssf/usermodel/XSSFWorkbook.java | 16 +- .../usermodel/helpers/XSSFFormulaUtils.java | 147 ++++++++++++++++++ .../poi/ss/usermodel/BaseTestWorkbook.java | 129 +++++++++++++++ 4 files changed, 290 insertions(+), 7 deletions(-) create mode 100644 src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 1884df0b99..fb78826cec 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,7 +33,10 @@ - + + 47100 - Change related formulas and named ranges when XSSFWorkbook.setSheetName is called + + 50610 - Ant tasks for running POI against a workbook 32903 - Correct XBAT chaining explanation in /poifs/fileformat.html 50829 - Support for getting the tables associated with a XSSFSheet diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index 1479d768d3..b7ae031502 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -46,7 +46,6 @@ import org.apache.poi.openxml4j.opc.PackageRelationship; 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.CellReference; 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 POIXMLDocument implements Workbook, Iterable + *

+ * 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. + *

+ * + * @param sheetIndex the 0-based index of the sheet being changed + * @param name the new sheet name + */ + public void updateSheetName(final int sheetIndex, final String name) { + + /** + * An instance of FormulaRenderingWorkbook that returns + */ + FormulaRenderingWorkbook frwb = new FormulaRenderingWorkbook() { + + public ExternalSheet getExternalSheet(int externSheetIndex) { + return _fpwb.getExternalSheet(externSheetIndex); + } + + public String getSheetNameByExternSheet(int externSheetIndex) { + if (externSheetIndex == sheetIndex) return name; + else return _fpwb.getSheetNameByExternSheet(externSheetIndex); + } + + public String resolveNameXText(NameXPtg nameXPtg) { + return _fpwb.resolveNameXText(nameXPtg); + } + + public String getNameText(NamePtg namePtg) { + return _fpwb.getNameText(namePtg); + } + }; + + // 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); + } + } + + // 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); + } + } + } + } + } + + /** + * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook. + * + * @param cell the cell to update + * @param frwb the formula rendering workbbok that returns new sheet name + */ + 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); + } + } + } + + /** + * 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); + } + } +} diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java index ba733d068a..c3ddee0dda 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java @@ -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 extends TestCase { 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 Bugzilla 47100 + */ + 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()); + } + } -- 2.39.5