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

Example 21 with AreaReference

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

the class TestXSSFPivotTableRef method setUp.

@Override
@Before
public void setUp() {
    wb = new XSSFWorkbook();
    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");
    Cell cell10 = row1.createCell(3);
    cell10.setCellValue("Value");
    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");
    Cell cell11 = row1.createCell(3);
    cell11.setCellValue(11.11);
    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");
    Cell cell12 = row1.createCell(3);
    cell12.setCellValue(12.12);
    AreaReference source = new AreaReference("A1:C2", SpreadsheetVersion.EXCEL2007);
    pivotTable = sheet.createPivotTable(source, new CellReference("H5"));
    XSSFSheet offsetSheet = wb.createSheet();
    Row tableRow_1 = offsetSheet.createRow(1);
    offsetOuterCell = tableRow_1.createCell(1);
    offsetOuterCell.setCellValue(-1);
    Cell tableCell_1_1 = tableRow_1.createCell(2);
    tableCell_1_1.setCellValue("Row #");
    Cell tableCell_1_2 = tableRow_1.createCell(3);
    tableCell_1_2.setCellValue("Exponent");
    Cell tableCell_1_3 = tableRow_1.createCell(4);
    tableCell_1_3.setCellValue("10^Exponent");
    Row tableRow_2 = offsetSheet.createRow(2);
    Cell tableCell_2_1 = tableRow_2.createCell(2);
    tableCell_2_1.setCellValue(0);
    Cell tableCell_2_2 = tableRow_2.createCell(3);
    tableCell_2_2.setCellValue(0);
    Cell tableCell_2_3 = tableRow_2.createCell(4);
    tableCell_2_3.setCellValue(1);
    Row tableRow_3 = offsetSheet.createRow(3);
    Cell tableCell_3_1 = tableRow_3.createCell(2);
    tableCell_3_1.setCellValue(1);
    Cell tableCell_3_2 = tableRow_3.createCell(3);
    tableCell_3_2.setCellValue(1);
    Cell tableCell_3_3 = tableRow_3.createCell(4);
    tableCell_3_3.setCellValue(10);
    Row tableRow_4 = offsetSheet.createRow(4);
    Cell tableCell_4_1 = tableRow_4.createCell(2);
    tableCell_4_1.setCellValue(2);
    Cell tableCell_4_2 = tableRow_4.createCell(3);
    tableCell_4_2.setCellValue(2);
    Cell tableCell_4_3 = tableRow_4.createCell(4);
    tableCell_4_3.setCellValue(100);
    AreaReference offsetSource = new AreaReference(new CellReference("C2"), new CellReference("E4"));
    offsetPivotTable = offsetSheet.createPivotTable(offsetSource, new CellReference("C6"));
}

Example 22 with AreaReference

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

the class TestStructuredReferences method testTableFormulas.

@Test
public void testTableFormulas() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
    try {
        final FormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
        final XSSFSheet tableSheet = wb.getSheet("Table");
        final XSSFSheet formulaSheet = wb.getSheet("Formulas");
        confirm(eval, tableSheet.getRow(5).getCell(0), 49);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209);
        confirm(eval, formulaSheet.getRow(1).getCell(0), "one");
        // test changing a table value, to see if the caches are properly cleared
        // Issue 59814
        // this test passes before the fix for 59814
        tableSheet.getRow(1).getCell(1).setCellValue("ONEA");
        confirm(eval, formulaSheet.getRow(1).getCell(0), "ONEA");
        // test adding a row to a table, issue 59814
        Row newRow = tableSheet.getRow(7);
        if (newRow == null)
            newRow = tableSheet.createRow(7);
        newRow.createCell(0, CellType.FORMULA).setCellFormula("\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
        newRow.createCell(1, CellType.STRING).setCellValue("thirteen");
        newRow.createCell(2, CellType.NUMERIC).setCellValue(13);
        // update Table
        final XSSFTable table = wb.getTable("\\_Prime.1");
        final AreaReference newArea = new AreaReference(table.getStartCellReference(), new CellReference(table.getEndRowIndex() + 1, table.getEndColIndex()));
        String newAreaStr = newArea.formatAsString();
        table.getCTTable().setRef(newAreaStr);
        table.getCTTable().getAutoFilter().setRef(newAreaStr);
        table.updateHeaders();
        table.updateReferences();
        // these fail before the fix for 59814
        confirm(eval, tableSheet.getRow(7).getCell(0), 13 * 13);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209 + 13 * 13);
    } finally {
        wb.close();
    }
}

Example 23 with AreaReference

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

the class TestXSSFSheet method testCreateTwoPivotTablesInOneSheet.

@Test
public void testCreateTwoPivotTablesInOneSheet() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet = wb.getSheetAt(0);
    assertNotNull(wb);
    assertNotNull(sheet);
    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
    assertNotNull(pivotTable);
    assertTrue(wb.getPivotTables().size() > 0);
    XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("L5"), sheet);
    assertNotNull(pivotTable2);
    assertTrue(wb.getPivotTables().size() > 1);
    wb.close();
}

Example 24 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 25 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");
}