--- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (revision 1807214) +++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (working copy) @@ -969,7 +969,7 @@ * @return CellValueRecordInterface representing the cell via the low level api. */ - protected CellValueRecordInterface getCellValueRecord() + public CellValueRecordInterface getCellValueRecord() { return _record; } --- src/java/org/apache/poi/ss/formula/CompleteFormulaShifter.java (nonexistent) +++ src/java/org/apache/poi/ss/formula/CompleteFormulaShifter.java (working copy) @@ -0,0 +1,227 @@ +package org.apache.poi.ss.formula; + +import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.util.CellReference; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.formula.FormulaParseException; +import org.apache.poi.ss.formula.FormulaParser; +import org.apache.poi.ss.formula.FormulaRenderer; +import org.apache.poi.ss.formula.FormulaShifter; +import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.formula.ptg.Area3DPtg; +import org.apache.poi.ss.formula.ptg.Area3DPxg; +import org.apache.poi.ss.formula.ptg.AreaPtg; +import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.formula.ptg.Ref3DPtg; +import org.apache.poi.ss.formula.ptg.Ref3DPxg; +import org.apache.poi.ss.formula.ptg.RefPtg; +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.ss.usermodel.Workbook; +import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; + +/*** + * Wrapper around FormulaShifter, with added functionality. Takes formula string as input (instead of Ptg[]), so it makes better automation. + * Works with both rows and columns. + * @author Dragan Jovanović (zmau3012 @ skype) + * + */ +public class CompleteFormulaShifter { + private FormulaShifter formulaShifter; + private SpreadsheetVersion spreadSheetVersion; + private Sheet shiftingSheet; + private boolean rowModeElseColumn; + + public static CompleteFormulaShifter createForItemShift(Sheet sheet, boolean rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){ + CompleteFormulaShifter instance = new CompleteFormulaShifter(sheet, rowModeElseColumn); + instance.configureForItemShifting(firstShiftItemIndex, lastShiftItemIndex, shiftStep); + return instance; + } + public static CompleteFormulaShifter createForItemCopy(Sheet sheet, boolean rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){ + CompleteFormulaShifter instance = new CompleteFormulaShifter(sheet, rowModeElseColumn); + instance.configureForItemCopiing(firstShiftItemIndex, lastShiftItemIndex, shiftStep); + return instance; + } + + public CompleteFormulaShifter(Sheet shiftingSheet, boolean rowModeElseColumn){ + this.shiftingSheet = shiftingSheet; + this.spreadSheetVersion = getSpreadsheetVersion(shiftingSheet); + this.rowModeElseColumn = rowModeElseColumn; + } + + public void configureForItemShifting(int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){ + formulaShifter = FormulaShifter.createForRowShift(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), + firstShiftItemIndex, lastShiftItemIndex, shiftStep, spreadSheetVersion); + } + public void configureForItemCopiing(int firstShiftRowIndex, int lastShiftItemIndex, int shiftStep){ + formulaShifter = FormulaShifter.createForRowCopy(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), + firstShiftRowIndex, lastShiftItemIndex, shiftStep, spreadSheetVersion); + } + public void processFormulasOnOtherSheets(){ + Workbook wb = shiftingSheet.getWorkbook(); + int sheetIndex = 0; + while(sheetIndex < wb.getNumberOfSheets()){ + Sheet aSheet = wb.getSheetAt(sheetIndex++); + if(!aSheet.getSheetName().equals(shiftingSheet.getSheetName())){ + shiftAllFormulas(aSheet); + } + } + } + + private void shiftAllFormulas(Sheet aSheet){ + for(int rowNo = 0; rowNo < aSheet.getPhysicalNumberOfRows(); rowNo++) + { + Row row = aSheet.getRow(rowNo); + for (int i = 0; i <= row.getPhysicalNumberOfCells(); i++){ + org.apache.poi.ss.usermodel.Cell currentCell = row.getCell(i); + if(currentCell != null){ + if(currentCell.getCellTypeEnum() == CellType.FORMULA) + shiftFormula(currentCell); + } + } + } + } + + public void shiftFormula(Cell cell) { + switch (spreadSheetVersion) { + case EXCEL2007: + int currentSheetIndex = getSheetIndex(cell.getSheet()); + String shiftedFormula = getShiftedFormulaForXSSF(cell.toString(), currentSheetIndex); + cell.setCellFormula(shiftedFormula); + break; + case EXCEL97: + shiftFormulaForHSSF((HSSFCell)cell); + break; + } + } + + // original code in org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.shiftFormula() + /** Recalculates formula, for EXCEL2007 spreadsheets + * @param formula string representing formula + * */ + public String getShiftedFormulaForXSSF(String formula, int currentSheetIndex) { + boolean adjustSucceeded; + Workbook wb = shiftingSheet.getWorkbook(); + XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); + try { + Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, currentSheetIndex); + String shiftedFmla = null; + if(rowModeElseColumn) + adjustSucceeded = formulaShifter.adjustFormula(ptgs, currentSheetIndex); + else { + Ptg[] transponedPtgs = transpose(ptgs); + adjustSucceeded = formulaShifter.adjustFormula(transponedPtgs, currentSheetIndex); + if (adjustSucceeded) + ptgs = transpose(transponedPtgs); + } + if(adjustSucceeded){ + shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); + // dont use logger here, because of transfer to POI project + //System.out.println(String.format("original : %s; shifted : %s", formula, shiftedFmla)); + return shiftedFmla; + } + else return formula; // better return some error msg ? + } catch (FormulaParseException fpe) { + // Log, but don't change, rather than breaking; uncomment this in POI context + //logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe); + return formula; + } + } + + // original code in org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate.updateFormulasAfterRowShift() + /** Recalculates formula, for EXCEL97 spreadsheets + * @param formula string representing formula + * */ + void shiftFormulaForHSSF(HSSFCell cell) { + boolean adjustSucceeded; + int sheetIndex = getSheetIndex(cell.getSheet()); + FormulaRecordAggregate fra = (FormulaRecordAggregate)cell.getCellValueRecord(); + Ptg[] ptgs = fra.getFormulaTokens(); + if(rowModeElseColumn) + adjustSucceeded = formulaShifter.adjustFormula(ptgs, sheetIndex); + else { + Ptg[] transposedPtgs = transpose(ptgs); + adjustSucceeded = formulaShifter.adjustFormula(transposedPtgs, sheetIndex); + if (adjustSucceeded) + ptgs = transpose(transposedPtgs); + } + fra.setParsedExpression(ptgs); + } + + private Ptg[] transpose(Ptg[] original){ + Ptg[] transposed = new Ptg[original.length]; + int index = 0; + for (Ptg ptg : original) { + String ptgType = ptg.getClass().getSimpleName(); + if(ptgType.equals("Ref3DPtg")){ //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xls version + int oldColumnIndex = ((Ref3DPtg) ptg).getColumn(); + ((Ref3DPtg) ptg).setColumn(((Ref3DPtg) ptg).getRow()); + ((Ref3DPtg) ptg).setRow(oldColumnIndex); + transposed[index] = ptg; + } else if(ptgType.equals("Ref3DPxg")){ //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xlsx version + int oldColumnIndex = ((Ref3DPxg) ptg).getColumn(); + ((Ref3DPxg) ptg).setColumn(((Ref3DPxg) ptg).getRow()); + ((Ref3DPxg) ptg).setRow(oldColumnIndex); + transposed[index] = ptg; + } else if(ptgType.equals("AreaPtg")){ // region for aggregate function, for example A1:B3 or Sheet1!B3:Sheet1!C3 + int oldFirstColumnIndex = ((AreaPtg) ptg).getFirstColumn(); + ((AreaPtg) ptg).setFirstColumn(((AreaPtg) ptg).getFirstRow()); + ((AreaPtg) ptg).setFirstRow(oldFirstColumnIndex); + int oldLastColumnIndex = ((AreaPtg) ptg).getLastColumn(); + ((AreaPtg) ptg).setLastColumn(((AreaPtg) ptg).getLastRow()); + ((AreaPtg) ptg).setLastRow(oldLastColumnIndex); + transposed[index] = ptg; + } + else if(ptgType.equals("Area3DPtg")){ //for example SUM(Sheet1!B3:C3); xls version + int oldFirstColumnIndex = ((Area3DPtg) ptg).getFirstColumn(); + ((Area3DPtg) ptg).setFirstColumn(((Area3DPtg) ptg).getFirstRow()); + ((Area3DPtg) ptg).setFirstRow(oldFirstColumnIndex); + int oldLastColumnIndex = ((Area3DPtg) ptg).getLastColumn(); + ((Area3DPtg) ptg).setLastColumn(((Area3DPtg) ptg).getLastRow()); + ((Area3DPtg) ptg).setLastRow(oldLastColumnIndex); + transposed[index] = ptg; + } + else if(ptgType.equals("Area3DPxg")){ //for example SUM(Sheet1!B3:C3); xlsx version + int oldFirstColumnIndex = ((Area3DPxg) ptg).getFirstColumn(); + ((Area3DPxg) ptg).setFirstColumn(((Area3DPxg) ptg).getFirstRow()); + ((Area3DPxg) ptg).setFirstRow(oldFirstColumnIndex); + int oldLastColumnIndex = ((Area3DPxg) ptg).getLastColumn(); + ((Area3DPxg) ptg).setLastColumn(((Area3DPxg) ptg).getLastRow()); + ((Area3DPxg) ptg).setLastRow(oldLastColumnIndex); + transposed[index] = ptg; + } else if(ptgType.equals("RefPtg")){ // common simple reference, like A2 + RefPtg transponedCellRefToken = new RefPtg(transpose(ptg.toFormulaString())); + transposed[index] = transponedCellRefToken; + } + else // operators like + or SUM, for example + transposed[index] = ptg; + index++; + } + return transposed; + } + + public static String transpose(String cellreference){ + CellReference original = new CellReference(cellreference); + // transpose, calling public CellReference(int *pRow*, int *pCol*) !!!! + CellReference transposed = new CellReference(original.getCol(), original.getRow(), original.isColAbsolute(), original.isRowAbsolute()); + return transposed.formatAsString(); + } + + private int getSheetIndex(Sheet sheet){ + return sheet.getWorkbook().getSheetIndex(sheet); + } + + public static SpreadsheetVersion getSpreadsheetVersion(Sheet sheet){ + if(sheet.getWorkbook() instanceof XSSFWorkbook) + return SpreadsheetVersion.EXCEL2007; + else if(sheet.getWorkbook() instanceof HSSFWorkbook) + return SpreadsheetVersion.EXCEL97; + else return null; + } + +} --- src/java/org/apache/poi/ss/formula/FormulaShifter.java (revision 1807214) +++ src/java/org/apache/poi/ss/formula/FormulaShifter.java (working copy) @@ -40,7 +40,7 @@ */ public final class FormulaShifter { - private static enum ShiftMode { + public static enum ShiftMode { RowMove, RowCopy, SheetMove,