--- src/java/org/apache/poi/ss/usermodel/Sheet.java (revision 1704452) +++ src/java/org/apache/poi/ss/usermodel/Sheet.java (working copy) @@ -22,6 +22,7 @@ import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.util.Beta; /** * High level representation of a Excel worksheet. @@ -650,6 +651,39 @@ * @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); + + /** + * Copies rows from srcRows to this sheet starting at destStartRow + * Additionally copies merged regions that are completely defined in these rows + * + * @param srcRows the rows to copy. Formulas with relative cell and area + * references will be offset by the distance between the first row in srcRows + * and destStartRow (even if srcRows are from a different sheet). + * @param destStartRow the row in this sheet to paste the first row of srcRows + * the remainder of srcRows will be pasted below destStartRow per the cell copy policy + * @param cellCopyPolicy is the cell copy policy, which can be used to copy styles only, + * paste values only, merge the source and destination when the source is + * blank, etc. + */ + @Beta + void copyRows(List srcRows, int destStartRow, CellCopyPolicy cellCopyPolicy); + + /** + * Copies rows between srcStartRow and srcEndRow in this sheet to this sheet + * starting at destStartRow using cellCopyPolicy to choose what to copy + * + * Equivalent to sheet.copyRows(sheet.getRows(srcStartRow, srcEndRow), destStartRow, cellCopyPolicy) + * + * @param srcStartRow the first row in this sheet to copy + * @param srcEndRow the last row in this sheet to copy + * @param destStartRow destStartRow the row in this sheet to paste the first row of srcRows + * the remainder of srcRows will be pasted below destStartRow per the cell copy policy + * @param cellCopyPolicy is the cell copy policy, which can be used to copy styles only, + * paste values only, merge the source and destination when the source is + * blank, etc. + */ + @Beta + void copyRows(int srcStartRow, int srcEndRow, int destStartRow, CellCopyPolicy cellCopyPolicy); /** * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. --- src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java (revision 0) +++ src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java (revision 0) @@ -0,0 +1,186 @@ +package org.apache.poi.ss.usermodel; + +import org.apache.poi.util.Beta; + +@Beta +public class CellCopyPolicy implements Cloneable { + public static final boolean DEFAULT_COPY_CELL_VALUE_POLICY = true; + public static final boolean DEFAULT_COPY_CELL_STYLE_POLICY = true; + public static final boolean DEFAULT_COPY_CELL_FORMULA_POLICY = true; + public static final boolean DEFAULT_COPY_MERGED_REGIONS_POLICY = true; + public static final boolean DEFAULT_COPY_ROW_HEIGHT_POLICY = true; + public static final boolean DEFAULT_CONDENSE_ROWS_POLICY = false; + + private boolean copyCellValue = DEFAULT_COPY_CELL_VALUE_POLICY; + private boolean copyCellStyle = DEFAULT_COPY_CELL_STYLE_POLICY; + private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY; + private boolean copyMergedRegions = DEFAULT_COPY_MERGED_REGIONS_POLICY; + private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY; + private boolean condenseRows = DEFAULT_CONDENSE_ROWS_POLICY; + + /** + * Default CellCopyPolicy, uses default policy + * For custom CellCopyPolicy, use {@link #Builder} class + */ + public CellCopyPolicy() { } + + // should builder be replaced with CellCopyPolicy setters that return the object + // to allow setters to be chained together? + // policy.setCopyCellValue(true).setCopyCellStyle(true) + private CellCopyPolicy(Builder builder) { + copyCellValue = builder.copyCellValue; + copyCellStyle = builder.copyCellStyle; + copyCellFormula = builder.copyCellFormula; + copyMergedRegions = builder.copyMergedRegions; + copyRowHeight = builder.copyRowHeight; + condenseRows = builder.condenseRows; + } + + public static class Builder { + private boolean copyCellValue = DEFAULT_COPY_CELL_VALUE_POLICY; + private boolean copyCellStyle = DEFAULT_COPY_CELL_STYLE_POLICY; + private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY; + private boolean copyMergedRegions = DEFAULT_COPY_MERGED_REGIONS_POLICY; + private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY; + private boolean condenseRows = DEFAULT_CONDENSE_ROWS_POLICY; + + /** + * Builder class for CellCopyPolicy + */ + public Builder() { + } + + public Builder cellValue(boolean copyCellValue) { + this.copyCellValue = copyCellValue; + return this; + } + public Builder cellStyle(boolean copyCellStyle) { + this.copyCellStyle = copyCellStyle; + return this; + } + public Builder cellFormula(boolean copyCellFormula) { + this.copyCellFormula = copyCellFormula; + return this; + } + public Builder mergedRegions(boolean copyMergedRegions) { + this.copyMergedRegions = copyMergedRegions; + return this; + } + public Builder rowHeight(boolean copyRowHeight) { + this.copyRowHeight = copyRowHeight; + return this; + } + public Builder condenseRows(boolean condenseRows) { + this.condenseRows = condenseRows; + return this; + } + public CellCopyPolicy build() { + return new CellCopyPolicy(this); + } + } + + private Builder createBuilder() { + final Builder builder = new Builder() + .cellValue(copyCellValue) + .cellStyle(copyCellStyle) + .cellFormula(copyCellFormula) + .mergedRegions(copyMergedRegions) + .rowHeight(copyRowHeight) + .condenseRows(condenseRows); + return builder; + } + + @Override + public CellCopyPolicy clone() { + return createBuilder().build(); + } + + /** + * @return the copyCellValue + */ + public boolean isCopyCellValue() { + return copyCellValue; + } + + /** + * @param copyCellValue the copyCellValue to set + */ + public void setCopyCellValue(boolean copyCellValue) { + this.copyCellValue = copyCellValue; + } + + /** + * @return the copyCellStyle + */ + public boolean isCopyCellStyle() { + return copyCellStyle; + } + + /** + * @param copyCellStyle the copyCellStyle to set + */ + public void setCopyCellStyle(boolean copyCellStyle) { + this.copyCellStyle = copyCellStyle; + } + + /** + * @return the copyCellFormula + */ + public boolean isCopyCellFormula() { + return copyCellFormula; + } + + /** + * @param copyCellFormula the copyCellFormula to set + */ + public void setCopyCellFormula(boolean copyCellFormula) { + this.copyCellFormula = copyCellFormula; + } + + /** + * @return the copyMergedRegions + */ + public boolean isCopyMergedRegions() { + return copyMergedRegions; + } + + /** + * @param copyMergedRegions the copyMergedRegions to set + */ + public void setCopyMergedRegions(boolean copyMergedRegions) { + this.copyMergedRegions = copyMergedRegions; + } + + /** + * @return the copyRowHeight + */ + public boolean isCopyRowHeight() { + return copyRowHeight; + } + + /** + * @param copyRowHeight the copyRowHeight to set + */ + public void setCopyRowHeight(boolean copyRowHeight) { + this.copyRowHeight = copyRowHeight; + } + + /** + * If condenseRows is true, a discontinuities in srcRows will be removed when copied to destination + * For example: + * Sheet.copyRows({Row(1), Row(2), Row(5)}, 11, policy) results in rows 1, 2, and 5 + * being copied to rows 11, 12, and 13 if condenseRows is True, or rows 11, 11, 15 if condenseRows is false + * @return the condenseRows + */ + public boolean isCondenseRows() { + return condenseRows; + } + + /** + * @param condenseRows the condenseRows to set + */ + public void setCondenseRows(boolean condenseRows) { + this.condenseRows = condenseRows; + } + +} --- src/java/org/apache/poi/ss/usermodel/Row.java (revision 1704452) +++ src/java/org/apache/poi/ss/usermodel/Row.java (working copy) @@ -19,6 +19,9 @@ import java.util.Iterator; +import org.apache.poi.util.Beta; +import org.apache.poi.util.Internal; + /** * High level representation of a row of a spreadsheet. */ @@ -241,4 +244,16 @@ * you take it out of them. */ public int getOutlineLevel(); + + /** + * copy the cells from srcRow to this row + * If this row is not a blank row, this will merge the two rows, overwriting + * the cells in this row with the values/styles/formulas/etc of the cells in + * srcRow per cellCopyPolicy. + * @param srcRow the row to copy from (may be from a different sheet) + * @param policy the policy to determine what gets copied + */ + @Beta + @Internal + void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy); } --- src/java/org/apache/poi/ss/usermodel/Cell.java (revision 1704452) +++ src/java/org/apache/poi/ss/usermodel/Cell.java (working copy) @@ -22,6 +22,8 @@ import org.apache.poi.ss.formula.FormulaParseException; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.util.Beta; +import org.apache.poi.util.Internal; /** * High level representation of a cell in a row of a spreadsheet. @@ -324,6 +326,17 @@ * @see FormulaError for error codes */ byte getErrorCellValue(); + + /** + * Copy cell value, formula, and style, from srcCell per cell copy policy + * If srcCell is null, clears the cell value and cell style per cell copy policy + * + * @param srcCell + * @param policy + */ + @Beta + @Internal + void copyCellFrom(Cell srcCell, CellCopyPolicy policy); /** * Set the style for the cell. The style should be an CellStyle created/retreived from --- src/java/org/apache/poi/ss/formula/FormulaShifter.java (revision 1704452) +++ src/java/org/apache/poi/ss/formula/FormulaShifter.java (working copy) @@ -17,6 +17,7 @@ package org.apache.poi.ss.formula; +import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.ptg.Area2DPtgBase; import org.apache.poi.ss.formula.ptg.Area3DPtg; import org.apache.poi.ss.formula.ptg.Area3DPxg; @@ -39,9 +40,10 @@ */ public final class FormulaShifter { - static enum ShiftMode { - Row, - Sheet + private static enum ShiftMode { + RowMove, + RowCopy, + SheetMove, } /** @@ -61,6 +63,7 @@ private final int _srcSheetIndex; private final int _dstSheetIndex; + private final SpreadsheetVersion _version; private final ShiftMode _mode; @@ -69,7 +72,7 @@ * * For example, this will be called on {@link org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)} } */ - private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove) { + private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove, ShiftMode mode, SpreadsheetVersion version) { if (amountToMove == 0) { throw new IllegalArgumentException("amountToMove must not be zero"); } @@ -81,7 +84,8 @@ _firstMovedIndex = firstMovedIndex; _lastMovedIndex = lastMovedIndex; _amountToMove = amountToMove; - _mode = ShiftMode.Row; + _mode = mode; + _version = version; _srcSheetIndex = _dstSheetIndex = -1; } @@ -94,15 +98,35 @@ private FormulaShifter(int srcSheetIndex, int dstSheetIndex) { _externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1; _sheetName = null; + _version = null; _srcSheetIndex = srcSheetIndex; _dstSheetIndex = dstSheetIndex; - _mode = ShiftMode.Sheet; + _mode = ShiftMode.SheetMove; } - public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) { - return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove); - } + /** + * @deprecated As of 3.14 (September 2015), replaced by {@link #createForRowShift(int, String, int, int, int, SpreadsheetVersion)} + * + * @param externSheetIndex + * @param sheetName + * @param firstMovedRowIndex + * @param lastMovedRowIndex + * @param numberOfRowsToMove + * @return rowFormulaShifter + */ + @Deprecated + public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) { + return createForRowShift(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, SpreadsheetVersion.EXCEL97); + } + + 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); + } + + public static FormulaShifter createForRowCopy(int externSheetIndex, String sheetName, int firstCopiedRowIndex, int lastCopiedRowIndex, int rowOffset, SpreadsheetVersion version) { + return new FormulaShifter(externSheetIndex, sheetName, firstCopiedRowIndex, lastCopiedRowIndex, rowOffset, ShiftMode.RowCopy, version); + } public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) { return new FormulaShifter(srcSheetIndex, dstSheetIndex); @@ -137,62 +161,69 @@ return refsWereChanged; } - private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) { - switch(_mode){ - case Row: + private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) { + switch(_mode){ + case RowMove: return adjustPtgDueToRowMove(ptg, currentExternSheetIx); - case Sheet: + case RowCopy: + //Scenarios: + //* row copy on same sheet + //* row copy between different sheets in the same workbook + return adjustPtgDueToRowCopy(ptg); + case SheetMove: return adjustPtgDueToShiftMove(ptg); default: throw new IllegalStateException("Unsupported shift mode: " + _mode); } - } - /** - * @return true if this Ptg needed to be changed - */ - private Ptg adjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) { - if(ptg instanceof RefPtg) { - if (currentExternSheetIx != _externSheetIndex) { - // local refs on other sheets are unaffected - return null; - } - RefPtg rptg = (RefPtg)ptg; - return rowMoveRefPtg(rptg); - } - if(ptg instanceof Ref3DPtg) { - Ref3DPtg rptg = (Ref3DPtg)ptg; - if (_externSheetIndex != rptg.getExternSheetIndex()) { - // only move 3D refs that refer to the sheet with cells being moved - // (currentExternSheetIx is irrelevant) - return null; - } - return rowMoveRefPtg(rptg); - } - if(ptg instanceof Ref3DPxg) { - Ref3DPxg rpxg = (Ref3DPxg)ptg; - if (rpxg.getExternalWorkbookNumber() > 0 || - ! _sheetName.equals(rpxg.getSheetName())) { + } + + + /** + * @return deleted ref ptg, in-place modified ptg, or null + */ + private Ptg adjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) { + if(ptg instanceof RefPtg) { + if (currentExternSheetIx != _externSheetIndex) { + // local refs on other sheets are unaffected + return null; + } + RefPtg rptg = (RefPtg)ptg; + return rowMoveRefPtg(rptg); + } + if(ptg instanceof Ref3DPtg) { + Ref3DPtg rptg = (Ref3DPtg)ptg; + if (_externSheetIndex != rptg.getExternSheetIndex()) { // only move 3D refs that refer to the sheet with cells being moved - return null; - } + // (currentExternSheetIx is irrelevant) + return null; + } + return rowMoveRefPtg(rptg); + } + if(ptg instanceof Ref3DPxg) { + Ref3DPxg rpxg = (Ref3DPxg)ptg; + if (rpxg.getExternalWorkbookNumber() > 0 || + ! _sheetName.equals(rpxg.getSheetName())) { + // only move 3D refs that refer to the sheet with cells being moved + return null; + } return rowMoveRefPtg(rpxg); - } - if(ptg instanceof Area2DPtgBase) { - if (currentExternSheetIx != _externSheetIndex) { - // local refs on other sheets are unaffected - return ptg; - } - return rowMoveAreaPtg((Area2DPtgBase)ptg); - } - if(ptg instanceof Area3DPtg) { - Area3DPtg aptg = (Area3DPtg)ptg; - if (_externSheetIndex != aptg.getExternSheetIndex()) { - // only move 3D refs that refer to the sheet with cells being moved - // (currentExternSheetIx is irrelevant) - return null; - } - return rowMoveAreaPtg(aptg); - } + } + if(ptg instanceof Area2DPtgBase) { + if (currentExternSheetIx != _externSheetIndex) { + // local refs on other sheets are unaffected + return ptg; + } + return rowMoveAreaPtg((Area2DPtgBase)ptg); + } + if(ptg instanceof Area3DPtg) { + Area3DPtg aptg = (Area3DPtg)ptg; + if (_externSheetIndex != aptg.getExternSheetIndex()) { + // only move 3D refs that refer to the sheet with cells being moved + // (currentExternSheetIx is irrelevant) + return null; + } + return rowMoveAreaPtg(aptg); + } if(ptg instanceof Area3DPxg) { Area3DPxg apxg = (Area3DPxg)ptg; if (apxg.getExternalWorkbookNumber() > 0 || @@ -202,8 +233,51 @@ } return rowMoveAreaPtg(apxg); } - return null; - } + return null; + } + + + /** + * Call this on any ptg reference contained in a row of cells that was copied. + * If the ptg reference is relative, the references will be shifted by the distance + * that the rows were copied. + * In the future similar functions could be written due to column copying or + * individual cell copying. Just make sure to only call adjustPtgDueToRowCopy on + * formula cells that are copied (unless row shifting, where references outside + * of the shifted region need to be updated to reflect the shift, a copy is self-contained). + * + * @param ptg the ptg to shift + * @return deleted ref ptg, in-place modified ptg, or null + * If Ptg would be shifted off the first or last row of a sheet, return deleted ref + * If Ptg needs to be changed, modifies Ptg in-place + * If Ptg doesn't need to be changed, returns null + */ + private Ptg adjustPtgDueToRowCopy(Ptg ptg) { + if(ptg instanceof RefPtg) { + RefPtg rptg = (RefPtg)ptg; + return rowCopyRefPtg(rptg); + } + if(ptg instanceof Ref3DPtg) { + Ref3DPtg rptg = (Ref3DPtg)ptg; + return rowCopyRefPtg(rptg); + } + if(ptg instanceof Ref3DPxg) { + Ref3DPxg rpxg = (Ref3DPxg)ptg; + return rowCopyRefPtg(rpxg); + } + if(ptg instanceof Area2DPtgBase) { + return rowCopyAreaPtg((Area2DPtgBase)ptg); + } + if(ptg instanceof Area3DPtg) { + Area3DPtg aptg = (Area3DPtg)ptg; + return rowCopyAreaPtg(aptg); + } + if(ptg instanceof Area3DPxg) { + Area3DPxg apxg = (Area3DPxg)ptg; + return rowCopyAreaPtg(apxg); + } + return null; + } private Ptg adjustPtgDueToShiftMove(Ptg ptg) { Ptg updatedPtg = null; @@ -373,7 +447,63 @@ throw new IllegalStateException("Situation not covered: (" + _firstMovedIndex + ", " + _lastMovedIndex + ", " + _amountToMove + ", " + aFirstRow + ", " + aLastRow + ")"); } + + /** + * Modifies rptg in-place and return a reference to rptg if the cell reference + * would move due to a row copy operation + * Returns null or {@link #RefErrorPtg} if no change was made + * + * @param aptg + * @return + */ + private Ptg rowCopyRefPtg(RefPtgBase rptg) { + final int refRow = rptg.getRow(); + if (rptg.isRowRelative()) { + final int destRowIndex = _firstMovedIndex + _amountToMove; + if (destRowIndex < 0 || _version.getLastRowIndex() < destRowIndex) + return createDeletedRef(rptg); + rptg.setRow(refRow + _amountToMove); + return rptg; + } + return null; + } + + /** + * Modifies aptg in-place and return a reference to aptg if the first or last row of + * of the Area reference would move due to a row copy operation + * Returns null or {@link #AreaErrPtg} if no change was made + * + * @param aptg + * @return null, AreaErrPtg, or modified aptg + */ + private Ptg rowCopyAreaPtg(AreaPtgBase aptg) { + boolean changed = false; + + final int aFirstRow = aptg.getFirstRow(); + final int aLastRow = aptg.getLastRow(); + + if (aptg.isFirstRowRelative()) { + final int destFirstRowIndex = aFirstRow + _amountToMove; + if (destFirstRowIndex < 0 || _version.getLastRowIndex() < destFirstRowIndex) + return createDeletedRef(aptg); + aptg.setFirstRow(destFirstRowIndex); + changed = true; + } + if (aptg.isLastRowRelative()) { + final int destLastRowIndex = aLastRow + _amountToMove; + if (destLastRowIndex < 0 || _version.getLastRowIndex() < destLastRowIndex) + return createDeletedRef(aptg); + aptg.setLastRow(destLastRowIndex); + changed = true; + } + if (changed) { + aptg.sortTopLeftToBottomRight(); + } + + return changed ? aptg : null; + } + private static Ptg createDeletedRef(Ptg ptg) { if (ptg instanceof RefPtg) { return new RefErrorPtg(); --- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (revision 1704452) +++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (working copy) @@ -43,14 +43,18 @@ import org.apache.poi.ss.formula.ptg.ExpPtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; +import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.Hyperlink; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.NumberToTextConverter; +import org.apache.poi.util.Beta; +import org.apache.poi.util.Internal; import org.apache.poi.util.LocaleUtil; /** @@ -884,6 +888,19 @@ } /** + * Copy cell value, formula, and style, from srcCell per cell copy policy + * If srcCell is null, clears the cell value and cell style per cell copy policy + * + * @param srcCell + * @param policy + */ + @Beta + @Internal + public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) { + throw new UnsupportedOperationException(); + } + + /** * set the style for the cell. The style should be an HSSFCellStyle created/retreived from * the HSSFWorkbook. * --- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 1704452) +++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy) @@ -54,6 +54,7 @@ import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.UnionPtg; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellRange; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataValidation; @@ -64,6 +65,7 @@ import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.SSCellRange; import org.apache.poi.ss.util.SheetUtil; +import org.apache.poi.util.Beta; import org.apache.poi.util.Configurator; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -1390,6 +1392,7 @@ * @param endRow the row to end shifting * @param n the number of rows to shift */ + @Override public void shiftRows(int startRow, int endRow, int n) { shiftRows(startRow, endRow, n, false, false); } @@ -1411,6 +1414,7 @@ * @param copyRowHeight whether to copy the row height during the shift * @param resetOriginalRowHeight whether to set the original row's height to the default */ + @Override public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight, true); } @@ -1563,7 +1567,7 @@ String sheetName = _workbook.getSheetName(sheetIndex); short externSheetIndex = _book.checkExternSheet(sheetIndex); FormulaShifter shifter = FormulaShifter.createForRowShift( - externSheetIndex, sheetName, startRow, endRow, n); + externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97); _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex); int nSheets = _workbook.getNumberOfSheets(); @@ -1577,7 +1581,48 @@ } _workbook.getWorkbook().updateNamesAfterCellShift(shifter); } + + /** + * Copies rows from srcRows to this sheet, starting at destStartRow. + * srcRows may be rows from a different sheet. + * Copies features using cellCopyPolicy + * + *

Additionally copies merged regions that are completely defined in these + * rows (ie. merged 2 cells on a row to be shifted). srcRows, int destStartRow, CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + + } + + /** + * Copies rows between startRow and endRow n number of rows. + * If you use a negative number, it will copy rows up. + * Code ensures that rows don't wrap around. + * + * Calls copyRows(startRow, endRow, n, false, false); + * + *

+ * Additionally copies merged regions that are completely defined in these + * rows (ie. merged 2 cells on a row to be shifted). + * @param startRow the row to start shifting + * @param endRow the row to end shifting + * @param n the number of rows to shift + */ + @Beta + @Override + public void copyRows(int srcStartRow, int srcEndRow, int destStartRow, CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + + } + protected void insertChartRecords(List records) { int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid); _sheet.getRecords().addAll(window2Loc, records); --- src/java/org/apache/poi/hssf/usermodel/HSSFRow.java (revision 1704452) +++ src/java/org/apache/poi/hssf/usermodel/HSSFRow.java (working copy) @@ -25,8 +25,10 @@ import org.apache.poi.hssf.record.RowRecord; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.util.Beta; import org.apache.poi.util.Configurator; /** @@ -298,6 +300,22 @@ } /** + * Copy the cells from srcRow to this row + * If this row is not a blank row, this will merge the two rows, overwriting + * the cells in this row with the cells in srcRow + * If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy + * srcRow may be from a different sheet in the same workbook + * @param srcRow the rows to copy from + * @param cellCopyPolicy the policy to determine what gets copied + */ + @Beta + @Override + public void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + throw new UnsupportedOperationException(); + } + + /** * used internally to add a cell. */ private void addCell(HSSFCell cell) { --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (working copy) @@ -17,17 +17,24 @@ package org.apache.poi.xssf.usermodel; +import java.util.HashSet; import java.util.Iterator; +import java.util.Set; import java.util.TreeMap; +import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +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.XSSFRowShifter; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; @@ -513,7 +520,82 @@ } setRowNum(rownum); } + + /** + * Copy the cells from srcRow to this row + * If this row is not a blank row, this will merge the two rows, overwriting + * the cells in this row with the cells in srcRow + * If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy + * srcRow may be from a different sheet in the same workbook + * @param srcRow the rows to copy from + * @param policy the policy to determine what gets copied + */ + @Beta + @Override + public void copyRowFrom(Row srcRow, CellCopyPolicy policy) { + if (srcRow == null) { + // srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy + for (Cell destCell : this) { + final XSSFCell srcCell = null; + // FIXME: undo type casting + ((XSSFCell)destCell).copyCellFrom(srcCell, policy); + } + if (policy.isCopyMergedRegions()) { + // Remove MergedRegions in dest row + final int destRowNum = getRowNum(); + int index = 0; + final Set indices = new HashSet(); + for (CellRangeAddress destRegion : getSheet().getMergedRegions()) { + if (destRowNum == destRegion.getFirstRow() && destRowNum == destRegion.getLastRow()) { + indices.add(index); + } + index++; + } + getSheet().removeMergedRegions(indices); + } + + if (policy.isCopyRowHeight()) { + // clear row height + setHeight((short)-1); + } + + } + else { + for(Cell c : srcRow){ + final XSSFCell srcCell = (XSSFCell)c; + final XSSFCell destCell = createCell(srcCell.getColumnIndex(), srcCell.getCellType()); + 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); + + // Copy merged regions that are fully contained on the row + // FIXME: is this something that rowShifter could be doing? + if (policy.isCopyMergedRegions()) { + for (CellRangeAddress srcRegion : srcRow.getSheet().getMergedRegions()) { + if (srcRowNum == srcRegion.getFirstRow() && srcRowNum == srcRegion.getLastRow()) { + CellRangeAddress destRegion = srcRegion.copy(); + destRegion.setFirstRow(destRowNum); + destRegion.setLastRow(destRowNum); + getSheet().addMergedRegion(destRegion); + } + } + } + + if (policy.isCopyRowHeight()) { + setHeight(srcRow.getHeight()); + } + } + } + public int getOutlineLevel() { return _row.getOutlineLevel(); } --- src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (working copy) @@ -162,7 +162,7 @@ } } - private void updateRowFormulas(XSSFRow row, FormulaShifter shifter) { + public void updateRowFormulas(XSSFRow row, FormulaShifter shifter) { for (Cell c : row) { XSSFCell cell = (XSSFCell) c; --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (working copy) @@ -23,6 +23,7 @@ import java.util.Date; import org.apache.poi.ss.SpreadsheetVersion; +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.FormulaType; @@ -30,6 +31,7 @@ import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DataFormatter; @@ -40,6 +42,7 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.util.Beta; import org.apache.poi.util.Internal; import org.apache.poi.util.LocaleUtil; import org.apache.poi.xssf.model.SharedStringsTable; @@ -53,7 +56,7 @@ * High level representation of a cell in a row of a spreadsheet. *

* Cells can be numeric, formula-based or string-based (text). The cell type - * specifies this. String cells cannot conatin numbers and numeric cells cannot + * specifies this. String cells cannot contain numbers and numeric cells cannot * contain strings (at least according to our model). Client apps should do the * conversions themselves. Formula cells have the formula string, as well as * the formula result, which can be numeric or string. @@ -115,6 +118,70 @@ _sharedStringSource = row.getSheet().getWorkbook().getSharedStringSource(); _stylesSource = row.getSheet().getWorkbook().getStylesSource(); } + + /** + * Copy cell value, formula, and style, from srcCell per cell copy policy + * If srcCell is null, clears the cell value and cell style per cell copy policy + * @param srcCell + * @param policy + * @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook + */ + @Beta + @Internal + public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) { + // Copy cell value (cell type is updated implicitly) + if (policy.isCopyCellValue()) { + if (srcCell != null) { + int copyCellType = srcCell.getCellType(); + if (copyCellType == Cell.CELL_TYPE_FORMULA && !policy.isCopyCellFormula()) { + // Copy formula result as value + // FIXME: Cached value may be stale + copyCellType = srcCell.getCachedFormulaResultType(); + } + switch (copyCellType) { + case Cell.CELL_TYPE_BOOLEAN: + setCellValue(srcCell.getBooleanCellValue()); + break; + case Cell.CELL_TYPE_ERROR: + setCellErrorValue(srcCell.getErrorCellValue()); + break; + case Cell.CELL_TYPE_FORMULA: + setCellFormula(srcCell.getCellFormula()); + break; + case Cell.CELL_TYPE_NUMERIC: + // DataFormat is not copied unless policy.isCopyCellStyle is true + if (DateUtil.isCellDateFormatted(srcCell)) { + setCellValue(srcCell.getDateCellValue()); + } + else { + setCellValue(srcCell.getNumericCellValue()); + } + break; + case Cell.CELL_TYPE_STRING: + setCellValue(srcCell.getStringCellValue()); + break; + case Cell.CELL_TYPE_BLANK: + setBlank(); + break; + default: + throw new IllegalArgumentException("Invalid cell type " + srcCell.getCellType()); + } + } else { //srcCell is null + setBlank(); + } + } + + // Copy CellStyle + if (policy.isCopyCellStyle()) { + if (srcCell != null) { + setCellStyle(srcCell.getCellStyle()); + } + else { + // clear cell style + setCellStyle(null); + } + } + } /** * @return table of strings shared across this workbook @@ -452,7 +519,6 @@ cellFormula.setRef(range.formatAsString()); } - @SuppressWarnings("resource") private void setFormula(String formula, int formulaType) { XSSFWorkbook wb = _row.getSheet().getWorkbook(); if (formula == null) { @@ -525,6 +591,7 @@ * * @param style reference contained in the workbook. * If the value is null then the style information is removed causing the cell to used the default workbook style. + * @throws IllegalArgumentException if style belongs to a different styles source (most likely because style is from a different Workbook) */ @Override public void setCellStyle(CellStyle style) { --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (working copy) @@ -50,6 +50,7 @@ import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.formula.SheetNameFormatter; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellRange; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataValidation; @@ -1238,6 +1239,37 @@ public XSSFRow getRow(int rownum) { return _rows.get(rownum); } + + /** + * returns all rows between startRow and endRow, inclusive. + * Rows between startRow and endRow that haven't been created are not included + * in result unless createRowIfMissing is true + * + * @param startRow the first row number in this sheet to return + * @param endRow the last row number in this sheet to return + * @param createRowIfMissing + * @return + * @throws IllegalArgumentException if startRowNum and endRowNum are not in ascending order + */ + private List getRows(int startRowNum, int endRowNum, boolean createRowIfMissing) { + if (startRowNum > endRowNum) { + throw new IllegalArgumentException("getRows: startRowNum must be less than or equal to endRowNum"); + } + final List rows = new ArrayList(); + if (createRowIfMissing) { + for (int i = startRowNum; i <= endRowNum; i++) { + XSSFRow row = getRow(i); + if (row == null) { + row = createRow(i); + } + rows.add(row); + } + } + else { + rows.addAll(_rows.subMap(startRowNum, endRowNum+1).values()); + } + return rows; + } /** * Horizontal page break information used for print layout view, page layout view, drawing print breaks in normal @@ -1706,6 +1738,12 @@ _rows.remove(row.getRowNum()); worksheet.getSheetData().removeRow(idx); } + + public void removeRow(int rowNum) { + final Row row = getRow(rowNum); + if (row != null) + removeRow(row); + } /** * Removes the page break at the indicated row @@ -2557,8 +2595,142 @@ if(scale < 10 || scale > 400) throw new IllegalArgumentException("Valid scale values range from 10 to 400"); getSheetTypeSheetView().setZoomScale(scale); } + + + private void copyRows_remove(int startRow, int endRow, int n, Map rowHeight) { + // first remove all rows which will be overwritten + for (Iterator it = rowIterator() ; it.hasNext() ; ) { + final XSSFRow row = (XSSFRow)it.next(); + final int rowNum = row.getRowNum(); + if (XSSFSheet.shouldRemoveRow(startRow, endRow, n, rowNum)) { + + // check if we should remove this row as it will be overwritten by the data later + if (rowHeight != null) { + rowHeight.put(rowNum, row.getHeight()); + } + + // remove row from worksheet.getSheetData row array + final int idx = _rows.headMap(rowNum).size(); + worksheet.getSheetData().removeRow(idx); + // remove row from _rows + it.remove(); + } + } + } + + /** + * copyRows rows from srcRows to this sheet starting at destStartRow + * + * Additionally copies merged regions that are completely defined in these + * rows (ie. merged 2 cells on a row to be shifted). + * @param srcRows the rows to copy. Formulas will be offset by the difference + * in the row number of the first row in srcRows and destStartRow (even if srcRows + * are from a different sheet). + * @param destStartRow the row in this sheet to paste the first row of srcRows + * the remainder of srcRows will be pasted below destStartRow per the cell copy policy + * @param policy is the cell copy policy, which can be used to merge the source and destination + * when the source is blank, copy styles only, paste as value, etc + */ + @Beta + @Override + public void copyRows(List srcRows, int destStartRow, CellCopyPolicy policy) { + if (srcRows == null || srcRows.size() == 0) { + throw new IllegalArgumentException("No rows to copy"); + } + final Row srcStartRow = srcRows.get(0); + final Row srcEndRow = srcRows.get(srcRows.size() - 1); + + if (srcStartRow == null) { + throw new IllegalArgumentException("copyRows: First row cannot be null"); + } + + final int srcStartRowNum = srcStartRow.getRowNum(); + final int srcEndRowNum = srcEndRow.getRowNum(); + + // check row numbers to make sure they are continuous and increasing (monotonic) + // and srcRows does not contain null rows + for (int index=1; index < srcRows.size(); index++) { + final Row prevRow = srcRows.get(index-1); + final Row curRow = srcRows.get(index); + if (prevRow == null || curRow == null) { + throw new IllegalArgumentException("srcRows may not contain null rows. Found null row at index " + + index + " after Row " + prevRow.getRowNum() + "."); + //} else if (curRow.getRowNum() != prevRow.getRowNum() + 1) { + // throw new IllegalArgumentException("srcRows must contain continuously increasing row numbers. " + + // "Got srcRows[" + (index-1) + "]=Row " + prevRow.getRowNum() + ", srcRows[" + index + "]=Row " + curRow.getRowNum() + "."); + // FIXME: assumes row objects belong to non-null sheets and sheets belong to non-null workbooks. + } else if (srcStartRow.getSheet().getWorkbook() != curRow.getSheet().getWorkbook()) { + throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet in the same workbook." + + "Expected all rows from same workbook (" + srcStartRow.getSheet().getWorkbook() + "). " + + "Got srcRows[" + index + "] from different workbook (" + curRow.getSheet().getWorkbook() + ")."); + } else if (srcStartRow.getSheet() != curRow.getSheet()) { + throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet. " + + "Expected all rows from " + srcStartRow.getSheet().getSheetName() + ". " + + "Got srcRows[" + index + "] from " + curRow.getSheet().getSheetName()); + } + } + + // FIXME: is special behavior needed if srcRows and destRows belong to the same sheets and the regions overlap? + + final CellCopyPolicy options = policy.clone(); + // avoid O(N^2) performance scanning through all regions for each row + // merged regions will be copied after all the rows have been copied + options.setCopyMergedRegions(false); + + // FIXME: if srcRows contains gaps or null values, clear out those rows that will be overwritten + // how will this work with merging (copy just values, leave cell styles in place?) + + int r = destStartRow; + for (Row srcRow : srcRows) { + int destRowNum; + if (policy.isCondenseRows()) { + destRowNum = r++; + } else { + final int shift = (srcRow.getRowNum() - srcStartRowNum); + destRowNum = destStartRow + shift; + } + //removeRow(destRowNum); //this probably clears all external formula references to destRow, causing unwanted #REF! errors + final Row destRow = createRow(destRowNum); + destRow.copyRowFrom(srcRow, options); + } + + // ====================== + // Only do additional copy operations here that cannot be done with Row.copyFromRow(Row, options) + // reasons: operation needs to interact with multiple rows or sheets + + // Copy merged regions that are contained within the copy region + if (policy.isCopyMergedRegions()) { + // FIXME: is this something that rowShifter could be doing? + final int shift = destStartRow - srcStartRowNum; + for (CellRangeAddress srcRegion : srcStartRow.getSheet().getMergedRegions()) { + if (srcStartRowNum <= srcRegion.getFirstRow() && srcRegion.getLastRow() <= srcEndRowNum) { + // srcRegion is fully inside the copied rows + final CellRangeAddress destRegion = srcRegion.copy(); + destRegion.setFirstRow(destRegion.getFirstRow() + shift); + destRegion.setLastRow(destRegion.getLastRow() + shift); + addMergedRegion(destRegion); + } + } + } + } + + /** + * Copies rows between srcStartRow and srcEndRow to the same sheet, starting at destStartRow + * Convenience function for {@link #copyRows(List, int, CellCopyPolicy)} + * + * Equivalent to copyRows(getRows(srcStartRow, srcEndRow, false), destStartRow, policy) + * + */ + @Beta + @Override + public void copyRows(int srcStartRow, int srcEndRow, int destStartRow, CellCopyPolicy cellCopyPolicy) { + final List srcRows = getRows(srcStartRow, srcEndRow, false); //FIXME: should be false, no need to create rows where src is only to copy them to dest + copyRows(srcRows, destStartRow, cellCopyPolicy); + } + + /** * Shifts rows between startRow and endRow n number of rows. * If you use a negative number, it will shift rows up. * Code ensures that rows don't wrap around. @@ -2603,7 +2775,7 @@ int rownum = row.getRowNum(); // check if we should remove this row as it will be overwritten by the data later - if (shouldRemoveRow(startRow, endRow, n, rownum)) { + if (XSSFSheet.shouldRemoveRow(startRow, endRow, n, rownum)) { // remove row from worksheet.getSheetData row array int idx = _rows.headMap(row.getRowNum()).size(); worksheet.getSheetData().removeRow(idx); @@ -2999,7 +3171,7 @@ return sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr(); } - private boolean shouldRemoveRow(int startRow, int endRow, int n, int rownum) { + private static final boolean shouldRemoveRow(int startRow, int endRow, int n, int rownum) { // is this row in the target-window where the moved rows will land? if (rownum >= (startRow + n) && rownum <= (endRow + n)) { // only remove it if the current row is not part of the data that is copied --- src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (working copy) @@ -28,6 +28,7 @@ import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.AutoFilter; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellRange; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; @@ -883,7 +884,19 @@ { throw new RuntimeException("NotImplemented"); } + + public void copyRows(List srcRows, int destStartRow, + CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + + } + public void copyRows(int srcStartRow, int srcEndRow, int destStartRow, + CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + + } + /** * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. * @param colSplit Horizonatal position of split. --- src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java (working copy) @@ -26,6 +26,7 @@ import org.apache.poi.ss.formula.FormulaParseException; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DateUtil; @@ -35,6 +36,8 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.util.Beta; +import org.apache.poi.util.Internal; import org.apache.poi.util.LocaleUtil; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -502,6 +505,19 @@ throw typeMismatch(CELL_TYPE_ERROR, cellType, false); } } + + /** + * Copy cell value, formula, and style, from srcCell per cell copy policy + * If srcCell is null, clears the cell value and cell style per cell copy policy + * @param srcCell + * @param policy + * @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook + */ + @Beta + @Internal + public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) { + throw new UnsupportedOperationException(); + } /** * Set the style for the cell. The style should be an CellStyle created/retreived from --- src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java (revision 1704452) +++ src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java (working copy) @@ -22,9 +22,11 @@ import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.util.Beta; /** * Streaming version of XSSFRow implementing the "BigGridDemo" strategy. @@ -488,5 +490,12 @@ throw new UnsupportedOperationException(); } } + + @Beta + @Override + public void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy) { + // TODO Auto-generated method stub + throw new UnsupportedOperationException(); + } } --- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java (revision 1704452) +++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java (working copy) @@ -1285,10 +1285,10 @@ assertNotNull(wb); assertNotNull(sheet); - XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5")); + XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5")); assertNotNull(pivotTable); assertTrue(wb.getPivotTables().size() > 0); - XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("L5"), sheet); + XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("L5"), sheet); assertNotNull(pivotTable2); assertTrue(wb.getPivotTables().size() > 1); } @@ -1300,12 +1300,12 @@ assertNotNull(wb); assertNotNull(sheet); - XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5")); + XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5")); assertNotNull(pivotTable); assertTrue(wb.getPivotTables().size() > 0); assertNotNull(wb); XSSFSheet sheet2 = wb.createSheet(); - XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"), sheet); + XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet); assertNotNull(pivotTable2); assertTrue(wb.getPivotTables().size() > 1); } @@ -1317,7 +1317,7 @@ assertNotNull(wb); assertNotNull(sheet); - XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5")); + XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5")); assertNotNull(pivotTable); assertTrue(wb.getPivotTables().size() > 0); } @@ -1329,7 +1329,7 @@ XSSFSheet sheet2 = wb.createSheet(); XSSFPivotTable pivotTable = sheet2.createPivotTable - (new AreaReference("A1:B2"), new CellReference("H5"), sheet1); + (new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet1); assertEquals(0, pivotTable.getRowLabelColumns().size()); assertEquals(1, wb.getPivotTables().size()); @@ -1344,7 +1344,7 @@ XSSFSheet sheet2 = wb.createSheet(); XSSFPivotTable pivotTable = sheet2.createPivotTable - (new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5")); + (new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5")); assertEquals(0, pivotTable.getRowLabelColumns().size()); } @@ -1355,7 +1355,7 @@ XSSFSheet sheet2 = wb.createSheet(); try { - sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5"), sheet2); + sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet2); } catch(IllegalArgumentException e) { return; } @@ -1382,4 +1382,14 @@ XSSFSheet sheet = wb.createSheet(); assertNotNull(sheet.createComment()); } + + @Test + public void testCopyOneRow() throws IOException { + baseTestCopyOneRow("XSSFSheet.copyRows.xlsx"); + } + + @Test + public void testCopyMultipleRows() throws IOException { + baseTestCopyMultipleRows("XSSFSheet.copyRows.xlsx"); + } } --- src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheet.java (revision 1704452) +++ src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheet.java (working copy) @@ -76,6 +76,16 @@ thrown.expectMessage("NotImplemented"); super.shiftMerged(); } + + @Test + public void testCopyOneRow() { + //TODO: implement this test + } + + @Test + public void testCopyMultipleRows() { + //TODO: implement this test + } /** * Bug 35084: cloning cells with formula --- src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java (revision 1704452) +++ src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java (working copy) @@ -22,6 +22,7 @@ import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.BaseTestRow; import org.apache.poi.xssf.SXSSFITestDataProvider; +import org.junit.Ignore; /** * Tests for XSSFRow @@ -45,5 +46,22 @@ public void testCellBounds() { baseTestCellBounds(SpreadsheetVersion.EXCEL2007.getLastColumnIndex()); } - + + @Ignore + @Override + public void testCopyRowFrom() { + //ignore this test + } + + @Ignore + @Override + public void testCopyRowFromExternalSheet() { + //ignore this test + } + + @Ignore + @Override + public void testCopyRowOverwritesExistingRow() { + //ignore this test + } } --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (revision 1704452) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (working copy) @@ -19,8 +19,10 @@ import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; +import static org.junit.Assert.assertSame; import static org.junit.Assert.fail; import java.io.IOException; @@ -36,6 +38,7 @@ import org.apache.poi.hssf.record.StringRecord; import org.apache.poi.ss.usermodel.BaseTestCell; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (revision 1704452) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (working copy) @@ -1139,4 +1139,14 @@ NameRecord record = wb.getWorkbook().getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1); assertNotNull(record); } + + @Test + public void testCopyOneRow() { + //TODO: implement this test + } + + @Test + public void testCopyMultipleRows() { + //TODO: implement this test + } } --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFRow.java (revision 1704452) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFRow.java (working copy) @@ -25,6 +25,7 @@ import org.apache.poi.hssf.record.BlankRecord; import org.apache.poi.hssf.record.RowRecord; import org.apache.poi.ss.usermodel.BaseTestRow; +import org.junit.Ignore; import org.apache.poi.ss.SpreadsheetVersion; /** @@ -139,4 +140,22 @@ workbook.close(); } + + @Ignore + @Override + public void testCopyRowFrom() { + //ignore this test + } + + @Ignore + @Override + public void testCopyRowFromExternalSheet() { + //ignore this test + } + + @Ignore + @Override + public void testCopyRowOverwritesExistingRow() { + //ignore this test + } } --- src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java (revision 1704452) +++ src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java (working copy) @@ -18,12 +18,19 @@ package org.apache.poi.ss.usermodel; import java.io.IOException; +import java.util.Calendar; +import java.util.Date; +import java.util.GregorianCalendar; import java.util.Iterator; import junit.framework.TestCase; import org.apache.poi.ss.ITestDataProvider; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.ss.util.CellUtil; + /** * A base class for testing implementations of * {@link org.apache.poi.ss.usermodel.Row} @@ -432,4 +439,160 @@ assertEquals(style, row2.getRowStyle()); assertEquals(4, style.getDataFormat()); } + + public void testCopyRowFrom() { + final Workbook workbook = _testDataProvider.createWorkbook(); + final Sheet sheet = workbook.createSheet("test"); + final Row srcRow = sheet.createRow(0); + srcRow.createCell(0).setCellValue("Hello"); + final Row destRow = sheet.createRow(1); + + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + assertNotNull(destRow.getCell(0)); + assertEquals("Hello", destRow.getCell(0).getStringCellValue()); + } + + public void testCopyRowFromExternalSheet() { + final Workbook workbook = _testDataProvider.createWorkbook(); + final Sheet srcSheet = workbook.createSheet("src"); + final Sheet destSheet = workbook.createSheet("dest"); + workbook.createSheet("other"); + + final Row srcRow = srcSheet.createRow(0); + int col = 0; + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("B5"); + srcRow.createCell(col++).setCellFormula("src!B5"); + srcRow.createCell(col++).setCellFormula("dest!B5"); + srcRow.createCell(col++).setCellFormula("other!B5"); + + //Test 2D and 3D Ref Ptgs with absolute row + srcRow.createCell(col++).setCellFormula("B$5"); + srcRow.createCell(col++).setCellFormula("src!B$5"); + srcRow.createCell(col++).setCellFormula("dest!B$5"); + srcRow.createCell(col++).setCellFormula("other!B$5"); + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("SUM(B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(src!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(dest!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(other!B5:D$5)"); + + // FIXME: temporary work-around until bug 58350 is fixed + if (srcSheet.getNumMergedRegions() == 0) { + // Goal: CTWorksheet.addNewMergeCells() + srcSheet.addMergedRegion(new CellRangeAddress(10, 11, 0, 0)); + } + + ////////////////// + + final Row destRow = destSheet.createRow(1); + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + + ////////////////// + + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + col = 0; + Cell cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("RefPtg", "B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "src!B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "dest!B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "other!B6", cell.getCellFormula()); + + ///////////////////////////////////////////// + + //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change) + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("RefPtg", "B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "src!B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "dest!B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "other!B$5", cell.getCellFormula()); + + ////////////////////////////////////////// + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + // Note: absolute row changes from last cell to first cell in order + // to maintain topLeft:bottomRight order + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Area2DPtg", "SUM(B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Area3DPtg", "SUM(src!B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(destRow.getCell(6)); + assertEquals("Area3DPtg", "SUM(dest!B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(destRow.getCell(7)); + assertEquals("Area3DPtg", "SUM(other!B$5:D6)", cell.getCellFormula()); + } + + public void testCopyRowOverwritesExistingRow() { + final Workbook workbook = _testDataProvider.createWorkbook(); + final Sheet sheet1 = workbook.createSheet("Sheet1"); + final Sheet sheet2 = workbook.createSheet("Sheet2"); + + final Row srcRow = sheet1.createRow(0); + final Row destRow = sheet1.createRow(1); + final Row observerRow = sheet1.createRow(2); + final Row externObserverRow = sheet2.createRow(0); + + srcRow.createCell(0).setCellValue("hello"); + srcRow.createCell(1).setCellValue("world"); + destRow.createCell(0).setCellValue(5.0); //A2 -> 5.0 + destRow.createCell(1).setCellFormula("A1"); // B2 -> A1 -> "hello" + observerRow.createCell(0).setCellFormula("A2"); // A3 -> A2 -> 5.0 + observerRow.createCell(1).setCellFormula("B2"); // B3 -> B2 -> A1 -> "hello" + externObserverRow.createCell(0).setCellFormula("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0 + + // FIXME: temporary work-around until bug 58350 is fixed + if (sheet1.getNumMergedRegions() == 0) { + // Goal: CTWorksheet.addNewMergeCells() + sheet1.addMergedRegion(new CellRangeAddress(10, 11, 0, 0)); + } + + // overwrite existing destRow with row-copy of srcRow + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + + // copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer + // to the new row (and allow the old row to be garbage collected) + // this is mostly so existing references to rows that are overwritten are updated + // rather than allowing users to continue updating rows that are no longer part of the sheet + assertSame("existing references to srcRow are still valid", srcRow, sheet1.getRow(0)); + assertSame("existing references to destRow are still valid", destRow, sheet1.getRow(1)); + assertSame("existing references to observerRow are still valid", observerRow, sheet1.getRow(2)); + assertSame("existing references to externObserverRow are still valid", externObserverRow, sheet2.getRow(0)); + + // Make sure copyRowFrom actually copied row (this is tested elsewhere) + assertEquals(Cell.CELL_TYPE_STRING, destRow.getCell(0).getCellType()); + assertEquals("hello", destRow.getCell(0).getStringCellValue()); + + // We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region + assertEquals("references to overwritten cells are unmodified", "A2", observerRow.getCell(0).getCellFormula()); + assertEquals("references to overwritten cells are unmodified", "B2", observerRow.getCell(1).getCellFormula()); + assertEquals("references to overwritten cells are unmodified", "Sheet1!A2", externObserverRow.getCell(0).getCellFormula()); + } } --- src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (revision 1704452) +++ src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (working copy) @@ -22,12 +22,17 @@ import static org.junit.Assert.*; import java.io.IOException; +import java.util.Calendar; +import java.util.Date; +import java.util.GregorianCalendar; import java.util.Iterator; import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.ITestDataProvider; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.ss.util.CellUtil; import org.junit.Rule; import org.junit.Test; import org.junit.rules.ExpectedException; @@ -348,7 +353,395 @@ region = sheet.getMergedRegion(0); assertEquals("Merged region not moved over to row 2", 2, region.getFirstRow()); } + + protected void baseTestCopyOneRow(String copyRowsTestWorkbook) throws IOException { + final double FLOAT_PRECISION = 1e-9; + final Workbook workbook = _testDataProvider.openSampleWorkbook(copyRowsTestWorkbook); + final Sheet sheet = workbook.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(1, 1, 6, defaultCopyPolicy); + final Row srcRow = sheet.getRow(1); + final Row destRow = sheet.getRow(6); + int col = 0; + Cell cell; + + cell = CellUtil.getCell(destRow, col++); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Style] B7 cell value", "Red", cell.getStringCellValue()); + assertEquals("[Style] B7 cell style", CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle()); + + // Blank + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Blank] C7 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + // Error + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Error] D7 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + final FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D7 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + // Date + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Date] E7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + final Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals("[Date] E7 cell value", date, cell.getDateCellValue()); + + // Boolean + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Boolean] F7 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F7 cell value", true, cell.getBooleanCellValue()); + + // String + cell = CellUtil.getCell(destRow, col++); + assertEquals("[String] G7 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G7 cell value", "Hello", cell.getStringCellValue()); + + // Int + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Int] H7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H7 cell value", 15, (int) cell.getNumericCellValue()); + + // Float + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Float] I7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I7 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula + cell = CellUtil.getCell(destRow, col++); + assertEquals("J7", new CellReference(cell).formatAsString()); + assertEquals("[Cell Formula] J7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J7 cell formula", "5+2", cell.getCellFormula()); + System.out.println("Cell formula evaluation currently unsupported"); + //assertEquals("[Cell Formula] J7 cell value", 7.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula with Reference + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow, col++); + assertEquals("K7", new CellReference(cell).formatAsString()); + assertEquals("[Cell Formula with Reference] K7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K7 cell formula", + "J7+H$2", cell.getCellFormula()); + //assertEquals("[Cell Formula with Reference] J7 cell value", + // 22.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell formula", + "G7&\" \"&G8", cell.getCellFormula()); + //assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell value", + // "World ", cell.getStringCellValue()); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Cell Formula with Area Reference] M7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M7 cell formula", + "SUM(H7:I8)", cell.getCellFormula()); + //assertEquals("[Cell Formula with Area Reference] M7 cell value", + // "75", cell.getStringCellValue()); + + // Array Formula + cell = CellUtil.getCell(destRow, col++); + System.out.println("Array formulas currently unsupported"); + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + /* + assertEquals("[Array Formula] N7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N7 cell formula", "{SUM(H7:J7*{1,2,3})}", cell.getCellFormula()); + */ + // Formula should be evaluated + //assertEquals("[Array Formula] N7 cell value", 61.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Data Format + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Data Format] O7 cell type;", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Data Format] O7 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION); + //FIXME: currently fails + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals("[Data Format] O7 data format", moneyFormat, cell.getCellStyle().getDataFormatString()); + + // Merged + cell = CellUtil.getCell(destRow, col); + assertEquals("[Merged] P7:Q7 cell value", + "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P7:Q7 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7"))); + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow, col); + // Note: this behavior deviates from Microsoft Excel, + // which will not overwrite a cell in destination row if merged region extends beyond the copied row. + // The Excel way would require: + //assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue()); + //assertFalse("[Merged across multiple rows] R7:S8 merged region", + // sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); + // As currently implemented, cell value is copied but merged region is not copied + assertEquals("[Merged across multiple rows] R7:S8 cell value", + "Merged cells across multiple rows", cell.getStringCellValue()); + assertFalse("[Merged across multiple rows] R7:S7 merged region (one row)", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7"))); //shouldn't do 1-row merge + assertFalse("[Merged across multiple rows] R7:S8 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); //shouldn't do 2-row merge + + // Make sure other rows are blank (off-by-one errors) + assertNull(sheet.getRow(5)); + assertNull(sheet.getRow(7)); + } + + public void baseTestCopyMultipleRows(String copyRowsTestWorkbook) throws IOException { + final double FLOAT_PRECISION = 1e-9; + final Workbook workbook = _testDataProvider.openSampleWorkbook(copyRowsTestWorkbook); + final Sheet sheet = workbook.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(0, 3, 8, defaultCopyPolicy); + + final Row srcHeaderRow = sheet.getRow(0); + final Row srcRow1 = sheet.getRow(1); + final Row srcRow2 = sheet.getRow(2); + final Row srcRow3 = sheet.getRow(3); + final Row destHeaderRow = sheet.getRow(8); + final Row destRow1 = sheet.getRow(9); + final Row destRow2 = sheet.getRow(10); + final Row destRow3 = sheet.getRow(11); + int col = 0; + Cell cell; + + // Header row should be copied + assertNotNull(destHeaderRow); + + // Data rows + cell = CellUtil.getCell(destRow1, col); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Style] B10 cell value", "Red", cell.getStringCellValue()); + assertEquals("[Style] B10 cell style", CellUtil.getCell(srcRow1, 1).getCellStyle(), cell.getCellStyle()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Style] B11 cell value", "Blue", cell.getStringCellValue()); + assertEquals("[Style] B11 cell style", CellUtil.getCell(srcRow2, 1).getCellStyle(), cell.getCellStyle()); + + // Blank + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Blank] C10 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Blank] C11 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + // Error + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Error] D10 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D10 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Error] D11 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D11 cell value", FormulaError.NAME, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + // Date + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Date] E10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals("[Date] E10 cell value", date, cell.getDateCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Date] E11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + date = new GregorianCalendar(2000, Calendar.JANUARY, 2).getTime(); + assertEquals("[Date] E11 cell value", date, cell.getDateCellValue()); + + // Boolean + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Boolean] F10 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F10 cell value", true, cell.getBooleanCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Boolean] F11 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F11 cell value", false, cell.getBooleanCellValue()); + + // String + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[String] G10 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G10 cell value", "Hello", cell.getStringCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[String] G11 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G11 cell value", "World", cell.getStringCellValue()); + + // Int + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Int] H10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H10 cell value", 15, (int) cell.getNumericCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Int] H11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H11 cell value", 42, (int) cell.getNumericCellValue()); + + // Float + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Float] I10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I10 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Float] I11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I11 cell value", 5.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula] J10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J10 cell formula", "5+2", cell.getCellFormula()); + // FIXME: formula evaluation + System.out.println("Cell formula evaluation currently unsupported"); + //assertEquals(7.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula] J11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J11 cell formula", "6+18", cell.getCellFormula()); + // FIXME: formula evaluation + //assertEquals("[Cell Formula] J11 cell formula result", 24.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula with Reference + col++; + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Reference] K10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K10 cell formula", + "J10+H$2", cell.getCellFormula()); + // FIXME: formula evaluation + //assertEquals("[Cell Formula with Reference] K10 cell formula result", + // 22.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Reference] K11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K11 cell formula", "J11+H$2", cell.getCellFormula()); + // FIXME: formula evaluation + //assertEquals("[Cell Formula with Reference] K11 cell formula result", + // 39.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula with Reference spanning multiple rows + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell formula", + "G10&\" \"&G11", cell.getCellFormula()); + // FIXME: Formula should be evaluated + //assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula result", + // "Hello World", cell.getStringCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula", + "G11&\" \"&G12", cell.getCellFormula()); + // FIXME: Formula should be evaluated + //assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula result", + // "World ", cell.getStringCellValue()); + + // Cell Formula with Area Reference + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Area Reference] M10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M10 cell formula", + "SUM(H10:I11)", cell.getCellFormula()); + // FIXME: Formula should be evaluated + //assertEquals("[Cell Formula with Area Reference] M10 cell formula result", + // "Hello World", cell.getStringCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Area Reference] M11 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M11 cell formula", + "SUM($H$3:I10)", cell.getCellFormula()); //Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order + // FIXME: Formula should be evaluated + //assertEquals("[Cell Formula with Area Reference] M11 cell formula result", + // "World ", cell.getStringCellValue()); + + // Array Formula + col++; + cell = CellUtil.getCell(destRow1, col); + System.out.println("Array formulas currently unsupported"); + /* + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + assertEquals("[Array Formula] N10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N10 cell formula", "{SUM(H10:J10*{1,2,3})}", cell.getCellFormula()); + // FIXME: Formula should be evaluated + assertEquals("[Array Formula] N10 cell formula result", 61.0, cell.getNumericCellValue(), FLOAT_PRECISION); + + cell = CellUtil.getCell(destRow2, col); + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + assertEquals("[Array Formula] N11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N11 cell formula", "{SUM(H11:J11*{1,2,3})}", cell.getCellFormula()); + // FIXME: Formula should be evaluated + assertEquals("[Array Formula] N11 cell formula result", 125.0, cell.getNumericCellValue(), FLOAT_PRECISION); + */ + + // Data Format + col++; + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Data Format] O10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Data Format] O10 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION); + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals("[Data Format] O10 cell data format", moneyFormat, cell.getCellStyle().getDataFormatString()); + + // Merged + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Merged] P10:Q10 cell value", + "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P10:Q10 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P10:Q10"))); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Merged] P11:Q11 cell value", "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P11:Q11 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P11:Q11"))); + + // Should Q10/Q11 be checked? + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Merged across multiple rows] R10:S11 cell value", + "Merged cells across multiple rows", cell.getStringCellValue()); + assertTrue("[Merged across multiple rows] R10:S11 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R10:S11"))); + + // Row 3 (zero-based) was empty, so Row 11 (zero-based) should be empty too. + if (srcRow3 == null) { + assertNull("Row 3 was empty, so Row 11 should be empty", destRow3); + } + + // Make sure other rows are blank (off-by-one errors) + assertNull("Off-by-one lower edge case", sheet.getRow(7)); //one row above destHeaderRow + assertNull("Off-by-one upper edge case", sheet.getRow(12)); //one row below destRow3 + } + /** * Tests the display of gridlines, formulas, and rowcolheadings. * @author Shawn Laubach (slaubach at apache dot org) @@ -996,4 +1389,6 @@ wb.close(); } + + } --- src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java (revision 1704452) +++ src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java (working copy) @@ -18,10 +18,12 @@ package org.apache.poi.ss.usermodel; import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; import static org.junit.Assert.assertTrue; +import static org.junit.Assert.assertSame; import static org.junit.Assert.fail; import java.io.IOException; @@ -810,4 +812,68 @@ } wb.close(); } + + + private Cell srcCell, destCell; //used for testCopyCellFrom_CellCopyPolicy + + @Test + public final void testCopyCellFrom_CellCopyPolicy_default() { + if (!_testDataProvider.getClass().getName().equals("org.apache.poi.xssf.XSSFITestDataProvider")) return; //TODO: enable this for HSSFCell and SXSSFCell tests + setUp_testCopyCellFrom_CellCopyPolicy(); + + // default copy policy + final CellCopyPolicy policy = new CellCopyPolicy(); + destCell.copyCellFrom(srcCell, policy); + + assertEquals(Cell.CELL_TYPE_FORMULA, destCell.getCellType()); + assertEquals("2+3", destCell.getCellFormula()); + assertEquals(srcCell.getCellStyle(), destCell.getCellStyle()); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_value() { + if (!_testDataProvider.getClass().getName().equals("org.apache.poi.xssf.XSSFITestDataProvider")) return; //TODO: enable this for HSSFCell and SXSSFCell tests + setUp_testCopyCellFrom_CellCopyPolicy(); + + // Paste values only + final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(false).build(); + destCell.copyCellFrom(srcCell, policy); + assertEquals(Cell.CELL_TYPE_NUMERIC, destCell.getCellType()); + System.out.println("ERROR: fix formula evaluation"); + //FIXME: the following assertion currently fails, since getNumericCellValue() returns 0 for unevaluated expressions + //assertEquals(5, (int) destCell.getNumericCellValue()); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_style() { + if (!_testDataProvider.getClass().getName().equals("org.apache.poi.xssf.XSSFITestDataProvider")) return; //TODO: enable this for HSSFCell and SXSSFCell tests + setUp_testCopyCellFrom_CellCopyPolicy(); + srcCell.setCellValue((String) null); + + // Paste styles only + final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellValue(false).build(); + destCell.copyCellFrom(srcCell, policy); + assertEquals(srcCell.getCellStyle(), destCell.getCellStyle()); + + // Old cell value should not have been overwritten + assertNotEquals(Cell.CELL_TYPE_BLANK, destCell.getCellType()); + assertEquals(Cell.CELL_TYPE_BOOLEAN, destCell.getCellType()); + assertEquals(true, destCell.getBooleanCellValue()); + } + + private final void setUp_testCopyCellFrom_CellCopyPolicy() { + final Workbook wb = _testDataProvider.createWorkbook(); + final Row row = wb.createSheet().createRow(0); + srcCell = row.createCell(0); + destCell = row.createCell(1); + + srcCell.setCellFormula("2+3"); + + final CellStyle style = wb.createCellStyle(); + style.setBorderTop(CellStyle.BORDER_THICK); + style.setFillBackgroundColor((short) 5); + srcCell.setCellStyle(style); + + destCell.setCellValue(true); + } } --- src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java (revision 1704452) +++ src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java (working copy) @@ -19,6 +19,7 @@ import junit.framework.TestCase; +import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.ptg.AreaErrPtg; import org.apache.poi.ss.formula.ptg.AreaPtg; import org.apache.poi.ss.formula.ptg.Ptg; @@ -74,6 +75,56 @@ confirmAreaShift(aptg, 18, 22, 5, 10, 25); // simple expansion at bottom } + + public void testCopyAreasSourceRowsRelRel() { + + // all these operations are on an area ref spanning rows 10 to 20 + final AreaPtg aptg = createAreaPtg(10, 20, true, true); + + confirmAreaCopy(aptg, 0, 30, 20, 30, 40, true); + confirmAreaCopy(aptg, 15, 25, -15, -1, -1, true); //DeletedRef + } + + public void testCopyAreasSourceRowsRelAbs() { + + // all these operations are on an area ref spanning rows 10 to 20 + final AreaPtg aptg = createAreaPtg(10, 20, true, false); + + // Only first row should move + confirmAreaCopy(aptg, 0, 30, 20, 20, 30, true); + confirmAreaCopy(aptg, 15, 25, -15, -1, -1, true); //DeletedRef + } + + public void testCopyAreasSourceRowsAbsRel() { + // aptg is part of a formula in a cell that was just copied to another row + // aptg row references should be updated by the difference in rows that the cell was copied + // No other references besides the cells that were involved in the copy need to be updated + // this makes the row copy significantly different from the row shift, where all references + // in the workbook need to track the row shift + + // all these operations are on an area ref spanning rows 10 to 20 + final AreaPtg aptg = createAreaPtg(10, 20, false, true); + + // Only last row should move + confirmAreaCopy(aptg, 0, 30, 20, 10, 40, true); + confirmAreaCopy(aptg, 15, 25, -15, 5, 10, true); //sortTopLeftToBottomRight swapped firstRow and lastRow because firstRow is absolute + } + + public void testCopyAreasSourceRowsAbsAbs() { + // aptg is part of a formula in a cell that was just copied to another row + // aptg row references should be updated by the difference in rows that the cell was copied + // No other references besides the cells that were involved in the copy need to be updated + // this makes the row copy significantly different from the row shift, where all references + // in the workbook need to track the row shift + + // all these operations are on an area ref spanning rows 10 to 20 + final AreaPtg aptg = createAreaPtg(10, 20, false, false); + + //AbsFirstRow AbsLastRow references should't change when copied to a different row + confirmAreaCopy(aptg, 0, 30, 20, 10, 20, false); + confirmAreaCopy(aptg, 15, 25, -15, 10, 20, false); + } + /** * Tests what happens to an area ref when some outside rows are moved to overlap * that area ref @@ -97,7 +148,7 @@ int firstRowMoved, int lastRowMoved, int numberRowsMoved, int expectedAreaFirstRow, int expectedAreaLastRow) { - FormulaShifter fs = FormulaShifter.createForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved); + FormulaShifter fs = FormulaShifter.createForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved, SpreadsheetVersion.EXCEL2007); boolean expectedChanged = aptg.getFirstRow() != expectedAreaFirstRow || aptg.getLastRow() != expectedAreaLastRow; AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method @@ -113,7 +164,41 @@ assertEquals(expectedAreaLastRow, copyPtg.getLastRow()); } + + + private static void confirmAreaCopy(AreaPtg aptg, + int firstRowCopied, int lastRowCopied, int rowOffset, + int expectedFirstRow, int expectedLastRow, boolean expectedChanged) { + + /*final boolean expectedChanged = ( + (aptg.isFirstRowRelative() && (aptg.getFirstRow() != expectedFirstRow)) || + (aptg.isLastRowRelative() && (aptg.getLastRow() != expectedLastRow)) + ); //absolute row references should not change for row copy*/ + + final AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method + final Ptg[] ptgs = { copyPtg, }; + final FormulaShifter fs = FormulaShifter.createForRowCopy(0, null, firstRowCopied, lastRowCopied, rowOffset, SpreadsheetVersion.EXCEL2007); + final boolean actualChanged = fs.adjustFormula(ptgs, 0); + + // DeletedAreaRef + if (expectedFirstRow < 0 || expectedLastRow < 0) { + assertEquals("Reference should have shifted off worksheet, producing #REF! error: " + ptgs[0], + AreaErrPtg.class, ptgs[0].getClass()); + return; + } + + assertEquals("Should this AreaPtg change due to row copy?", expectedChanged, actualChanged); + assertEquals("AreaPtgs should be modified in-place when a row containing the AreaPtg is copied", copyPtg, ptgs[0]); // expected to change in place (although this is not a strict requirement) + assertEquals("AreaPtg first row", expectedFirstRow, copyPtg.getFirstRow()); + assertEquals("AreaPtg last row", expectedLastRow, copyPtg.getLastRow()); + + } + private static AreaPtg createAreaPtg(int initialAreaFirstRow, int initialAreaLastRow) { - return new AreaPtg(initialAreaFirstRow, initialAreaLastRow, 2, 5, false, false, false, false); + return createAreaPtg(initialAreaFirstRow, initialAreaLastRow, false, false); } + + private static AreaPtg createAreaPtg(int initialAreaFirstRow, int initialAreaLastRow, boolean firstRowRelative, boolean lastRowRelative) { + return new AreaPtg(initialAreaFirstRow, initialAreaLastRow, 2, 5, firstRowRelative, lastRowRelative, false, false); + } }