Examples with CellRangeAddress org.apache.poi.ss.util.CellRangeAddress used on opensource projects

Example 66 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestWorkbook method testAddMergedRegionWithRegion.

@Test
public void testAddMergedRegionWithRegion() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet s = wb1.createSheet();
    for (int rownum = 0; rownum < 100; rownum++) {
        HSSFRow r = s.createRow(rownum);
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            c = r.createCell(cellnum + 1);
            c.setCellValue(new HSSFRichTextString("TEST"));
        }
    }
    s.addMergedRegion(new CellRangeAddress(0, 10, 0, 10));
    s.addMergedRegion(new CellRangeAddress(30, 40, 5, 15));
    sanityChecker.checkHSSFWorkbook(wb1);
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    s = wb2.getSheetAt(0);
    CellRangeAddress r1 = s.getMergedRegion(0);
    CellRangeAddress r2 = s.getMergedRegion(1);
    confirmRegion(new CellRangeAddress(0, 10, 0, 10), r1);
    confirmRegion(new CellRangeAddress(30, 40, 5, 15), r2);
    wb2.close();
    wb1.close();
}

Example 67 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class BaseTestSheetShiftRows method shiftRowsWithMergedRegionsThatDoNotContainColumnZero.

//@Ignore("bug 56454: Incorrectly handles merged regions that do not contain column 0")
@Test
public void shiftRowsWithMergedRegionsThatDoNotContainColumnZero() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("test");
    // populate sheet cells
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 12; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(i + "x" + j);
        }
    }
    CellRangeAddress A4_B7 = new CellRangeAddress(3, 6, 0, 1);
    CellRangeAddress C5_D7 = new CellRangeAddress(4, 6, 2, 3);
    sheet.addMergedRegion(A4_B7);
    sheet.addMergedRegion(C5_D7);
    // A4:B7 will elongate vertically
    // C5:D7 will be shifted down with same size
    sheet.shiftRows(4, sheet.getLastRowNum(), 1);
    // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
    try {
        assertEquals(2, sheet.getNumMergedRegions());
        assertEquals(CellRangeAddress.valueOf("A4:B8"), sheet.getMergedRegion(0));
        assertEquals(CellRangeAddress.valueOf("C5:D8"), sheet.getMergedRegion(1));
        testPassesNow(56454);
    } catch (AssertionError e) {
        skipTest(e);
    }
    wb.close();
}

Example 68 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class BaseTestSheetShiftRows method shiftWithMergedRegions_bug56454.

//@Ignore("bug 56454: Incorrectly handles merged regions that do not contain column 0")
@Test
public final void shiftWithMergedRegions_bug56454() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    // populate sheet cells
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {
            Cell cell = row.createCell(j, CellType.STRING);
            cell.setCellValue(i + "x" + j);
        }
    }
    CellRangeAddress A4_B7 = CellRangeAddress.valueOf("A4:B7");
    CellRangeAddress C4_D7 = CellRangeAddress.valueOf("C4:D7");
    sheet.addMergedRegion(A4_B7);
    sheet.addMergedRegion(C4_D7);
    assumeTrue(sheet.getLastRowNum() > 8);
    // Insert a row in the middle of both merged regions.
    sheet.shiftRows(4, sheet.getLastRowNum(), 1);
    // all regions should still start at row 3, and elongate by 1 row
    List<CellRangeAddress> expectedMergedRegions = new ArrayList<CellRangeAddress>();
    //A4:B7 should be elongated by 1 row
    CellRangeAddress A4_B8 = CellRangeAddress.valueOf("A4:B8");
    //C4:B7 should be elongated by 1 row
    CellRangeAddress C4_D8 = CellRangeAddress.valueOf("C4:D8");
    expectedMergedRegions.add(A4_B8);
    expectedMergedRegions.add(C4_D8);
    // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
    try {
        assertEquals(expectedMergedRegions, sheet.getMergedRegions());
        testPassesNow(56454);
    } catch (AssertionError e) {
        skipTest(e);
    }
    wb.close();
}

Example 69 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class BaseTestSheetUpdateArrayFormulas method testSetArrayFormula_singleCell.

/**
     *  Set single-cell array formula
     */
@Test
public final void testSetArrayFormula_singleCell() throws IOException {
    Cell[] cells;
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(0).createCell(0);
    assertFalse(cell.isPartOfArrayFormulaGroup());
    try {
        cell.getArrayFormulaRange();
        fail("expected exception");
    } catch (IllegalStateException e) {
        assertEquals("Cell A1 is not part of an array formula.", e.getMessage());
    }
    // row 3 does not yet exist
    assertNull(sheet.getRow(2));
    CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
    cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range).getFlattenedCells();
    assertEquals(1, cells.length);
    // sheet.setArrayFormula creates rows and cells for the designated range
    assertNotNull(sheet.getRow(2));
    cell = sheet.getRow(2).getCell(2);
    assertNotNull(cell);
    assertTrue(cell.isPartOfArrayFormulaGroup());
    //retrieve the range and check it is the same
    assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString());
    //check the formula
    assertEquals("SUM(C11:C12*D11:D12)", cell.getCellFormula());
    workbook.close();
}

Example 70 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class BaseTestSheetUpdateArrayFormulas method testModifyArrayCells_removeCell.

@Test
public void testModifyArrayCells_removeCell() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    //single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();
    Row srow = sheet.getRow(cra.getFirstRow());
    assertSame(srow, scell.getRow());
    srow.removeCell(scell);
    assertNull(srow.getCell(cra.getFirstColumn()));
    //re-create the removed cell
    scell = srow.createCell(cra.getFirstColumn());
    assertEquals(CellType.BLANK, scell.getCellTypeEnum());
    assertFalse(scell.isPartOfArrayFormulaGroup());
    //we cannot remove cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
        int columnIndex = mcell.getColumnIndex();
        Row mrow = mcell.getRow();
        try {
            mrow.removeCell(mcell);
            fail("expected exception");
        } catch (IllegalStateException e) {
            CellReference ref = new CellReference(mcell);
            String msg = "Cell " + ref.formatAsString() + " is part of a multi-cell array formula. You cannot change part of an array.";
            assertEquals(msg, e.getMessage());
        }
        // a failed invocation of Row.removeCell leaves the row
        // in the state that it was in prior to the invocation
        assertSame(mcell, mrow.getCell(columnIndex));
        assertTrue(mcell.isPartOfArrayFormulaGroup());
        assertEquals(CellType.FORMULA, mcell.getCellTypeEnum());
    }
    workbook.close();
}