Examples of org.apache.poi.ss.util.CellRangeAddress

org.apache.poi.ss.util.CellRangeAddress
See OOO documentation: excelfileformat.pdf sec 2.5.14 - 'Cell Range Address'

In the Microsoft documentation, this is also known as a Ref8U - see page 831 of version 1.0 of the documentation. Note - {@link SelectionRecord} uses the BIFF5 version of this structure @author Dragos Buleandra (dragos.buleandra@trade2b.ro)


    /**
     * @deprecated (Aug-2008) use {@link HSSFSheet#getMergedRegion(int)}
     */
    public Region getMergedRegionAt(int index) {
        CellRangeAddress cra = getMergedRegion(index);


        return new Region(cra.getFirstRow(), (short)cra.getFirstColumn(),
                cra.getLastRow(), (short)cra.getLastColumn());
    }

View Full Code Here

    public void cloneSheet() {
        Workbook book = _testDataProvider.createWorkbook();
        Sheet sheet = book.createSheet("TEST");
        sheet.createRow(0).createCell(0).setCellValue("Test");
        sheet.createRow(1).createCell(0).setCellValue(36.6);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 2));
        assertTrue(sheet.isSelected());


        Sheet clonedSheet = book.cloneSheet(0);
        assertEquals("TEST (2)", clonedSheet.getSheetName());
        assertEquals(2, clonedSheet.getPhysicalNumberOfRows());
        assertEquals(2, clonedSheet.getNumMergedRegions());
        assertFalse(clonedSheet.isSelected());


        //cloned sheet is a deep copy, adding rows in the original does not affect the clone
        sheet.createRow(2).createCell(0).setCellValue(1);
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 2));
        assertEquals(2, clonedSheet.getPhysicalNumberOfRows());
        assertEquals(2, clonedSheet.getPhysicalNumberOfRows());


        clonedSheet.createRow(2).createCell(0).setCellValue(1);
        clonedSheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 2));
        assertEquals(3, clonedSheet.getPhysicalNumberOfRows());
        assertEquals(3, clonedSheet.getPhysicalNumberOfRows());


    }

View Full Code Here

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

View Full Code Here

        // 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(Cell.CELL_TYPE_FORMULA, acell.getCellType());
            assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula());
            //retrieve the range and check it is the same
            assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString());
        }
    }

View Full Code Here

        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();


        try {
            sheet.setArrayFormula("incorrect-formula(C11_C12*D11_D12)",
                    new CellRangeAddress(10, 10, 10, 10));
            fail("expected exception");
        } catch (FormulaParseException e){
            //expected exception
        }
    }

View Full Code Here

        for ( int colIx = 0; colIx < maxColIx; colIx++ )
        {
            if ( !isOutputHiddenColumns() && sheet.isColumnHidden( colIx ) )
                continue;


            CellRangeAddress range = ExcelToHtmlUtils.getMergedRange(
                    mergedRanges, row.getRowNum(), colIx );


            if ( range != null
                    && ( range.getFirstColumn() != colIx || range.getFirstRow() != row
                            .getRowNum() ) )
                continue;


            HSSFCell cell = row.getCell( colIx );


            // spanning using overlapping blocks
            int divWidthPx = 0;
            {
                divWidthPx = getColumnWidth( sheet, colIx );


                boolean hasBreaks = false;
                for ( int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++ )
                {
                    if ( !isOutputHiddenColumns()
                            && sheet.isColumnHidden( nextColumnIndex ) )
                        continue;


                    if ( row.getCell( nextColumnIndex ) != null
                            && !isTextEmpty( row.getCell( nextColumnIndex ) ) )
                    {
                        hasBreaks = true;
                        break;
                    }


                    divWidthPx += getColumnWidth( sheet, nextColumnIndex );
                }


                if ( !hasBreaks )
                    divWidthPx = Integer.MAX_VALUE;
            }


            Element tableCellElement = foDocumentFacade.createTableCell();


            if ( range != null )
            {
                if ( range.getFirstColumn() != range.getLastColumn() )
                    tableCellElement.setAttribute(
                            "number-columns-spanned",
                            String.valueOf( range.getLastColumn()
                                    - range.getFirstColumn() + 1 ) );
                if ( range.getFirstRow() != range.getLastRow() )
                    tableCellElement.setAttribute(
                            "number-rows-spanned",
                            String.valueOf( range.getLastRow()
                                    - range.getFirstRow() + 1 ) );
            }


            boolean emptyCell;
            if ( cell != null )
            {

View Full Code Here

     */
    public final void testRemoveArrayFormula() {
        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());

View Full Code Here

    public void testModifyArrayCells_removeCell(){
        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(Cell.CELL_TYPE_BLANK, scell.getCellType());
        assertFalse(scell.isPartOfArrayFormulaGroup());


        //we cannot remove cells included in a multi-cell array formula
        CellRange<? extends Cell> mrange =

View Full Code Here

    public void testModifyArrayCells_removeRow(){
        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();
        assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());


        Row srow = scell.getRow();
        assertSame(srow, sheet.getRow(cra.getFirstRow()));
        sheet.removeRow(srow);
        assertNull(sheet.getRow(cra.getFirstRow()));


        //re-create the removed row and cell
        scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
        assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
        assertFalse(scell.isPartOfArrayFormulaGroup());


        //we cannot remove rows with cells included in a multi-cell array formula
        CellRange<? extends Cell> mrange =

View Full Code Here

        assertEquals(1, sheet.getNumMergedRegions());


        //we cannot merge cells included in an array formula
        CellRange<? extends Cell> mrange =
                sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
        CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3");
        try {
            sheet.addMergedRegion(cra);
            fail("expected exception");
        } catch (IllegalStateException e){
            String msg = "The range "+cra.formatAsString()+" intersects with a multi-cell array formula. You cannot merge cells of an array.";
            assertEquals(msg, e.getMessage());
        }
        //the number of merged regions remains the same
        assertEquals(1, sheet.getNumMergedRegions());
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.ss.util.CellRangeAddress

accounts.web.ReportPoiController

accounts.web.ReportPoiControllerTest

AlMaGe.export.ExcelReportDreiJahre

AlMaGe.export.ExcelReportFuenfJahre

AlMaGe.export.ExcelReportJahr

AlMaGe.export.ExcelReportQuartal

AlMaGe.export.ExcelReportZehnJahre

br.net.woodstock.rockframework.office.spreadsheet.poi.HSSFSpreadsheetDocumentWriter

br.net.woodstock.rockframework.office.spreadsheet.poi.XSSFSpreadsheetDocumentWriter

com.dtrules.compiler.excel.util.Rules2Excel

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.