ASF Bugzilla – Attachment 35384 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]
patch283
0003.patch (text/plain), 25.32 KB, created by
Dragan Jovanović
on 2017-09-28 13:47:42 UTC
(
hide
)
Description:
patch283
Filename:
MIME Type:
Creator:
Dragan Jovanović
Created:
2017-09-28 13:47:42 UTC
Size:
25.32 KB
patch
obsolete
>From 2d1a23916e7f213e7865ecb6fe319ba9a54f6635 Mon Sep 17 00:00:00 2001 >From: zmau <drjovanovic@gmail.com> >Date: Tue, 26 Sep 2017 15:50:06 +0200 >Subject: [PATCH 3/5] 660: Add column shifting functionality to Apache POI > project > >Introduced class XSSFColumnShifter, which should work analog to XSSFRowShifter. >Introduced class XSSFFormulaShiftingManager which is used by both row shifter and column shifter. >--- > .../org/apache/poi/hssf/usermodel/HSSFSheet.java | 3 + > .../poi/hssf/usermodel/helpers/HSSFRowShifter.java | 11 -- > src/java/org/apache/poi/ss/usermodel/Sheet.java | 1 + > .../poi/ss/usermodel/helpers/ColumnShifter.java | 16 +++ > .../poi/ss/usermodel/helpers/RowShifter.java | 14 --- > .../org/apache/poi/xssf/streaming/SXSSFSheet.java | 5 + > .../org/apache/poi/xssf/usermodel/XSSFRow.java | 5 +- > .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 27 +++++ > .../xssf/usermodel/helpers/XSSFColumnShifter.java | 126 ++++++++++++++++++++ > .../helpers/XSSFFormulaShiftingManager.java | 130 +++++++++++++++++++++ > .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 107 ++--------------- > 11 files changed, 319 insertions(+), 126 deletions(-) > create mode 100644 src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java > create mode 100644 src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java > create mode 100644 src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java > >diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >index 0af1268..c003669 100644 >--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >@@ -1718,6 +1718,9 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { > _workbook.getWorkbook().updateNamesAfterCellShift(shifter); > } > >+ public void shiftColumns(int startColumn, int endColumn, int n){ >+ >+ } > protected void insertChartRecords(List<Record> records) { > int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid); > _sheet.getRecords().addAll(window2Loc, records); >diff --git a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >index 0c4f78b..49deeb0 100644 >--- a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >+++ b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >@@ -49,17 +49,6 @@ public final class HSSFRowShifter extends RowShifter { > } > > @NotImplemented >- public void updateFormulas() { >- throw new NotImplementedException("updateFormulas"); >- } >- >- @Internal >- @NotImplemented >- public void updateRowFormulas(Row row) { >- throw new NotImplementedException("updateRowFormulas"); >- } >- >- @NotImplemented > public void updateConditionalFormatting() { > throw new NotImplementedException("updateConditionalFormatting"); > } >diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java >index 80788a0..65d38a2 100644 >--- a/src/java/org/apache/poi/ss/usermodel/Sheet.java >+++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java >@@ -692,6 +692,7 @@ public interface Sheet extends Iterable<Row> { > * @param resetOriginalRowHeight whether to set the original row's height to the default > */ > void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight); >+ void shiftColumns(int startColumn, int endColumn, int n); > > /** > * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. >diff --git a/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java b/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java >new file mode 100644 >index 0000000..e2e56d7 >--- /dev/null >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java >@@ -0,0 +1,16 @@ >+package org.apache.poi.ss.usermodel.helpers; >+ >+import org.apache.poi.ss.formula.FormulaShifter; >+import org.apache.poi.ss.usermodel.Sheet; >+ >+public class ColumnShifter { >+ protected final Sheet shiftingSheet; >+ protected FormulaShifter shifter; >+ >+ public ColumnShifter(Sheet sheet, FormulaShifter shifter) { >+ shiftingSheet = sheet; >+ this.shifter = shifter; >+ } >+ >+ >+} >diff --git a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >index 5d815e6..0ebacdf 100644 >--- a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >@@ -105,20 +105,6 @@ public abstract class RowShifter { > */ > public abstract void updateNamedRanges(); > >- /** >- * Update formulas. >- */ >- public abstract void updateFormulas(); >- >- /** >- * Update the formulas in specified row using the formula shifting policy specified by shifter >- * >- * @param row the row to update the formulas on >- * @param shifter the formula shifting policy >- */ >- @Internal >- public abstract void updateRowFormulas(Row row); >- > public abstract void updateConditionalFormatting(); > > /** >diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java >index 0362404..bd710d7 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java >@@ -1000,6 +1000,11 @@ public class SXSSFSheet implements Sheet > throw new RuntimeException("NotImplemented"); > } > >+ @NotImplemented >+ @Override >+ public void shiftColumns(int startColumn, int endColumn, int n){ >+ throw new RuntimeException("NotImplemented"); >+ } > /** > * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. > * @param colSplit Horizonatal position of split. >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >index d79172d..41a6b1e 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >@@ -35,6 +35,7 @@ import org.apache.poi.util.Beta; > import org.apache.poi.util.Internal; > import org.apache.poi.xssf.model.CalculationChain; > import org.apache.poi.xssf.model.StylesTable; >+import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaShiftingManager; > import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; >@@ -646,8 +647,8 @@ public class XSSFRow implements Row, Comparable<XSSFRow> { > final int destRowNum = getRowNum(); > final int rowDifference = destRowNum - srcRowNum; > final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007); >- final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet, shifter); >- rowShifter.updateRowFormulas(this); >+ final XSSFFormulaShiftingManager formulaShiftingManager = new XSSFFormulaShiftingManager(_sheet, shifter); >+ formulaShiftingManager.updateRowFormulas(this); > > // Copy merged regions that are fully contained on the row > // FIXME: is this something that rowShifter could be doing? >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >index 2348f4e..4b4f294 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >@@ -87,6 +87,7 @@ import org.apache.poi.util.Units; > import org.apache.poi.xssf.model.CommentsTable; > import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator; > import org.apache.poi.xssf.usermodel.helpers.ColumnHelper; >+import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter; > import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper; > import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; > import org.apache.xmlbeans.XmlCursor; >@@ -2970,6 +2971,32 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > _rows.putAll(map); > } > >+ @Override >+ public void shiftColumns(int startColumn, int endColumn, final int n) { >+ XSSFVMLDrawing vml = getVMLDrawing(false); >+ // removeOverwritten(vml, startColumn, endColumn, n); >+ //doShifting(vml, startColumn, endColumn, n, false); >+ >+ FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n); >+ XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter); >+ >+ /*columnShifter.updateNamedRanges(); >+ columnShifter.updateFormulas(); >+ columnShifter.shiftMergedRegions(startColumn, startColumn, n); >+ columnShifter.updateConditionalFormatting(); >+ columnShifter.updateHyperlinks();*/ >+ >+ //rebuild the _rows map >+ Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >+ for(XSSFRow r : _rows.values()) { >+ // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory >+ final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR >+ map.put(rownumI, r); >+ } >+ _rows.clear(); >+ _rows.putAll(map); >+ } >+ > // remove all rows which will be overwritten > private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){ > for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >new file mode 100644 >index 0000000..073a648 >--- /dev/null >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >@@ -0,0 +1,126 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+import org.apache.poi.ss.formula.FormulaShifter; >+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.helpers.ColumnShifter; >+import org.apache.poi.util.POILogFactory; >+import org.apache.poi.util.POILogger; >+import org.apache.poi.xssf.usermodel.XSSFCell; >+ >+public class XSSFColumnShifter extends ColumnShifter{ >+ >+ private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); >+ >+ private int firstShiftColumnIndex; >+ private int lastShiftColumnIndex; >+ private int shiftStep; >+ >+ private XSSFFormulaShiftingManager formulaShiftingManager; >+ >+ public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) { >+ super(sh, shifter); >+ formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter); >+ } >+ >+ public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){ >+ this.firstShiftColumnIndex = firstShiftColumnIndex; >+ this.shiftStep = step; >+ if(shiftStep > 0) >+ shiftColumnsRight(); >+ //else if(shiftStep < 0) >+ //shiftColumnsLeft(); >+ } >+ /** >+ * Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right >+ * (see constructor for parameters) >+ */ >+ >+ private void shiftColumnsRight(){ >+ for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++) >+ { >+ Row row = shiftingSheet.getRow(rowNo); >+ for (int columnIndex = row.getLastCellNum()-1; columnIndex >= 0 ; columnIndex--){ // process cells backwards, because of shifting >+ XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex); >+ if(oldCell == null) >+ continue; >+ if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >+ formulaShiftingManager.updateCellFormula(row, oldCell); >+ >+ if(columnIndex >= firstShiftColumnIndex){ // shift existing cell >+ org.apache.poi.ss.usermodel.Cell newCell = null; >+ newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellTypeEnum()); >+ cloneCellValue(oldCell,newCell); >+ if(columnIndex <= firstShiftColumnIndex + shiftStep - 1){ // clear existing cells on place of insertion >+ oldCell.setCellValue(""); >+ oldCell.setCellType(CellType.STRING); >+ } >+ } >+ } >+ } >+ formulaShiftingManager.updateFormulas(); >+ } >+ /*private void shiftColumnsLeft(){ >+ for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++) >+ { >+ Row row = shiftingSheet.getRow(rowNo); >+ for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ // process cells backwards, because of shifting >+ org.apache.poi.ss.usermodel.Cell oldCell = row.getCell(columnIndex); >+ if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell >+ org.apache.poi.ss.usermodel.Cell newCell = null; >+ newCell = row.getCell(columnIndex - shiftStep); >+ if(oldCell != null){ >+ if(newCell != null){ >+ oldCell.setCellType(newCell.getCellType()); >+ cloneCellValue(newCell, oldCell); >+ if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >+ columnFormulaShifter.shiftFormula(oldCell); >+ } >+ else { >+ oldCell.setCellType(CellType.STRING); >+ oldCell.setCellValue(""); >+ } >+ } >+ else { >+ oldCell = row.createCell(columnIndex); >+ if(newCell != null){ >+ oldCell.setCellType(newCell.getCellType()); >+ cloneCellValue(newCell, oldCell); >+ if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >+ columnFormulaShifter.shiftFormula(oldCell); >+ } >+ else { >+ oldCell.setCellType(CellType.STRING); >+ oldCell.setCellValue(""); >+ } >+ } >+ } >+ } >+ } >+ columnFormulaShifter.processFormulasOnOtherSheets(); >+ }*/ >+ >+ public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) { >+ switch (oldCell.getCellTypeEnum()) { >+ case STRING: >+ newCell.setCellValue(oldCell.getStringCellValue()); >+ break; >+ case NUMERIC: >+ newCell.setCellValue(oldCell.getNumericCellValue()); >+ break; >+ case BOOLEAN: >+ newCell.setCellValue(oldCell.getBooleanCellValue()); >+ break; >+ case FORMULA: >+ newCell.setCellFormula(oldCell.getCellFormula()); >+ break; >+ case ERROR: >+ newCell.setCellErrorValue(oldCell.getErrorCellValue()); >+ case BLANK: >+ case _NONE: >+ break; >+ } >+ } >+ >+} >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java >new file mode 100644 >index 0000000..da4e323 >--- /dev/null >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java >@@ -0,0 +1,130 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+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.Ptg; >+import org.apache.poi.ss.usermodel.Cell; >+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.util.Internal; >+import org.apache.poi.util.POILogFactory; >+import org.apache.poi.util.POILogger; >+import org.apache.poi.xssf.usermodel.XSSFCell; >+import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; >+import org.apache.poi.xssf.usermodel.XSSFRow; >+import org.apache.poi.xssf.usermodel.XSSFSheet; >+import org.apache.poi.xssf.usermodel.XSSFWorkbook; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; >+ >+public class XSSFFormulaShiftingManager { >+ >+ private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); >+ >+ protected final Sheet shiftingSheet; >+ protected FormulaShifter shifter; >+ >+ public XSSFFormulaShiftingManager(Sheet shiftingSheet, FormulaShifter shifter){ >+ this.shiftingSheet = shiftingSheet; >+ this.shifter = shifter; >+ } >+ >+ public void updateFormulas() { >+ //update formulas on the parent sheet >+ updateSheetFormulas(shiftingSheet); >+ >+ //update formulas on other sheets >+ Workbook wb = shiftingSheet.getWorkbook(); >+ for (Sheet sh : wb) { >+ if (shiftingSheet == sh) continue; >+ updateSheetFormulas(sh); >+ } >+ } >+ >+ private void updateSheetFormulas(Sheet sh) { >+ for (Row r : sh) { >+ XSSFRow row = (XSSFRow) r; >+ updateRowFormulas(row); >+ } >+ } >+ >+ /** >+ * Update the formulas in specified row using the formula shifting policy specified by shifter >+ * >+ * @param row the row to update the formulas on >+ * @param shifter the formula shifting policy >+ */ >+ @Internal >+ public void updateRowFormulas(Row row) { >+ for (Cell c : row) { >+ updateCellFormula(row, (XSSFCell) c); >+ } >+ } >+ >+ public void updateCellFormula(Row row, XSSFCell cell){ >+ CTCell ctCell = cell.getCTCell(); >+ if (ctCell.isSetF()) { >+ CTCellFormula f = ctCell.getF(); >+ String formula = f.getStringValue(); >+ if (formula.length() > 0) { >+ String shiftedFormula = shiftFormula(row, formula); >+ if (shiftedFormula != null) { >+ f.setStringValue(shiftedFormula); >+ if(f.getT() == STCellFormulaType.SHARED){ >+ int si = (int)f.getSi(); >+ XSSFSheet sheet = (XSSFSheet) row.getSheet(); >+ CTCellFormula sf = sheet.getSharedFormula(si); >+ sf.setStringValue(shiftedFormula); >+ updateRefInCTCellFormula(row, sf); >+ } >+ } >+ } >+ //Range of cells which the formula applies to. >+ updateRefInCTCellFormula(row, f); >+ } >+ } >+ private void updateRefInCTCellFormula(Row row, CTCellFormula f) { >+ if (f.isSetRef()) { //Range of cells which the formula applies to. >+ String ref = f.getRef(); >+ String shiftedRef = shiftFormula(row, ref); >+ if (shiftedRef != null) f.setRef(shiftedRef); >+ } >+ } >+ >+ /** >+ * Shift a formula using the supplied FormulaShifter >+ * >+ * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. >+ * @param formula the formula to shift >+ * @param shifter the FormulaShifter object that operates on the parsed formula tokens >+ * @return the shifted formula if the formula was changed, >+ * <code>null</code> if the formula wasn't modified >+ */ >+ private String shiftFormula(Row row, String formula) { >+ Sheet sheet = row.getSheet(); >+ Workbook wb = sheet.getWorkbook(); >+ int sheetIndex = wb.getSheetIndex(sheet); >+ final int rowIndex = row.getRowNum(); >+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); >+ >+ 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; >+ } >+ } >+ >+ >+} >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 f9ad224..7838fe9 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 >@@ -59,14 +59,21 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; > public final class XSSFRowShifter extends RowShifter { > private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); > >+ private XSSFFormulaShiftingManager formulaShiftingManager; >+ > public XSSFRowShifter(XSSFSheet sh) { > super(sh); > } > public XSSFRowShifter(Sheet sh, FormulaShifter shifter) { > super(sh, shifter); >+ formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter); > } > >- >+ >+ public void updateFormulas(){ >+ formulaShiftingManager.updateFormulas(); >+ } >+ > /** > * Shift merged regions > * >@@ -99,103 +106,6 @@ public final class XSSFRowShifter extends RowShifter { > } > } > >- /** >- * Update formulas. >- */ >- public void updateFormulas() { >- //update formulas on the parent sheet >- updateSheetFormulas(sheet); >- >- //update formulas on other sheets >- Workbook wb = sheet.getWorkbook(); >- for (Sheet sh : wb) { >- if (sheet == sh) continue; >- updateSheetFormulas(sh); >- } >- } >- >- private void updateSheetFormulas(Sheet sh) { >- for (Row r : sh) { >- XSSFRow row = (XSSFRow) r; >- updateRowFormulas(row); >- } >- } >- >- /** >- * Update the formulas in specified row using the formula shifting policy specified by shifter >- * >- * @param row the row to update the formulas on >- * @param shifter the formula shifting policy >- */ >- @Internal >- public void updateRowFormulas(Row row) { >- XSSFSheet sheet = (XSSFSheet) row.getSheet(); >- for (Cell c : row) { >- XSSFCell cell = (XSSFCell) c; >- >- CTCell ctCell = cell.getCTCell(); >- if (ctCell.isSetF()) { >- CTCellFormula f = ctCell.getF(); >- String formula = f.getStringValue(); >- if (formula.length() > 0) { >- String shiftedFormula = shiftFormula(row, formula, shifter); >- if (shiftedFormula != null) { >- f.setStringValue(shiftedFormula); >- if(f.getT() == STCellFormulaType.SHARED){ >- int si = (int)f.getSi(); >- CTCellFormula sf = sheet.getSharedFormula(si); >- sf.setStringValue(shiftedFormula); >- updateRefInCTCellFormula(row, sf); >- } >- } >- >- } >- >- //Range of cells which the formula applies to. >- updateRefInCTCellFormula(row, f); >- } >- >- } >- } >- >- private void updateRefInCTCellFormula(Row row, CTCellFormula f) { >- if (f.isSetRef()) { //Range of cells which the formula applies to. >- String ref = f.getRef(); >- String shiftedRef = shiftFormula(row, ref, shifter); >- if (shiftedRef != null) f.setRef(shiftedRef); >- } >- } >- >- /** >- * Shift a formula using the supplied FormulaShifter >- * >- * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. >- * @param formula the formula to shift >- * @param shifter the FormulaShifter object that operates on the parsed formula tokens >- * @return the shifted formula if the formula was changed, >- * <code>null</code> if the formula wasn't modified >- */ >- private static String shiftFormula(Row row, String formula, FormulaShifter shifter) { >- Sheet sheet = row.getSheet(); >- Workbook wb = sheet.getWorkbook(); >- int sheetIndex = wb.getSheetIndex(sheet); >- final int rowIndex = row.getRowNum(); >- XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); >- >- 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() { > XSSFSheet xsheet = (XSSFSheet) sheet; > XSSFWorkbook wb = xsheet.getWorkbook(); >@@ -299,5 +209,4 @@ public final class XSSFRowShifter extends RowShifter { > } > throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")"); > } >- > } >-- >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