--- 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 extends Row> 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 extends Row> 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 extends Row> 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);
+ }
}