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

Example 26 with AreaReference

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

the class TestXSSFWorkbook method setPivotData.

protected void setPivotData(XSSFWorkbook wb) {
    XSSFSheet sheet = wb.createSheet();
    Row row1 = sheet.createRow(0);
    // Create a cell and put a value in it.
    Cell cell = row1.createCell(0);
    cell.setCellValue("Names");
    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("#");
    Cell cell7 = row1.createCell(2);
    cell7.setCellValue("Data");
    Row row2 = sheet.createRow(1);
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("Jan");
    Cell cell4 = row2.createCell(1);
    cell4.setCellValue(10);
    Cell cell8 = row2.createCell(2);
    cell8.setCellValue("Apa");
    Row row3 = sheet.createRow(2);
    Cell cell5 = row3.createCell(0);
    cell5.setCellValue("Ben");
    Cell cell6 = row3.createCell(1);
    cell6.setCellValue(9);
    Cell cell9 = row3.createCell(2);
    cell9.setCellValue("Bepa");
    AreaReference source = new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007);
    sheet.createPivotTable(source, new CellReference("H5"));
}

Example 27 with AreaReference

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

the class TestAreaReference method testReferenceWithSheet.

/**
     * References failed when sheet names were being used
     * Reported by Arne.Clauss@gedas.de
     */
public void testReferenceWithSheet() {
    AreaReference ar;
    ar = new AreaReference("Tabelle1!B5:B5");
    assertTrue(ar.isSingleCell());
    TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, false, false, "Tabelle1!B5");
    assertEquals(1, ar.getAllReferencedCells().length);
    ar = new AreaReference("Tabelle1!$B$5:$B$7");
    assertFalse(ar.isSingleCell());
    TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
    TestCellReference.confirmCell(ar.getLastCell(), "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
    // And all that make it up
    CellReference[] allCells = ar.getAllReferencedCells();
    assertEquals(3, allCells.length);
    TestCellReference.confirmCell(allCells[0], "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
    TestCellReference.confirmCell(allCells[1], "Tabelle1", 5, 1, true, true, "Tabelle1!$B$6");
    TestCellReference.confirmCell(allCells[2], "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
}

Example 28 with AreaReference

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

the class TestAreaReference method confirmWholeColumnRef.

private static void confirmWholeColumnRef(String ref, int firstCol, int lastCol, boolean firstIsAbs, boolean lastIsAbs) {
    AreaReference ar = new AreaReference(ref);
    confirmCell(ar.getFirstCell(), 0, firstCol, true, firstIsAbs);
    confirmCell(ar.getLastCell(), 0xFFFF, lastCol, true, lastIsAbs);
}

Example 29 with AreaReference

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

the class TestAreaReference method testAreaRef1.

public void testAreaRef1() {
    AreaReference ar = new AreaReference("$A$1:$B$2");
    assertFalse("Two cells expected", ar.isSingleCell());
    CellReference cf = ar.getFirstCell();
    assertTrue("row is 4", cf.getRow() == 0);
    assertTrue("col is 1", cf.getCol() == 0);
    assertTrue("row is abs", cf.isRowAbsolute());
    assertTrue("col is abs", cf.isColAbsolute());
    assertTrue("string is $A$1", cf.formatAsString().equals("$A$1"));
    cf = ar.getLastCell();
    assertTrue("row is 4", cf.getRow() == 1);
    assertTrue("col is 1", cf.getCol() == 1);
    assertTrue("row is abs", cf.isRowAbsolute());
    assertTrue("col is abs", cf.isColAbsolute());
    assertTrue("string is $B$2", cf.formatAsString().equals("$B$2"));
    CellReference[] refs = ar.getAllReferencedCells();
    assertEquals(4, refs.length);
    assertEquals(0, refs[0].getRow());
    assertEquals(0, refs[0].getCol());
    assertNull(refs[0].getSheetName());
    assertEquals(0, refs[1].getRow());
    assertEquals(1, refs[1].getCol());
    assertNull(refs[1].getSheetName());
    assertEquals(1, refs[2].getRow());
    assertEquals(0, refs[2].getCol());
    assertNull(refs[2].getSheetName());
    assertEquals(1, refs[3].getRow());
    assertEquals(1, refs[3].getCol());
    assertNull(refs[3].getSheetName());
}

Example 30 with AreaReference

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

the class XSSFPivotCacheDefinition method createCacheFields.

/**
     * Generates a cache field for each column in the reference area for the pivot table.
     * @param sheet The sheet where the data i collected from
     */
@Beta
protected void createCacheFields(Sheet sheet) {
    //Get values for start row, start and end column
    AreaReference ar = getPivotArea(sheet.getWorkbook());
    CellReference firstCell = ar.getFirstCell();
    CellReference lastCell = ar.getLastCell();
    int columnStart = firstCell.getCol();
    int columnEnd = lastCell.getCol();
    Row row = sheet.getRow(firstCell.getRow());
    CTCacheFields cFields;
    if (ctPivotCacheDefinition.getCacheFields() != null) {
        cFields = ctPivotCacheDefinition.getCacheFields();
    } else {
        cFields = ctPivotCacheDefinition.addNewCacheFields();
    }
    //For each column, create a cache field and give it en empty sharedItems
    for (int i = columnStart; i <= columnEnd; i++) {
        CTCacheField cf = cFields.addNewCacheField();
        if (i == columnEnd) {
            cFields.setCount(cFields.sizeOfCacheFieldArray());
        }
        //General number format
        cf.setNumFmtId(0);
        Cell cell = row.getCell(i);
        cell.setCellType(CellType.STRING);
        cf.setName(row.getCell(i).getStringCellValue());
        cf.addNewSharedItems();
    }
}