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

Example 76 with CellRangeAddress

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

the class BaseTestSheet method addMerged.

/**
     * Test adding merged regions. If the region's bounds are outside of the allowed range
     * then an IllegalArgumentException should be thrown
     *
     */
@Test
public void addMerged() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    assertEquals(0, sheet.getNumMergedRegions());
    SpreadsheetVersion ssVersion = _testDataProvider.getSpreadsheetVersion();
    CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
    sheet.addMergedRegion(region);
    assertEquals(1, sheet.getNumMergedRegions());
    try {
        region = new CellRangeAddress(-1, -1, -1, -1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
    // TODO: assertEquals("Minimum row number is 0.", e.getMessage());
    }
    try {
        region = new CellRangeAddress(0, 0, 0, ssVersion.getLastColumnIndex() + 1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
        assertEquals("Maximum column number is " + ssVersion.getLastColumnIndex(), e.getMessage());
    }
    try {
        region = new CellRangeAddress(0, ssVersion.getLastRowIndex() + 1, 0, 1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
        assertEquals("Maximum row number is " + ssVersion.getLastRowIndex(), e.getMessage());
    }
    assertEquals(1, sheet.getNumMergedRegions());
    wb.close();
}

Example 77 with CellRangeAddress

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

the class BaseTestSheet method bug55723d_RowsOver65k.

@Test
public void bug55723d_RowsOver65k() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    CellRangeAddress range = CellRangeAddress.valueOf("A4:B75000");
    AutoFilter filter = sheet.setAutoFilter(range);
    assertNotNull(filter);
    wb.close();
}

Example 78 with CellRangeAddress

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

the class BaseTestSheetUpdateArrayFormulas method shouldNotBeAbleToCreateArrayFormulaOnPreexistingMergedRegion.

@Ignore
@Test
public void shouldNotBeAbleToCreateArrayFormulaOnPreexistingMergedRegion() throws IOException {
    /*
         *  m  = merged region
         *  f  = array formula
         *  fm = cell belongs to a merged region and an array formula (illegal, that's what this tests for)
         *  
         *   A  B  C
         * 1    f  f
         * 2    fm fm
         * 3    f  f
         */
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    CellRangeAddress mergedRegion = CellRangeAddress.valueOf("B2:C2");
    sheet.addMergedRegion(mergedRegion);
    CellRangeAddress arrayFormula = CellRangeAddress.valueOf("C1:C3");
    assumeTrue(mergedRegion.intersects(arrayFormula));
    assumeTrue(arrayFormula.intersects(mergedRegion));
    try {
        sheet.setArrayFormula("SUM(A1:A3)", arrayFormula);
        fail("expected exception: should not be able to create an array formula that intersects with a merged region");
    } catch (IllegalStateException e) {
    // expected
    }
    workbook.close();
}

Example 79 with CellRangeAddress

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

the class BaseTestSheetUpdateArrayFormulas method testRemoveArrayFormula.

/**
     * create and remove array formulas
     */
@Test
public final void testRemoveArrayFormula() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2);
    assertEquals("C4:C6", range.formatAsString());
    CellRange<?> cr = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range);
    assertEquals(3, cr.size());
    // remove the formula cells in C4:C6
    CellRange<?> dcells = sheet.removeArrayFormula(cr.getTopLeftCell());
    // removeArrayFormula should return the same cells as setArrayFormula
    assertArrayEquals(cr.getFlattenedCells(), dcells.getFlattenedCells());
    for (Cell acell : cr) {
        assertFalse(acell.isPartOfArrayFormulaGroup());
        assertEquals(CellType.BLANK, acell.getCellTypeEnum());
    }
    // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException
    for (Cell acell : cr) {
        try {
            sheet.removeArrayFormula(acell);
            fail("expected exception");
        } catch (IllegalArgumentException e) {
            String ref = new CellReference(acell).formatAsString();
            assertEquals("Cell " + ref + " is not part of an array formula.", e.getMessage());
        }
    }
    workbook.close();
}

Example 80 with CellRangeAddress

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

the class BaseTestSheetUpdateArrayFormulas method testSetArrayFormula_multiCell.

/**
     * Set multi-cell array formula
     */
@Test
public final void testSetArrayFormula_multiCell() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    // multi-cell formula
    // rows 3-5 don't exist yet
    assertNull(sheet.getRow(3));
    assertNull(sheet.getRow(4));
    assertNull(sheet.getRow(5));
    CellRangeAddress range = CellRangeAddress.valueOf("C4:C6");
    Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells();
    assertEquals(3, cells.length);
    // sheet.setArrayFormula creates rows and cells for the designated range
    assertSame(cells[0], sheet.getRow(3).getCell(2));
    assertSame(cells[1], sheet.getRow(4).getCell(2));
    assertSame(cells[2], sheet.getRow(5).getCell(2));
    for (Cell acell : cells) {
        assertTrue(acell.isPartOfArrayFormulaGroup());
        assertEquals(CellType.FORMULA, acell.getCellTypeEnum());
        assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula());
        //retrieve the range and check it is the same
        assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString());
    }
    workbook.close();
}