ASF Bugzilla – Attachment 35386 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]
patch285
0005.patch (text/plain), 33.13 KB, created by
Dragan Jovanović
on 2017-09-28 13:48:36 UTC
(
hide
)
Description:
patch285
Filename:
MIME Type:
Creator:
Dragan Jovanović
Created:
2017-09-28 13:48:36 UTC
Size:
33.13 KB
patch
obsolete
>From c5ceaded3b8a3a4d4a35536b1cef29242589da85 Mon Sep 17 00:00:00 2001 >From: zmau <drjovanovic@gmail.com> >Date: Thu, 28 Sep 2017 15:09:28 +0200 >Subject: [PATCH 5/5] 660: Add column shifting functionality to Apache POI > project > >Moved some more code from XSSFRowShifter to class which also works for columns. >--- > .../poi/ss/usermodel/helpers/RowShifter.java | 11 - > .../org/apache/poi/xssf/usermodel/XSSFCell.java | 2 +- > .../org/apache/poi/xssf/usermodel/XSSFRow.java | 4 +- > .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 20 +- > .../xssf/usermodel/helpers/XSSFColumnShifter.java | 71 +----- > .../helpers/XSSFFormulaShiftingManager.java | 130 ----------- > .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 107 +-------- > .../usermodel/helpers/XSSFShiftingManager.java | 246 +++++++++++++++++++++ > 8 files changed, 266 insertions(+), 325 deletions(-) > delete mode 100644 src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java > create mode 100644 src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java > >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 0ebacdf..f974c81 100644 >--- a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >@@ -105,15 +105,4 @@ public abstract class RowShifter { > */ > public abstract void updateNamedRanges(); > >- public abstract void updateConditionalFormatting(); >- >- /** >- * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink >- * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks >- * do not track the content they point to. >- * >- * @param shifter the formula shifting policy >- */ >- public abstract void updateHyperlinks(); >- > } >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >index 93c570a..ee615d3 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >@@ -1330,7 +1330,7 @@ public final class XSSFCell implements Cell { > } > > /*** >- * Moved from XSSFRow.shift(). Not sure what is purpose. >+ * Moved from XSSFRow.shift(). Not sure what is purpose of method. > */ > public void updateCellReferencesForShifting(String msg){ > if(isPartOfArrayFormulaGroup()) >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 9919fe5..8ca369e 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >@@ -35,7 +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.XSSFShiftingManager; > import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; >@@ -635,7 +635,7 @@ 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 XSSFFormulaShiftingManager formulaShiftingManager = new XSSFFormulaShiftingManager(_sheet, shifter); >+ final XSSFShiftingManager formulaShiftingManager = new XSSFShiftingManager(_sheet, shifter); > formulaShiftingManager.updateRowFormulas(this); > > // Copy merged regions that are fully contained on the row >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 c444469..cdbbde7 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >@@ -88,7 +88,7 @@ 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.XSSFFormulaShiftingManager; >+import org.apache.poi.xssf.usermodel.helpers.XSSFShiftingManager; > import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper; > import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; > import org.apache.xmlbeans.XmlCursor; >@@ -2956,10 +2956,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter); > > rowShifter.updateNamedRanges(); >- rowShifter.updateFormulas(); > rowShifter.shiftMergedRegions(startRow, endRow, n); >- rowShifter.updateConditionalFormatting(); >- rowShifter.updateHyperlinks(); >+ >+ XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter); >+ shiftingManager.updateFormulas(); >+ shiftingManager.updateConditionalFormatting(); >+ shiftingManager.updateHyperlinks(); > > //rebuild the _rows map > Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >@@ -2981,13 +2983,13 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n); > XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter); > columnShifter.shiftColumns(startColumn, endColumn, n); >- //XSSFFormulaShiftingManager shiftingManager = new XSSFFormulaShiftingManager(this, shifter); >- //shiftingManager.updateFormulas(); >+ XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter); >+ shiftingManager.updateFormulas(); >+ shiftingManager.updateConditionalFormatting(); >+ shiftingManager.updateHyperlinks(); > > /*columnShifter.updateNamedRanges(); >- columnShifter.shiftMergedRegions(startColumn, startColumn, n); >- columnShifter.updateConditionalFormatting(); >- columnShifter.updateHyperlinks();*/ >+ columnShifter.shiftMergedRegions(startColumn, startColumn, n);*/ > > //rebuild the _rows map > Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >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 >index fec5455..956ed5a 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >@@ -30,11 +30,11 @@ public class XSSFColumnShifter extends ColumnShifter{ > private int lastShiftColumnIndex; > private int shiftStep; > >- private XSSFFormulaShiftingManager formulaShiftingManager; >+ private XSSFShiftingManager formulaShiftingManager; > > public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) { > super(sh, shifter); >- formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter); >+ formulaShiftingManager = new XSSFShiftingManager(sh, shifter); > } > > public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){ >@@ -42,8 +42,8 @@ public class XSSFColumnShifter extends ColumnShifter{ > this.shiftStep = step; > if(shiftStep > 0) > shiftColumnsRight(); >- //else if(shiftStep < 0) >- //shiftColumnsLeft(); >+ else if(shiftStep < 0) >+ shiftColumnsLeft(); > formulaShiftingManager.updateFormulas(); > } > /** >@@ -128,67 +128,4 @@ public class XSSFColumnShifter extends ColumnShifter{ > } > } > >- private void processComments(CommentsTable sheetComments, XSSFVMLDrawing vml, int startRow, int endRow, final int n, boolean copyRowHeight){ >- SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() { >- @Override >- public int compare(XSSFComment o1, XSSFComment o2) { >- int column1Index = o1.getColumn(); >- int column2Index = o2.getColumn(); >- >- if(column1Index == column2Index) >- return o1.hashCode() - o2.hashCode(); >- >- if(n > 0) // when shifting right, sort higher column-values first >- return column1Index < column2Index ? 1 : -1; >- else // sort lower-column values first when shifting left >- return column1Index > column2Index ? 1 : -1; >- } >- }); >- >- for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { >- XSSFRow row = (XSSFRow)it.next(); >- int rownum = row.getRowNum(); >- >- if(sheetComments != null){ >- // calculate the new rownum >- int newrownum = shiftedRowNum(startRow, endRow, n, rownum); >- >- // is there a change necessary for the current row? >- if(newrownum != rownum) { >- CTCommentList lst = sheetComments.getCTComments().getCommentList(); >- for (CTComment comment : lst.getCommentArray()) { >- String oldRef = comment.getRef(); >- CellReference ref = new CellReference(oldRef); >- >- // is this comment part of the current row? >- if(ref.getRow() == rownum) { >- XSSFComment xssfComment = new XSSFComment(sheetComments, comment, >- vml == null ? null : vml.findCommentShape(rownum, ref.getCol())); >- >- // we should not perform the shifting right here as we would then find >- // already shifted comments and would shift them again... >- commentsToShift.put(xssfComment, newrownum); >- } >- } >- } >- } >- >- if(rownum < startRow || rownum > endRow) { >- continue; >- } >- if (!copyRowHeight) { >- row.setHeight((short)-1); >- } >- row.shift(n); >- } >- >- // adjust all the affected comment-structures now >- // the Map is sorted and thus provides them in the order that we need here, >- // i.e. from down to up if shifting down, vice-versa otherwise >- for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) { >- entry.getKey().setRow(entry.getValue()); >- } >- >- } >- > } >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 >deleted file mode 100644 >index da4e323..0000000 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java >+++ /dev/null >@@ -1,130 +0,0 @@ >-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 7838fe9..3784baa 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,14 @@ 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; >+ private XSSFShiftingManager formulaShiftingManager; > > public XSSFRowShifter(XSSFSheet sh) { > super(sh); > } > public XSSFRowShifter(Sheet sh, FormulaShifter shifter) { > super(sh, shifter); >- formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter); >+ formulaShiftingManager = new XSSFShiftingManager(sh, shifter); > } > > >@@ -106,107 +106,4 @@ public final class XSSFRowShifter extends RowShifter { > } > } > >- public void updateConditionalFormatting() { >- XSSFSheet xsheet = (XSSFSheet) sheet; >- XSSFWorkbook wb = xsheet.getWorkbook(); >- int sheetIndex = wb.getSheetIndex(sheet); >- final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting >- >- XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); >- CTWorksheet ctWorksheet = xsheet.getCTWorksheet(); >- CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray(); >- // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) >- for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) { >- CTConditionalFormatting cf = conditionalFormattingArray[j]; >- >- ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>(); >- for (Object stRef : cf.getSqref()) { >- String[] regions = stRef.toString().split(" "); >- for (String region : regions) { >- cellRanges.add(CellRangeAddress.valueOf(region)); >- } >- } >- >- boolean changed = false; >- List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>(); >- for (CellRangeAddress craOld : cellRanges) { >- CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex); >- if (craNew == null) { >- changed = true; >- continue; >- } >- temp.add(craNew); >- if (craNew != craOld) { >- changed = true; >- } >- } >- >- if (changed) { >- int nRanges = temp.size(); >- if (nRanges == 0) { >- ctWorksheet.removeConditionalFormatting(j); >- continue; >- } >- List<String> refs = new ArrayList<String>(); >- for(CellRangeAddress a : temp) refs.add(a.formatAsString()); >- cf.setSqref(refs); >- } >- >- for(CTCfRule cfRule : cf.getCfRuleArray()){ >- String[] formulaArray = cfRule.getFormulaArray(); >- for (int i = 0; i < formulaArray.length; i++) { >- String formula = formulaArray[i]; >- Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex); >- if (shifter.adjustFormula(ptgs, sheetIndex)) { >- String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); >- cfRule.setFormulaArray(i, shiftedFmla); >- } >- } >- } >- } >- } >- >- /** >- * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink >- * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks >- * do not track the content they point to. >- * >- * @param shifter >- */ >- public void updateHyperlinks() { >- int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); >- List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); >- >- for (Hyperlink hyperlink : hyperlinkList) { >- XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; >- String cellRef = xhyperlink.getCellRef(); >- CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); >- CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex); >- if (shiftedRange != null && shiftedRange != cra) { >- // shiftedRange should not be null. If shiftedRange is null, that means >- // that a hyperlink wasn't deleted at the beginning of shiftRows when >- // identifying rows that should be removed because they will be overwritten >- xhyperlink.setCellReference(shiftedRange.formatAsString()); >- } >- } >- } >- >- private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) { >- // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here >- AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false); >- Ptg[] ptgs = { aptg, }; >- >- if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) { >- return cra; >- } >- Ptg ptg0 = ptgs[0]; >- if (ptg0 instanceof AreaPtg) { >- AreaPtg bptg = (AreaPtg) ptg0; >- return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn()); >- } >- if (ptg0 instanceof AreaErrPtg) { >- return null; >- } >- throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")"); >- } > } >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java >new file mode 100644 >index 0000000..b060460 >--- /dev/null >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java >@@ -0,0 +1,246 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+import java.util.ArrayList; >+import java.util.List; >+ >+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.AreaErrPtg; >+import org.apache.poi.ss.formula.ptg.AreaPtg; >+import org.apache.poi.ss.formula.ptg.Ptg; >+import org.apache.poi.ss.usermodel.Cell; >+import org.apache.poi.ss.usermodel.Hyperlink; >+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.ss.util.CellRangeAddress; >+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.XSSFHyperlink; >+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.CTCfRule; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; >+ >+public class XSSFShiftingManager { >+ >+ private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); >+ >+ protected final Sheet shiftingSheet; >+ protected FormulaShifter shifter; >+ >+ public XSSFShiftingManager(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; >+ } >+ } >+ >+ >+ public void updateConditionalFormatting() { >+ XSSFSheet xsheet = (XSSFSheet) shiftingSheet; >+ XSSFWorkbook wb = xsheet.getWorkbook(); >+ int sheetIndex = wb.getSheetIndex(shiftingSheet); >+ final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting >+ >+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); >+ CTWorksheet ctWorksheet = xsheet.getCTWorksheet(); >+ CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray(); >+ // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) >+ for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) { >+ CTConditionalFormatting cf = conditionalFormattingArray[j]; >+ >+ ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>(); >+ for (Object stRef : cf.getSqref()) { >+ String[] regions = stRef.toString().split(" "); >+ for (String region : regions) { >+ cellRanges.add(CellRangeAddress.valueOf(region)); >+ } >+ } >+ >+ boolean changed = false; >+ List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>(); >+ for (CellRangeAddress craOld : cellRanges) { >+ CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex); >+ if (craNew == null) { >+ changed = true; >+ continue; >+ } >+ temp.add(craNew); >+ if (craNew != craOld) { >+ changed = true; >+ } >+ } >+ >+ if (changed) { >+ int nRanges = temp.size(); >+ if (nRanges == 0) { >+ ctWorksheet.removeConditionalFormatting(j); >+ continue; >+ } >+ List<String> refs = new ArrayList<String>(); >+ for(CellRangeAddress a : temp) refs.add(a.formatAsString()); >+ cf.setSqref(refs); >+ } >+ >+ for(CTCfRule cfRule : cf.getCfRuleArray()){ >+ String[] formulaArray = cfRule.getFormulaArray(); >+ for (int i = 0; i < formulaArray.length; i++) { >+ String formula = formulaArray[i]; >+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex); >+ if (shifter.adjustFormula(ptgs, sheetIndex)) { >+ String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); >+ cfRule.setFormulaArray(i, shiftedFmla); >+ } >+ } >+ } >+ } >+ } >+ >+ private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) { >+ // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here >+ AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false); >+ Ptg[] ptgs = { aptg, }; >+ >+ if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) { >+ return cra; >+ } >+ Ptg ptg0 = ptgs[0]; >+ if (ptg0 instanceof AreaPtg) { >+ AreaPtg bptg = (AreaPtg) ptg0; >+ return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn()); >+ } >+ if (ptg0 instanceof AreaErrPtg) { >+ return null; >+ } >+ throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")"); >+ } >+ >+ /** >+ * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink >+ * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks >+ * do not track the content they point to. >+ * >+ * @param shifter >+ */ >+ public void updateHyperlinks() { >+ int sheetIndex = shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet); >+ List<? extends Hyperlink> hyperlinkList = shiftingSheet.getHyperlinkList(); >+ >+ for (Hyperlink hyperlink : hyperlinkList) { >+ XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; >+ String cellRef = xhyperlink.getCellRef(); >+ CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); >+ CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex); >+ if (shiftedRange != null && shiftedRange != cra) { >+ // shiftedRange should not be null. If shiftedRange is null, that means >+ // that a hyperlink wasn't deleted at the beginning of shiftRows when >+ // identifying rows that should be removed because they will be overwritten >+ xhyperlink.setCellReference(shiftedRange.formatAsString()); >+ } >+ } >+ } >+ >+ >+} >-- >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