[patch] new class CompleteFormulaShifter, and two minor changes, in patch format

CompleteFormulaShifter.patch (text/plain), 12.22 KB, created by Dragan Jovanović on 2017-09-04 12:39:29 UTC

(hide)

Description: new class CompleteFormulaShifter, and two minor changes, in patch format

Filename:

MIME Type:

Creator: Dragan Jovanović

Created: 2017-09-04 12:39:29 UTC

Size: 12.22 KB

patch

obsolete

>Index: src/java/org/apache/poi/hssf/usermodel/HSSFCell.java >=================================================================== >--- 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; > } >Index: src/java/org/apache/poi/ss/formula/CompleteFormulaShifter.java >=================================================================== >--- 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; >+ } >+ >+} >Index: src/java/org/apache/poi/ss/formula/FormulaShifter.java >=================================================================== >--- 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, View Attachment As Diff View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]