From 757dbd1649f3e5e26d1e4ba0034a25632148b333 Mon Sep 17 00:00:00 2001 From: Javen O'Neal Date: Mon, 2 Nov 2015 02:50:33 +0000 Subject: [PATCH] bug 58348: add support for copying rows for XSSFWorkbooks git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1711885 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/usermodel/CellCopyPolicy.java | 186 +++++++++ .../apache/poi/xssf/usermodel/XSSFCell.java | 69 +++- .../apache/poi/xssf/usermodel/XSSFRow.java | 81 ++++ .../apache/poi/xssf/usermodel/XSSFSheet.java | 145 +++++++ .../usermodel/helpers/XSSFRowShifter.java | 10 +- .../poi/xssf/usermodel/TestXSSFCell.java | 62 +++ .../poi/xssf/usermodel/TestXSSFRow.java | 158 ++++++++ .../poi/xssf/usermodel/TestXSSFSheet.java | 369 ++++++++++++++++++ test-data/spreadsheet/XSSFSheet.copyRows.xlsx | Bin 0 -> 9883 bytes 9 files changed, 1077 insertions(+), 3 deletions(-) create mode 100644 src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java create mode 100644 test-data/spreadsheet/XSSFSheet.copyRows.xlsx diff --git a/src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java b/src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java new file mode 100644 index 0000000000..f7c1575af5 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java @@ -0,0 +1,186 @@ +package org.apache.poi.ss.usermodel; + +import org.apache.poi.util.Beta; + +@Beta +public class CellCopyPolicy implements Cloneable { + public static final boolean DEFAULT_COPY_CELL_VALUE_POLICY = true; + public static final boolean DEFAULT_COPY_CELL_STYLE_POLICY = true; + public static final boolean DEFAULT_COPY_CELL_FORMULA_POLICY = true; + public static final boolean DEFAULT_COPY_MERGED_REGIONS_POLICY = true; + public static final boolean DEFAULT_COPY_ROW_HEIGHT_POLICY = true; + public static final boolean DEFAULT_CONDENSE_ROWS_POLICY = false; + + private boolean copyCellValue = DEFAULT_COPY_CELL_VALUE_POLICY; + private boolean copyCellStyle = DEFAULT_COPY_CELL_STYLE_POLICY; + private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY; + private boolean copyMergedRegions = DEFAULT_COPY_MERGED_REGIONS_POLICY; + private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY; + private boolean condenseRows = DEFAULT_CONDENSE_ROWS_POLICY; + + /** + * Default CellCopyPolicy, uses default policy + * For custom CellCopyPolicy, use {@link #Builder} class + */ + public CellCopyPolicy() { } + + // should builder be replaced with CellCopyPolicy setters that return the object + // to allow setters to be chained together? + // policy.setCopyCellValue(true).setCopyCellStyle(true) + private CellCopyPolicy(Builder builder) { + copyCellValue = builder.copyCellValue; + copyCellStyle = builder.copyCellStyle; + copyCellFormula = builder.copyCellFormula; + copyMergedRegions = builder.copyMergedRegions; + copyRowHeight = builder.copyRowHeight; + condenseRows = builder.condenseRows; + } + + public static class Builder { + private boolean copyCellValue = DEFAULT_COPY_CELL_VALUE_POLICY; + private boolean copyCellStyle = DEFAULT_COPY_CELL_STYLE_POLICY; + private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY; + private boolean copyMergedRegions = DEFAULT_COPY_MERGED_REGIONS_POLICY; + private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY; + private boolean condenseRows = DEFAULT_CONDENSE_ROWS_POLICY; + + /** + * Builder class for CellCopyPolicy + */ + public Builder() { + } + + public Builder cellValue(boolean copyCellValue) { + this.copyCellValue = copyCellValue; + return this; + } + public Builder cellStyle(boolean copyCellStyle) { + this.copyCellStyle = copyCellStyle; + return this; + } + public Builder cellFormula(boolean copyCellFormula) { + this.copyCellFormula = copyCellFormula; + return this; + } + public Builder mergedRegions(boolean copyMergedRegions) { + this.copyMergedRegions = copyMergedRegions; + return this; + } + public Builder rowHeight(boolean copyRowHeight) { + this.copyRowHeight = copyRowHeight; + return this; + } + public Builder condenseRows(boolean condenseRows) { + this.condenseRows = condenseRows; + return this; + } + public CellCopyPolicy build() { + return new CellCopyPolicy(this); + } + } + + private Builder createBuilder() { + final Builder builder = new Builder() + .cellValue(copyCellValue) + .cellStyle(copyCellStyle) + .cellFormula(copyCellFormula) + .mergedRegions(copyMergedRegions) + .rowHeight(copyRowHeight) + .condenseRows(condenseRows); + return builder; + } + + @Override + public CellCopyPolicy clone() { + return createBuilder().build(); + } + + /** + * @return the copyCellValue + */ + public boolean isCopyCellValue() { + return copyCellValue; + } + + /** + * @param copyCellValue the copyCellValue to set + */ + public void setCopyCellValue(boolean copyCellValue) { + this.copyCellValue = copyCellValue; + } + + /** + * @return the copyCellStyle + */ + public boolean isCopyCellStyle() { + return copyCellStyle; + } + + /** + * @param copyCellStyle the copyCellStyle to set + */ + public void setCopyCellStyle(boolean copyCellStyle) { + this.copyCellStyle = copyCellStyle; + } + + /** + * @return the copyCellFormula + */ + public boolean isCopyCellFormula() { + return copyCellFormula; + } + + /** + * @param copyCellFormula the copyCellFormula to set + */ + public void setCopyCellFormula(boolean copyCellFormula) { + this.copyCellFormula = copyCellFormula; + } + + /** + * @return the copyMergedRegions + */ + public boolean isCopyMergedRegions() { + return copyMergedRegions; + } + + /** + * @param copyMergedRegions the copyMergedRegions to set + */ + public void setCopyMergedRegions(boolean copyMergedRegions) { + this.copyMergedRegions = copyMergedRegions; + } + + /** + * @return the copyRowHeight + */ + public boolean isCopyRowHeight() { + return copyRowHeight; + } + + /** + * @param copyRowHeight the copyRowHeight to set + */ + public void setCopyRowHeight(boolean copyRowHeight) { + this.copyRowHeight = copyRowHeight; + } + + /** + * If condenseRows is true, a discontinuities in srcRows will be removed when copied to destination + * For example: + * Sheet.copyRows({Row(1), Row(2), Row(5)}, 11, policy) results in rows 1, 2, and 5 + * being copied to rows 11, 12, and 13 if condenseRows is True, or rows 11, 11, 15 if condenseRows is false + * @return the condenseRows + */ + public boolean isCondenseRows() { + return condenseRows; + } + + /** + * @param condenseRows the condenseRows to set + */ + public void setCondenseRows(boolean condenseRows) { + this.condenseRows = condenseRows; + } + +} diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index aa1925920f..9ee050b581 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -30,6 +30,7 @@ import org.apache.poi.ss.formula.SharedFormula; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DataFormatter; @@ -40,6 +41,7 @@ import org.apache.poi.ss.usermodel.RichTextString; 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; @@ -115,6 +117,70 @@ public final class XSSFCell implements Cell { _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 @@ -523,8 +589,7 @@ public final class XSSFCell implements Cell { * * @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) + * @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) { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java index 197228d5aa..a3d647c9b4 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java @@ -17,17 +17,24 @@ package org.apache.poi.xssf.usermodel; +import java.util.HashSet; import java.util.Iterator; +import java.util.Set; import java.util.TreeMap; +import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.util.Beta; import org.apache.poi.util.Internal; import org.apache.poi.xssf.model.CalculationChain; import org.apache.poi.xssf.model.StylesTable; +import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; @@ -513,6 +520,80 @@ public class XSSFRow implements Row, Comparable { } 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 + 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: remove type casting when copyCellFrom(Cell, CellCopyPolicy) is added to Cell interface + ((XSSFCell)destCell).copyCellFrom(srcCell, policy); + } + + if (policy.isCopyMergedRegions()) { + // Remove MergedRegions in dest row + final int destRowNum = getRowNum(); + int index = 0; + final Set indices = new HashSet(); + for (CellRangeAddress destRegion : getSheet().getMergedRegions()) { + if (destRowNum == destRegion.getFirstRow() && destRowNum == destRegion.getLastRow()) { + indices.add(index); + } + index++; + } + getSheet().removeMergedRegions(indices); + } + + if (policy.isCopyRowHeight()) { + // clear row height + setHeight((short)-1); + } + + } + else { + for (final 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(); diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index b334983925..e9ef3a2ffd 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -51,6 +51,7 @@ import org.apache.poi.ss.SpreadsheetVersion; 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; @@ -1279,6 +1280,37 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { public XSSFRow getRow(int rownum) { return _rows.get(rownum); } + + /** + * returns all rows between startRow and endRow, inclusive. + * Rows between startRow and endRow that haven't been created are not included + * in result unless createRowIfMissing is true + * + * @param startRow the first row number in this sheet to return + * @param endRow the last row number in this sheet to return + * @param createRowIfMissing + * @return + * @throws IllegalArgumentException if startRowNum and endRowNum are not in ascending order + */ + private List getRows(int startRowNum, int endRowNum, boolean createRowIfMissing) { + if (startRowNum > endRowNum) { + throw new IllegalArgumentException("getRows: startRowNum must be less than or equal to endRowNum"); + } + final List rows = new ArrayList(); + if (createRowIfMissing) { + for (int i = startRowNum; i <= endRowNum; i++) { + XSSFRow row = getRow(i); + if (row == null) { + row = createRow(i); + } + rows.add(row); + } + } + else { + rows.addAll(_rows.subMap(startRowNum, endRowNum+1).values()); + } + return rows; + } /** * Horizontal page break information used for print layout view, page layout view, drawing print breaks in normal @@ -2599,6 +2631,119 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { getSheetTypeSheetView().setZoomScale(scale); } + + /** + * 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 + public void copyRows(List srcRows, int destStartRow, CellCopyPolicy policy) { + if (srcRows == null || srcRows.size() == 0) { + throw new IllegalArgumentException("No rows to copy"); + } + final Row srcStartRow = srcRows.get(0); + final Row srcEndRow = srcRows.get(srcRows.size() - 1); + + if (srcStartRow == null) { + throw new IllegalArgumentException("copyRows: First row cannot be null"); + } + + final int srcStartRowNum = srcStartRow.getRowNum(); + final int srcEndRowNum = srcEndRow.getRowNum(); + + // check row numbers to make sure they are continuous and increasing (monotonic) + // and srcRows does not contain null rows + for (int index=1; index < srcRows.size(); index++) { + final Row prevRow = srcRows.get(index-1); + final Row curRow = srcRows.get(index); + if (prevRow == null || curRow == null) { + throw new IllegalArgumentException("srcRows may not contain null rows. Found null row at index " + + index + " after Row " + prevRow.getRowNum() + "."); + //} else if (curRow.getRowNum() != prevRow.getRowNum() + 1) { + // throw new IllegalArgumentException("srcRows must contain continuously increasing row numbers. " + + // "Got srcRows[" + (index-1) + "]=Row " + prevRow.getRowNum() + ", srcRows[" + index + "]=Row " + curRow.getRowNum() + "."); + // FIXME: assumes row objects belong to non-null sheets and sheets belong to non-null workbooks. + } else if (srcStartRow.getSheet().getWorkbook() != curRow.getSheet().getWorkbook()) { + throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet in the same workbook." + + "Expected all rows from same workbook (" + srcStartRow.getSheet().getWorkbook() + "). " + + "Got srcRows[" + index + "] from different workbook (" + curRow.getSheet().getWorkbook() + ")."); + } else if (srcStartRow.getSheet() != curRow.getSheet()) { + throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet. " + + "Expected all rows from " + srcStartRow.getSheet().getSheetName() + ". " + + "Got srcRows[" + index + "] from " + curRow.getSheet().getSheetName()); + } + } + + // FIXME: is special behavior needed if srcRows and destRows belong to the same sheets and the regions overlap? + + final CellCopyPolicy options = policy.clone(); + // avoid O(N^2) performance scanning through all regions for each row + // merged regions will be copied after all the rows have been copied + options.setCopyMergedRegions(false); + + // FIXME: if srcRows contains gaps or null values, clear out those rows that will be overwritten + // how will this work with merging (copy just values, leave cell styles in place?) + + int r = destStartRow; + for (Row srcRow : srcRows) { + int destRowNum; + if (policy.isCondenseRows()) { + destRowNum = r++; + } else { + final int shift = (srcRow.getRowNum() - srcStartRowNum); + destRowNum = destStartRow + shift; + } + //removeRow(destRowNum); //this probably clears all external formula references to destRow, causing unwanted #REF! errors + final XSSFRow 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, cellCopyPolicy) + * + * @param srcStartRow the index of the first row to copy the cells from in this sheet + * @param srcEndRow the index of the last row to copy the cells from in this sheet + * @param destStartRow the index of the first row to copy the cells to in this sheet + * @param cellCopyPolicy the policy to use to determine how cells are copied + */ + @Beta + public void copyRows(int srcStartRow, int srcEndRow, int destStartRow, CellCopyPolicy cellCopyPolicy) { + final List srcRows = getRows(srcStartRow, srcEndRow, false); //FIXME: should be false, no need to create rows where src is only to copy them to dest + copyRows(srcRows, destStartRow, cellCopyPolicy); + } + /** * Shifts rows between startRow and endRow n number of rows. * If you use a negative number, it will shift rows up. diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java index 02eabd46e7..49944c4649 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java @@ -35,6 +35,7 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.util.Internal; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; import org.apache.poi.xssf.usermodel.XSSFCell; @@ -164,7 +165,14 @@ public final class XSSFRowShifter { } } - private void updateRowFormulas(XSSFRow row, FormulaShifter shifter) { + /** + * Update the formulas in specified row using the formula shifting policy specified by shifter + * + * @param row the row to update the formulas on + * @param shifter the formula shifting policy + */ + @Internal + public void updateRowFormulas(XSSFRow row, FormulaShifter shifter) { for (Cell c : row) { XSSFCell cell = (XSSFCell) c; diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java index e1deb4d89f..14510c83a7 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java @@ -18,6 +18,7 @@ package org.apache.poi.xssf.usermodel; import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; import static org.junit.Assert.assertTrue; @@ -28,6 +29,8 @@ import org.apache.poi.hssf.HSSFITestDataProvider; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.BaseTestXCell; 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.DataFormatter; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; @@ -494,4 +497,63 @@ public final class TestXSSFCell extends BaseTestXCell { wb.close(); } } + + private XSSFCell srcCell, destCell; //used for testCopyCellFrom_CellCopyPolicy + + @Test + public final void testCopyCellFrom_CellCopyPolicy_default() { + 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() { + 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"); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_style() { + 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() { + @SuppressWarnings("resource") + final XSSFWorkbook wb = new XSSFWorkbook(); + final XSSFRow 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); + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFRow.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFRow.java index 79a820b688..67b5038576 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFRow.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFRow.java @@ -17,10 +17,18 @@ package org.apache.poi.xssf.usermodel; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertSame; + import java.io.IOException; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.BaseTestRow; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyPolicy; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.XSSFITestDataProvider; import org.junit.Test; @@ -42,4 +50,154 @@ public final class TestXSSFRow extends BaseTestRow { public void testCellBounds() throws IOException { baseTestCellBounds(SpreadsheetVersion.EXCEL2007.getLastColumnIndex()); } + + public void testCopyRowFrom() throws IOException { + final XSSFWorkbook workbook = new XSSFWorkbook(); + final XSSFSheet sheet = workbook.createSheet("test"); + final XSSFRow srcRow = sheet.createRow(0); + srcRow.createCell(0).setCellValue("Hello"); + final XSSFRow destRow = sheet.createRow(1); + + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + assertNotNull(destRow.getCell(0)); + assertEquals("Hello", destRow.getCell(0).getStringCellValue()); + + workbook.close(); + } + + public void testCopyRowFromExternalSheet() throws IOException { + final XSSFWorkbook workbook = new XSSFWorkbook(); + final Sheet srcSheet = workbook.createSheet("src"); + final XSSFSheet destSheet = workbook.createSheet("dest"); + workbook.createSheet("other"); + + final Row srcRow = srcSheet.createRow(0); + int col = 0; + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("B5"); + srcRow.createCell(col++).setCellFormula("src!B5"); + srcRow.createCell(col++).setCellFormula("dest!B5"); + srcRow.createCell(col++).setCellFormula("other!B5"); + + //Test 2D and 3D Ref Ptgs with absolute row + srcRow.createCell(col++).setCellFormula("B$5"); + srcRow.createCell(col++).setCellFormula("src!B$5"); + srcRow.createCell(col++).setCellFormula("dest!B$5"); + srcRow.createCell(col++).setCellFormula("other!B$5"); + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("SUM(B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(src!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(dest!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(other!B5:D$5)"); + + ////////////////// + + final XSSFRow destRow = destSheet.createRow(1); + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + + ////////////////// + + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + col = 0; + Cell cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("RefPtg", "B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "src!B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "dest!B6", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "other!B6", cell.getCellFormula()); + + ///////////////////////////////////////////// + + //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change) + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("RefPtg", "B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "src!B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "dest!B$5", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Ref3DPtg", "other!B$5", cell.getCellFormula()); + + ////////////////////////////////////////// + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + // Note: absolute row changes from last cell to first cell in order + // to maintain topLeft:bottomRight order + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Area2DPtg", "SUM(B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("Area3DPtg", "SUM(src!B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(destRow.getCell(6)); + assertEquals("Area3DPtg", "SUM(dest!B$5:D6)", cell.getCellFormula()); + + cell = destRow.getCell(col++); + assertNotNull(destRow.getCell(7)); + assertEquals("Area3DPtg", "SUM(other!B$5:D6)", cell.getCellFormula()); + + workbook.close(); + } + + public void testCopyRowOverwritesExistingRow() throws IOException { + final XSSFWorkbook workbook = new XSSFWorkbook(); + final XSSFSheet sheet1 = workbook.createSheet("Sheet1"); + final Sheet sheet2 = workbook.createSheet("Sheet2"); + + final Row srcRow = sheet1.createRow(0); + final XSSFRow destRow = sheet1.createRow(1); + final Row observerRow = sheet1.createRow(2); + final Row externObserverRow = sheet2.createRow(0); + + srcRow.createCell(0).setCellValue("hello"); + srcRow.createCell(1).setCellValue("world"); + destRow.createCell(0).setCellValue(5.0); //A2 -> 5.0 + destRow.createCell(1).setCellFormula("A1"); // B2 -> A1 -> "hello" + observerRow.createCell(0).setCellFormula("A2"); // A3 -> A2 -> 5.0 + observerRow.createCell(1).setCellFormula("B2"); // B3 -> B2 -> A1 -> "hello" + externObserverRow.createCell(0).setCellFormula("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0 + + // overwrite existing destRow with row-copy of srcRow + destRow.copyRowFrom(srcRow, new CellCopyPolicy()); + + // copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer + // to the new row (and allow the old row to be garbage collected) + // this is mostly so existing references to rows that are overwritten are updated + // rather than allowing users to continue updating rows that are no longer part of the sheet + assertSame("existing references to srcRow are still valid", srcRow, sheet1.getRow(0)); + assertSame("existing references to destRow are still valid", destRow, sheet1.getRow(1)); + assertSame("existing references to observerRow are still valid", observerRow, sheet1.getRow(2)); + assertSame("existing references to externObserverRow are still valid", externObserverRow, sheet2.getRow(0)); + + // Make sure copyRowFrom actually copied row (this is tested elsewhere) + assertEquals(Cell.CELL_TYPE_STRING, destRow.getCell(0).getCellType()); + assertEquals("hello", destRow.getCell(0).getStringCellValue()); + + // We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region + assertEquals("references to overwritten cells are unmodified", "A2", observerRow.getCell(0).getCellFormula()); + assertEquals("references to overwritten cells are unmodified", "B2", observerRow.getCell(1).getCellFormula()); + assertEquals("references to overwritten cells are unmodified", "Sheet1!A2", externObserverRow.getCell(0).getCellFormula()); + + workbook.close(); + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java index 86f54ee848..5fd62aa498 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -29,6 +29,9 @@ import static org.junit.Assert.assertTrue; import java.io.IOException; import java.util.Arrays; +import java.util.Calendar; +import java.util.Date; +import java.util.GregorianCalendar; import java.util.HashSet; import java.util.List; import java.util.Set; @@ -41,12 +44,15 @@ import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.AutoFilter; import org.apache.poi.ss.usermodel.BaseTestSheet; 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.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.ss.util.CellUtil; import org.apache.poi.xssf.XSSFITestDataProvider; import org.apache.poi.xssf.XSSFTestDataSamples; import org.apache.poi.xssf.model.CalculationChain; @@ -1439,4 +1445,367 @@ public final class TestXSSFSheet extends BaseTestSheet { assertNotNull(sheet.createComment()); wb.close(); } + + protected void testCopyOneRow(String copyRowsTestWorkbook) throws IOException { + final double FLOAT_PRECISION = 1e-9; + final XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook); + final XSSFSheet sheet = workbook.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(1, 1, 6, defaultCopyPolicy); + + final Row srcRow = sheet.getRow(1); + final Row destRow = sheet.getRow(6); + int col = 0; + Cell cell; + + cell = CellUtil.getCell(destRow, col++); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Style] B7 cell value", "Red", cell.getStringCellValue()); + assertEquals("[Style] B7 cell style", CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle()); + + // Blank + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Blank] C7 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + // Error + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Error] D7 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + final FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D7 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + // Date + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Date] E7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + final Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals("[Date] E7 cell value", date, cell.getDateCellValue()); + + // Boolean + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Boolean] F7 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F7 cell value", true, cell.getBooleanCellValue()); + + // String + cell = CellUtil.getCell(destRow, col++); + assertEquals("[String] G7 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G7 cell value", "Hello", cell.getStringCellValue()); + + // Int + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Int] H7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H7 cell value", 15, (int) cell.getNumericCellValue()); + + // Float + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Float] I7 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I7 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula + cell = CellUtil.getCell(destRow, col++); + assertEquals("J7", new CellReference(cell).formatAsString()); + assertEquals("[Cell Formula] J7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J7 cell formula", "5+2", cell.getCellFormula()); + System.out.println("Cell formula evaluation currently unsupported"); + + // Cell Formula with Reference + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow, col++); + assertEquals("K7", new CellReference(cell).formatAsString()); + assertEquals("[Cell Formula with Reference] K7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K7 cell formula", + "J7+H$2", cell.getCellFormula()); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell formula", + "G7&\" \"&G8", cell.getCellFormula()); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Cell Formula with Area Reference] M7 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M7 cell formula", + "SUM(H7:I8)", cell.getCellFormula()); + + // Array Formula + cell = CellUtil.getCell(destRow, col++); + System.out.println("Array formulas currently unsupported"); + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + /* + assertEquals("[Array Formula] N7 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N7 cell formula", "{SUM(H7:J7*{1,2,3})}", cell.getCellFormula()); + */ + + // Data Format + cell = CellUtil.getCell(destRow, col++); + assertEquals("[Data Format] O7 cell type;", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Data Format] O7 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION); + //FIXME: currently fails + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals("[Data Format] O7 data format", moneyFormat, cell.getCellStyle().getDataFormatString()); + + // Merged + cell = CellUtil.getCell(destRow, col); + assertEquals("[Merged] P7:Q7 cell value", + "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P7:Q7 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7"))); + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow, col); + // Note: this behavior deviates from Microsoft Excel, + // which will not overwrite a cell in destination row if merged region extends beyond the copied row. + // The Excel way would require: + //assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue()); + //assertFalse("[Merged across multiple rows] R7:S8 merged region", + // sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); + // As currently implemented, cell value is copied but merged region is not copied + assertEquals("[Merged across multiple rows] R7:S8 cell value", + "Merged cells across multiple rows", cell.getStringCellValue()); + assertFalse("[Merged across multiple rows] R7:S7 merged region (one row)", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7"))); //shouldn't do 1-row merge + assertFalse("[Merged across multiple rows] R7:S8 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); //shouldn't do 2-row merge + + // Make sure other rows are blank (off-by-one errors) + assertNull(sheet.getRow(5)); + assertNull(sheet.getRow(7)); + } + + public void testCopyMultipleRows(String copyRowsTestWorkbook) throws IOException { + final double FLOAT_PRECISION = 1e-9; + final XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook); + final XSSFSheet sheet = workbook.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(0, 3, 8, defaultCopyPolicy); + + @SuppressWarnings("unused") + final Row srcHeaderRow = sheet.getRow(0); + final Row srcRow1 = sheet.getRow(1); + final Row srcRow2 = sheet.getRow(2); + final Row srcRow3 = sheet.getRow(3); + final Row destHeaderRow = sheet.getRow(8); + final Row destRow1 = sheet.getRow(9); + final Row destRow2 = sheet.getRow(10); + final Row destRow3 = sheet.getRow(11); + int col = 0; + Cell cell; + + // Header row should be copied + assertNotNull(destHeaderRow); + + // Data rows + cell = CellUtil.getCell(destRow1, col); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Style] B10 cell value", "Red", cell.getStringCellValue()); + assertEquals("[Style] B10 cell style", CellUtil.getCell(srcRow1, 1).getCellStyle(), cell.getCellStyle()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Style] B11 cell value", "Blue", cell.getStringCellValue()); + assertEquals("[Style] B11 cell style", CellUtil.getCell(srcRow2, 1).getCellStyle(), cell.getCellStyle()); + + // Blank + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Blank] C10 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Blank] C11 cell type", Cell.CELL_TYPE_BLANK, cell.getCellType()); + + // Error + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Error] D10 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D10 cell value", FormulaError.NA, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Error] D11 cell type", Cell.CELL_TYPE_ERROR, cell.getCellType()); + error = FormulaError.forInt(cell.getErrorCellValue()); + assertEquals("[Error] D11 cell value", FormulaError.NAME, error); //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + + // Date + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Date] E10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + Date date = new GregorianCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals("[Date] E10 cell value", date, cell.getDateCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Date] E11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + date = new GregorianCalendar(2000, Calendar.JANUARY, 2).getTime(); + assertEquals("[Date] E11 cell value", date, cell.getDateCellValue()); + + // Boolean + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Boolean] F10 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F10 cell value", true, cell.getBooleanCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Boolean] F11 cell type", Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals("[Boolean] F11 cell value", false, cell.getBooleanCellValue()); + + // String + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[String] G10 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G10 cell value", "Hello", cell.getStringCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[String] G11 cell type", Cell.CELL_TYPE_STRING, cell.getCellType()); + assertEquals("[String] G11 cell value", "World", cell.getStringCellValue()); + + // Int + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Int] H10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H10 cell value", 15, (int) cell.getNumericCellValue()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Int] H11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Int] H11 cell value", 42, (int) cell.getNumericCellValue()); + + // Float + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Float] I10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I10 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Float] I11 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Float] I11 cell value", 5.5, cell.getNumericCellValue(), FLOAT_PRECISION); + + // Cell Formula + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula] J10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J10 cell formula", "5+2", cell.getCellFormula()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula] J11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula] J11 cell formula", "6+18", cell.getCellFormula()); + + // Cell Formula with Reference + col++; + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Reference] K10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K10 cell formula", + "J10+H$2", cell.getCellFormula()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Reference] K11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference] K11 cell formula", "J11+H$2", cell.getCellFormula()); + + // Cell Formula with Reference spanning multiple rows + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L10 cell formula", + "G10&\" \"&G11", cell.getCellFormula()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Reference spanning multiple rows] L11 cell formula", + "G11&\" \"&G12", cell.getCellFormula()); + + // Cell Formula with Area Reference + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Cell Formula with Area Reference] M10 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M10 cell formula", + "SUM(H10:I11)", cell.getCellFormula()); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Cell Formula with Area Reference] M11 cell type", + Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Cell Formula with Area Reference] M11 cell formula", + "SUM($H$3:I10)", cell.getCellFormula()); //Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order + + // Array Formula + col++; + cell = CellUtil.getCell(destRow1, col); + System.out.println("Array formulas currently unsupported"); + /* + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + assertEquals("[Array Formula] N10 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N10 cell formula", "{SUM(H10:J10*{1,2,3})}", cell.getCellFormula()); + + cell = CellUtil.getCell(destRow2, col); + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + assertEquals("[Array Formula] N11 cell type", Cell.CELL_TYPE_FORMULA, cell.getCellType()); + assertEquals("[Array Formula] N11 cell formula", "{SUM(H11:J11*{1,2,3})}", cell.getCellFormula()); + */ + + // Data Format + col++; + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Data Format] O10 cell type", Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + assertEquals("[Data Format] O10 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION); + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals("[Data Format] O10 cell data format", moneyFormat, cell.getCellStyle().getDataFormatString()); + + // Merged + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Merged] P10:Q10 cell value", + "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P10:Q10 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P10:Q10"))); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("[Merged] P11:Q11 cell value", "Merged cells", cell.getStringCellValue()); + assertTrue("[Merged] P11:Q11 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P11:Q11"))); + + // Should Q10/Q11 be checked? + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow1, col); + assertEquals("[Merged across multiple rows] R10:S11 cell value", + "Merged cells across multiple rows", cell.getStringCellValue()); + assertTrue("[Merged across multiple rows] R10:S11 merged region", + sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R10:S11"))); + + // Row 3 (zero-based) was empty, so Row 11 (zero-based) should be empty too. + if (srcRow3 == null) { + assertNull("Row 3 was empty, so Row 11 should be empty", destRow3); + } + + // Make sure other rows are blank (off-by-one errors) + assertNull("Off-by-one lower edge case", sheet.getRow(7)); //one row above destHeaderRow + assertNull("Off-by-one upper edge case", sheet.getRow(12)); //one row below destRow3 + } + + @Test + public void testCopyOneRow() throws IOException { + testCopyOneRow("XSSFSheet.copyRows.xlsx"); + } + + @Test + public void testCopyMultipleRows() throws IOException { + testCopyMultipleRows("XSSFSheet.copyRows.xlsx"); + } } \ No newline at end of file diff --git a/test-data/spreadsheet/XSSFSheet.copyRows.xlsx b/test-data/spreadsheet/XSSFSheet.copyRows.xlsx new file mode 100644 index 0000000000000000000000000000000000000000..250b5d74ebf801e3b539b6a34c06eb29f5cb4456 GIT binary patch literal 9883 zcmeHt^+R0A()JMC87#PKaCZ;x?hpb5cX#)o!GgQH1^3`C!Gc5Z;0~F0vb*=&-RypU z!M*2)({tu@)l7HSQ(aY0E6GAaVFF+PZ~y>+6p%%4ZD$1m0H8qw02lzc7rJ70w$7%u z&W36p_NGqyjP5qpBzaITXmS8A!2SPs{TI(bMPiRaA2U+usZ_gI5}jqegD_m>icvR^ ziiL=T;yWILd>%Lc%F6aTW6_cd(W63T{6|n%vny4-zHuD`)Z9d}wcdLz7!BUSlQNUj z(Y>cEQ=Aa>UT#Fn709}!s|U-vuU_5n>ll(NVy1=cW9mXB-fZ$~Etl$7Vd;yoVn3^hZvVR#s;V+Urjv7#@IUIZ|Aq$L1w5xpBO&}&j z7?ESqvWn#j?r|>`YrFi^!*ZG=gV7LZO)BaQL^e9h+{|XI5X#VfxhKv`qpxlOKQb&Y zhD9?4i-ut3znDr^DNf$-{7rB>J21Z3l%HA3tCQkuAMn5rQgLz=m>?HRI?w?@(0VN0 zFxi3)F{g{fjvEB|H1~Zou6M{oo`KaKsL;CiRBHFK$D;rq<;>6j6lhcLq$rA(7dji~ z%JDcHUj*915cBGyQ6gO9Up1R$0-F~I1PM_38%7(|naQufZ{)xbLZIV$F4<4hCAQW5RV}Qm(3($NIfox6 z)BP5biw=;5!g=J5XFg&LIlVPos`4JPCcVDLQ&%}N(2zYX~jG~MaI>Z26u z@NQZq)h6B7-#(FB?k%jY&(5ip3h!`toNXP8RPQZOMB9p=Tjd=EF`gNgi8MlBg{!Ht z74+%n(Q)$dZgW}dOiE}-=*7JS%{EQT`0e61X@cP;Z-ZX+eYGn@T2=}pbr4wMF3mWN zGd{3d$`^bq@A#nzb!L)-L}(T>OeH`l@e@g8ZtMies;$hDU4ZX-I(`=soAqpJ0Qz-6 zCzZyTE_FO`$MkVL?S^1d^uwdp$oY6OP8|nsXNC}rSU<*KDb^0Rc5sU!hJHWH_6 zAWvg5qvIz_8YItG>x+Y9ll3fipq`=n#XTYL(*M(jz7g^{GlHFL02Ba#3hn{6p}$&B zh1#g?3Nu>!%Y7lV_7zVp8G;^_4NYZ8@i;15$KjA74Q!)GHR-Y@<*W6!gfdPWM%v}5 zW(v)|%#W023&k=COm8ZtUKRE%7DHh!v?x6$`Yw0k%hT4zlx9d*k`$WNT=9B6zH^=X zRx|q`j3&I4m^?8bv8Qby`uSLEA;-RWs^~3B@w?NdWx%Kn{Kl(;kWRl%bvc@4a*A&p zIIT2LVM*WQp+~MsU?`llIvK=mU>hR4V5;7Jo0=o9D#s&r6-99SE=*hMUbjSNv#3CJ zqKbw(gwyF?1;w>|*c%NTIg0Y`;X3Oe*{3zD7nO3~H>IwqveTN+BBPBTYjt5H*rDy{ zE*0#xLo6zN86DS*|5|xpmFdyqb0sMcW<|Vh6cy50Dvn|=vo%}%oE|T8B#)Nmu2LWI zA*kGd@TP8VAEhN|GMd?6etIN%{A|>{*Yzg3C4pU{=*sxQxhr$>G@LERky&wa&XlS0 z5zzIfPr%5vi$+uvKmmD?6s@fjCw(JP+dS0iJ zF#fPfXkJeL(LuNG4#F^&j|O|}t?F%Xje_&rl4blKBb{Ie^iR`rZ%=8>2fG|?uxVlb zZdy*}MvkT?s?LrUwq{O0z0-TO9)%TQv<~uf)O-Oo@j&ij2xUurp!popvJAZXq^?)E zrv7Pbz46SG0EQ}Q!;w;q@3zO}<-4rQ@~+Ad*n&6hbaU3M>i$(1jSlIe&&|47`ZRDA z`jwS#S|x-qF6uZOxA4WQgHR}0is1ONrtHgqaD#yRg z9(a7Q+{RSvbki$wQa;GMpWPnEU3v%w@?|u7Us)5A%W-Ob zv4*U}H+7>ih|FYF!|}Bn;WWNKHTd0Thxmz#G&-#DRlWHGj-J+u@8AYCnIgQ!wwG=n z6BHBvN`leA>wCHxs>TiY5E(&|{sxd`D;e>Nh7QwNZK#VCv1%0dv*ggm(^9lgpi!vo~+(kGo8bDS(~jKQ;{AO?36T`#@409;|{m6kSZk@=PIQo8z6LCD=ovI z?^8ZoHOX734s}gYk1vu}?%Jl=k}zoZvF6`jT^)3At$$#D$+Q|>>J+T1pf%%K{_<5- z&<7y?2a{!2I~3T$q9gl&1eQGd9vioS7B7p(=Uc`+2H++R-;~g%Zpn#+=vdQ;sbJu{ zex@2+g^i`)LKbxUbcNAvOv0em)MTq98KoGYv>vqxOKJz_$2g#C=uAOd2m4yFeL5T? zz6ASfN>S~!n9*REdc+1I(aao=Xv0Y_x3MdKzgx(WQa?1mc^lUCUh*fV#~4_rQHpqtQ$N4n4C3(4pE>YA zhO8C_QVwt8wQj*)+w-hone^_*V69zrh~y{ZGbzO(mmepH$!U>s?&`e%UgHb^z!ylx z^#h}DvvPnY4&gLBQW5pCML>!*B+B<}>XZfE9Yz^gf1<=Hnruq4(obwxQqg z)A>&N%h=AE?UoqOb(o^>{k;<(8kMg5=6y=6pV!0Py=L~0AFJeu?H}?W2?Z%wzD=lm zAAfzrGA8fQV-kZpBsx}JXjl1vnE`=A_vD<8;8-bh^}N$3TsD5ZRUBppazTBYhgJ0ij@d|w}7Z;MeJ6+TZX=JE^(wIir#_8_#Q&dBV=9*CymGT~d;~Rg=)KJtJwI9*oCY zDkmE;zm-EjBZNl@9D6wf(}&dJ#L~z0`%`0%Yx|f*&&NhSQpCqR`xA>slI`(gaNZHI zt)qFMItULB*sqfcrVDf4`o!yO7a+*Hb}vvx~v${ z3R0M>FLTzJO!bpo^BSO8Gor*abV0aQ?DJNyn}w9aq!_>pBfivqhvDF}3LyIa%H_ z-1wUaLv{RV+DUoE7B*u7jUQ#&HWBBvE0!SU*xNbr8+MVoLW zLiR}g#gX>q1%-KA4{70>XKIRu9yxRu#E5}9VBVP<#oX9Li1=u49_=`jSmXPx5iDUF zEG#V{o|;3;w$7AHsO};JW&UJ`5ftd;>7*W>1t~j6s(Cg5HpVCt4cTgf9+!gR1fy)D zW_W{$s4Ulq;N(ys-Znqqb^eFT_b%mdr6P*0Z|^xV+407?fL63*#SH;ettwOfJhp8j zt6{UXz_i9y_~WNd)+~|l_ATGVoe>7j$m8EJJsr`E((mSlxhL}IMJN=(umHJTXd+gs zGJd?mH_u~#?NI(j7;R0*ks&VWQ#1k8DiUj!+?mwA(SlAvalNk6og7_-qWbLu>}ZxX z%H~?^z~BT#W-%99LF#&m3~UU17-Wv+w{eyLjPay6tb4aI=H0q-V%jo-k!4_}I88N8 zYpR(&WgZ>xq3tf@31c=JxASTUtg7}KkjuP4o0{yTMV|JYoS3@Fd_&YhI^6|cd0seP z1?z#v3XFldzE!D9T*;SL4ifV|A%Qu{bTDis(gR1V^c;M49Hp7tfx?%sYkKU;@r)VInNi60i=UF~7}^I`Y8onAE~PeeqrDr*QqR#b1&dzn~xC$Lo zYoP%er)QACK9_FLW0g}uw?Q$Hr%0DJLYa})MPOvXx^)tsLFJcoyv%p&M_(sXSeP&6 z1RV|iy|AiJy8d+!yH47XeE^3nq(}e&@*jbTvxl|mZxKm@hLzn4C+4%@8<5DGP$Oun zBctJ3y-{%+N#TGhoMa@N8WczdWfWyWwFre1l;_h93QAq+j%5o@A7FOaUe>4=Q!!Ih z29H~TZ3!kV0*s(z_>if{XI}BHb(8NnJkh;*-Es&Cd4v{IQA@Y$^7xI$I}gcdyMSPG z73Xp_j`Mb^E=mF}NNpelTVVx{##H5kf$BWe%jmWKk1rp73~i?(m;kBxgw!jv3wjy&kJJ9|FM( zwXyK-j72QOvHh|lPwfaEQ$9X|Lx}A@SHj|)5>gYzyIZHj>sR8q-h$qqgCGwrE6kzF z64%fp)Lt5CMkdH-{g@A%etq3+hd~mFwH0+(e?i>d?zqwML9Vx?E}(X@!Gc%K4-@DlNc6;e+abY?p~g9FNqN>5ZE3g42{io`UXR?b}r}?#&OXc zU0F+_uYl9`MdF9@+Njb>T}mcN$I)X2Mv`ssymh(;-O4oH%{09$DksD<5$ciksKAeo zRS{>D@@H3AO{|rUr8BHjHdX!Qudb+bJDaM>g6Nm@*F86nZxCLWX$s$Z<}eY9HcjIfBX-vMSf(Fec%0S1J@@ zYg4&zp_>YBIQY5R1sheOm8?g=YgKqAq^rKvBJxK|njzI_Ilc4X5AS0MG4wWRe#2IW zhE%2$?X9pMTdJojMJd^sdIvOkOaHW&}Pz?^1m)_XiC(e5)Z# z?-Bnd;o438~`t+GM}<+_BLUtMQr046d<(Y>EUVG-&ygkzvI?&ETm}4lqs~O7D-I&4NS>2 z4yw>l4)>z)R3xg+`W|J5*#LTV;9ja=TncH-7kmVVITA{GwzZ4OuuC+V z7cmUQ0~MhZFc@c66u@ol0(SRZ5Yu#m?Q+q!PNT$UaT43$Ngm2!E{HRB0!&h!Dmajr z_&3)8r{!2JBpp+?Y9x@?E?xQI9G)iVTVt(eFepJ&K2@px8A_X_|8<%Mq{i_WaNZj#~CWB_)ttpSrV`8*~fd)nl3XXt16m97#Ypl%&hpL=-bT`Ek zV};u6B3DV$C}w{s$Rm!%CZF8S4Q>@U)fhS2-YFoSjJo8b(V&Vy3#5kc33{(#?R?JE zzCyqDo{I6Fqq{O3??^Cp2!$g2c=JH_J&#mVS9<6$2)5=+a|-D(NB^BfoqH*B#qsI+ z(KD0SAbv|1b`R~eqU_9=O`K^@I<2cn_ftne?d6K${)fxq=Qd_FO%+~@&>QWt?oRSB zj+fjGKgcN)JI@Qzj2^M9SW({s(oU5nBS5uiMto);r#7U>t{(Y}r^i#BSAtJ=r6f!v zu3zs!}lIeZ+)v6^Y9j?RnT*N7olcb;U}^;+0k z0ZY=xfNVy9d=;Hszi>|uK{_J7YD}tT;%iM%J}fQjraz3?D1jND6gG89E_1_iHt;x5 z`D)8JCV5AZ8=-!ez@?+M+z~Sdt8VXO%@vC@bQPrqsUt}#PRWHTw(*Ivg>W9l)KS4; ztNp-&N_}6$G8F+JW=`#k{HHP9f|@-E*c<1KN|fWH?f?{m!|qy?_SpaoLhkthHbU+c zVbgN=xbAJwl(Lwd-aWTGl$V-^l{L+yCNYG{49(al7Sp;6%L{ps47_uB#tfdt;?z3M zW#VQ!&W!n%=Rz|{<)~rRpxXVVXL0>&nU|-mxX6gk=8ENB<>HSz&N<>?I?fg1rw#jR zQ49WtYk@!>Wx92aMTHB8#x{?;Q{bvUOt5>=Brdz52ztC88@5EIu_z*;&^QHh)Aso)K zNNB$KNHm@H8n(Gj)dbMb>f%3(MBQvT77E}ZksG)M2?W=$P3(-79PR9#n2hZlP5+=` z|5v&OPu=^3Q7{#W`TADsNo)tXd`^Lwzk(tO;1WPXq6`IQ89v#(eu}e6yE%18;Dl;K zOuiWt$ZB!qT^(Uv_IC-qK(js=T<<1J)W;3|cxqh#t<26=S z53-!=hr7pkV)65~DH{kWbNdXjTe^{``^I49DZoZpob5!^|L%SZUhCslmcePA$;4#e zatd_#%IZP9v95a|?Ytl~83K!i?qN=Q?Dy|4*%DEuStEw!2AevafY___Mr-CIw=mx4U!h2g|tr6?aqJtcdTy!^H&WL1=%) zosqr$|6m86vOlg2Fw3~jj0Qdd0A?B84l$Fl$_U8Sv>X67g_zvdZ8}2pg!ON@JAaH@K?A5kxn=AFP#wujDZLYS;-~4iPi%oET$qyPglHGV! zGSS-;7abE7<7=tov3j|A>G=}929t|aD<7+nb~k@|I=`B>ZN;)CP%)isIw@y8KR0s> zI=d)uZlsqLcT`6ksz(T6i%6}jA&C`OxNklkKt;^P4w_k>(7q`{^UMfzd4;!2V{~1| zg8LRfF31%iH}(w-xs)XVc@?sDch|Ly_(Q0>ASDrw z#2T>p_7&pips5b{%HdwT_+`YMpwkrSmqquH-HN5 zke!TcV^?RLxFX!R;B9kt>qjKil3_S;5yhYBs)F&f%{K}Huf^}6g?PaLrV{@BAkTkR z>_6?l9P3e%{kwv{pECH5;Lo-I42!>UQ`*|$G6A;bTJ zi~CjhSAOMBVROXag#W>|{Ho#CV*Z~R1i*C>@GyQY?fiGXufKKw;pZME0@T>IS ledwRE001=UUw-@#kE$dK4YrM+$5{{oo#1F^ko@P<{{blQJ+%M; literal 0 HcmV?d00001 -- 2.39.5