[patch] Whole patch for current state of column shifting

column shifting - 0 - 2017.10.13.patch (text/plain), 79.96 KB, created by Dragan Jovanović on 2017-10-13 12:32:24 UTC

(hide)

Description: Whole patch for current state of column shifting

Filename:

MIME Type:

Creator: Dragan Jovanović

Created: 2017-10-13 12:32:24 UTC

Size: 79.96 KB

patch

obsolete

>From 54408b86ae2ebf8aae1e96799309f4b39de8a3dd Mon Sep 17 00:00:00 2001 >From: zmau <drjovanovic@gmail.com> >Date: Thu, 12 Oct 2017 15:27:45 +0200 >Subject: [PATCH] Auto stash before revert of "commit for patch creation" > >--- > .../org/apache/poi/hssf/usermodel/HSSFSheet.java | 3 + > .../poi/hssf/usermodel/helpers/HSSFRowShifter.java | 5 +- > .../org/apache/poi/ss/formula/FormulaShifter.java | 151 ++++++-- > src/java/org/apache/poi/ss/usermodel/Sheet.java | 1 + > .../poi/ss/usermodel/helpers/ColumnShifter.java | 74 ++++ > .../poi/ss/usermodel/helpers/RowShifter.java | 16 +- > .../org/apache/poi/xssf/streaming/SXSSFSheet.java | 6 + > .../org/apache/poi/xssf/usermodel/XSSFCell.java | 20 ++ > .../org/apache/poi/xssf/usermodel/XSSFRow.java | 23 +- > .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 72 +++- > .../apache/poi/xssf/usermodel/XSSFVMLDrawing.java | 2 +- > .../xssf/usermodel/helpers/XSSFColumnShifter.java | 193 +++++++++++ > .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 319 ++++++----------- > .../usermodel/helpers/XSSFShiftingManager.java | 286 +++++++++++++++ > .../usermodel/helpers/XSSFColumnShifterTest.java | 384 +++++++++++++++++++++ > 15 files changed, 1295 insertions(+), 260 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/XSSFShiftingManager.java > create mode 100644 src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java > >diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >index 4f886d299..39e959ccf 100644 >--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >@@ -2640,4 +2640,7 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { > _sheet.setActiveCellRow(row); > _sheet.setActiveCellCol(col); > } >+ >+ public void shiftColumns(int startColumn, int endColumn, int n){ >+ } > } >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 88a6f9b73..3c5530311 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,7 +39,9 @@ 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) { > throw new NotImplementedException("HSSFRowShifter.updateNamedRanges"); >diff --git a/src/java/org/apache/poi/ss/formula/FormulaShifter.java b/src/java/org/apache/poi/ss/formula/FormulaShifter.java >index b2edea349..9635c35d4 100644 >--- a/src/java/org/apache/poi/ss/formula/FormulaShifter.java >+++ b/src/java/org/apache/poi/ss/formula/FormulaShifter.java >@@ -17,6 +17,8 @@ > > package org.apache.poi.ss.formula; > >+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.ptg.Area2DPtgBase; > import org.apache.poi.ss.formula.ptg.Area3DPtg; >@@ -33,6 +35,8 @@ 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.Sheet; >+import org.apache.poi.xssf.usermodel.XSSFWorkbook; > > > /** >@@ -40,7 +44,7 @@ import org.apache.poi.ss.formula.ptg.RefPtgBase; > */ > public final class FormulaShifter { > >- private static enum ShiftMode { >+ public static enum ShiftMode { > RowMove, > RowCopy, > SheetMove, >@@ -67,6 +71,9 @@ public final class FormulaShifter { > > private final ShiftMode _mode; > >+ private boolean _rowModeElseColumn; >+ >+ > /** > * Create an instance for shifting row. > * >@@ -89,6 +96,7 @@ public final class FormulaShifter { > _version = version; > > _srcSheetIndex = _dstSheetIndex = -1; >+ _rowModeElseColumn = true; // default > } > > /** >@@ -104,16 +112,33 @@ public final class FormulaShifter { > _srcSheetIndex = srcSheetIndex; > _dstSheetIndex = dstSheetIndex; > _mode = ShiftMode.SheetMove; >+ _rowModeElseColumn = true; // default > } > >- public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove, >+ 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; >+ 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) { >- return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version); >+ FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version); >+ 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._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) { >- return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowCopy, version); >+ FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowCopy, version); >+ return instance; > } > > public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) { >@@ -130,23 +155,6 @@ public final class FormulaShifter { > "]"; > } > >- /** >- * @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: >@@ -548,4 +556,105 @@ public final class FormulaShifter { > > throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")"); > } >+ >+ >+ // ******** logic which processes columns in same way as row ******** >+ >+ >+ /** >+ * @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; >+ } >+ } >+ else { >+ Ptg transposedPtg = transpose(ptgs[i]); >+ newPtg = adjustPtg(transposedPtg, currentExternSheetIx); >+ if (newPtg != null) { >+ refsWereChanged = true; >+ ptgs[i] = transpose(transposedPtg); >+ } >+ } >+ } >+ return refsWereChanged; >+ } >+ >+ >+ 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); >+ 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); >+ 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()); >+ ((AreaPtg) ptg).setFirstRow(oldFirstColumnIndex); >+ int oldLastColumnIndex = ((AreaPtg) ptg).getLastColumn(); >+ ((AreaPtg) ptg).setLastColumn(((AreaPtg) ptg).getLastRow()); >+ ((AreaPtg) ptg).setLastRow(oldLastColumnIndex); >+ return 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); >+ return 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); >+ return ptg; >+ } else if(ptgType.equals("RefPtg")){ // common simple reference, like A2 >+ RefPtg transponedCellRefToken = new RefPtg(transpose(ptg.toFormulaString())); >+ return transponedCellRefToken; >+ } >+ else // operators like + or SUM, for example >+ return ptg; >+ } >+ >+ 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; >+ } >+ >+ >+ > } >diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java >index a0d1a3de7..c4bff16f7 100644 >--- a/src/java/org/apache/poi/ss/usermodel/Sheet.java >+++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java >@@ -702,6 +702,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 000000000..0be693504 >--- /dev/null >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java >@@ -0,0 +1,74 @@ >+package org.apache.poi.ss.usermodel.helpers; >+ >+import java.util.ArrayList; >+import java.util.HashSet; >+import java.util.List; >+import java.util.Set; >+ >+import org.apache.poi.ss.formula.FormulaShifter; >+import org.apache.poi.ss.usermodel.Sheet; >+import org.apache.poi.ss.util.CellRangeAddress; >+ >+public class ColumnShifter { >+ protected final Sheet shiftingSheet; >+ protected FormulaShifter shifter; >+ >+ public ColumnShifter(Sheet sheet, FormulaShifter shifter) { >+ shiftingSheet = sheet; >+ this.shifter = shifter; >+ } >+ >+ >+ /** >+ * Shifts, grows, or shrinks the merged regions due to a column shift. >+ * Merged regions that are completely overlaid by shifting will be deleted. >+ * >+ * @param startColumnIndex index of the column to start shifting >+ * @param endColumnIndex index of the column to end shifting >+ * @param n the number of columns to shift >+ * @return an array of affected merged regions, doesn't contain deleted ones >+ */ >+ public List<CellRangeAddress> shiftMergedRegions(int startColumnIndex, int endColumnIndex, int n) { >+ List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>(); >+ Set<Integer> removedIndices = new HashSet<Integer>(); >+ //move merged regions completely if they fall within the new region boundaries when they are shifted >+ int size = shiftingSheet.getNumMergedRegions(); >+ for (int i = 0; i < size; i++) { >+ CellRangeAddress merged = shiftingSheet.getMergedRegion(i); >+ >+ // remove merged region that overlaps shifting >+ if (startColumnIndex + n <= merged.getFirstColumn() && endColumnIndex + n >= merged.getLastColumn()) { >+ removedIndices.add(i); >+ continue; >+ } >+ >+ boolean inStart = (merged.getFirstColumn() >= startColumnIndex || merged.getLastColumn() >= startColumnIndex); >+ boolean inEnd = (merged.getFirstColumn() <= endColumnIndex || merged.getLastColumn() <= endColumnIndex); >+ >+ //don't check if it's not within the shifted area >+ if (!inStart || !inEnd) >+ continue; >+ >+ //only shift if the region outside the shifted columns is not merged too >+ if (!merged.containsColumn(startColumnIndex - 1) && !merged.containsColumn(endColumnIndex + 1)) { >+ merged.setFirstColumn(merged.getFirstColumn() + n); >+ merged.setLastColumn(merged.getLastColumn() + n); >+ //have to remove/add it back >+ shiftedRegions.add(merged); >+ removedIndices.add(i); >+ } >+ } >+ >+ if(!removedIndices.isEmpty()) { >+ shiftingSheet.removeMergedRegions(removedIndices); >+ } >+ >+ //read so it doesn't get shifted again >+ for (CellRangeAddress region : shiftedRegions) { >+ shiftingSheet.addMergedRegion(region); >+ } >+ return shiftedRegions; >+ } >+ >+ >+} >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 83c634de7..d9530346f 100644 >--- a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >@@ -36,11 +36,17 @@ import org.apache.poi.util.Internal; > public abstract class RowShifter { > protected final Sheet sheet; > >- public RowShifter(Sheet sh) { >- sheet = sh; >- } >- >- /** >+ 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. > * >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 bb378d7e5..0887de6a3 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java >@@ -2115,4 +2115,10 @@ public class SXSSFSheet implements Sheet > color.setIndexed(colorIndex); > pr.setTabColor(color); > } >+ >+ @NotImplemented >+ @Override >+ public void shiftColumns(int startColumn, int endColumn, int n){ >+ throw new RuntimeException("NotImplemented"); >+ } > } >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 b6abbaf3c..6f39b9052 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >@@ -53,6 +53,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; >+import org.apache.poi.xssf.model.CalculationChain; > > /** > * High level representation of a cell in a row of a spreadsheet. >@@ -1311,4 +1312,23 @@ public final class XSSFCell implements Cell { > "You cannot change part of an array."; > notifyArrayFormulaChanging(msg); > } >+ >+ /*** >+ * Moved from XSSFRow.shift(). Not sure what is purpose. >+ */ >+ public void updateCellReferencesForShifting(String msg){ >+ if(isPartOfArrayFormulaGroup()) >+ notifyArrayFormulaChanging(msg); >+ CalculationChain calcChain = getSheet().getWorkbook().getCalculationChain(); >+ int sheetId = (int)getSheet().sheet.getSheetId(); >+ >+ //remove the reference in the calculation chain >+ if(calcChain != null) calcChain.removeItem(sheetId, getReference()); >+ >+ CTCell ctCell = getCTCell(); >+ String r = new CellReference(getRowIndex(), getColumnIndex()).formatAsString(); >+ ctCell.setR(r); >+ } >+ > } >+ >\ No newline at end of file >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 b33de4257..c6ba80a7f 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.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; >@@ -572,25 +573,13 @@ public class XSSFRow implements Row, Comparable<XSSFRow> { > * > * @param n the number of rows to move > */ >- protected void shift(int n) { >+ public void shift(int n) { > int rownum = getRowNum() + n; >- CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain(); >- int sheetId = (int)_sheet.sheet.getSheetId(); > String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in a multi-cell array formula. " + > "You cannot change part of an array."; > for(Cell c : this){ >- XSSFCell cell = (XSSFCell)c; >- if(cell.isPartOfArrayFormulaGroup()){ >- cell.notifyArrayFormulaChanging(msg); >- } >- >- //remove the reference in the calculation chain >- if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference()); >- >- CTCell ctCell = cell.getCTCell(); >- String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString(); >- ctCell.setR(r); >- } >+ ((XSSFCell)c).updateCellReferencesForShifting(msg); >+ } > setRowNum(rownum); > } > >@@ -640,14 +629,14 @@ public class XSSFRow implements Row, Comparable<XSSFRow> { > 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 XSSFShiftingManager formulaShiftingManager = new XSSFShiftingManager(_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 ccac2cc47..8dd624169 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >@@ -84,8 +84,10 @@ 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.poi.xssf.usermodel.helpers.XSSFShiftingManager; > import org.apache.xmlbeans.XmlCursor; > import org.apache.xmlbeans.XmlException; > import org.apache.xmlbeans.XmlObject; >@@ -2985,7 +2987,69 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { > XSSFVMLDrawing vml = getVMLDrawing(false); > >- // first remove all rows which will be overwritten >+ removeOverwritten(vml, startRow, endRow, n); >+ >+ 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); >+ rowShifter.doShiftingAndProcessComments(vml, startRow, endRow, n, copyRowHeight, rowIterator(), sheetComments); >+ rowShifter.shiftMergedRegions(startRow, endRow, n); >+ >+ XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter); >+ shiftingManager.updateNamedRanges(); >+ shiftingManager.updateFormulas(); >+ shiftingManager.updateConditionalFormatting(); >+ shiftingManager.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); >+ } >+ >+ /** >+ * Shifts columns between startColumn and endColumn n number of columns. >+ * If you use a negative number, it will shift columns up. >+ * Code ensures that columns don't wrap around >+ * >+ * @param startRow the column to start shifting >+ * @param endRow the column to end shifting >+ * @param n the number of columns to shift >+ */ @Override >+ public void shiftColumns(int startColumn, int endColumn, final int n) { >+ XSSFVMLDrawing vml = getVMLDrawing(false); >+ >+ FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n); >+ XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter); >+ columnShifter.shiftColumns(startColumn, endColumn, n); >+ columnShifter.shiftMergedRegions(startColumn, startColumn, n); >+ columnShifter.shiftComments(vml, startColumn, endColumn, n, sheetComments); >+ >+ XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter); >+ shiftingManager.updateFormulas(); >+ shiftingManager.updateConditionalFormatting(); >+ shiftingManager.updateHyperlinks(); >+ shiftingManager.updateNamedRanges(); >+ >+ //rebuild the _rows map >+ Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >+ for(XSSFRow r : _rows.values()) { >+ 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() ; ) { > XSSFRow row = (XSSFRow)it.next(); > int rownum = row.getRowNum(); >@@ -3087,10 +3151,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > continue; > } > >- if (!copyRowHeight) { >- row.setHeight((short)-1); >- } >- > row.shift(n); > } > >@@ -4515,4 +4575,4 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > } > > >-} >+} >\ No newline at end of file >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >index 5cfd38294..88a5e4109 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >@@ -264,7 +264,7 @@ public final class XSSFVMLDrawing extends POIXMLDocumentPart { > * > * @return the comment shape or <code>null</code> > */ >- protected CTShape findCommentShape(int row, int col){ >+ public CTShape findCommentShape(int row, int col){ > for(XmlObject itm : _items){ > if(itm instanceof CTShape){ > CTShape sh = (CTShape)itm; >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 000000000..40170e170 >--- /dev/null >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >@@ -0,0 +1,193 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+import java.util.ArrayList; >+import java.util.Comparator; >+import java.util.HashSet; >+import java.util.Iterator; >+import java.util.List; >+import java.util.Map; >+import java.util.Set; >+import java.util.SortedMap; >+import java.util.TreeMap; >+ >+import org.apache.poi.ss.ITestDataProvider; >+import org.apache.poi.ss.formula.FormulaShifter; >+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.helpers.ColumnShifter; >+import org.apache.poi.ss.util.CellRangeAddress; >+import org.apache.poi.ss.util.CellReference; >+import org.apache.poi.util.POILogFactory; >+import org.apache.poi.util.POILogger; >+import org.apache.poi.xssf.model.CommentsTable; >+import org.apache.poi.xssf.usermodel.XSSFCell; >+import org.apache.poi.xssf.usermodel.XSSFComment; >+import org.apache.poi.xssf.usermodel.XSSFRow; >+import org.apache.poi.xssf.usermodel.XSSFSheet; >+import org.apache.poi.xssf.usermodel.XSSFVMLDrawing; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList; >+ >+public class XSSFColumnShifter extends ColumnShifter{ >+ >+ private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); >+ >+ private int firstShiftColumnIndex; >+ private int lastShiftColumnIndex; >+ private int shiftStep; >+ >+ private XSSFShiftingManager formulaShiftingManager; >+ >+ >+ public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) { >+ super(sh, shifter); >+ formulaShiftingManager = new XSSFShiftingManager(sh, shifter); >+ } >+ >+ public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){ >+ this.firstShiftColumnIndex = firstShiftColumnIndex; >+ this.lastShiftColumnIndex = lastShiftColumnIndex; >+ this.shiftStep = step; >+ if(shiftStep > 0) >+ shiftColumnsRight(); >+ else if(shiftStep < 0) >+ shiftColumnsLeft(); >+// formulaShiftingManager.updateFormulas(); >+ } >+ /** >+ * 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); >+ if(row == null) >+ continue; >+ for (int columnIndex = lastShiftColumnIndex; columnIndex >= firstShiftColumnIndex; columnIndex--){ // process cells backwards, because of shifting >+ XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex); >+ Cell newCell = null; >+ if(oldCell == null){ >+ newCell = row.getCell(columnIndex + shiftStep); >+ newCell = null; >+ continue; >+ } >+ else { >+ 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); >+ } >+ } >+ } >+ } >+ } >+ private void shiftColumnsLeft(){ >+ for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++) >+ { >+ XSSFRow row = (XSSFRow)shiftingSheet.getRow(rowNo); >+ if(row == null) >+ continue; >+ for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ >+ XSSFCell oldCell = (XSSFCell)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); >+ } >+ else { >+ oldCell.setCellType(CellType.STRING); >+ oldCell.setCellValue(""); >+ } >+ } >+ else { >+ oldCell = row.createCell(columnIndex); >+ if(newCell != null){ >+ oldCell.setCellType(newCell.getCellType()); >+ cloneCellValue(newCell, oldCell); >+ } >+ else { >+ oldCell.setCellType(CellType.STRING); >+ oldCell.setCellValue(""); >+ } >+ } >+ } >+ } >+ } >+ } >+ >+ public void shiftComments(XSSFVMLDrawing vml, int startColumnIndex, int endColumnIndex, final int n, CommentsTable sheetComments){ >+ SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() { >+ @Override >+ public int compare(XSSFComment o1, XSSFComment o2) { >+ int column1 = o1.getColumn(); >+ int column2 = o2.getColumn(); >+ >+ if(column1 == column2) { >+ // ordering is not important when column is equal, but don't return zero to still >+ // get multiple comments per column into the map >+ return o1.hashCode() - o2.hashCode(); >+ } >+ >+ // when shifting down, sort higher column-values first >+ if(n > 0) { >+ return column1 < column2 ? 1 : -1; >+ } else { >+ // sort lower-column values first when shifting up >+ return column1 > column2 ? 1 : -1; >+ } >+ } >+ }); >+ >+ if(sheetComments != null){ >+ CTCommentList lst = sheetComments.getCTComments().getCommentList(); >+ for (CTComment comment : lst.getCommentArray()) { >+ String oldRef = comment.getRef(); >+ CellReference ref = new CellReference(oldRef); >+ >+ int newColumnIndex = XSSFShiftingManager.shiftedItemIndex(startColumnIndex, endColumnIndex, n, ref.getCol()); >+ >+ // is there a change necessary for the current row? >+ if(newColumnIndex != ref.getCol()) { >+ XSSFComment xssfComment = new XSSFComment(sheetComments, comment, >+ vml == null ? null : vml.findCommentShape(ref.getRow(), ref.getCol())); >+ commentsToShift.put(xssfComment, newColumnIndex); >+ } >+ } >+ for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) >+ entry.getKey().setColumn(entry.getValue()); >+ } >+ >+ } >+ >+ public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) { >+ newCell.setCellComment(oldCell.getCellComment()); >+ 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/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >index 60e7ce78f..0a8f07d8f 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 >@@ -18,7 +18,12 @@ > package org.apache.poi.xssf.usermodel.helpers; > > import java.util.ArrayList; >+import java.util.Comparator; >+import java.util.Iterator; > import java.util.List; >+import java.util.Map; >+import java.util.SortedMap; >+import java.util.TreeMap; > > import org.apache.poi.ss.formula.FormulaParseException; > import org.apache.poi.ss.formula.FormulaParser; >@@ -36,18 +41,24 @@ import org.apache.poi.ss.usermodel.Sheet; > import org.apache.poi.ss.usermodel.Workbook; > import org.apache.poi.ss.usermodel.helpers.RowShifter; > import org.apache.poi.ss.util.CellRangeAddress; >+import org.apache.poi.ss.util.CellReference; > import org.apache.poi.util.Internal; > import org.apache.poi.util.POILogFactory; > import org.apache.poi.util.POILogger; >+import org.apache.poi.xssf.model.CommentsTable; > import org.apache.poi.xssf.usermodel.XSSFCell; >+import org.apache.poi.xssf.usermodel.XSSFComment; > 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.XSSFVMLDrawing; > 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.CTComment; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; >@@ -60,9 +71,91 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; > public final class XSSFRowShifter extends RowShifter { > private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class); > >+ private XSSFShiftingManager formulaShiftingManager; >+ > public XSSFRowShifter(XSSFSheet sh) { > super(sh); > } >+ public XSSFRowShifter(Sheet sh, FormulaShifter shifter) { >+ super(sh, shifter); >+ formulaShiftingManager = new XSSFShiftingManager(sh, shifter); >+ } >+ >+ // 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 >+ public void doShiftingAndProcessComments(XSSFVMLDrawing vml, int startRow, int endRow, final int n, >+ boolean copyRowHeight, Iterator<Row> rowIterator, CommentsTable sheetComments){ >+ SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() { >+ @Override >+ public int compare(XSSFComment o1, XSSFComment o2) { >+ int row1 = o1.getRow(); >+ int row2 = o2.getRow(); >+ >+ if(row1 == row2) { >+ // ordering is not important when row is equal, but don't return zero to still >+ // get multiple comments per row into the map >+ return o1.hashCode() - o2.hashCode(); >+ } >+ >+ // when shifting down, sort higher row-values first >+ if(n > 0) { >+ return row1 < row2 ? 1 : -1; >+ } else { >+ // sort lower-row values first when shifting up >+ return row1 > row2 ? 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 = XSSFShiftingManager.shiftedItemIndex(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()); >+ int x = 5; >+ } >+ >+ } >+ >+ > > /** > * Shift merged regions >@@ -76,225 +169,33 @@ public final class XSSFRowShifter extends RowShifter { > public List<CellRangeAddress> shiftMerged(int startRow, int endRow, int n) { > return shiftMergedRegions(startRow, endRow, n); > } >- >+ > /** >- * Updated named ranges >- */ >+ @deprecated, use FormulaShiftingManager.updateNamedRanges() directly instead >+ */ > public void updateNamedRanges(FormulaShifter shifter) { >- Workbook wb = sheet.getWorkbook(); >- XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); >- for (Name name : wb.getAllNames()) { >- String formula = name.getRefersToFormula(); >- int sheetIndex = name.getSheetIndex(); >- final int rowIndex = -1; //don't care, named ranges are not allowed to include structured references >- >- Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex); >- if (shifter.adjustFormula(ptgs, sheetIndex)) { >- String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); >- name.setRefersToFormula(shiftedFmla); >- } >- } >+ formulaShiftingManager.updateNamedRanges(); > } >- > /** >- * Update formulas. >- */ >+ @deprecated, use FormulaShiftingManager.updateFormulas() directly instead >+ */ > public void updateFormulas(FormulaShifter shifter) { >- //update formulas on the parent sheet >- updateSheetFormulas(sheet, shifter); >- >- //update formulas on other sheets >- Workbook wb = sheet.getWorkbook(); >- for (Sheet sh : wb) { >- if (sheet == sh) continue; >- updateSheetFormulas(sh, shifter); >- } >- } >- >- private void updateSheetFormulas(Sheet sh, FormulaShifter shifter) { >- for (Row r : sh) { >- XSSFRow row = (XSSFRow) r; >- updateRowFormulas(row, shifter); >- } >- } >- >- /** >- * 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, FormulaShifter shifter) { >- 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, shifter, sf); >- } >- } >- >- } >- >- //Range of cells which the formula applies to. >- updateRefInCTCellFormula(row, shifter, f); >- } >- >- } >+ formulaShiftingManager.updateFormulas(); > } >- >- private void updateRefInCTCellFormula(Row row, FormulaShifter shifter, 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; >- } >- } >- >+ @deprecated, use FormulaShiftingManager.updateConditionalFormatting() directly instead >+ */ > public void updateConditionalFormatting(FormulaShifter shifter) { >- 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<>(); >- 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<>(); >- 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<>(); >- 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); >- } >- } >- } >- } >- } >- >+ formulaShiftingManager.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 >- */ >+ @deprecated, use FormulaShiftingManager.updateHyperlinks() directly instead >+ */ > public void updateHyperlinks(FormulaShifter shifter) { >- 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()); >- } >- } >+ formulaShiftingManager.updateHyperlinks(); > } >- >- 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() + ")"); >+ public void updateRowFormulas(Row row, FormulaShifter shifter) { >+ // TODO Auto-generated method stub >+ > } >- > } >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 000000000..174bd8a7b >--- /dev/null >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java >@@ -0,0 +1,286 @@ >+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.Name; >+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()); >+ } >+ } >+ } >+ >+ public void updateNamedRanges() { >+ Workbook wb = shiftingSheet.getWorkbook(); >+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); >+ for (Name name : wb.getAllNames()) { >+ String formula = name.getRefersToFormula(); >+ int sheetIndex = name.getSheetIndex(); >+ final int rowIndex = -1; //don't care, named ranges are not allowed to include structured references >+ >+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex); >+ if (shifter.adjustFormula(ptgs, sheetIndex)) { >+ String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); >+ name.setRefersToFormula(shiftedFmla); >+ } >+ } >+ } >+ >+ public static int shiftedItemIndex(int startShiftingIndex, int endShiftingIndex, int shiftingStep, int originalItemIndex) { >+ // no change if before any affected item >+ if(originalItemIndex < startShiftingIndex && (shiftingStep > 0 || (startShiftingIndex - originalItemIndex) > shiftingStep)) { >+ return originalItemIndex; >+ } >+ // no change if after any affected item >+ if(originalItemIndex > endShiftingIndex && (shiftingStep < 0 || (originalItemIndex - endShiftingIndex) > shiftingStep)) { >+ return originalItemIndex; >+ } >+ // item before and things are moved up >+ if(originalItemIndex < startShiftingIndex) { >+ // item is moved down by the shifting >+ return originalItemIndex + (endShiftingIndex - startShiftingIndex); >+ } >+ // item is after and things are moved down >+ if(originalItemIndex > endShiftingIndex) { >+ // item is moved up by the shifting >+ return originalItemIndex - (endShiftingIndex - startShiftingIndex); >+ } >+ // item is part of the shifted block >+ return originalItemIndex + shiftingStep; >+ } >+ >+ >+} >diff --git a/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java b/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java >new file mode 100644 >index 000000000..4897a3465 >--- /dev/null >+++ b/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java >@@ -0,0 +1,384 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+import static org.apache.poi.POITestCase.skipTest; >+import static org.apache.poi.POITestCase.testPassesNow; >+import static org.junit.Assert.assertEquals; >+import static org.junit.Assert.assertNotNull; >+import static org.junit.Assert.assertNull; >+import static org.junit.Assert.assertTrue; >+import static org.junit.Assert.fail; >+import static org.junit.Assume.assumeTrue; >+ >+import java.io.IOException; >+import java.util.ArrayList; >+import java.util.List; >+ >+import org.apache.poi.common.usermodel.HyperlinkType; >+import org.apache.poi.ss.ITestDataProvider; >+import org.apache.poi.ss.usermodel.*; >+import org.apache.poi.ss.util.CellAddress; >+import org.apache.poi.ss.util.CellRangeAddress; >+import org.apache.poi.ss.util.CellUtil; >+import org.apache.poi.xssf.XSSFITestDataProvider; >+import org.apache.poi.xssf.XSSFTestDataSamples; >+import org.apache.poi.xssf.usermodel.*; >+import org.junit.Before; >+import org.junit.Ignore; >+import org.junit.Test; >+import org.slf4j.Logger; >+import org.slf4j.LoggerFactory; >+ >+public class XSSFColumnShifterTest { >+ >+ //private static Logger log = LoggerFactory.getLogger(XSSFColumnShifterTest.class + "_T"); >+ private XSSFSheet sheet1, sheet2; >+ private Workbook wb07; >+ >+ protected final ITestDataProvider _testDataProvider; >+ >+ public XSSFColumnShifterTest(){ >+ _testDataProvider = XSSFITestDataProvider.instance; >+ } >+ >+ @Before >+ public void init() { >+ wb07 = new XSSFWorkbook(); >+ sheet1 = (XSSFSheet) wb07.createSheet("sheet1"); >+ XSSFRow row = sheet1.createRow(0); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1); >+ XSSFCell c1 = row.createCell(2, CellType.NUMERIC); >+ c1.setCellValue(2); >+ >+ row = sheet1.createRow(1); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0.1); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.1); >+ row.createCell(2, CellType.NUMERIC).setCellValue(2.1); >+ row = sheet1.createRow(2); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0.2); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.2); >+ row.createCell(2, CellType.NUMERIC).setCellValue(2.2); >+ row = sheet1.createRow(3); >+ row.createCell(0, CellType.FORMULA).setCellFormula("A2*B3"); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.3); >+ row.createCell(2, CellType.FORMULA).setCellFormula("B1-B3"); >+ row = sheet1.createRow(4); >+ row.createCell(0, CellType.FORMULA).setCellFormula("SUM(C1:C4)"); >+ row.createCell(1, CellType.FORMULA).setCellFormula("SUM(A3:C3)"); >+ row.createCell(2, CellType.FORMULA).setCellFormula("$C1+C$2"); >+ row = sheet1.createRow(5); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.5); >+ /* >+ * sheet2 = (XSSFSheet)wb07.createSheet("sheet2"); row = >+ * sheet2.createRow(0); row.createCell(0, >+ * CellType.NUMERIC).setCellValue(10); row.createCell(1, >+ * CellType.NUMERIC).setCellValue(11); row.createCell(2, >+ * CellType.FORMULA).setCellFormula("SUM(Sheet1!B3:C3)"); row = >+ * sheet2.createRow(1); row.createCell(0, >+ * CellType.NUMERIC).setCellValue(21); row.createCell(1, >+ * CellType.NUMERIC).setCellValue(22); row.createCell(2, >+ * CellType.NUMERIC).setCellValue(23); row = sheet2.createRow(2); >+ * row.createCell(0, >+ * CellType.FORMULA).setCellFormula("Sheet1!A4+Sheet1!C2+A2"); >+ * row.createCell(1, >+ * CellType.FORMULA).setCellFormula("SUM(Sheet1!A3:$C3)"); row = >+ * sheet2.createRow(3); row.createCell(0, >+ * CellType.STRING).setCellValue("dummy"); >+ */ >+ // writeSheetToLog(sheet1); >+ } >+ >+ @Test >+ public void testInsertOneColumn() { >+ sheet1.shiftColumns(1, 2, 1); >+ writeSheetToLog(sheet1); >+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula(); >+ assertEquals("A2*C3", formulaA4); >+ String formulaC4 = sheet1.getRow(3).getCell(3).getCellFormula(); >+ assertEquals("C1-C3", formulaC4); >+ String formulaB5 = sheet1.getRow(4).getCell(2).getCellFormula(); >+ assertEquals("SUM(A3:D3)", formulaB5); >+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); // $C1+C$2 >+ assertEquals("$D1+D$2", formulaD5); >+ >+ String newb5Empty = sheet1.getRow(4).getCell(1).getStringCellValue(); >+ assertEquals(newb5Empty, ""); >+ } >+ >+ @Test >+ public void testInsertTwoColumns() { >+ sheet1.shiftColumns(1, 2, 2); >+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula(); >+ assertEquals("A2*D3", formulaA4); >+ String formulaD4 = sheet1.getRow(3).getCell(4).getCellFormula(); >+ assertEquals("D1-D3", formulaD4); >+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); >+ assertEquals("SUM(A3:E3)", formulaD5); >+ >+ String b5Empty = sheet1.getRow(4).getCell(1).getStringCellValue(); >+ assertEquals(b5Empty, ""); >+ Object c6Null = sheet1.getRow(5).getCell(2); // null cell A5 is shifted >+ // for 2 columns, so now >+ // c5 should be null >+ assertEquals(c6Null, null); >+ } >+ >+ public static void writeSheetToLog(Sheet sheet) { >+ int rowIndex = sheet.getFirstRowNum();/* >+ while (rowIndex <= sheet.getLastRowNum()) { >+ Row row = sheet.getRow(rowIndex); >+ if (row == null) >+ ;//log.trace("null row!"); >+ else >+ log.trace(String.format( >+ "%1$12s; %2$12s; %3$12s; %4$12s; %5$12s; %6$12s; %7$12s; %8$12s; %9$12s; %10$12s; %11$12s", >+ row.getCell(0) != null ? row.getCell(0).getCellComment() : "null", >+ row.getCell(1) != null ? row.getCell(1).getCellComment() : "null", >+ row.getCell(2) != null ? row.getCell(2).getCellComment() : "null", >+ row.getCell(3) != null ? row.getCell(3).getCellComment() : "null", >+ row.getCell(4) != null ? row.getCell(4).getCellComment() : "null", >+ row.getCell(5) != null ? row.getCell(5).getCellComment() : "null", >+ row.getCell(6) != null ? row.getCell(6).getCellComment() : "null", >+ row.getCell(7) != null ? row.getCell(7).getCellComment() : "null", >+ row.getCell(8) != null ? row.getCell(8).getCellComment() : "null", >+ row.getCell(9) != null ? row.getCell(9).getCellComment() : "null", >+ row.getCell(10) != null ? row.getCell(10).getCellComment() : "null")); >+ rowIndex++; >+ } >+ log.trace("");*/ >+ } >+ >+ @Test >+ public void testShiftHyperlinks() throws IOException { >+ Workbook wb = _testDataProvider.createWorkbook(); >+ Sheet sheet = wb.createSheet("test"); >+ Row row = sheet.createRow(0); >+ >+ // How to create hyperlinks >+ // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks >+ CreationHelper helper = wb.getCreationHelper(); >+ CellStyle hlinkStyle = wb.createCellStyle(); >+ Font hlinkFont = wb.createFont(); >+ hlinkFont.setUnderline(Font.U_SINGLE); >+ hlinkFont.setColor(IndexedColors.BLUE.getIndex()); >+ hlinkStyle.setFont(hlinkFont); >+ >+ // 3D relative document link >+ // CellAddress=A1, shifted to A4 >+ Cell cell = row.createCell(0); >+ cell.setCellStyle(hlinkStyle); >+ createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1"); >+ >+ // URL >+ cell = row.createCell(1); >+ // CellAddress=B1, shifted to B4 >+ cell.setCellStyle(hlinkStyle); >+ createHyperlink(helper, cell, HyperlinkType.URL, "http://poi.apache.org/"); >+ >+ // row0 will be shifted on top of row1, so this URL should be removed >+ // from the workbook >+ Row overwrittenRow = sheet.createRow(3); >+ cell = overwrittenRow.createCell(2); >+ // CellAddress=C4, will be overwritten (deleted) >+ cell.setCellStyle(hlinkStyle); >+ createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org"); >+ >+ Row unaffectedRow = sheet.createRow(20); >+ cell = unaffectedRow.createCell(3); >+ // CellAddress=D21, will be unaffected >+ cell.setCellStyle(hlinkStyle); >+ createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx"); >+ >+ cell = wb.createSheet("other").createRow(0).createCell(0); >+ // CellAddress=Other!A1, will be unaffected >+ cell.setCellStyle(hlinkStyle); >+ createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/"); >+ >+ int startRow = 0; >+ int endRow = 4; >+ int n = 3; >+ writeSheetToLog(sheet); >+ sheet.shiftColumns(startRow, endRow, n); >+ writeSheetToLog(sheet); >+ >+ Workbook read = _testDataProvider.writeOutAndReadBack(wb); >+ wb.close(); >+ >+ Sheet sh = read.getSheet("test"); >+ >+ Row shiftedRow = sh.getRow(0); >+ >+ // document link anchored on a shifted cell should be moved >+ // Note that hyperlinks do not track what they point to, so this >+ // hyperlink should still refer to test!E1 >+ verifyHyperlink(shiftedRow.getCell(3), HyperlinkType.DOCUMENT, "test!E1"); >+ >+ // URL, EMAIL, and FILE links anchored on a shifted cell should be moved >+ verifyHyperlink(shiftedRow.getCell(4), HyperlinkType.URL, "http://poi.apache.org/"); >+ >+ // Make sure hyperlinks were moved and not copied >+ assertNull("Document hyperlink should be moved, not copied", sh.getHyperlink(0, 0)); >+ assertNull("URL hyperlink should be moved, not copied", sh.getHyperlink(1, 0)); >+ >+ assertEquals(4, sh.getHyperlinkList().size()); >+ read.close(); >+ } >+ >+ private void createHyperlink(CreationHelper helper, Cell cell, HyperlinkType linkType, String ref) { >+ cell.setCellValue(ref); >+ Hyperlink link = helper.createHyperlink(linkType); >+ link.setAddress(ref); >+ cell.setHyperlink(link); >+ } >+ >+ private void verifyHyperlink(Cell cell, HyperlinkType linkType, String ref) { >+ assertTrue(cellHasHyperlink(cell)); >+ Hyperlink link = cell.getHyperlink(); >+ assertEquals(linkType, link.getTypeEnum()); >+ assertEquals(ref, link.getAddress()); >+ } >+ >+ private boolean cellHasHyperlink(Cell cell) { >+ return (cell != null) && (cell.getHyperlink() != null); >+ } >+ >+ @Test >+ public void shiftMergedColumnsToMergedColumnsRight() throws IOException { >+ Workbook wb = _testDataProvider.createWorkbook(); >+ Sheet sheet = wb.createSheet("test"); >+ >+ // populate sheet cells >+ populateSheetCells(sheet); >+ writeSheetToLog(sheet); >+ CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0); >+ CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1); >+ >+ sheet.addMergedRegion(B1_B3); >+ sheet.addMergedRegion(A1_A5); >+ >+ // A1:A5 should be moved to B1:B5 >+ // B1:B3 will be removed >+ sheet.shiftColumns(0, 0, 1); >+ writeSheetToLog(sheet); >+ >+ assertEquals(1, sheet.getNumMergedRegions()); >+ assertEquals(CellRangeAddress.valueOf("B1:B5"), sheet.getMergedRegion(0)); >+ >+ wb.close(); >+ } >+ @Test >+ public void shiftMergedColumnsToMergedColumnsLeft() throws IOException { >+ Workbook wb = _testDataProvider.createWorkbook(); >+ Sheet sheet = wb.createSheet("test"); >+ populateSheetCells(sheet); >+ >+ CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0); >+ CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1); >+ >+ sheet.addMergedRegion(A1_A5); >+ sheet.addMergedRegion(B1_B3); >+ >+ // A1:E1 should be removed >+ // B1:B3 will be A1:A3 >+ sheet.shiftColumns(1, 5, -1); >+ >+ assertEquals(1, sheet.getNumMergedRegions()); >+ assertEquals(CellRangeAddress.valueOf("A1:A3"), sheet.getMergedRegion(0)); >+ >+ wb.close(); >+ } >+ >+ private void populateSheetCells(Sheet sheet) { >+ // populate sheet cells >+ for (int i = 0; i < 2; i++) { >+ Row row = sheet.createRow(i); >+ for (int j = 0; j < 5; j++) { >+ Cell cell = row.createCell(j); >+ cell.setCellValue(i + "x" + j); >+ } >+ } >+ } >+ >+ @Test >+ public final void testShiftWithMergedRegions() throws IOException { >+ Workbook wb = _testDataProvider.createWorkbook(); >+ Sheet sheet = wb.createSheet(); >+ Row row = sheet.createRow(0); >+ row.createCell(0).setCellValue(1.1); >+ row = sheet.createRow(1); >+ row.createCell(0).setCellValue(2.2); >+ CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0); >+ assertEquals("A1:A3", region.formatAsString()); >+ >+ sheet.addMergedRegion(region); >+ >+ sheet.shiftColumns(0, 1, 2); >+ region = sheet.getMergedRegion(0); >+ assertEquals("C1:C3", region.formatAsString()); >+ wb.close(); >+ } >+ >+ @Test >+ public void testCommentsShifting() throws IOException { >+ Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56017.xlsx"); >+ >+ Sheet sheet = wb.getSheetAt(0); >+ Comment comment = sheet.getCellComment(new CellAddress(0, 0)); >+ assertNotNull(comment); >+ assertEquals("Amdocs", comment.getAuthor()); >+ assertEquals("Amdocs:\ntest\n", comment.getString().getString()); >+ >+ sheet.shiftColumns(0, 1, 1); >+ >+ // comment in column 0 is gone >+ comment = sheet.getCellComment(new CellAddress(0, 0)); >+ assertNull(comment); >+ >+ // comment is column in column 1 >+ comment = sheet.getCellComment(new CellAddress(0, 1)); >+ assertNotNull(comment); >+ assertEquals("Amdocs", comment.getAuthor()); >+ assertEquals("Amdocs:\ntest\n", comment.getString().getString()); >+ >+ Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); >+ wb.close(); >+ assertNotNull(wbBack); >+ >+ Sheet sheetBack = wbBack.getSheetAt(0); >+ >+ // comment in column 0 is gone >+ comment = sheetBack.getCellComment(new CellAddress(0, 0)); >+ assertNull(comment); >+ >+ // comment is now in column 1 >+ comment = sheetBack.getCellComment(new CellAddress(0, 1)); >+ assertNotNull(comment); >+ assertEquals("Amdocs", comment.getAuthor()); >+ assertEquals("Amdocs:\ntest\n", comment.getString().getString()); >+ wbBack.close(); >+ } >+ >+ // transposed version of TestXSSFSheetShiftRows.testBug54524() >+ @Test >+ public void testBug54524() throws IOException { >+ Workbook wb = _testDataProvider.createWorkbook(); >+ Sheet sheet = wb.createSheet(); >+ Row firstRow = sheet.createRow(0); >+ firstRow.createCell(0).setCellValue(""); >+ firstRow.createCell(1).setCellValue(1); >+ firstRow.createCell(2).setCellValue(2); >+ firstRow.createCell(3).setCellFormula("SUM(B1:C1)"); >+ firstRow.createCell(4).setCellValue("X"); >+ >+ sheet.shiftColumns(3, 5, -1); >+ >+ Cell cell = CellUtil.getCell(sheet.getRow(0), 1); >+ assertEquals(1.0, cell.getNumericCellValue(), 0); >+ cell = CellUtil.getCell(sheet.getRow(0), 2); >+ assertEquals("SUM(B1:B1)", cell.getCellFormula()); >+ cell = CellUtil.getCell(sheet.getRow(0), 3); >+ assertEquals("X", cell.getStringCellValue()); >+ wb.close(); >+ } >+ >+} >-- >2.13.1.windows.2 > 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]