From 0b1a207f3764657afde1c20fb3d3f42bf339b421 Mon Sep 17 00:00:00 2001 From: zmau Date: Mon, 25 Sep 2017 16:02:02 +0200 Subject: [PATCH 2/5] 660: Add column shifting functionality to Apache POI project Refactored Sheet.shiftRows() a bit, in order to make it more readable. Refactored RowShifter, XSSFRowShifter and HSSFRowShifter classes, made shifter an attribute of them. --- .../poi/hssf/usermodel/helpers/HSSFRowShifter.java | 14 +++-- .../poi/ss/usermodel/helpers/RowShifter.java | 16 ++++-- .../org/apache/poi/xssf/usermodel/XSSFRow.java | 4 +- .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 67 ++++++++++++---------- .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 29 +++++----- 5 files changed, 74 insertions(+), 56 deletions(-) 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 88a6f9b..0c4f78b 100644 --- a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java +++ b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java @@ -21,6 +21,7 @@ import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.formula.eval.NotImplementedException; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.helpers.RowShifter; import org.apache.poi.util.Internal; import org.apache.poi.util.NotImplemented; @@ -38,30 +39,33 @@ public final class HSSFRowShifter extends RowShifter { public HSSFRowShifter(HSSFSheet sh) { super(sh); } + public HSSFRowShifter(Sheet sh, FormulaShifter shifter) { + super(sh, shifter); + } @NotImplemented - public void updateNamedRanges(FormulaShifter shifter) { + public void updateNamedRanges() { throw new NotImplementedException("HSSFRowShifter.updateNamedRanges"); } @NotImplemented - public void updateFormulas(FormulaShifter shifter) { + public void updateFormulas() { throw new NotImplementedException("updateFormulas"); } @Internal @NotImplemented - public void updateRowFormulas(Row row, FormulaShifter shifter) { + public void updateRowFormulas(Row row) { throw new NotImplementedException("updateRowFormulas"); } @NotImplemented - public void updateConditionalFormatting(FormulaShifter shifter) { + public void updateConditionalFormatting() { throw new NotImplementedException("updateConditionalFormatting"); } @NotImplemented - public void updateHyperlinks(FormulaShifter shifter) { + public void updateHyperlinks() { throw new NotImplementedException("updateHyperlinks"); } 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 680abff..5d815e6 100644 --- a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java +++ b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java @@ -37,11 +37,17 @@ import org.apache.poi.util.POILogger; */ public abstract class RowShifter { protected final Sheet sheet; + protected FormulaShifter shifter; public RowShifter(Sheet sh) { sheet = sh; } + public RowShifter(Sheet sh, FormulaShifter shifter) { + sheet = sh; + this.shifter = shifter; + } + /** * Shifts, grows, or shrinks the merged regions due to a row shift. * Merged regions that are completely overlaid by shifting will be deleted. @@ -97,12 +103,12 @@ public abstract class RowShifter { /** * Updated named ranges */ - public abstract void updateNamedRanges(FormulaShifter shifter); + public abstract void updateNamedRanges(); /** * Update formulas. */ - public abstract void updateFormulas(FormulaShifter shifter); + public abstract void updateFormulas(); /** * Update the formulas in specified row using the formula shifting policy specified by shifter @@ -111,9 +117,9 @@ public abstract class RowShifter { * @param shifter the formula shifting policy */ @Internal - public abstract void updateRowFormulas(Row row, FormulaShifter shifter); + public abstract void updateRowFormulas(Row row); - public abstract void updateConditionalFormatting(FormulaShifter shifter); + public abstract void updateConditionalFormatting(); /** * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink @@ -122,6 +128,6 @@ public abstract class RowShifter { * * @param shifter the formula shifting policy */ - public abstract void updateHyperlinks(FormulaShifter shifter); + public abstract void updateHyperlinks(); } 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 35ed242..d79172d 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java @@ -640,14 +640,14 @@ public class XSSFRow implements Row, Comparable { destCell.copyCellFrom(srcCell, policy); } - final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet); final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet); final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex); final int srcRowNum = srcRow.getRowNum(); final int destRowNum = getRowNum(); final int rowDifference = destRowNum - srcRowNum; final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007); - rowShifter.updateRowFormulas(this, shifter); + final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet, shifter); + rowShifter.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 b5b4e42..2348f4e 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -2943,8 +2943,35 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { @Override public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { XSSFVMLDrawing vml = getVMLDrawing(false); + removeOverwritten(vml, startRow, endRow, n); + doShifting(vml, startRow, endRow, n, copyRowHeight); + + + int sheetIndex = getWorkbook().getSheetIndex(this); + String sheetName = getWorkbook().getSheetName(sheetIndex); + FormulaShifter shifter = FormulaShifter.createForRowShift( + sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007); + XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter); - // first remove all rows which will be overwritten + rowShifter.updateNamedRanges(); + rowShifter.updateFormulas(); + rowShifter.shiftMergedRegions(startRow, endRow, n); + rowShifter.updateConditionalFormatting(); + rowShifter.updateHyperlinks(); + + //rebuild the _rows map + Map map = new HashMap(); + 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 it = rowIterator() ; it.hasNext() ; ) { XSSFRow row = (XSSFRow)it.next(); int rownum = row.getRowNum(); @@ -2987,10 +3014,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } } } - - // then do the actual moving and also adjust comments/rowHeight - // we need to sort it in a way so the shifting does not mess up the structures, - // i.e. when shifting down, start from down and go up, when shifting up, vice-versa + } + + // do the actual moving and also adjust comments/rowHeight + // we need to sort it in a way so the shifting does not mess up the structures, + // i.e. when shifting down, start from down and go up, when shifting up, vice-versa + private void doShifting(XSSFVMLDrawing vml, int startRow, int endRow, final int n, boolean copyRowHeight){ SortedMap commentsToShift = new TreeMap(new Comparator() { @Override public int compare(XSSFComment o1, XSSFComment o2) { @@ -3012,7 +3041,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } } }); - for (Iterator it = rowIterator() ; it.hasNext() ; ) { XSSFRow row = (XSSFRow)it.next(); @@ -3045,11 +3073,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { if(rownum < startRow || rownum > endRow) { continue; } - if (!copyRowHeight) { row.setHeight((short)-1); } - row.shift(n); } @@ -3059,30 +3085,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { for(Map.Entry entry : commentsToShift.entrySet()) { entry.getKey().setRow(entry.getValue()); } - - XSSFRowShifter rowShifter = new XSSFRowShifter(this); - - int sheetIndex = getWorkbook().getSheetIndex(this); - String sheetName = getWorkbook().getSheetName(sheetIndex); - FormulaShifter shifter = FormulaShifter.createForRowShift( - sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007); - - rowShifter.updateNamedRanges(shifter); - rowShifter.updateFormulas(shifter); - rowShifter.shiftMergedRegions(startRow, endRow, n); - rowShifter.updateConditionalFormatting(shifter); - rowShifter.updateHyperlinks(shifter); - - //rebuild the _rows map - Map map = new HashMap(); - 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); + } + private int shiftedRowNum(int startRow, int endRow, int n, int rownum) { // no change if before any affected row 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 46f0b89..f9ad224 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 @@ -19,7 +19,6 @@ 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; @@ -63,6 +62,10 @@ public final class XSSFRowShifter extends RowShifter { public XSSFRowShifter(XSSFSheet sh) { super(sh); } + public XSSFRowShifter(Sheet sh, FormulaShifter shifter) { + super(sh, shifter); + } + /** * Shift merged regions @@ -80,7 +83,7 @@ public final class XSSFRowShifter extends RowShifter { /** * Updated named ranges */ - public void updateNamedRanges(FormulaShifter shifter) { + public void updateNamedRanges() { Workbook wb = sheet.getWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); for (Name name : wb.getAllNames()) { @@ -99,22 +102,22 @@ public final class XSSFRowShifter extends RowShifter { /** * Update formulas. */ - public void updateFormulas(FormulaShifter shifter) { + public void updateFormulas() { //update formulas on the parent sheet - updateSheetFormulas(sheet, shifter); + updateSheetFormulas(sheet); //update formulas on other sheets Workbook wb = sheet.getWorkbook(); for (Sheet sh : wb) { if (sheet == sh) continue; - updateSheetFormulas(sh, shifter); + updateSheetFormulas(sh); } } - private void updateSheetFormulas(Sheet sh, FormulaShifter shifter) { + private void updateSheetFormulas(Sheet sh) { for (Row r : sh) { XSSFRow row = (XSSFRow) r; - updateRowFormulas(row, shifter); + updateRowFormulas(row); } } @@ -125,7 +128,7 @@ public final class XSSFRowShifter extends RowShifter { * @param shifter the formula shifting policy */ @Internal - public void updateRowFormulas(Row row, FormulaShifter shifter) { + public void updateRowFormulas(Row row) { XSSFSheet sheet = (XSSFSheet) row.getSheet(); for (Cell c : row) { XSSFCell cell = (XSSFCell) c; @@ -142,20 +145,20 @@ public final class XSSFRowShifter extends RowShifter { int si = (int)f.getSi(); CTCellFormula sf = sheet.getSharedFormula(si); sf.setStringValue(shiftedFormula); - updateRefInCTCellFormula(row, shifter, sf); + updateRefInCTCellFormula(row, sf); } } } //Range of cells which the formula applies to. - updateRefInCTCellFormula(row, shifter, f); + updateRefInCTCellFormula(row, f); } } } - private void updateRefInCTCellFormula(Row row, FormulaShifter shifter, CTCellFormula 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); @@ -193,7 +196,7 @@ public final class XSSFRowShifter extends RowShifter { } } - public void updateConditionalFormatting(FormulaShifter shifter) { + public void updateConditionalFormatting() { XSSFSheet xsheet = (XSSFSheet) sheet; XSSFWorkbook wb = xsheet.getWorkbook(); int sheetIndex = wb.getSheetIndex(sheet); @@ -260,7 +263,7 @@ public final class XSSFRowShifter extends RowShifter { * * @param shifter */ - public void updateHyperlinks(FormulaShifter shifter) { + public void updateHyperlinks() { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List hyperlinkList = sheet.getHyperlinkList(); -- 2.13.1.windows.2