Attachment #33138 for bug #58348

View | Details | Raw Unified | Return to bug 58348
Collapse All | Expand All





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.

     * @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.




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;
    }

}

Lines 19-24 Link Here

(-)src/java/org/apache/poi/ss/usermodel/Row.java (+15 lines)
19		19
20	import java.util.Iterator;	20	import java.util.Iterator;
21		21
		22	import org.apache.poi.util.Beta;
		23	import org.apache.poi.util.Internal;
		24
22	/**	25	/**
23	* High level representation of a row of a spreadsheet.	26	* High level representation of a row of a spreadsheet.
24	*/	27	*/
Lines 241-244 Link Here
241	* you take it out of them.	244	* you take it out of them.
242	*/	245	*/
243	public int getOutlineLevel();	246	public int getOutlineLevel();
		247
		248	/**
		249	* copy the cells from srcRow to this row
		250	* If this row is not a blank row, this will merge the two rows, overwriting
		251	* the cells in this row with the values/styles/formulas/etc of the cells in
		252	* srcRow per cellCopyPolicy.
		253	* @param srcRow the row to copy from (may be from a different sheet)
		254	* @param policy the policy to determine what gets copied
		255	*/
		256	@Beta
		257	@Internal
		258	void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy);
244	}	259	}

Lines 22-27 Link Here

(-)src/java/org/apache/poi/ss/usermodel/Cell.java (+13 lines)
22		22
23	import org.apache.poi.ss.formula.FormulaParseException;	23	import org.apache.poi.ss.formula.FormulaParseException;
24	import org.apache.poi.ss.util.CellRangeAddress;	24	import org.apache.poi.ss.util.CellRangeAddress;
		25	import org.apache.poi.util.Beta;
		26	import org.apache.poi.util.Internal;
25		27
26	/**	28	/**
27	* High level representation of a cell in a row of a spreadsheet.	29	* High level representation of a cell in a row of a spreadsheet.
Lines 324-329 Link Here
324	* @see FormulaError for error codes	326	* @see FormulaError for error codes
325	*/	327	*/
326	byte getErrorCellValue();	328	byte getErrorCellValue();
		329
		330	/**
		331	* Copy cell value, formula, and style, from srcCell per cell copy policy
		332	* If srcCell is null, clears the cell value and cell style per cell copy policy
		333	*
		334	* @param srcCell
		335	* @param policy
		336	*/
		337	@Beta
		338	@Internal
		339	void copyCellFrom(Cell srcCell, CellCopyPolicy policy);
327		340
328	/**	341	/**
329	* Set the style for the cell. The style should be an CellStyle created/retreived from	342	* Set the style for the cell. The style should be an CellStyle created/retreived from





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;

 */
public final class FormulaShifter {

    private static enum ShiftMode {
        RowMove,
        RowCopy,
        SheetMove,
    }

	/**


    private final int _srcSheetIndex;
    private final int _dstSheetIndex;
    private final SpreadsheetVersion _version;

    private final ShiftMode _mode;


     *
     * 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, ShiftMode mode, SpreadsheetVersion version) {
		if (amountToMove == 0) {
			throw new IllegalArgumentException("amountToMove must not be zero");
		}

		_firstMovedIndex = firstMovedIndex;
		_lastMovedIndex = lastMovedIndex;
		_amountToMove = amountToMove;
        _mode = mode;
        _version = version;

        _srcSheetIndex = _dstSheetIndex = -1;
	}

    private FormulaShifter(int srcSheetIndex, int dstSheetIndex) {
        _externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1;
        _sheetName = null;
        _version = null;

        _srcSheetIndex = srcSheetIndex;
        _dstSheetIndex = dstSheetIndex;
        _mode = ShiftMode.SheetMove;
    }

    /**
     * @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);

		return refsWereChanged;
	}

    private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) {
        switch(_mode){
            case RowMove:
                return adjustPtgDueToRowMove(ptg, currentExternSheetIx);
            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 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()) {
				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
                // (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 Area3DPxg) {
            Area3DPxg apxg = (Area3DPxg)ptg;
            if (apxg.getExternalWorkbookNumber() > 0 ||

            }
            return rowMoveAreaPtg(apxg);
        }
        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 <code>null</code>
     */
    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;

		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 <code>null</code> 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 <code>null</code> 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();

Lines 43-56 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (+17 lines)
43	import org.apache.poi.ss.formula.ptg.ExpPtg;	43	import org.apache.poi.ss.formula.ptg.ExpPtg;
44	import org.apache.poi.ss.formula.ptg.Ptg;	44	import org.apache.poi.ss.formula.ptg.Ptg;
45	import org.apache.poi.ss.usermodel.Cell;	45	import org.apache.poi.ss.usermodel.Cell;
		46	import org.apache.poi.ss.usermodel.CellCopyPolicy;
46	import org.apache.poi.ss.usermodel.CellStyle;	47	import org.apache.poi.ss.usermodel.CellStyle;
47	import org.apache.poi.ss.usermodel.Comment;	48	import org.apache.poi.ss.usermodel.Comment;
		49	import org.apache.poi.ss.usermodel.DateUtil;
48	import org.apache.poi.ss.usermodel.FormulaError;	50	import org.apache.poi.ss.usermodel.FormulaError;
49	import org.apache.poi.ss.usermodel.Hyperlink;	51	import org.apache.poi.ss.usermodel.Hyperlink;
50	import org.apache.poi.ss.usermodel.RichTextString;	52	import org.apache.poi.ss.usermodel.RichTextString;
51	import org.apache.poi.ss.util.CellRangeAddress;	53	import org.apache.poi.ss.util.CellRangeAddress;
52	import org.apache.poi.ss.util.CellReference;	54	import org.apache.poi.ss.util.CellReference;
53	import org.apache.poi.ss.util.NumberToTextConverter;	55	import org.apache.poi.ss.util.NumberToTextConverter;
		56	import org.apache.poi.util.Beta;
		57	import org.apache.poi.util.Internal;
54	import org.apache.poi.util.LocaleUtil;	58	import org.apache.poi.util.LocaleUtil;
55		59
56	/**	60	/**
Lines 884-889 Link Here
884	}	888	}
885		889
886	/**	890	/**
		891	* Copy cell value, formula, and style, from srcCell per cell copy policy
		892	* If srcCell is null, clears the cell value and cell style per cell copy policy
		893	*
		894	* @param srcCell
		895	* @param policy
		896	*/
		897	@Beta
		898	@Internal
		899	public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) {
		900	throw new UnsupportedOperationException();
		901	}
		902
		903	/**
887	* set the style for the cell. The style should be an HSSFCellStyle created/retreived from	904	* set the style for the cell. The style should be an HSSFCellStyle created/retreived from
888	* the HSSFWorkbook.	905	* the HSSFWorkbook.
889	*	906	*




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;

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;

     * @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);
    }

     * @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);
    }

        String sheetName = _workbook.getSheetName(sheetIndex);
        short externSheetIndex = _book.checkExternSheet(sheetIndex);
        FormulaShifter shifter = FormulaShifter.createForRowShift(
                         externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
        _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex);

        int nSheets = _workbook.getNumberOfSheets();

        }
        _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
     *
     * <p>Additionally copies merged regions that are completely defined in these
     * rows (ie. merged 2 cells on a row to be shifted).</p<
     * 
     * @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(List<? extends Row> 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);
     *
     * <p>
     * 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<Record> records) {
        int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);
        _sheet.getRecords().addAll(window2Loc, records);




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;

/**

    }

    /**
     * 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) {





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;


        }
        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<Integer> indices = new HashSet<Integer>();
                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();
    }

Lines 162-168 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-1 / +1 lines)
162	}	162	}
163	}	163	}
164		164
165	private void updateRowFormulas(XSSFRow row, FormulaShifter shifter) {	165	public void updateRowFormulas(XSSFRow row, FormulaShifter shifter) {
166	for (Cell c : row) {	166	for (Cell c : row) {
167	XSSFCell cell = (XSSFCell) c;	167	XSSFCell cell = (XSSFCell) c;
168		168




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;

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;

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;

 * High level representation of a cell in a row of a spreadsheet.
 * <p>
 * Cells can be numeric, formula-based or string-based (text).  The cell type
 * 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.

        _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

        cellFormula.setRef(range.formatAsString());
    }

    @SuppressWarnings("resource")
    private void setFormula(String formula, int formulaType) {
        XSSFWorkbook wb = _row.getSheet().getWorkbook();
        if (formula == null) {

     *
     * @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) {




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;

    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<XSSFRow> 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<XSSFRow> rows = new ArrayList<XSSFRow>();
        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

        _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

        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<Integer, Short> rowHeight) {
        // first remove all rows which will be overwritten
        for (Iterator<Row> 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<XSSFRow> 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.

            int rownum = row.getRowNum();

            // check if we should remove this row as it will be overwritten by the data later
            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);

        return sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr();
    }

    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

Lines 28-33 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (+13 lines)
28	import org.apache.poi.ss.SpreadsheetVersion;	28	import org.apache.poi.ss.SpreadsheetVersion;
29	import org.apache.poi.ss.usermodel.AutoFilter;	29	import org.apache.poi.ss.usermodel.AutoFilter;
30	import org.apache.poi.ss.usermodel.Cell;	30	import org.apache.poi.ss.usermodel.Cell;
		31	import org.apache.poi.ss.usermodel.CellCopyPolicy;
31	import org.apache.poi.ss.usermodel.CellRange;	32	import org.apache.poi.ss.usermodel.CellRange;
32	import org.apache.poi.ss.usermodel.CellStyle;	33	import org.apache.poi.ss.usermodel.CellStyle;
33	import org.apache.poi.ss.usermodel.Comment;	34	import org.apache.poi.ss.usermodel.Comment;
Lines 883-889 Link Here
883	{	884	{
884	throw new RuntimeException("NotImplemented");	885	throw new RuntimeException("NotImplemented");
885	}	886	}
		887
		888	public void copyRows(List<? extends Row> srcRows, int destStartRow,
		889	CellCopyPolicy cellCopyPolicy) {
		890	// TODO Auto-generated method stub
		891
		892	}
886		893
		894	public void copyRows(int srcStartRow, int srcEndRow, int destStartRow,
		895	CellCopyPolicy cellCopyPolicy) {
		896	// TODO Auto-generated method stub
		897
		898	}
		899
887	/**	900	/**
888	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.	901	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
889	* @param colSplit Horizonatal position of split.	902	* @param colSplit Horizonatal position of split.

Lines 26-31 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java (+16 lines)
26	import org.apache.poi.ss.formula.FormulaParseException;	26	import org.apache.poi.ss.formula.FormulaParseException;
27	import org.apache.poi.ss.formula.eval.ErrorEval;	27	import org.apache.poi.ss.formula.eval.ErrorEval;
28	import org.apache.poi.ss.usermodel.Cell;	28	import org.apache.poi.ss.usermodel.Cell;
		29	import org.apache.poi.ss.usermodel.CellCopyPolicy;
29	import org.apache.poi.ss.usermodel.CellStyle;	30	import org.apache.poi.ss.usermodel.CellStyle;
30	import org.apache.poi.ss.usermodel.Comment;	31	import org.apache.poi.ss.usermodel.Comment;
31	import org.apache.poi.ss.usermodel.DateUtil;	32	import org.apache.poi.ss.usermodel.DateUtil;
Lines 35-40 Link Here
35	import org.apache.poi.ss.usermodel.Row;	36	import org.apache.poi.ss.usermodel.Row;
36	import org.apache.poi.ss.util.CellRangeAddress;	37	import org.apache.poi.ss.util.CellRangeAddress;
37	import org.apache.poi.ss.util.CellReference;	38	import org.apache.poi.ss.util.CellReference;
		39	import org.apache.poi.util.Beta;
		40	import org.apache.poi.util.Internal;
38	import org.apache.poi.util.LocaleUtil;	41	import org.apache.poi.util.LocaleUtil;
39	import org.apache.poi.util.POILogFactory;	42	import org.apache.poi.util.POILogFactory;
40	import org.apache.poi.util.POILogger;	43	import org.apache.poi.util.POILogger;
Lines 502-507 Link Here
502	throw typeMismatch(CELL_TYPE_ERROR, cellType, false);	505	throw typeMismatch(CELL_TYPE_ERROR, cellType, false);
503	}	506	}
504	}	507	}
		508
		509	/**
		510	* Copy cell value, formula, and style, from srcCell per cell copy policy
		511	* If srcCell is null, clears the cell value and cell style per cell copy policy
		512	* @param srcCell
		513	* @param policy
		514	* @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook
		515	*/
		516	@Beta
		517	@Internal
		518	public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) {
		519	throw new UnsupportedOperationException();
		520	}
505		521
506	/**	522	/**
507	* Set the style for the cell. The style should be an CellStyle created/retreived from	523	* Set the style for the cell. The style should be an CellStyle created/retreived from





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.

            throw new UnsupportedOperationException();
        }
    }
    
    @Beta
    @Override
    public void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy) {
        // TODO Auto-generated method stub
        throw new UnsupportedOperationException();
    }
}






        assertNotNull(wb);
        assertNotNull(sheet);
        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", SpreadsheetVersion.EXCEL2007), new CellReference("L5"), sheet);
        assertNotNull(pivotTable2);
        assertTrue(wb.getPivotTables().size() > 1);
    }


        assertNotNull(wb);
        assertNotNull(sheet);
        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", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet);
        assertNotNull(pivotTable2);
        assertTrue(wb.getPivotTables().size() > 1);
    }


        assertNotNull(wb);
        assertNotNull(sheet);
        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
        assertNotNull(pivotTable);
        assertTrue(wb.getPivotTables().size() > 0);
    }

        XSSFSheet sheet2 = wb.createSheet();

        XSSFPivotTable pivotTable = sheet2.createPivotTable
                (new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet1);
        assertEquals(0, pivotTable.getRowLabelColumns().size());
        
        assertEquals(1, wb.getPivotTables().size());

        XSSFSheet sheet2 = wb.createSheet();

        XSSFPivotTable pivotTable = sheet2.createPivotTable
                (new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
        assertEquals(0, pivotTable.getRowLabelColumns().size());
    }


        XSSFSheet sheet2 = wb.createSheet();

        try {
            sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet2);
        } catch(IllegalArgumentException e) {
            return;
        }

        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");
    }
}




        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




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

    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
    }
}





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;

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;

Lines 1139-1142 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (+10 lines)
1139	NameRecord record = wb.getWorkbook().getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);	1139	NameRecord record = wb.getWorkbook().getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);
1140	assertNotNull(record);	1140	assertNotNull(record);
1141	}	1141	}
		1142
		1143	@Test
		1144	public void testCopyOneRow() {
		1145	//TODO: implement this test
		1146	}
		1147
		1148	@Test
		1149	public void testCopyMultipleRows() {
		1150	//TODO: implement this test
		1151	}
1142	}	1152	}




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;

/**

        
        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
    }
}

Lines 18-29 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java (+163 lines)
18	package org.apache.poi.ss.usermodel;	18	package org.apache.poi.ss.usermodel;
19		19
20	import java.io.IOException;	20	import java.io.IOException;
		21	import java.util.Calendar;
		22	import java.util.Date;
		23	import java.util.GregorianCalendar;
21	import java.util.Iterator;	24	import java.util.Iterator;
22		25
23	import junit.framework.TestCase;	26	import junit.framework.TestCase;
24		27
25	import org.apache.poi.ss.ITestDataProvider;	28	import org.apache.poi.ss.ITestDataProvider;
		29	import org.apache.poi.ss.util.CellRangeAddress;
		30	import org.apache.poi.ss.util.CellReference;
		31	import org.apache.poi.ss.util.CellUtil;
26		32
		33
27	/**	34	/**
28	* A base class for testing implementations of	35	* A base class for testing implementations of
29	* {@link org.apache.poi.ss.usermodel.Row}	36	* {@link org.apache.poi.ss.usermodel.Row}
Lines 432-435 Link Here
432	assertEquals(style, row2.getRowStyle());	439	assertEquals(style, row2.getRowStyle());
433	assertEquals(4, style.getDataFormat());	440	assertEquals(4, style.getDataFormat());
434	}	441	}
		442
		443	public void testCopyRowFrom() {
		444	final Workbook workbook = _testDataProvider.createWorkbook();
		445	final Sheet sheet = workbook.createSheet("test");
		446	final Row srcRow = sheet.createRow(0);
		447	srcRow.createCell(0).setCellValue("Hello");
		448	final Row destRow = sheet.createRow(1);
		449
		450	destRow.copyRowFrom(srcRow, new CellCopyPolicy());
		451	assertNotNull(destRow.getCell(0));
		452	assertEquals("Hello", destRow.getCell(0).getStringCellValue());
		453	}
		454
		455	public void testCopyRowFromExternalSheet() {
		456	final Workbook workbook = _testDataProvider.createWorkbook();
		457	final Sheet srcSheet = workbook.createSheet("src");
		458	final Sheet destSheet = workbook.createSheet("dest");
		459	workbook.createSheet("other");
		460
		461	final Row srcRow = srcSheet.createRow(0);
		462	int col = 0;
		463	//Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
		464	srcRow.createCell(col++).setCellFormula("B5");
		465	srcRow.createCell(col++).setCellFormula("src!B5");
		466	srcRow.createCell(col++).setCellFormula("dest!B5");
		467	srcRow.createCell(col++).setCellFormula("other!B5");
		468
		469	//Test 2D and 3D Ref Ptgs with absolute row
		470	srcRow.createCell(col++).setCellFormula("B$5");
		471	srcRow.createCell(col++).setCellFormula("src!B$5");
		472	srcRow.createCell(col++).setCellFormula("dest!B$5");
		473	srcRow.createCell(col++).setCellFormula("other!B$5");
		474
		475	//Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
		476	srcRow.createCell(col++).setCellFormula("SUM(B5:D$5)");
		477	srcRow.createCell(col++).setCellFormula("SUM(src!B5:D$5)");
		478	srcRow.createCell(col++).setCellFormula("SUM(dest!B5:D$5)");
		479	srcRow.createCell(col++).setCellFormula("SUM(other!B5:D$5)");
		480
		481	// FIXME: temporary work-around until bug 58350 is fixed
		482	if (srcSheet.getNumMergedRegions() == 0) {
		483	// Goal: CTWorksheet.addNewMergeCells()
		484	srcSheet.addMergedRegion(new CellRangeAddress(10, 11, 0, 0));
		485	}
		486
		487	//////////////////
		488
		489	final Row destRow = destSheet.createRow(1);
		490	destRow.copyRowFrom(srcRow, new CellCopyPolicy());
		491
		492	//////////////////
		493
		494	//Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
		495	col = 0;
		496	Cell cell = destRow.getCell(col++);
		497	assertNotNull(cell);
		498	assertEquals("RefPtg", "B6", cell.getCellFormula());
		499
		500	cell = destRow.getCell(col++);
		501	assertNotNull(cell);
		502	assertEquals("Ref3DPtg", "src!B6", cell.getCellFormula());
		503
		504	cell = destRow.getCell(col++);
		505	assertNotNull(cell);
506	assertEquals("Ref3DPtg", "dest!B6", cell.getCellFormula());
507
508	cell = destRow.getCell(col++);
509	assertNotNull(cell);
510	assertEquals("Ref3DPtg", "other!B6", cell.getCellFormula());
511
512	/////////////////////////////////////////////
513
514	//Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change)
515	cell = destRow.getCell(col++);
516	assertNotNull(cell);
517	assertEquals("RefPtg", "B$5", cell.getCellFormula());
518
519	cell = destRow.getCell(col++);
520	assertNotNull(cell);
521	assertEquals("Ref3DPtg", "src!B$5", cell.getCellFormula());
522
523	cell = destRow.getCell(col++);
524	assertNotNull(cell);
525	assertEquals("Ref3DPtg", "dest!B$5", cell.getCellFormula());
526
527	cell = destRow.getCell(col++);
528	assertNotNull(cell);
529	assertEquals("Ref3DPtg", "other!B$5", cell.getCellFormula());
530
531	//////////////////////////////////////////
532
533	//Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
534	// Note: absolute row changes from last cell to first cell in order
535	// to maintain topLeft:bottomRight order
536	cell = destRow.getCell(col++);
537	assertNotNull(cell);
538	assertEquals("Area2DPtg", "SUM(B$5:D6)", cell.getCellFormula());
539
540	cell = destRow.getCell(col++);
541	assertNotNull(cell);
542	assertEquals("Area3DPtg", "SUM(src!B$5:D6)", cell.getCellFormula());
543
544	cell = destRow.getCell(col++);
545	assertNotNull(destRow.getCell(6));
546	assertEquals("Area3DPtg", "SUM(dest!B$5:D6)", cell.getCellFormula());
547
548	cell = destRow.getCell(col++);
549	assertNotNull(destRow.getCell(7));
550	assertEquals("Area3DPtg", "SUM(other!B$5:D6)", cell.getCellFormula());
551	}
552
553	public void testCopyRowOverwritesExistingRow() {
554	final Workbook workbook = _testDataProvider.createWorkbook();
555	final Sheet sheet1 = workbook.createSheet("Sheet1");
556	final Sheet sheet2 = workbook.createSheet("Sheet2");
557
558	final Row srcRow = sheet1.createRow(0);
559	final Row destRow = sheet1.createRow(1);
560	final Row observerRow = sheet1.createRow(2);
561	final Row externObserverRow = sheet2.createRow(0);
562
563	srcRow.createCell(0).setCellValue("hello");
564	srcRow.createCell(1).setCellValue("world");
565	destRow.createCell(0).setCellValue(5.0); //A2 -> 5.0
566	destRow.createCell(1).setCellFormula("A1"); // B2 -> A1 -> "hello"
567	observerRow.createCell(0).setCellFormula("A2"); // A3 -> A2 -> 5.0
568	observerRow.createCell(1).setCellFormula("B2"); // B3 -> B2 -> A1 -> "hello"
569	externObserverRow.createCell(0).setCellFormula("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0
570
571	// FIXME: temporary work-around until bug 58350 is fixed
572	if (sheet1.getNumMergedRegions() == 0) {
573	// Goal: CTWorksheet.addNewMergeCells()
574	sheet1.addMergedRegion(new CellRangeAddress(10, 11, 0, 0));
575	}
576
577	// overwrite existing destRow with row-copy of srcRow
578	destRow.copyRowFrom(srcRow, new CellCopyPolicy());
579
580	// copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer
581	// to the new row (and allow the old row to be garbage collected)
582	// this is mostly so existing references to rows that are overwritten are updated
583	// rather than allowing users to continue updating rows that are no longer part of the sheet
584	assertSame("existing references to srcRow are still valid", srcRow, sheet1.getRow(0));
585	assertSame("existing references to destRow are still valid", destRow, sheet1.getRow(1));
586	assertSame("existing references to observerRow are still valid", observerRow, sheet1.getRow(2));
587	assertSame("existing references to externObserverRow are still valid", externObserverRow, sheet2.getRow(0));
588
589	// Make sure copyRowFrom actually copied row (this is tested elsewhere)
590	assertEquals(Cell.CELL_TYPE_STRING, destRow.getCell(0).getCellType());
591	assertEquals("hello", destRow.getCell(0).getStringCellValue());
592
593	// 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
594	assertEquals("references to overwritten cells are unmodified", "A2", observerRow.getCell(0).getCellFormula());
595	assertEquals("references to overwritten cells are unmodified", "B2", observerRow.getCell(1).getCellFormula());
596	assertEquals("references to overwritten cells are unmodified", "Sheet1!A2", externObserverRow.getCell(0).getCellFormula());
597	}
435	}	598	}

Lines 22-33 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (+395 lines)
22	import static org.junit.Assert.*;	22	import static org.junit.Assert.*;
23		23
24	import java.io.IOException;	24	import java.io.IOException;
		25	import java.util.Calendar;
		26	import java.util.Date;
		27	import java.util.GregorianCalendar;
25	import java.util.Iterator;	28	import java.util.Iterator;
26		29
27	import org.apache.poi.hssf.util.PaneInformation;	30	import org.apache.poi.hssf.util.PaneInformation;
28	import org.apache.poi.ss.ITestDataProvider;	31	import org.apache.poi.ss.ITestDataProvider;
29	import org.apache.poi.ss.SpreadsheetVersion;	32	import org.apache.poi.ss.SpreadsheetVersion;
30	import org.apache.poi.ss.util.CellRangeAddress;	33	import org.apache.poi.ss.util.CellRangeAddress;
		34	import org.apache.poi.ss.util.CellReference;
		35	import org.apache.poi.ss.util.CellUtil;
31	import org.junit.Rule;	36	import org.junit.Rule;
32	import org.junit.Test;	37	import org.junit.Test;
33	import org.junit.rules.ExpectedException;	38	import org.junit.rules.ExpectedException;
Lines 348-354 Link Here
348	region = sheet.getMergedRegion(0);	353	region = sheet.getMergedRegion(0);
349	assertEquals("Merged region not moved over to row 2", 2, region.getFirstRow());	354	assertEquals("Merged region not moved over to row 2", 2, region.getFirstRow());
350	}	355	}
		356
		357	protected void baseTestCopyOneRow(String copyRowsTestWorkbook) throws IOException {
		358	final double FLOAT_PRECISION = 1e-9;
		359	final Workbook workbook = _testDataProvider.openSampleWorkbook(copyRowsTestWorkbook);
		360	final Sheet sheet = workbook.getSheetAt(0);
		361	final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy();
		362	sheet.copyRows(1, 1, 6, defaultCopyPolicy);
351		363
		364	final Row srcRow = sheet.getRow(1);
		365	final Row destRow = sheet.getRow(6);
		366	int col = 0;
		367	Cell cell;
		368
		369	cell = CellUtil.getCell(destRow, col++);
		370	assertEquals("Source row ->", cell.getStringCellValue());
		371
		372	// Style
		373	cell = CellUtil.getCell(destRow, col++);
		374	assertEquals("[Style] B7 cell value", "Red", cell.getStringCellValue());
		375	assertEquals("[Style] B7 cell style", CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle());
		376
		377	// Blank
		378	cell = CellUtil.getCell(destRow, col++);
		379	assertEquals("[Blank] C7 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType());
		380
		381	// Error
		382	cell = CellUtil.getCell(destRow, col++);
		383	assertEquals("[Error] D7 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType());
		384	final FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
		385	assertEquals("[Error] D7 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
		386
		387	// Date
		388	cell = CellUtil.getCell(destRow, col++);
		389	assertEquals("[Date] E7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
		390	final Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime();
		391	assertEquals("[Date] E7 cell value", date, cell.getDateCellValue());
		392
		393	// Boolean
		394	cell = CellUtil.getCell(destRow, col++);
		395	assertEquals("[Boolean] F7 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType());
		396	assertEquals("[Boolean] F7 cell value", true, cell.getBooleanCellValue());
		397
		398	// String
		399	cell = CellUtil.getCell(destRow, col++);
		400	assertEquals("[String] G7 cell type", Cell.CELL_TYPE_STRING, cell.getCellType());
		401	assertEquals("[String] G7 cell value", "Hello", cell.getStringCellValue());
		402
		403	// Int
		404	cell = CellUtil.getCell(destRow, col++);
		405	assertEquals("[Int] H7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
		406	assertEquals("[Int] H7 cell value", 15, (int) cell.getNumericCellValue());
		407
		408	// Float
		409	cell = CellUtil.getCell(destRow, col++);
		410	assertEquals("[Float] I7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
		411	assertEquals("[Float] I7 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION);
		412
		413	// Cell Formula
		414	cell = CellUtil.getCell(destRow, col++);
		415	assertEquals("J7", new CellReference(cell).formatAsString());
		416	assertEquals("[Cell Formula] J7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
		417	assertEquals("[Cell Formula] J7 cell formula", "5+2", cell.getCellFormula());
		418	System.out.println("Cell formula evaluation currently unsupported");
		419	//assertEquals("[Cell Formula] J7 cell value", 7.0, cell.getNumericCellValue(), FLOAT_PRECISION);
		420
		421	// Cell Formula with Reference
		422	// Formula row references should be adjusted by destRowNum-srcRowNum
		423	cell = CellUtil.getCell(destRow, col++);
		424	assertEquals("K7", new CellReference(cell).formatAsString());
		425	assertEquals("[Cell Formula with Reference] K7 cell type",
		426	Cell.CELL_TYPE_FORMULA, cell.getCellType());
		427	assertEquals("[Cell Formula with Reference] K7 cell formula",
428	"J7+H$2", cell.getCellFormula());
429	//assertEquals("[Cell Formula with Reference] J7 cell value",
430	// 22.0, cell.getNumericCellValue(), FLOAT_PRECISION);
431
432	// Cell Formula with Reference spanning multiple rows
433	cell = CellUtil.getCell(destRow, col++);
434	assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell type",
435	Cell.CELL_TYPE_FORMULA, cell.getCellType());
436	assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell formula",
437	"G7&\" \"&G8", cell.getCellFormula());
438	//assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell value",
439	// "World ", cell.getStringCellValue());
440
441	// Cell Formula with Reference spanning multiple rows
442	cell = CellUtil.getCell(destRow, col++);
443	assertEquals("[Cell Formula with Area Reference] M7 cell type",
444	Cell.CELL_TYPE_FORMULA, cell.getCellType());
445	assertEquals("[Cell Formula with Area Reference] M7 cell formula",
446	"SUM(H7:I8)", cell.getCellFormula());
447	//assertEquals("[Cell Formula with Area Reference] M7 cell value",
448	// "75", cell.getStringCellValue());
449
450	// Array Formula
451	cell = CellUtil.getCell(destRow, col++);
452	System.out.println("Array formulas currently unsupported");
453	// FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula()
454	/*
455	assertEquals("[Array Formula] N7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
456	assertEquals("[Array Formula] N7 cell formula", "{SUM(H7:J7*{1,2,3})}", cell.getCellFormula());
457	*/
458	// Formula should be evaluated
459	//assertEquals("[Array Formula] N7 cell value", 61.0, cell.getNumericCellValue(), FLOAT_PRECISION);
460
461	// Data Format
462	cell = CellUtil.getCell(destRow, col++);
463	assertEquals("[Data Format] O7 cell type;", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
464	assertEquals("[Data Format] O7 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION);
465	//FIXME: currently fails
466	final String moneyFormat = "\"$\"#,##0.00_);[Red]\$\"$\"#,##0.00\$";
467	assertEquals("[Data Format] O7 data format", moneyFormat, cell.getCellStyle().getDataFormatString());
468
469	// Merged
470	cell = CellUtil.getCell(destRow, col);
471	assertEquals("[Merged] P7:Q7 cell value",
472	"Merged cells", cell.getStringCellValue());
473	assertTrue("[Merged] P7:Q7 merged region",
474	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7")));
475
476	// Merged across multiple rows
477	// Microsoft Excel 2013 does not copy a merged region unless all rows of
478	// the source merged region are selected
479	// POI's behavior should match this behavior
480	col += 2;
481	cell = CellUtil.getCell(destRow, col);
482	// Note: this behavior deviates from Microsoft Excel,
483	// which will not overwrite a cell in destination row if merged region extends beyond the copied row.
484	// The Excel way would require:
485	//assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue());
486	//assertFalse("[Merged across multiple rows] R7:S8 merged region",
487	// sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")));
488	// As currently implemented, cell value is copied but merged region is not copied
489	assertEquals("[Merged across multiple rows] R7:S8 cell value",
490	"Merged cells across multiple rows", cell.getStringCellValue());
491	assertFalse("[Merged across multiple rows] R7:S7 merged region (one row)",
492	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7"))); //shouldn't do 1-row merge
493	assertFalse("[Merged across multiple rows] R7:S8 merged region",
494	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); //shouldn't do 2-row merge
495
496	// Make sure other rows are blank (off-by-one errors)
497	assertNull(sheet.getRow(5));
498	assertNull(sheet.getRow(7));
499	}
500
501	public void baseTestCopyMultipleRows(String copyRowsTestWorkbook) throws IOException {
502	final double FLOAT_PRECISION = 1e-9;
503	final Workbook workbook = _testDataProvider.openSampleWorkbook(copyRowsTestWorkbook);
504	final Sheet sheet = workbook.getSheetAt(0);
505	final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy();
506	sheet.copyRows(0, 3, 8, defaultCopyPolicy);
507
508	final Row srcHeaderRow = sheet.getRow(0);
509	final Row srcRow1 = sheet.getRow(1);
510	final Row srcRow2 = sheet.getRow(2);
511	final Row srcRow3 = sheet.getRow(3);
512	final Row destHeaderRow = sheet.getRow(8);
513	final Row destRow1 = sheet.getRow(9);
514	final Row destRow2 = sheet.getRow(10);
515	final Row destRow3 = sheet.getRow(11);
516	int col = 0;
517	Cell cell;
518
519	// Header row should be copied
520	assertNotNull(destHeaderRow);
521
522	// Data rows
523	cell = CellUtil.getCell(destRow1, col);
524	assertEquals("Source row ->", cell.getStringCellValue());
525
526	// Style
527	col++;
528	cell = CellUtil.getCell(destRow1, col);
529	assertEquals("[Style] B10 cell value", "Red", cell.getStringCellValue());
530	assertEquals("[Style] B10 cell style", CellUtil.getCell(srcRow1, 1).getCellStyle(), cell.getCellStyle());
531
532	cell = CellUtil.getCell(destRow2, col);
533	assertEquals("[Style] B11 cell value", "Blue", cell.getStringCellValue());
534	assertEquals("[Style] B11 cell style", CellUtil.getCell(srcRow2, 1).getCellStyle(), cell.getCellStyle());
535
536	// Blank
537	col++;
538	cell = CellUtil.getCell(destRow1, col);
539	assertEquals("[Blank] C10 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType());
540
541	cell = CellUtil.getCell(destRow2, col);
542	assertEquals("[Blank] C11 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType());
543
544	// Error
545	col++;
546	cell = CellUtil.getCell(destRow1, col);
547	assertEquals("[Error] D10 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType());
548	FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
549	assertEquals("[Error] D10 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
550
551	cell = CellUtil.getCell(destRow2, col);
552	assertEquals("[Error] D11 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType());
553	error = FormulaError.forInt(cell.getErrorCellValue());
554	assertEquals("[Error] D11 cell value", FormulaError.NAME, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
555
556	// Date
557	col++;
558	cell = CellUtil.getCell(destRow1, col);
559	assertEquals("[Date] E10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
560	Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime();
561	assertEquals("[Date] E10 cell value", date, cell.getDateCellValue());
562
563	cell = CellUtil.getCell(destRow2, col);
564	assertEquals("[Date] E11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
565	date = new GregorianCalendar(2000, Calendar.JANUARY, 2).getTime();
566	assertEquals("[Date] E11 cell value", date, cell.getDateCellValue());
567
568	// Boolean
569	col++;
570	cell = CellUtil.getCell(destRow1, col);
571	assertEquals("[Boolean] F10 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType());
572	assertEquals("[Boolean] F10 cell value", true, cell.getBooleanCellValue());
573
574	cell = CellUtil.getCell(destRow2, col);
575	assertEquals("[Boolean] F11 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType());
576	assertEquals("[Boolean] F11 cell value", false, cell.getBooleanCellValue());
577
578	// String
579	col++;
580	cell = CellUtil.getCell(destRow1, col);
581	assertEquals("[String] G10 cell type", Cell.CELL_TYPE_STRING, cell.getCellType());
582	assertEquals("[String] G10 cell value", "Hello", cell.getStringCellValue());
583
584	cell = CellUtil.getCell(destRow2, col);
585	assertEquals("[String] G11 cell type", Cell.CELL_TYPE_STRING, cell.getCellType());
586	assertEquals("[String] G11 cell value", "World", cell.getStringCellValue());
587
588	// Int
589	col++;
590	cell = CellUtil.getCell(destRow1, col);
591	assertEquals("[Int] H10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
592	assertEquals("[Int] H10 cell value", 15, (int) cell.getNumericCellValue());
593
594	cell = CellUtil.getCell(destRow2, col);
595	assertEquals("[Int] H11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
596	assertEquals("[Int] H11 cell value", 42, (int) cell.getNumericCellValue());
597
598	// Float
599	col++;
600	cell = CellUtil.getCell(destRow1, col);
601	assertEquals("[Float] I10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
602	assertEquals("[Float] I10 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION);
603
604	cell = CellUtil.getCell(destRow2, col);
605	assertEquals("[Float] I11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
606	assertEquals("[Float] I11 cell value", 5.5, cell.getNumericCellValue(), FLOAT_PRECISION);
607
608	// Cell Formula
609	col++;
610	cell = CellUtil.getCell(destRow1, col);
611	assertEquals("[Cell Formula] J10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
612	assertEquals("[Cell Formula] J10 cell formula", "5+2", cell.getCellFormula());
613	// FIXME: formula evaluation
614	System.out.println("Cell formula evaluation currently unsupported");
615	//assertEquals(7.0, cell.getNumericCellValue(), FLOAT_PRECISION);
616
617	cell = CellUtil.getCell(destRow2, col);
618	assertEquals("[Cell Formula] J11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
619	assertEquals("[Cell Formula] J11 cell formula", "6+18", cell.getCellFormula());
620	// FIXME: formula evaluation
621	//assertEquals("[Cell Formula] J11 cell formula result", 24.0, cell.getNumericCellValue(), FLOAT_PRECISION);
622
623	// Cell Formula with Reference
624	col++;
625	// Formula row references should be adjusted by destRowNum-srcRowNum
626	cell = CellUtil.getCell(destRow1, col);
627	assertEquals("[Cell Formula with Reference] K10 cell type",
628	Cell.CELL_TYPE_FORMULA, cell.getCellType());
629	assertEquals("[Cell Formula with Reference] K10 cell formula",
630	"J10+H$2", cell.getCellFormula());
631	// FIXME: formula evaluation
632	//assertEquals("[Cell Formula with Reference] K10 cell formula result",
633	// 22.0, cell.getNumericCellValue(), FLOAT_PRECISION);
634
635	cell = CellUtil.getCell(destRow2, col);
636	assertEquals("[Cell Formula with Reference] K11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
637	assertEquals("[Cell Formula with Reference] K11 cell formula", "J11+H$2", cell.getCellFormula());
638	// FIXME: formula evaluation
639	//assertEquals("[Cell Formula with Reference] K11 cell formula result",
640	// 39.0, cell.getNumericCellValue(), FLOAT_PRECISION);
641
642	// Cell Formula with Reference spanning multiple rows
643	col++;
644	cell = CellUtil.getCell(destRow1, col);
645	assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell type",
646	Cell.CELL_TYPE_FORMULA, cell.getCellType());
647	assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell formula",
648	"G10&\" \"&G11", cell.getCellFormula());
649	// FIXME: Formula should be evaluated
650	//assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula result",
651	// "Hello World", cell.getStringCellValue());
652
653	cell = CellUtil.getCell(destRow2, col);
654	assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell type",
655	Cell.CELL_TYPE_FORMULA, cell.getCellType());
656	assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula",
657	"G11&\" \"&G12", cell.getCellFormula());
658	// FIXME: Formula should be evaluated
659	//assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula result",
660	// "World ", cell.getStringCellValue());
661
662	// Cell Formula with Area Reference
663	col++;
664	cell = CellUtil.getCell(destRow1, col);
665	assertEquals("[Cell Formula with Area Reference] M10 cell type",
666	Cell.CELL_TYPE_FORMULA, cell.getCellType());
667	assertEquals("[Cell Formula with Area Reference] M10 cell formula",
668	"SUM(H10:I11)", cell.getCellFormula());
669	// FIXME: Formula should be evaluated
670	//assertEquals("[Cell Formula with Area Reference] M10 cell formula result",
671	// "Hello World", cell.getStringCellValue());
672
673	cell = CellUtil.getCell(destRow2, col);
674	assertEquals("[Cell Formula with Area Reference] M11 cell type",
675	Cell.CELL_TYPE_FORMULA, cell.getCellType());
676	assertEquals("[Cell Formula with Area Reference] M11 cell formula",
677	"SUM($H$3:I10)", cell.getCellFormula()); //Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order
678	// FIXME: Formula should be evaluated
679	//assertEquals("[Cell Formula with Area Reference] M11 cell formula result",
680	// "World ", cell.getStringCellValue());
681
682	// Array Formula
683	col++;
684	cell = CellUtil.getCell(destRow1, col);
685	System.out.println("Array formulas currently unsupported");
686	/*
687	// FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula()
688	assertEquals("[Array Formula] N10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
689	assertEquals("[Array Formula] N10 cell formula", "{SUM(H10:J10*{1,2,3})}", cell.getCellFormula());
690	// FIXME: Formula should be evaluated
691	assertEquals("[Array Formula] N10 cell formula result", 61.0, cell.getNumericCellValue(), FLOAT_PRECISION);
692
693	cell = CellUtil.getCell(destRow2, col);
694	// FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula()
695	assertEquals("[Array Formula] N11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType());
696	assertEquals("[Array Formula] N11 cell formula", "{SUM(H11:J11*{1,2,3})}", cell.getCellFormula());
697	// FIXME: Formula should be evaluated
698	assertEquals("[Array Formula] N11 cell formula result", 125.0, cell.getNumericCellValue(), FLOAT_PRECISION);
699	*/
700
701	// Data Format
702	col++;
703	cell = CellUtil.getCell(destRow2, col);
704	assertEquals("[Data Format] O10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType());
705	assertEquals("[Data Format] O10 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION);
706	final String moneyFormat = "\"$\"#,##0.00_);[Red]\$\"$\"#,##0.00\$";
707	assertEquals("[Data Format] O10 cell data format", moneyFormat, cell.getCellStyle().getDataFormatString());
708
709	// Merged
710	col++;
711	cell = CellUtil.getCell(destRow1, col);
712	assertEquals("[Merged] P10:Q10 cell value",
713	"Merged cells", cell.getStringCellValue());
714	assertTrue("[Merged] P10:Q10 merged region",
715	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P10:Q10")));
716
717	cell = CellUtil.getCell(destRow2, col);
718	assertEquals("[Merged] P11:Q11 cell value", "Merged cells", cell.getStringCellValue());
719	assertTrue("[Merged] P11:Q11 merged region",
720	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P11:Q11")));
721
722	// Should Q10/Q11 be checked?
723
724	// Merged across multiple rows
725	// Microsoft Excel 2013 does not copy a merged region unless all rows of
726	// the source merged region are selected
727	// POI's behavior should match this behavior
728	col += 2;
729	cell = CellUtil.getCell(destRow1, col);
730	assertEquals("[Merged across multiple rows] R10:S11 cell value",
731	"Merged cells across multiple rows", cell.getStringCellValue());
732	assertTrue("[Merged across multiple rows] R10:S11 merged region",
733	sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R10:S11")));
734
735	// Row 3 (zero-based) was empty, so Row 11 (zero-based) should be empty too.
736	if (srcRow3 == null) {
737	assertNull("Row 3 was empty, so Row 11 should be empty", destRow3);
738	}
739
740	// Make sure other rows are blank (off-by-one errors)
741	assertNull("Off-by-one lower edge case", sheet.getRow(7)); //one row above destHeaderRow
742	assertNull("Off-by-one upper edge case", sheet.getRow(12)); //one row below destRow3
743	}
744
352	/**	745	/**
353	* Tests the display of gridlines, formulas, and rowcolheadings.	746	* Tests the display of gridlines, formulas, and rowcolheadings.
354	* @author Shawn Laubach (slaubach at apache dot org)	747	* @author Shawn Laubach (slaubach at apache dot org)
Lines 996-999 Link Here
996		1389
997	wb.close();	1390	wb.close();
998	}	1391	}
		1392
		1393
999	}	1394	}




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;

		}
		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);
    }
}





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;


		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

			int firstRowMoved, int lastRowMoved, int numberRowsMoved,
			int expectedAreaFirstRow, int expectedAreaLastRow) {

		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

		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 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);
	}
}

Return to bug 58348

Lines 1285-1294 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java (-8 / +18 lines)
1285		1285
1286	assertNotNull(wb);	1286	assertNotNull(wb);
1287	assertNotNull(sheet);	1287	assertNotNull(sheet);
1288	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));	1288	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
1289	assertNotNull(pivotTable);	1289	assertNotNull(pivotTable);
1290	assertTrue(wb.getPivotTables().size() > 0);	1290	assertTrue(wb.getPivotTables().size() > 0);
1291	XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("L5"), sheet);	1291	XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("L5"), sheet);
1292	assertNotNull(pivotTable2);	1292	assertNotNull(pivotTable2);
1293	assertTrue(wb.getPivotTables().size() > 1);	1293	assertTrue(wb.getPivotTables().size() > 1);
1294	}	1294	}
Lines 1300-1311 Link Here
1300		1300
1301	assertNotNull(wb);	1301	assertNotNull(wb);
1302	assertNotNull(sheet);	1302	assertNotNull(sheet);
1303	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));	1303	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
1304	assertNotNull(pivotTable);	1304	assertNotNull(pivotTable);
1305	assertTrue(wb.getPivotTables().size() > 0);	1305	assertTrue(wb.getPivotTables().size() > 0);
1306	assertNotNull(wb);	1306	assertNotNull(wb);
1307	XSSFSheet sheet2 = wb.createSheet();	1307	XSSFSheet sheet2 = wb.createSheet();
1308	XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"), sheet);	1308	XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet);
1309	assertNotNull(pivotTable2);	1309	assertNotNull(pivotTable2);
1310	assertTrue(wb.getPivotTables().size() > 1);	1310	assertTrue(wb.getPivotTables().size() > 1);
1311	}	1311	}
Lines 1317-1323 Link Here
1317		1317
1318	assertNotNull(wb);	1318	assertNotNull(wb);
1319	assertNotNull(sheet);	1319	assertNotNull(sheet);
1320	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));	1320	XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
1321	assertNotNull(pivotTable);	1321	assertNotNull(pivotTable);
1322	assertTrue(wb.getPivotTables().size() > 0);	1322	assertTrue(wb.getPivotTables().size() > 0);
1323	}	1323	}
Lines 1329-1335 Link Here
1329	XSSFSheet sheet2 = wb.createSheet();	1329	XSSFSheet sheet2 = wb.createSheet();
1330		1330
1331	XSSFPivotTable pivotTable = sheet2.createPivotTable	1331	XSSFPivotTable pivotTable = sheet2.createPivotTable
1332	(new AreaReference("A1:B2"), new CellReference("H5"), sheet1);	1332	(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet1);
1333	assertEquals(0, pivotTable.getRowLabelColumns().size());	1333	assertEquals(0, pivotTable.getRowLabelColumns().size());
1334		1334
1335	assertEquals(1, wb.getPivotTables().size());	1335	assertEquals(1, wb.getPivotTables().size());
Lines 1344-1350 Link Here
1344	XSSFSheet sheet2 = wb.createSheet();	1344	XSSFSheet sheet2 = wb.createSheet();
1345		1345
1346	XSSFPivotTable pivotTable = sheet2.createPivotTable	1346	XSSFPivotTable pivotTable = sheet2.createPivotTable
1347	(new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5"));	1347	(new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
1348	assertEquals(0, pivotTable.getRowLabelColumns().size());	1348	assertEquals(0, pivotTable.getRowLabelColumns().size());
1349	}	1349	}
1350		1350
Lines 1355-1361 Link Here
1355	XSSFSheet sheet2 = wb.createSheet();	1355	XSSFSheet sheet2 = wb.createSheet();
1356		1356
1357	try {	1357	try {
1358	sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5"), sheet2);	1358	sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet2);
1359	} catch(IllegalArgumentException e) {	1359	} catch(IllegalArgumentException e) {
1360	return;	1360	return;
1361	}	1361	}
Lines 1382-1385 Link Here
1382	XSSFSheet sheet = wb.createSheet();	1382	XSSFSheet sheet = wb.createSheet();
1383	assertNotNull(sheet.createComment());	1383	assertNotNull(sheet.createComment());
1384	}	1384	}
		1385
		1386	@Test
		1387	public void testCopyOneRow() throws IOException {
		1388	baseTestCopyOneRow("XSSFSheet.copyRows.xlsx");
		1389	}
		1390
		1391	@Test
		1392	public void testCopyMultipleRows() throws IOException {
		1393	baseTestCopyMultipleRows("XSSFSheet.copyRows.xlsx");
		1394	}
1385	}	1395	}

Lines 17-22 Link Here

(-)src/java/org/apache/poi/ss/formula/FormulaShifter.java (-60 / +190 lines)
17		17
18	package org.apache.poi.ss.formula;	18	package org.apache.poi.ss.formula;
19		19
		20	import org.apache.poi.ss.SpreadsheetVersion;
20	import org.apache.poi.ss.formula.ptg.Area2DPtgBase;	21	import org.apache.poi.ss.formula.ptg.Area2DPtgBase;
21	import org.apache.poi.ss.formula.ptg.Area3DPtg;	22	import org.apache.poi.ss.formula.ptg.Area3DPtg;
22	import org.apache.poi.ss.formula.ptg.Area3DPxg;	23	import org.apache.poi.ss.formula.ptg.Area3DPxg;
Lines 39-47 Link Here
39	*/	40	*/
40	public final class FormulaShifter {	41	public final class FormulaShifter {
41		42
42	static enum ShiftMode {	43	private static enum ShiftMode {
43	Row,	44	RowMove,
44	Sheet	45	RowCopy,
		46	SheetMove,
45	}	47	}
46		48
47	/**	49	/**
Lines 61-66 Link Here
61		63
62	private final int _srcSheetIndex;	64	private final int _srcSheetIndex;
63	private final int _dstSheetIndex;	65	private final int _dstSheetIndex;
		66	private final SpreadsheetVersion _version;
64		67
65	private final ShiftMode _mode;	68	private final ShiftMode _mode;
66		69
Lines 69-75 Link Here
69	*	72	*
70	* For example, this will be called on {@link org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)} }	73	* For example, this will be called on {@link org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)} }
71	*/	74	*/
72	private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove) {	75	private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove, ShiftMode mode, SpreadsheetVersion version) {
73	if (amountToMove == 0) {	76	if (amountToMove == 0) {
74	throw new IllegalArgumentException("amountToMove must not be zero");	77	throw new IllegalArgumentException("amountToMove must not be zero");
75	}	78	}
Lines 81-87 Link Here
81	_firstMovedIndex = firstMovedIndex;	84	_firstMovedIndex = firstMovedIndex;
82	_lastMovedIndex = lastMovedIndex;	85	_lastMovedIndex = lastMovedIndex;
83	_amountToMove = amountToMove;	86	_amountToMove = amountToMove;
84	_mode = ShiftMode.Row;	87	_mode = mode;
		88	_version = version;
85		89
86	_srcSheetIndex = _dstSheetIndex = -1;	90	_srcSheetIndex = _dstSheetIndex = -1;
87	}	91	}
Lines 94-108 Link Here
94	private FormulaShifter(int srcSheetIndex, int dstSheetIndex) {	98	private FormulaShifter(int srcSheetIndex, int dstSheetIndex) {
95	_externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1;	99	_externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1;
96	_sheetName = null;	100	_sheetName = null;
		101	_version = null;
97		102
98	_srcSheetIndex = srcSheetIndex;	103	_srcSheetIndex = srcSheetIndex;
99	_dstSheetIndex = dstSheetIndex;	104	_dstSheetIndex = dstSheetIndex;
100	_mode = ShiftMode.Sheet;	105	_mode = ShiftMode.SheetMove;
101	}	106	}
102		107
103	public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) {	108	/**
104	return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove);	109	* @deprecated As of 3.14 (September 2015), replaced by {@link #createForRowShift(int, String, int, int, int, SpreadsheetVersion)}
105	}	110	*
		111	* @param externSheetIndex
		112	* @param sheetName
		113	* @param firstMovedRowIndex
		114	* @param lastMovedRowIndex
		115	* @param numberOfRowsToMove
		116	* @return rowFormulaShifter
		117	*/
		118	@Deprecated
		119	public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) {
		120	return createForRowShift(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, SpreadsheetVersion.EXCEL97);
		121	}
		122
		123	public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove, SpreadsheetVersion version) {
		124	return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version);
		125	}
		126
		127	public static FormulaShifter createForRowCopy(int externSheetIndex, String sheetName, int firstCopiedRowIndex, int lastCopiedRowIndex, int rowOffset, SpreadsheetVersion version) {
		128	return new FormulaShifter(externSheetIndex, sheetName, firstCopiedRowIndex, lastCopiedRowIndex, rowOffset, ShiftMode.RowCopy, version);
		129	}
106		130
107	public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) {	131	public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) {
108	return new FormulaShifter(srcSheetIndex, dstSheetIndex);	132	return new FormulaShifter(srcSheetIndex, dstSheetIndex);
Lines 137-198 Link Here
137	return refsWereChanged;	161	return refsWereChanged;
138	}	162	}
139		163
140	private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) {	164	private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) {
141	switch(_mode){	165	switch(_mode){
142	case Row:	166	case RowMove:
143	return adjustPtgDueToRowMove(ptg, currentExternSheetIx);	167	return adjustPtgDueToRowMove(ptg, currentExternSheetIx);
144	case Sheet:	168	case RowCopy:
		169	//Scenarios:
		170	//* row copy on same sheet
		171	//* row copy between different sheets in the same workbook
		172	return adjustPtgDueToRowCopy(ptg);
		173	case SheetMove:
145	return adjustPtgDueToShiftMove(ptg);	174	return adjustPtgDueToShiftMove(ptg);
146	default:	175	default:
147	throw new IllegalStateException("Unsupported shift mode: " + _mode);	176	throw new IllegalStateException("Unsupported shift mode: " + _mode);
148	}	177	}
149	}	178	}
150	/**	179
151	* @return <code>true</code> if this Ptg needed to be changed	180
152	*/	181	/**
153	private Ptg adjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) {	182	* @return deleted ref ptg, in-place modified ptg, or null
154	if(ptg instanceof RefPtg) {	183	*/
155	if (currentExternSheetIx != _externSheetIndex) {	184	private Ptg adjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) {
156	// local refs on other sheets are unaffected	185	if(ptg instanceof RefPtg) {
157	return null;	186	if (currentExternSheetIx != _externSheetIndex) {
158	}	187	// local refs on other sheets are unaffected
159	RefPtg rptg = (RefPtg)ptg;	188	return null;
160	return rowMoveRefPtg(rptg);	189	}
161	}	190	RefPtg rptg = (RefPtg)ptg;
162	if(ptg instanceof Ref3DPtg) {	191	return rowMoveRefPtg(rptg);
163	Ref3DPtg rptg = (Ref3DPtg)ptg;	192	}
164	if (_externSheetIndex != rptg.getExternSheetIndex()) {	193	if(ptg instanceof Ref3DPtg) {
165	// only move 3D refs that refer to the sheet with cells being moved	194	Ref3DPtg rptg = (Ref3DPtg)ptg;
166	// (currentExternSheetIx is irrelevant)	195	if (_externSheetIndex != rptg.getExternSheetIndex()) {
167	return null;
168	}
169	return rowMoveRefPtg(rptg);
170	}
171	if(ptg instanceof Ref3DPxg) {
172	Ref3DPxg rpxg = (Ref3DPxg)ptg;
173	if (rpxg.getExternalWorkbookNumber() > 0 \|\|
174	! _sheetName.equals(rpxg.getSheetName())) {
175	// only move 3D refs that refer to the sheet with cells being moved	196	// only move 3D refs that refer to the sheet with cells being moved
176	return null;	197	// (currentExternSheetIx is irrelevant)
177	}	198	return null;
		199	}
		200	return rowMoveRefPtg(rptg);
		201	}
		202	if(ptg instanceof Ref3DPxg) {
		203	Ref3DPxg rpxg = (Ref3DPxg)ptg;
		204	if (rpxg.getExternalWorkbookNumber() > 0 \|\|
		205	! _sheetName.equals(rpxg.getSheetName())) {
		206	// only move 3D refs that refer to the sheet with cells being moved
		207	return null;
		208	}
178	return rowMoveRefPtg(rpxg);	209	return rowMoveRefPtg(rpxg);
179	}	210	}
180	if(ptg instanceof Area2DPtgBase) {	211	if(ptg instanceof Area2DPtgBase) {
181	if (currentExternSheetIx != _externSheetIndex) {	212	if (currentExternSheetIx != _externSheetIndex) {
182	// local refs on other sheets are unaffected	213	// local refs on other sheets are unaffected
183	return ptg;	214	return ptg;
184	}	215	}
185	return rowMoveAreaPtg((Area2DPtgBase)ptg);	216	return rowMoveAreaPtg((Area2DPtgBase)ptg);
186	}	217	}
187	if(ptg instanceof Area3DPtg) {	218	if(ptg instanceof Area3DPtg) {
188	Area3DPtg aptg = (Area3DPtg)ptg;	219	Area3DPtg aptg = (Area3DPtg)ptg;
189	if (_externSheetIndex != aptg.getExternSheetIndex()) {	220	if (_externSheetIndex != aptg.getExternSheetIndex()) {
190	// only move 3D refs that refer to the sheet with cells being moved	221	// only move 3D refs that refer to the sheet with cells being moved
191	// (currentExternSheetIx is irrelevant)	222	// (currentExternSheetIx is irrelevant)
192	return null;	223	return null;
193	}	224	}
194	return rowMoveAreaPtg(aptg);	225	return rowMoveAreaPtg(aptg);
195	}	226	}
196	if(ptg instanceof Area3DPxg) {	227	if(ptg instanceof Area3DPxg) {
197	Area3DPxg apxg = (Area3DPxg)ptg;	228	Area3DPxg apxg = (Area3DPxg)ptg;
198	if (apxg.getExternalWorkbookNumber() > 0 \|\|	229	if (apxg.getExternalWorkbookNumber() > 0 \|\|
Lines 202-209 Link Here
202	}	233	}
203	return rowMoveAreaPtg(apxg);	234	return rowMoveAreaPtg(apxg);
204	}	235	}
205	return null;	236	return null;
206	}	237	}
		238
		239
		240	/**
		241	* Call this on any ptg reference contained in a row of cells that was copied.
		242	* If the ptg reference is relative, the references will be shifted by the distance
		243	* that the rows were copied.
		244	* In the future similar functions could be written due to column copying or
		245	* individual cell copying. Just make sure to only call adjustPtgDueToRowCopy on
		246	* formula cells that are copied (unless row shifting, where references outside
		247	* of the shifted region need to be updated to reflect the shift, a copy is self-contained).
		248	*
		249	* @param ptg the ptg to shift
		250	* @return deleted ref ptg, in-place modified ptg, or null
		251	* If Ptg would be shifted off the first or last row of a sheet, return deleted ref
		252	* If Ptg needs to be changed, modifies Ptg in-place
		253	* If Ptg doesn't need to be changed, returns <code>null</code>
		254	*/
		255	private Ptg adjustPtgDueToRowCopy(Ptg ptg) {
		256	if(ptg instanceof RefPtg) {
		257	RefPtg rptg = (RefPtg)ptg;
		258	return rowCopyRefPtg(rptg);
		259	}
		260	if(ptg instanceof Ref3DPtg) {
		261	Ref3DPtg rptg = (Ref3DPtg)ptg;
		262	return rowCopyRefPtg(rptg);
		263	}
		264	if(ptg instanceof Ref3DPxg) {
		265	Ref3DPxg rpxg = (Ref3DPxg)ptg;
		266	return rowCopyRefPtg(rpxg);
		267	}
		268	if(ptg instanceof Area2DPtgBase) {
		269	return rowCopyAreaPtg((Area2DPtgBase)ptg);
		270	}
		271	if(ptg instanceof Area3DPtg) {
		272	Area3DPtg aptg = (Area3DPtg)ptg;
		273	return rowCopyAreaPtg(aptg);
		274	}
		275	if(ptg instanceof Area3DPxg) {
		276	Area3DPxg apxg = (Area3DPxg)ptg;
		277	return rowCopyAreaPtg(apxg);
		278	}
		279	return null;
		280	}
207		281
208	private Ptg adjustPtgDueToShiftMove(Ptg ptg) {	282	private Ptg adjustPtgDueToShiftMove(Ptg ptg) {
209	Ptg updatedPtg = null;	283	Ptg updatedPtg = null;
Lines 373-379 Link Here
373	throw new IllegalStateException("Situation not covered: (" + _firstMovedIndex + ", " +	447	throw new IllegalStateException("Situation not covered: (" + _firstMovedIndex + ", " +
374	_lastMovedIndex + ", " + _amountToMove + ", " + aFirstRow + ", " + aLastRow + ")");	448	_lastMovedIndex + ", " + _amountToMove + ", " + aFirstRow + ", " + aLastRow + ")");
375	}	449	}
		450
		451	/**
		452	* Modifies rptg in-place and return a reference to rptg if the cell reference
		453	* would move due to a row copy operation
		454	* Returns <code>null</code> or {@link #RefErrorPtg} if no change was made
		455	*
		456	* @param aptg
		457	* @return
		458	*/
		459	private Ptg rowCopyRefPtg(RefPtgBase rptg) {
		460	final int refRow = rptg.getRow();
		461	if (rptg.isRowRelative()) {
		462	final int destRowIndex = _firstMovedIndex + _amountToMove;
		463	if (destRowIndex < 0 \|\| _version.getLastRowIndex() < destRowIndex)
		464	return createDeletedRef(rptg);
		465	rptg.setRow(refRow + _amountToMove);
		466	return rptg;
		467	}
		468	return null;
		469	}
		470
		471	/**
		472	* Modifies aptg in-place and return a reference to aptg if the first or last row of
		473	* of the Area reference would move due to a row copy operation
		474	* Returns <code>null</code> or {@link #AreaErrPtg} if no change was made
		475	*
		476	* @param aptg
		477	* @return null, AreaErrPtg, or modified aptg
		478	*/
		479	private Ptg rowCopyAreaPtg(AreaPtgBase aptg) {
		480	boolean changed = false;
		481
		482	final int aFirstRow = aptg.getFirstRow();
		483	final int aLastRow = aptg.getLastRow();
		484
		485	if (aptg.isFirstRowRelative()) {
		486	final int destFirstRowIndex = aFirstRow + _amountToMove;
		487	if (destFirstRowIndex < 0 \|\| _version.getLastRowIndex() < destFirstRowIndex)
		488	return createDeletedRef(aptg);
		489	aptg.setFirstRow(destFirstRowIndex);
		490	changed = true;
		491	}
		492	if (aptg.isLastRowRelative()) {
		493	final int destLastRowIndex = aLastRow + _amountToMove;
		494	if (destLastRowIndex < 0 \|\| _version.getLastRowIndex() < destLastRowIndex)
		495	return createDeletedRef(aptg);
		496	aptg.setLastRow(destLastRowIndex);
		497	changed = true;
		498	}
		499	if (changed) {
		500	aptg.sortTopLeftToBottomRight();
		501	}
		502
		503	return changed ? aptg : null;
		504	}
376		505
		506
377	private static Ptg createDeletedRef(Ptg ptg) {	507	private static Ptg createDeletedRef(Ptg ptg) {
378	if (ptg instanceof RefPtg) {	508	if (ptg instanceof RefPtg) {
379	return new RefErrorPtg();	509	return new RefErrorPtg();

Lines 25-32 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFRow.java (+18 lines)
25	import org.apache.poi.hssf.record.RowRecord;	25	import org.apache.poi.hssf.record.RowRecord;
26	import org.apache.poi.ss.SpreadsheetVersion;	26	import org.apache.poi.ss.SpreadsheetVersion;
27	import org.apache.poi.ss.usermodel.Cell;	27	import org.apache.poi.ss.usermodel.Cell;
		28	import org.apache.poi.ss.usermodel.CellCopyPolicy;
28	import org.apache.poi.ss.usermodel.CellStyle;	29	import org.apache.poi.ss.usermodel.CellStyle;
29	import org.apache.poi.ss.usermodel.Row;	30	import org.apache.poi.ss.usermodel.Row;
		31	import org.apache.poi.util.Beta;
30	import org.apache.poi.util.Configurator;	32	import org.apache.poi.util.Configurator;
31		33
32	/**	34	/**
Lines 298-303 Link Here
298	}	300	}
299		301
300	/**	302	/**
		303	* Copy the cells from srcRow to this row
		304	* If this row is not a blank row, this will merge the two rows, overwriting
		305	* the cells in this row with the cells in srcRow
		306	* If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy
		307	* srcRow may be from a different sheet in the same workbook
		308	* @param srcRow the rows to copy from
		309	* @param cellCopyPolicy the policy to determine what gets copied
		310	*/
		311	@Beta
		312	@Override
		313	public void copyRowFrom(Row srcRow, CellCopyPolicy cellCopyPolicy) {
		314	// TODO Auto-generated method stub
		315	throw new UnsupportedOperationException();
		316	}
		317
		318	/**
301	* used internally to add a cell.	319	* used internally to add a cell.
302	*/	320	*/
303	private void addCell(HSSFCell cell) {	321	private void addCell(HSSFCell cell) {

Lines 17-33 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (+82 lines)
17		17
18	package org.apache.poi.xssf.usermodel;	18	package org.apache.poi.xssf.usermodel;
19		19
		20	import java.util.HashSet;
20	import java.util.Iterator;	21	import java.util.Iterator;
		22	import java.util.Set;
21	import java.util.TreeMap;	23	import java.util.TreeMap;
22		24
		25	import org.apache.poi.ss.formula.FormulaShifter;
23	import org.apache.poi.ss.SpreadsheetVersion;	26	import org.apache.poi.ss.SpreadsheetVersion;
24	import org.apache.poi.ss.usermodel.Cell;	27	import org.apache.poi.ss.usermodel.Cell;
		28	import org.apache.poi.ss.usermodel.CellCopyPolicy;
25	import org.apache.poi.ss.usermodel.CellStyle;	29	import org.apache.poi.ss.usermodel.CellStyle;
26	import org.apache.poi.ss.usermodel.Row;	30	import org.apache.poi.ss.usermodel.Row;
		31	import org.apache.poi.ss.util.CellRangeAddress;
27	import org.apache.poi.ss.util.CellReference;	32	import org.apache.poi.ss.util.CellReference;
		33	import org.apache.poi.util.Beta;
28	import org.apache.poi.util.Internal;	34	import org.apache.poi.util.Internal;
29	import org.apache.poi.xssf.model.CalculationChain;	35	import org.apache.poi.xssf.model.CalculationChain;
30	import org.apache.poi.xssf.model.StylesTable;	36	import org.apache.poi.xssf.model.StylesTable;
		37	import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
31	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;	38	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
32	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;	39	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
33		40
Lines 513-519 Link Here
513	}	520	}
514	setRowNum(rownum);	521	setRowNum(rownum);
515	}	522	}
		523
		524	/**
		525	* Copy the cells from srcRow to this row
		526	* If this row is not a blank row, this will merge the two rows, overwriting
		527	* the cells in this row with the cells in srcRow
		528	* If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy
		529	* srcRow may be from a different sheet in the same workbook
		530	* @param srcRow the rows to copy from
		531	* @param policy the policy to determine what gets copied
		532	*/
		533	@Beta
		534	@Override
		535	public void copyRowFrom(Row srcRow, CellCopyPolicy policy) {
		536	if (srcRow == null) {
		537	// srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy
		538	for (Cell destCell : this) {
		539	final XSSFCell srcCell = null;
		540	// FIXME: undo type casting
		541	((XSSFCell)destCell).copyCellFrom(srcCell, policy);
		542	}
516		543
		544	if (policy.isCopyMergedRegions()) {
		545	// Remove MergedRegions in dest row
		546	final int destRowNum = getRowNum();
		547	int index = 0;
		548	final Set<Integer> indices = new HashSet<Integer>();
		549	for (CellRangeAddress destRegion : getSheet().getMergedRegions()) {
		550	if (destRowNum == destRegion.getFirstRow() && destRowNum == destRegion.getLastRow()) {
		551	indices.add(index);
		552	}
		553	index++;
		554	}
		555	getSheet().removeMergedRegions(indices);
		556	}
		557
		558	if (policy.isCopyRowHeight()) {
		559	// clear row height
		560	setHeight((short)-1);
		561	}
		562
		563	}
		564	else {
		565	for(Cell c : srcRow){
		566	final XSSFCell srcCell = (XSSFCell)c;
		567	final XSSFCell destCell = createCell(srcCell.getColumnIndex(), srcCell.getCellType());
		568	destCell.copyCellFrom(srcCell, policy);
		569	}
		570
		571	final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
		572	final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
		573	final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
		574	final int srcRowNum = srcRow.getRowNum();
		575	final int destRowNum = getRowNum();
		576	final int rowDifference = destRowNum - srcRowNum;
		577	final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
		578	rowShifter.updateRowFormulas(this, shifter);
		579
		580	// Copy merged regions that are fully contained on the row
		581	// FIXME: is this something that rowShifter could be doing?
		582	if (policy.isCopyMergedRegions()) {
		583	for (CellRangeAddress srcRegion : srcRow.getSheet().getMergedRegions()) {
		584	if (srcRowNum == srcRegion.getFirstRow() && srcRowNum == srcRegion.getLastRow()) {
		585	CellRangeAddress destRegion = srcRegion.copy();
		586	destRegion.setFirstRow(destRowNum);
		587	destRegion.setLastRow(destRowNum);
		588	getSheet().addMergedRegion(destRegion);
		589	}
		590	}
		591	}
		592
		593	if (policy.isCopyRowHeight()) {
		594	setHeight(srcRow.getHeight());
		595	}
		596	}
		597	}
		598
517	public int getOutlineLevel() {	599	public int getOutlineLevel() {
518	return _row.getOutlineLevel();	600	return _row.getOutlineLevel();
519	}	601	}

Lines 76-81 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheet.java (+10 lines)
76	thrown.expectMessage("NotImplemented");	76	thrown.expectMessage("NotImplemented");
77	super.shiftMerged();	77	super.shiftMerged();
78	}	78	}
		79
		80	@Test
		81	public void testCopyOneRow() {
		82	//TODO: implement this test
		83	}
		84
		85	@Test
		86	public void testCopyMultipleRows() {
		87	//TODO: implement this test
		88	}
79		89
80	/**	90	/**
81	* Bug 35084: cloning cells with formula	91	* Bug 35084: cloning cells with formula

Lines 19-26 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (+3 lines)
19		19
20	import static org.junit.Assert.assertEquals;	20	import static org.junit.Assert.assertEquals;
21	import static org.junit.Assert.assertFalse;	21	import static org.junit.Assert.assertFalse;
		22	import static org.junit.Assert.assertNotEquals;
22	import static org.junit.Assert.assertNotNull;	23	import static org.junit.Assert.assertNotNull;
23	import static org.junit.Assert.assertNull;	24	import static org.junit.Assert.assertNull;
		25	import static org.junit.Assert.assertSame;
24	import static org.junit.Assert.fail;	26	import static org.junit.Assert.fail;
25		27
26	import java.io.IOException;	28	import java.io.IOException;
Lines 36-41 Link Here
36	import org.apache.poi.hssf.record.StringRecord;	38	import org.apache.poi.hssf.record.StringRecord;
37	import org.apache.poi.ss.usermodel.BaseTestCell;	39	import org.apache.poi.ss.usermodel.BaseTestCell;
38	import org.apache.poi.ss.usermodel.Cell;	40	import org.apache.poi.ss.usermodel.Cell;
		41	import org.apache.poi.ss.usermodel.CellCopyPolicy;
39	import org.apache.poi.ss.usermodel.FormulaError;	42	import org.apache.poi.ss.usermodel.FormulaError;
40	import org.apache.poi.ss.usermodel.RichTextString;	43	import org.apache.poi.ss.usermodel.RichTextString;
41	import org.apache.poi.ss.usermodel.Row;	44	import org.apache.poi.ss.usermodel.Row;

Lines 25-30 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFRow.java (+19 lines)
25	import org.apache.poi.hssf.record.BlankRecord;	25	import org.apache.poi.hssf.record.BlankRecord;
26	import org.apache.poi.hssf.record.RowRecord;	26	import org.apache.poi.hssf.record.RowRecord;
27	import org.apache.poi.ss.usermodel.BaseTestRow;	27	import org.apache.poi.ss.usermodel.BaseTestRow;
		28	import org.junit.Ignore;
28	import org.apache.poi.ss.SpreadsheetVersion;	29	import org.apache.poi.ss.SpreadsheetVersion;
29		30
30	/**	31	/**
Lines 139-142 Link Here
139		140
140	workbook.close();	141	workbook.close();
141	}	142	}
		143
		144	@Ignore
		145	@Override
		146	public void testCopyRowFrom() {
		147	//ignore this test
		148	}
		149
		150	@Ignore
		151	@Override
		152	public void testCopyRowFromExternalSheet() {
		153	//ignore this test
		154	}
		155
		156	@Ignore
		157	@Override
		158	public void testCopyRowOverwritesExistingRow() {
		159	//ignore this test
		160	}
142	}	161	}

Lines 19-24 Link Here

(-)src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java (-2 / +87 lines)
19		19
20	import junit.framework.TestCase;	20	import junit.framework.TestCase;
21		21
		22	import org.apache.poi.ss.SpreadsheetVersion;
22	import org.apache.poi.ss.formula.ptg.AreaErrPtg;	23	import org.apache.poi.ss.formula.ptg.AreaErrPtg;
23	import org.apache.poi.ss.formula.ptg.AreaPtg;	24	import org.apache.poi.ss.formula.ptg.AreaPtg;
24	import org.apache.poi.ss.formula.ptg.Ptg;	25	import org.apache.poi.ss.formula.ptg.Ptg;
Lines 74-79 Link Here
74		75
75	confirmAreaShift(aptg, 18, 22, 5, 10, 25); // simple expansion at bottom	76	confirmAreaShift(aptg, 18, 22, 5, 10, 25); // simple expansion at bottom
76	}	77	}
		78
		79	public void testCopyAreasSourceRowsRelRel() {
		80
		81	// all these operations are on an area ref spanning rows 10 to 20
		82	final AreaPtg aptg = createAreaPtg(10, 20, true, true);
		83
		84	confirmAreaCopy(aptg, 0, 30, 20, 30, 40, true);
		85	confirmAreaCopy(aptg, 15, 25, -15, -1, -1, true); //DeletedRef
		86	}
		87
		88	public void testCopyAreasSourceRowsRelAbs() {
		89
		90	// all these operations are on an area ref spanning rows 10 to 20
		91	final AreaPtg aptg = createAreaPtg(10, 20, true, false);
		92
		93	// Only first row should move
		94	confirmAreaCopy(aptg, 0, 30, 20, 20, 30, true);
		95	confirmAreaCopy(aptg, 15, 25, -15, -1, -1, true); //DeletedRef
		96	}
		97
		98	public void testCopyAreasSourceRowsAbsRel() {
		99	// aptg is part of a formula in a cell that was just copied to another row
		100	// aptg row references should be updated by the difference in rows that the cell was copied
		101	// No other references besides the cells that were involved in the copy need to be updated
		102	// this makes the row copy significantly different from the row shift, where all references
		103	// in the workbook need to track the row shift
		104
		105	// all these operations are on an area ref spanning rows 10 to 20
		106	final AreaPtg aptg = createAreaPtg(10, 20, false, true);
		107
		108	// Only last row should move
		109	confirmAreaCopy(aptg, 0, 30, 20, 10, 40, true);
		110	confirmAreaCopy(aptg, 15, 25, -15, 5, 10, true); //sortTopLeftToBottomRight swapped firstRow and lastRow because firstRow is absolute
		111	}
		112
		113	public void testCopyAreasSourceRowsAbsAbs() {
		114	// aptg is part of a formula in a cell that was just copied to another row
		115	// aptg row references should be updated by the difference in rows that the cell was copied
		116	// No other references besides the cells that were involved in the copy need to be updated
		117	// this makes the row copy significantly different from the row shift, where all references
		118	// in the workbook need to track the row shift
		119
		120	// all these operations are on an area ref spanning rows 10 to 20
		121	final AreaPtg aptg = createAreaPtg(10, 20, false, false);
		122
		123	//AbsFirstRow AbsLastRow references should't change when copied to a different row
		124	confirmAreaCopy(aptg, 0, 30, 20, 10, 20, false);
		125	confirmAreaCopy(aptg, 15, 25, -15, 10, 20, false);
		126	}
		127
77	/**	128	/**
78	* Tests what happens to an area ref when some outside rows are moved to overlap	129	* Tests what happens to an area ref when some outside rows are moved to overlap
79	* that area ref	130	* that area ref
Lines 97-103 Link Here
97	int firstRowMoved, int lastRowMoved, int numberRowsMoved,	148	int firstRowMoved, int lastRowMoved, int numberRowsMoved,
98	int expectedAreaFirstRow, int expectedAreaLastRow) {	149	int expectedAreaFirstRow, int expectedAreaLastRow) {
99		150
100	FormulaShifter fs = FormulaShifter.createForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved);	151	FormulaShifter fs = FormulaShifter.createForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved, SpreadsheetVersion.EXCEL2007);
101	boolean expectedChanged = aptg.getFirstRow() != expectedAreaFirstRow \|\| aptg.getLastRow() != expectedAreaLastRow;	152	boolean expectedChanged = aptg.getFirstRow() != expectedAreaFirstRow \|\| aptg.getLastRow() != expectedAreaLastRow;
102		153
103	AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method	154	AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method
Lines 113-119 Link Here
113	assertEquals(expectedAreaLastRow, copyPtg.getLastRow());	164	assertEquals(expectedAreaLastRow, copyPtg.getLastRow());
114		165
115	}	166	}
		167
		168
		169	private static void confirmAreaCopy(AreaPtg aptg,
		170	int firstRowCopied, int lastRowCopied, int rowOffset,
		171	int expectedFirstRow, int expectedLastRow, boolean expectedChanged) {
		172
		173	/*final boolean expectedChanged = (
		174	(aptg.isFirstRowRelative() && (aptg.getFirstRow() != expectedFirstRow)) \|\|
		175	(aptg.isLastRowRelative() && (aptg.getLastRow() != expectedLastRow))
		176	); //absolute row references should not change for row copy*/
		177
		178	final AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method
		179	final Ptg[] ptgs = { copyPtg, };
		180	final FormulaShifter fs = FormulaShifter.createForRowCopy(0, null, firstRowCopied, lastRowCopied, rowOffset, SpreadsheetVersion.EXCEL2007);
		181	final boolean actualChanged = fs.adjustFormula(ptgs, 0);
		182
		183	// DeletedAreaRef
		184	if (expectedFirstRow < 0 \|\| expectedLastRow < 0) {
		185	assertEquals("Reference should have shifted off worksheet, producing #REF! error: " + ptgs[0],
		186	AreaErrPtg.class, ptgs[0].getClass());
		187	return;
		188	}
		189
		190	assertEquals("Should this AreaPtg change due to row copy?", expectedChanged, actualChanged);
		191	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)
		192	assertEquals("AreaPtg first row", expectedFirstRow, copyPtg.getFirstRow());
		193	assertEquals("AreaPtg last row", expectedLastRow, copyPtg.getLastRow());
		194
		195	}
		196
116	private static AreaPtg createAreaPtg(int initialAreaFirstRow, int initialAreaLastRow) {	197	private static AreaPtg createAreaPtg(int initialAreaFirstRow, int initialAreaLastRow) {
117	return new AreaPtg(initialAreaFirstRow, initialAreaLastRow, 2, 5, false, false, false, false);	198	return createAreaPtg(initialAreaFirstRow, initialAreaLastRow, false, false);
118	}	199	}
		200
		201	private static AreaPtg createAreaPtg(int initialAreaFirstRow, int initialAreaLastRow, boolean firstRowRelative, boolean lastRowRelative) {
		202	return new AreaPtg(initialAreaFirstRow, initialAreaLastRow, 2, 5, firstRowRelative, lastRowRelative, false, false);
		203	}
119	}	204	}