org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of poi-ooxml Show documentation
Apache POI - Java API To Access Microsoft Format Files
There is a newer version: 5.2.5
/*
 *  ====================================================================
 * 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.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.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;

/**
 * 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 formulas and named ranges.
     * Called from {@link XSSFWorkbook#setSheetName(int, String)}
     * 
     * 

     * 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
     * 
     *
     * @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.getCellTypeEnum() == CellType.FORMULA) {
                        updateFormula((XSSFCell) cell, 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);
                    }
                }
            }
        }
    }
}