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

Example 16 with AreaReference

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

the class TestXSSFSheet method testCreatePivotTableInOtherSheetThanDataSheetUsingAreaReference.

@Test
public void testCreatePivotTableInOtherSheetThanDataSheetUsingAreaReference() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFSheet sheet2 = wb.createSheet("TEST");
    XSSFPivotTable pivotTable = sheet2.createPivotTable(new AreaReference(sheet.getSheetName() + "!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
    assertEquals(0, pivotTable.getRowLabelColumns().size());
    wb.close();
}

Example 17 with AreaReference

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

the class TestXSSFSheet method testCreatePivotTableWithConflictingDataSheets.

@Test(expected = IllegalArgumentException.class)
public void testCreatePivotTableWithConflictingDataSheets() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFSheet sheet2 = wb.createSheet("TEST");
    sheet2.createPivotTable(new AreaReference(sheet.getSheetName() + "!A$1:B$2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet2);
    wb.close();
}

Example 18 with AreaReference

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

the class TestXSSFSheet method testCreatePivotTableInOtherSheetThanDataSheet.

@Test
public void testCreatePivotTableInOtherSheetThanDataSheet() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet1 = wb.getSheetAt(0);
    XSSFSheet sheet2 = wb.createSheet();
    XSSFPivotTable pivotTable = sheet2.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet1);
    assertEquals(0, pivotTable.getRowLabelColumns().size());
    assertEquals(1, wb.getPivotTables().size());
    assertEquals(0, sheet1.getPivotTables().size());
    assertEquals(1, sheet2.getPivotTables().size());
    wb.close();
}

Example 19 with AreaReference

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

the class CreatePivotTable method main.

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    //Create some data to build the pivot table on
    setCellData(sheet);
    AreaReference source = new AreaReference("A1:D4", SpreadsheetVersion.EXCEL2007);
    CellReference position = new CellReference("H5");
    // Create a pivot table on this sheet, with H5 as the top-left cell..
    // The pivot table's data source is on the same sheet in A1:D4
    XSSFPivotTable pivotTable = sheet.createPivotTable(source, position);
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);
    FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

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