From 54408b86ae2ebf8aae1e96799309f4b39de8a3dd Mon Sep 17 00:00:00 2001 From: zmau 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 true if a change was made to the formula tokens - */ - public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) { - boolean refsWereChanged = false; - for(int i=0; itrue if a change was made to the formula tokens + */ + public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) { + boolean refsWereChanged = false; + for(int i=0; i { * @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 shiftMergedRegions(int startColumnIndex, int endColumnIndex, int n) { + List shiftedRegions = new ArrayList(); + Set removedIndices = new HashSet(); + //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 { * * @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 { 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 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); + } + + /** + * 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 map = new HashMap(); + 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 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 null */ - 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 commentsToShift = new TreeMap(new Comparator() { + @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 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 rowIterator, CommentsTable sheetComments){ + SortedMap commentsToShift = new TreeMap(new Comparator() { + @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 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 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 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, - * null 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 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 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 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 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, + * null 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 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 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 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); + } + } + } + } + } + + 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 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