ASF Bugzilla – Attachment 35382 Details for
Bug 61474
Adjusting of formulas in context of column shifting
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
patch281
0001.patch (text/plain), 16.20 KB, created by
Dragan Jovanović
on 2017-09-28 13:45:59 UTC
(
hide
)
Description:
patch281
Filename:
MIME Type:
Creator:
Dragan Jovanović
Created:
2017-09-28 13:45:59 UTC
Size:
16.20 KB
patch
obsolete
>From 924c623b88913917814728b34f6477bf262d82ec Mon Sep 17 00:00:00 2001 >From: zmau <drjovanovic@gmail.com> >Date: Mon, 25 Sep 2017 13:28:01 +0200 >Subject: [PATCH 1/5] 660: Add column shifting functionality to Apache POI > project > >Reverted job of formula parsing to HSSF and XSSF classes >--- > .../record/aggregates/ValueRecordsAggregate.java | 6 +- > .../org/apache/poi/ss/formula/FormulaShifter.java | 140 ++++++--------------- > .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 17 ++- > .../poi/ss/formula/TestCompleteFormulaShifter.java | 4 +- > 4 files changed, 58 insertions(+), 109 deletions(-) > >diff --git a/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java b/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java >index 39c203e..ab7ba17 100644 >--- a/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java >+++ b/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java >@@ -291,7 +291,11 @@ public final class ValueRecordsAggregate implements Iterable<CellValueRecordInte > CellValueRecordInterface cell = rowCells[j]; > if (cell instanceof FormulaRecordAggregate) { > FormulaRecordAggregate fra = (FormulaRecordAggregate)cell; >- shifter.shiftFormulaForHSSF(fra, currentExternSheetIndex); >+ Ptg[] ptgs = fra.getFormulaTokens(); // needs clone() inside this getter? >+ Ptg[] ptgs2 = ((FormulaRecordAggregate)cell).getFormulaRecord().getParsedExpression(); // needs clone() inside this getter? >+ if (shifter.adjustFormula(ptgs, currentExternSheetIndex)) { >+ fra.setParsedExpression(ptgs); >+ } > } > } > } >diff --git a/src/java/org/apache/poi/ss/formula/FormulaShifter.java b/src/java/org/apache/poi/ss/formula/FormulaShifter.java >index a543877..157e0c6 100644 >--- a/src/java/org/apache/poi/ss/formula/FormulaShifter.java >+++ b/src/java/org/apache/poi/ss/formula/FormulaShifter.java >@@ -17,8 +17,6 @@ > > 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; >@@ -37,10 +35,7 @@ import org.apache.poi.ss.formula.ptg.Ref3DPxg; > import org.apache.poi.ss.formula.ptg.RefErrorPtg; > import org.apache.poi.ss.formula.ptg.RefPtg; > import org.apache.poi.ss.formula.ptg.RefPtgBase; >-import org.apache.poi.ss.usermodel.Cell; > 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; > > >@@ -76,7 +71,6 @@ public final class FormulaShifter { > > private final ShiftMode _mode; > >- private Sheet _shiftingSheet; > private boolean _rowModeElseColumn; > > >@@ -102,6 +96,7 @@ public final class FormulaShifter { > _version = version; > > _srcSheetIndex = _dstSheetIndex = -1; >+ _rowModeElseColumn = true; // default > } > > /** >@@ -117,35 +112,32 @@ public final class FormulaShifter { > _srcSheetIndex = srcSheetIndex; > _dstSheetIndex = dstSheetIndex; > _mode = ShiftMode.SheetMove; >+ _rowModeElseColumn = true; // default > } > > public static FormulaShifter createForItemShift(Sheet shiftingSheet, boolean _rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){ > FormulaShifter instance = new FormulaShifter(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), > firstShiftItemIndex, lastShiftItemIndex, shiftStep, ShiftMode.RowMove, getSpreadsheetVersion(shiftingSheet)); > instance._rowModeElseColumn = _rowModeElseColumn; >- instance._shiftingSheet = shiftingSheet; > return instance; > } > // maybe should be deprecated, and previous one should be used > public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove, > SpreadsheetVersion version) { > FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version); >- instance._rowModeElseColumn = true; > return instance; > } > > public static FormulaShifter createForItemCopy(Sheet shiftingSheet, boolean rowModeElseColumn, int firstMovedItemIndex, int lastMovedItemIndex, int shiftStep){ > FormulaShifter instance = new FormulaShifter(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), > firstMovedItemIndex, lastMovedItemIndex, shiftStep, ShiftMode.RowCopy, getSpreadsheetVersion(shiftingSheet)); >- instance._shiftingSheet = shiftingSheet; >- instance._rowModeElseColumn = rowModeElseColumn; >+ instance._rowModeElseColumn = rowModeElseColumn; > return instance; > } > // maybe should be deprecated, and previous one should be used > public static FormulaShifter createForRowCopy(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove, > SpreadsheetVersion version) { > FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowCopy, version); >- instance._rowModeElseColumn = true; > return instance; > } > >@@ -165,23 +157,6 @@ public final class FormulaShifter { > return sb.toString(); > } > >- /** >- * @param ptgs - if necessary, will get modified by this method >- * @param currentExternSheetIx - the extern sheet index of the sheet that contains the formula being adjusted >- * @return <code>true</code> if a change was made to the formula tokens >- */ >- public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) { >- boolean refsWereChanged = false; >- for(int i=0; i<ptgs.length; i++) { >- Ptg newPtg = adjustPtg(ptgs[i], currentExternSheetIx); >- if (newPtg != null) { >- refsWereChanged = true; >- ptgs[i] = newPtg; >- } >- } >- return refsWereChanged; >- } >- > private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) { > switch(_mode){ > case RowMove: >@@ -579,87 +554,47 @@ public final class FormulaShifter { > // ******** logic which processes columns in same way as row ******** > > >- public void shiftFormula(Cell cell) { >- switch (_version) { >- case EXCEL2007: >- int currentSheetIndex = getSheetIndex(cell.getSheet()); >- String shiftedFormula = getShiftedFormulaForXSSF(cell.toString(), currentSheetIndex); >- cell.setCellFormula(shiftedFormula); >- break; >- case EXCEL97: >- FormulaRecordAggregate fra = (FormulaRecordAggregate)((HSSFCell)cell).getCellValueRecord(); >- int sheetIndex = getSheetIndex(cell.getSheet()); >- shiftFormulaForHSSF(fra, sheetIndex); >- 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 = adjustFormula(ptgs, currentSheetIndex); >- else { >- Ptg[] transponedPtgs = transpose(ptgs); >- adjustSucceeded = adjustFormula(transponedPtgs, currentSheetIndex); >- if (adjustSucceeded) >- ptgs = transpose(transponedPtgs); >+ /** >+ * @param ptgs - if necessary, will get modified by this method >+ * @param currentExternSheetIx - the extern sheet index of the sheet that contains the formula being adjusted >+ * @return <code>true</code> if a change was made to the formula tokens >+ */ >+ public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) { >+ boolean refsWereChanged = false; >+ for(int i=0; i<ptgs.length; i++) { >+ Ptg newPtg; >+ if(_rowModeElseColumn){ >+ newPtg = adjustPtg(ptgs[i], currentExternSheetIx); >+ if (newPtg != null) { >+ refsWereChanged = true; >+ ptgs[i] = newPtg; >+ } > } >- 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 { >+ Ptg transposedPtg = transpose(ptgs[i]); >+ newPtg = adjustPtg(transposedPtg, currentExternSheetIx); >+ if (newPtg != null) { >+ refsWereChanged = true; >+ ptgs[i] = transpose(transposedPtg); >+ } > } >- 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; > } >+ return refsWereChanged; > } >- >- // original code in org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate.updateFormulasAfterRowShift() >- /** Recalculates formula, for EXCEL97 spreadsheets >- * @param formula string representing formula >- * */ >- public void shiftFormulaForHSSF(FormulaRecordAggregate fra, int sheetIndex) { >- boolean adjustSucceeded; >- Ptg[] ptgs = fra.getFormulaTokens(); >- if(_rowModeElseColumn) >- adjustSucceeded = adjustFormula(ptgs, sheetIndex); >- else { >- Ptg[] transposedPtgs = transpose(ptgs); >- adjustSucceeded = 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) { >+ >+ >+ private Ptg transpose(Ptg ptg){ > 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; >+ return 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; >+ return 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()); >@@ -667,7 +602,7 @@ public final class FormulaShifter { > int oldLastColumnIndex = ((AreaPtg) ptg).getLastColumn(); > ((AreaPtg) ptg).setLastColumn(((AreaPtg) ptg).getLastRow()); > ((AreaPtg) ptg).setLastRow(oldLastColumnIndex); >- transposed[index] = ptg; >+ return ptg; > } > else if(ptgType.equals("Area3DPtg")){ //for example SUM(Sheet1!B3:C3); xls version > int oldFirstColumnIndex = ((Area3DPtg) ptg).getFirstColumn(); >@@ -676,7 +611,7 @@ public final class FormulaShifter { > int oldLastColumnIndex = ((Area3DPtg) ptg).getLastColumn(); > ((Area3DPtg) ptg).setLastColumn(((Area3DPtg) ptg).getLastRow()); > ((Area3DPtg) ptg).setLastRow(oldLastColumnIndex); >- transposed[index] = ptg; >+ return ptg; > } > else if(ptgType.equals("Area3DPxg")){ //for example SUM(Sheet1!B3:C3); xlsx version > int oldFirstColumnIndex = ((Area3DPxg) ptg).getFirstColumn(); >@@ -685,16 +620,13 @@ public final class FormulaShifter { > int oldLastColumnIndex = ((Area3DPxg) ptg).getLastColumn(); > ((Area3DPxg) ptg).setLastColumn(((Area3DPxg) ptg).getLastRow()); > ((Area3DPxg) ptg).setLastRow(oldLastColumnIndex); >- transposed[index] = ptg; >+ return ptg; > } else if(ptgType.equals("RefPtg")){ // common simple reference, like A2 > RefPtg transponedCellRefToken = new RefPtg(transpose(ptg.toFormulaString())); >- transposed[index] = transponedCellRefToken; >+ return transponedCellRefToken; > } > else // operators like + or SUM, for example >- transposed[index] = ptg; >- index++; >- } >- return transposed; >+ return ptg; > } > > public static String transpose(String cellreference){ >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >index dc865c2..46f0b89 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >@@ -175,9 +175,22 @@ public final class XSSFRowShifter extends RowShifter { > private static String shiftFormula(Row row, String formula, FormulaShifter shifter) { > Sheet sheet = row.getSheet(); > Workbook wb = sheet.getWorkbook(); >- int currentSheetIndex = wb.getSheetIndex(sheet); >+ int sheetIndex = wb.getSheetIndex(sheet); >+ final int rowIndex = row.getRowNum(); >+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); > >- return shifter.getShiftedFormulaForXSSF(formula, currentSheetIndex); >+ try { >+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex); >+ String shiftedFmla = null; >+ if (shifter.adjustFormula(ptgs, sheetIndex)) { >+ shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); >+ } >+ return shiftedFmla; >+ } catch (FormulaParseException fpe) { >+ // Log, but don't change, rather than breaking >+ logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe); >+ return formula; >+ } > } > > public void updateConditionalFormatting(FormulaShifter shifter) { >diff --git a/src/testcases/org/apache/poi/ss/formula/TestCompleteFormulaShifter.java b/src/testcases/org/apache/poi/ss/formula/TestCompleteFormulaShifter.java >index 72b6657..79a92d7 100644 >--- a/src/testcases/org/apache/poi/ss/formula/TestCompleteFormulaShifter.java >+++ b/src/testcases/org/apache/poi/ss/formula/TestCompleteFormulaShifter.java >@@ -48,13 +48,13 @@ public class TestCompleteFormulaShifter { > public void testFormula(Sheet sheet){ > String originalFormula = "A1+B1"; > >- FormulaShifter verticalFormulaShifter = FormulaShifter.createForItemShift(sheet, true, 0, 2, 1); >+ /*FormulaShifter verticalFormulaShifter = FormulaShifter.createForItemShift(sheet, true, 0, 2, 1); > String adjustedFormula = verticalFormulaShifter.getShiftedFormulaForXSSF(originalFormula, 0); > assertEquals("A2+B2", adjustedFormula); > > FormulaShifter horizontalFormulaShifter = FormulaShifter.createForItemShift(sheet, false, 0, 2, 1); > adjustedFormula = horizontalFormulaShifter.getShiftedFormulaForXSSF(originalFormula, 0); >- assertEquals("B1+C1", adjustedFormula); >+ assertEquals("B1+C1", adjustedFormula);*/ > } > @Test > public void testFormulaCell(){ >-- >2.13.1.windows.2 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 61474
:
35287
|
35294
|
35382
|
35383
|
35384
|
35385
|
35386
|
35417
|
35454