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

Example 1 with CellCopyPolicy

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

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_style.

@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(CellType.BLANK, destCell.getCellTypeEnum());
    assertEquals(CellType.BOOLEAN, destCell.getCellTypeEnum());
    assertEquals(true, destCell.getBooleanCellValue());
}

Example 2 with CellCopyPolicy

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

the class TestXSSFRow method testCopyRowFrom.

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

Example 3 with CellCopyPolicy

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

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_mergeHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_mergeHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    destCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    destCell.setCellStyle(hlinkStyle);
    // Pre-condition assumptions. This test is broken if either of these fail.
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    assertNull(srcCell.getHyperlink());
    // Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared).
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().mergeHyperlink(true).copyHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    assertSame(link, destCell.getHyperlink());
    List<XSSFHyperlink> links;
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 1, links.size());
    assertEquals("source hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    // Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell.
    srcCell.copyCellFrom(destCell, policy);
    assertNotNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("dest hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}

Example 4 with CellCopyPolicy

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

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_value.

@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(CellType.NUMERIC, destCell.getCellTypeEnum());
}

Example 5 with CellCopyPolicy

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

the class TestXSSFSheet method testCopyMultipleRows.

protected void testCopyMultipleRows(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(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", CellType.BLANK, cell.getCellTypeEnum());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Blank] C11 cell type", CellType.BLANK, cell.getCellTypeEnum());
    // Error
    col++;
    cell = CellUtil.getCell(destRow1, col);
    assertEquals("[Error] D10 cell type", CellType.ERROR, cell.getCellTypeEnum());
    FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
    //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
    assertEquals("[Error] D10 cell value", FormulaError.NA, error);
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Error] D11 cell type", CellType.ERROR, cell.getCellTypeEnum());
    error = FormulaError.forInt(cell.getErrorCellValue());
    //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
    assertEquals("[Error] D11 cell value", FormulaError.NAME, error);
    // Date
    col++;
    cell = CellUtil.getCell(destRow1, col);
    assertEquals("[Date] E10 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime();
    assertEquals("[Date] E10 cell value", date, cell.getDateCellValue());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Date] E11 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    date = LocaleUtil.getLocaleCalendar(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", CellType.BOOLEAN, cell.getCellTypeEnum());
    assertEquals("[Boolean] F10 cell value", true, cell.getBooleanCellValue());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Boolean] F11 cell type", CellType.BOOLEAN, cell.getCellTypeEnum());
    assertEquals("[Boolean] F11 cell value", false, cell.getBooleanCellValue());
    // String
    col++;
    cell = CellUtil.getCell(destRow1, col);
    assertEquals("[String] G10 cell type", CellType.STRING, cell.getCellTypeEnum());
    assertEquals("[String] G10 cell value", "Hello", cell.getStringCellValue());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[String] G11 cell type", CellType.STRING, cell.getCellTypeEnum());
    assertEquals("[String] G11 cell value", "World", cell.getStringCellValue());
    // Int
    col++;
    cell = CellUtil.getCell(destRow1, col);
    assertEquals("[Int] H10 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Int] H10 cell value", 15, (int) cell.getNumericCellValue());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Int] H11 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Int] H11 cell value", 42, (int) cell.getNumericCellValue());
    // Float
    col++;
    cell = CellUtil.getCell(destRow1, col);
    assertEquals("[Float] I10 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Float] I10 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION);
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Float] I11 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula] J10 cell formula", "5+2", cell.getCellFormula());
    cell = CellUtil.getCell(destRow2, col);
    assertEquals("[Cell Formula] J11 cell type", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    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", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula with Area Reference] M11 cell formula", "SUM($H$3:I10)", //Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order
    cell.getCellFormula());
    // 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", CellType.FORMULA, cell.getCellTypeEnum());
        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", CellType.FORMULA, cell.getCellTypeEnum());
        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", CellType.NUMERIC, cell.getCellTypeEnum());
    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)
    //one row above destHeaderRow
    assertNull("Off-by-one lower edge case", sheet.getRow(7));
    //one row below destRow3
    assertNull("Off-by-one upper edge case", sheet.getRow(12));
    wb.close();
}