Examples with CellCopyPolicy org.apache.poi.ss.usermodel.CellCopyPolicy used on opensource projects

Example 6 with CellCopyPolicy

use of org.apache.poi.ss.usermodel.CellCopyPolicy in project poi by apache.

the class TestXSSFRow method testCopyRowOverwritesExistingRow.

@Test
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");
    //A2 -> 5.0
    destRow.createCell(0).setCellValue(5.0);
    // B2 -> A1 -> "hello"
    destRow.createCell(1).setCellFormula("A1");
    // A3 -> A2 -> 5.0
    observerRow.createCell(0).setCellFormula("A2");
    // B3 -> B2 -> A1 -> "hello"
    observerRow.createCell(1).setCellFormula("B2");
    //Sheet2!A1 -> Sheet1!A2 -> 5.0
    externObserverRow.createCell(0).setCellFormula("Sheet1!A2");
    // 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(CellType.STRING, destRow.getCell(0).getCellTypeEnum());
    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();
}

Example 7 with CellCopyPolicy

use of org.apache.poi.ss.usermodel.CellCopyPolicy in project poi by apache.

the class TestXSSFRow method testCopyRowFromExternalSheet.

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

Example 8 with CellCopyPolicy

use of org.apache.poi.ss.usermodel.CellCopyPolicy in project poi by apache.

the class TestXSSFSheet method testCopyOneRow.

protected void testCopyOneRow(String copyRowsTestWorkbook) throws IOException {
    final double FLOAT_PRECISION = 1e-9;
    final XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook);
    final XSSFSheet sheet = wb.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", CellType.BLANK, cell.getCellTypeEnum());
    // Error
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Error] D7 cell type", CellType.ERROR, cell.getCellTypeEnum());
    final FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
    //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
    assertEquals("[Error] D7 cell value", FormulaError.NA, error);
    // Date
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Date] E7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    final Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime();
    assertEquals("[Date] E7 cell value", date, cell.getDateCellValue());
    // Boolean
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Boolean] F7 cell type", CellType.BOOLEAN, cell.getCellTypeEnum());
    assertEquals("[Boolean] F7 cell value", true, cell.getBooleanCellValue());
    // String
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[String] G7 cell type", CellType.STRING, cell.getCellTypeEnum());
    assertEquals("[String] G7 cell value", "Hello", cell.getStringCellValue());
    // Int
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Int] H7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Int] H7 cell value", 15, (int) cell.getNumericCellValue());
    // Float
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Float] I7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
        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;", CellType.NUMERIC, cell.getCellTypeEnum());
    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)", //shouldn't do 1-row merge
    sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7")));
    assertFalse("[Merged across multiple rows] R7:S8 merged region", //shouldn't do 2-row merge
    sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")));
    // Make sure other rows are blank (off-by-one errors)
    assertNull(sheet.getRow(5));
    assertNull(sheet.getRow(7));
    wb.close();
}

Example 9 with CellCopyPolicy

use of org.apache.poi.ss.usermodel.CellCopyPolicy in project poi by apache.

the class XSSFSheet method copyRows.

/**
     * 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<? 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
    final int size = srcRows.size();
    for (int index = 1; index < size; index++) {
        final Row curRow = srcRows.get(index);
        if (curRow == null) {
            throw new IllegalArgumentException("srcRows may not contain null rows. Found null row at index " + index + ".");
        //} 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 = new CellCopyPolicy(policy);
    // 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);
    }
    // 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);
            }
        }
    }
}

Example 10 with CellCopyPolicy

use of org.apache.poi.ss.usermodel.CellCopyPolicy in project poi by apache.

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_formulaWithUnregisteredUDF.

@Test
public final void testCopyCellFrom_CellCopyPolicy_formulaWithUnregisteredUDF() {
    setUp_testCopyCellFrom_CellCopyPolicy();
    srcCell.setCellFormula("MYFUNC2(123, $A5, Sheet1!$B7)");
    // Copy formula verbatim (no shifting). This is okay because copyCellFrom is Internal.
    // Users should use higher-level copying functions to row- or column-shift formulas.
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(true).build();
    destCell.copyCellFrom(srcCell, policy);
    assertEquals("MYFUNC2(123, $A5, Sheet1!$B7)", destCell.getCellFormula());
}