| /* |
| * ==================================================================== |
| * 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 java.util.Iterator; |
| import java.util.List; |
| |
| import org.apache.poi.ooxml.POIXMLDocumentPart; |
| import org.apache.poi.ss.formula.FormulaParser; |
| import org.apache.poi.ss.formula.FormulaRenderer; |
| import org.apache.poi.ss.formula.FormulaType; |
| import org.apache.poi.ss.formula.ptg.Ptg; |
| import org.apache.poi.ss.formula.ptg.Pxg; |
| import org.apache.poi.ss.formula.ptg.Pxg3D; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.CellType; |
| 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.XSSFChart; |
| import org.apache.poi.xssf.usermodel.XSSFDrawing; |
| 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 org.w3c.dom.Node; |
| import org.w3c.dom.NodeList; |
| |
| /** |
| * Utility to update formulas and named ranges when a sheet name was changed |
| * |
| * @author Yegor Kozlov |
| */ |
| public final class XSSFFormulaUtils { |
| private final XSSFWorkbook _wb; |
| private final XSSFEvaluationWorkbook _fpwb; |
| |
| public XSSFFormulaUtils(XSSFWorkbook wb) { |
| _wb = wb; |
| _fpwb = XSSFEvaluationWorkbook.create(_wb); |
| } |
| |
| /** |
| * Update sheet name in all charts, 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. This is done by parsing into Ptgs, |
| * looking for ones with sheet references in them, and changing those |
| * </p> |
| * |
| * @param sheetIndex the 0-based index of the sheet being changed |
| * @param oldName the old sheet name |
| * @param newName the new sheet name |
| */ |
| public void updateSheetName(final int sheetIndex, final String oldName, final String newName) { |
| // update named ranges |
| for (XSSFName nm : _wb.getAllNames()) { |
| if (nm.getSheetIndex() == -1 || nm.getSheetIndex() == sheetIndex) { |
| updateName(nm, oldName, newName); |
| } |
| } |
| |
| // update formulas |
| for (Sheet sh : _wb) { |
| for (Row row : sh) { |
| for (Cell cell : row) { |
| if (cell.getCellType() == CellType.FORMULA) { |
| updateFormula((XSSFCell) cell, oldName, newName); |
| } |
| } |
| } |
| } |
| |
| // update charts |
| List<POIXMLDocumentPart> rels = _wb.getSheetAt(sheetIndex).getRelations(); |
| for (POIXMLDocumentPart r : rels) { |
| if (r instanceof XSSFDrawing) { |
| XSSFDrawing dg = (XSSFDrawing) r; |
| Iterator<XSSFChart> it = dg.getCharts().iterator(); |
| while (it.hasNext()) { |
| XSSFChart chart = it.next(); |
| Node dom = chart.getCTChartSpace().getDomNode(); |
| updateDomSheetReference(dom, oldName, newName); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Parse cell formula and re-assemble it back using the new sheet name |
| * |
| * @param cell the cell to update |
| */ |
| private void updateFormula(XSSFCell cell, String oldName, String newName) { |
| CTCellFormula f = cell.getCTCell().getF(); |
| if (f != null) { |
| String formula = f.getStringValue(); |
| if (formula != null && formula.length() > 0) { |
| int sheetIndex = _wb.getSheetIndex(cell.getSheet()); |
| Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.CELL, sheetIndex, cell.getRowIndex()); |
| for (Ptg ptg : ptgs) { |
| updatePtg(ptg, oldName, newName); |
| } |
| String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs); |
| if (!formula.equals(updatedFormula)) { |
| f.setStringValue(updatedFormula); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Parse formula in the named range and re-assemble it back using the new sheet name. |
| * |
| * @param name the name to update |
| */ |
| private void updateName(XSSFName name, String oldName, String newName) { |
| String formula = name.getRefersToFormula(); |
| if (formula != null) { |
| int sheetIndex = name.getSheetIndex(); |
| int rowIndex = -1; //don't care |
| Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex); |
| for (Ptg ptg : ptgs) { |
| updatePtg(ptg, oldName, newName); |
| } |
| String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs); |
| if (!formula.equals(updatedFormula)) { |
| name.setRefersToFormula(updatedFormula); |
| } |
| } |
| } |
| |
| private void updatePtg(Ptg ptg, String oldName, String newName) { |
| if (ptg instanceof Pxg) { |
| Pxg pxg = (Pxg)ptg; |
| if (pxg.getExternalWorkbookNumber() < 1) { |
| if (pxg.getSheetName() != null && |
| pxg.getSheetName().equals(oldName)) { |
| pxg.setSheetName(newName); |
| } |
| if (pxg instanceof Pxg3D) { |
| Pxg3D pxg3D = (Pxg3D)pxg; |
| if (pxg3D.getLastSheetName() != null && |
| pxg3D.getLastSheetName().equals(oldName)) { |
| pxg3D.setLastSheetName(newName); |
| } |
| } |
| } |
| } |
| } |
| |
| |
| /** |
| * Parse the DOM tree recursively searching for text containing reference to the old sheet name and replacing it. |
| * |
| * @param dom the XML node in which to perform the replacement. |
| * |
| * Code extracted from: <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=54470">Bug 54470</a> |
| */ |
| private void updateDomSheetReference(Node dom, final String oldName, final String newName) { |
| String value = dom.getNodeValue(); |
| if (value != null) { |
| // make sure the value contains the old sheet and not a similar sheet |
| // (ex: Valid: 'Sheet1'! or Sheet1! ; NotValid: 'Sheet1Test'! or Sheet1Test!) |
| if (value.contains(oldName+"!") || value.contains(oldName+"'!")) { |
| XSSFName temporary = _wb.createName(); |
| temporary.setRefersToFormula(value); |
| updateName(temporary, oldName, newName); |
| dom.setNodeValue(temporary.getRefersToFormula()); |
| _wb.removeName(temporary); |
| } |
| } |
| NodeList nl = dom.getChildNodes(); |
| for (int i = 0; i < nl.getLength(); i++) { |
| updateDomSheetReference(nl.item(i), oldName, newName); |
| } |
| } |
| |
| } |