Examples with Workbook org.apache.poi.ss.usermodel.Workbook used on opensource projects

Example 41 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFFormulaEvaluation method testMultiSheetReferencesHSSFandXSSF.

/**
     * A handful of functions (such as SUM, COUNTA, MIN) support
     *  multi-sheet references (eg Sheet1:Sheet3!A1 = Cell A1 from
     *  Sheets 1 through Sheet 3).
     * This test, based on common test files for HSSF and XSSF, checks
     *  that we can correctly evaluate these
     */
@Test
public void testMultiSheetReferencesHSSFandXSSF() throws Exception {
    Workbook wb1 = HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls");
    Workbook wb2 = XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx");
    for (Workbook wb : new Workbook[] { wb1, wb2 }) {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet s1 = wb.getSheetAt(0);
        // Simple SUM over numbers
        Cell sumF = s1.getRow(2).getCell(0);
        assertNotNull(sumF);
        assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
        assertEquals("Failed for " + wb.getClass(), "66.0", evaluator.evaluate(sumF).formatAsString());
        // Various Stats formulas on numbers
        Cell avgF = s1.getRow(2).getCell(1);
        assertNotNull(avgF);
        assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
        assertEquals("22.0", evaluator.evaluate(avgF).formatAsString());
        Cell minF = s1.getRow(3).getCell(1);
        assertNotNull(minF);
        assertEquals("MIN(Sheet1:Sheet3!A$1)", minF.getCellFormula());
        assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
        Cell maxF = s1.getRow(4).getCell(1);
        assertNotNull(maxF);
        assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
        assertEquals("33.0", evaluator.evaluate(maxF).formatAsString());
        Cell countF = s1.getRow(5).getCell(1);
        assertNotNull(countF);
        assertEquals("COUNT(Sheet1:Sheet3!A$1)", countF.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countF).formatAsString());
        // Various CountAs on Strings
        Cell countA_1F = s1.getRow(2).getCell(2);
        assertNotNull(countA_1F);
        assertEquals("COUNTA(Sheet1:Sheet3!C1)", countA_1F.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countA_1F).formatAsString());
        Cell countA_2F = s1.getRow(2).getCell(3);
        assertNotNull(countA_2F);
        assertEquals("COUNTA(Sheet1:Sheet3!D1)", countA_2F.getCellFormula());
        assertEquals("0.0", evaluator.evaluate(countA_2F).formatAsString());
        Cell countA_3F = s1.getRow(2).getCell(4);
        assertNotNull(countA_3F);
        assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
    }
    wb2.close();
    wb1.close();
}

Example 42 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFFormulaEvaluation method test59736.

@Test
public void test59736() {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("59736.xlsx");
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
    assertEquals(1, cell.getNumericCellValue(), 0.001);
    cell = wb.getSheetAt(0).getRow(1).getCell(0);
    CellValue value = evaluator.evaluate(cell);
    assertEquals(1, value.getNumberValue(), 0.001);
    cell = wb.getSheetAt(0).getRow(2).getCell(0);
    value = evaluator.evaluate(cell);
    assertEquals(1, value.getNumberValue(), 0.001);
}

Example 43 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFWorkbook method bug47090b.

@Test
public void bug47090b() throws IOException {
    Workbook workbook = XSSFTestDataSamples.openSampleWorkbook("47090.xlsx");
    assertSheetOrder(workbook, "Sheet1", "Sheet2");
    workbook.removeSheetAt(1);
    assertSheetOrder(workbook, "Sheet1");
    workbook.createSheet();
    // Sheet0 because it uses "Sheet" + sheets.size() as starting point!
    assertSheetOrder(workbook, "Sheet1", "Sheet0");
    Workbook read = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    assertSheetOrder(read, "Sheet1", "Sheet0");
    read.close();
    workbook.close();
}

Example 44 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFChartTitle method createWorkbookWithChart.

private Workbook createWorkbookWithChart() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("linechart");
    final int NUM_OF_ROWS = 3;
    final int NUM_OF_COLUMNS = 10;
    // Create a row and put some cells in it. Rows are 0 based.
    Row row;
    Cell cell;
    for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
            cell = row.createCell((short) colIndex);
            cell.setCellValue(colIndex * (rowIndex + 1));
        }
    }
    Drawing<?> drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);
    LineChartData data = chart.getChartDataFactory().createLineChartData();
    // Use a category axis for the bottom axis.
    ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
    data.addSeries(xs, ys1);
    data.addSeries(xs, ys2);
    chart.plot(data, bottomAxis, leftAxis);
    return wb;
}

Example 45 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFChartTitle method testExistingChartWithFormulaTitle.

@Test
public void testExistingChartWithFormulaTitle() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("chartTitle_withTitleFormula.xlsx");
    XSSFChart chart = getChartFromWorkbook(wb, "Sheet1");
    assertNotNull(chart);
    XSSFRichTextString originalTitle = chart.getTitleText();
    assertNotNull(originalTitle);
    assertEquals("", originalTitle.toString());
    String formula = chart.getTitleFormula();
    assertNotNull(formula);
    assertEquals("Sheet1!$E$1", formula);
    wb.close();
}