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

Example 46 with Workbook

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

the class TestXSSFWorkbook method testSetFirstVisibleTab_57373.

@Test
public void testSetFirstVisibleTab_57373() throws IOException {
    Workbook wb = new XSSFWorkbook();
    try {
        /*Sheet sheet1 =*/
        wb.createSheet();
        Sheet sheet2 = wb.createSheet();
        int idx2 = wb.getSheetIndex(sheet2);
        Sheet sheet3 = wb.createSheet();
        int idx3 = wb.getSheetIndex(sheet3);
        // add many sheets so "first visible" is relevant
        for (int i = 0; i < 30; i++) {
            wb.createSheet();
        }
        wb.setFirstVisibleTab(idx2);
        wb.setActiveSheet(idx3);
        //wb.write(new FileOutputStream(new File("C:\\temp\\test.xlsx")));
        assertEquals(idx2, wb.getFirstVisibleTab());
        assertEquals(idx3, wb.getActiveSheetIndex());
        Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb);
        sheet2 = wbBack.getSheetAt(idx2);
        sheet3 = wbBack.getSheetAt(idx3);
        assertEquals(idx2, wb.getFirstVisibleTab());
        assertEquals(idx3, wb.getActiveSheetIndex());
        wbBack.close();
    } finally {
        wb.close();
    }
}

Example 47 with Workbook

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

the class TestXSSFLineChartData method testOneSeriePlot.

@Test
public void testOneSeriePlot() throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = new SheetBuilder(wb, plotData).build();
    Drawing<?> drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
    Chart chart = drawing.createChart(anchor);
    ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
    ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    LineChartData lineChartData = chart.getChartDataFactory().createLineChartData();
    ChartDataSource<String> xs = DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
    ChartDataSource<Number> ys = DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
    LineChartSeries series = lineChartData.addSeries(xs, ys);
    assertNotNull(series);
    assertEquals(1, lineChartData.getSeries().size());
    assertTrue(lineChartData.getSeries().contains(series));
    chart.plot(lineChartData, bottomAxis, leftAxis);
    wb.close();
}

Example 48 with Workbook

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

the class TestBugs method bug45970.

/**
     * Problems with formula references to
     *  sheets via URLs
     */
@Test
public void bug45970() throws Exception {
    HSSFWorkbook wb1 = openSample("FormulaRefs.xls");
    assertEquals(3, wb1.getNumberOfSheets());
    HSSFSheet s = wb1.getSheetAt(0);
    HSSFRow row;
    row = s.getRow(0);
    assertEquals(CellType.NUMERIC, row.getCell(1).getCellTypeEnum());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(1);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("B1", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(2);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("Sheet1!B1", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(3);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(4);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("'[$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    // Link our new workbook
    Workbook externalWb1 = new HSSFWorkbook();
    externalWb1.createSheet("Sheet1");
    wb1.linkExternalWorkbook("$http://gagravarr.org/FormulaRefs2.xls", externalWb1);
    // Change 4
    row.getCell(1).setCellFormula("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2");
    row.getCell(1).setCellValue(123.0);
    // Link our new workbook
    Workbook externalWb2 = new HSSFWorkbook();
    externalWb2.createSheet("Sheet1");
    wb1.linkExternalWorkbook("$http://example.com/FormulaRefs.xls", externalWb2);
    // Add 5
    row = s.createRow(5);
    row.createCell(1, CellType.FORMULA);
    row.getCell(1).setCellFormula("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1");
    row.getCell(1).setCellValue(234.0);
    // Re-test
    HSSFWorkbook wb2 = writeOutAndReadBack(wb1);
    wb1.close();
    s = wb2.getSheetAt(0);
    row = s.getRow(0);
    assertEquals(CellType.NUMERIC, row.getCell(1).getCellTypeEnum());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(1);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("B1", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(2);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("Sheet1!B1", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(3);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula());
    assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(4);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula());
    assertEquals(123.0, row.getCell(1).getNumericCellValue(), 0);
    row = s.getRow(5);
    assertEquals(CellType.FORMULA, row.getCell(1).getCellTypeEnum());
    assertEquals("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
    assertEquals(234.0, row.getCell(1).getNumericCellValue(), 0);
    wb2.close();
}

Example 49 with Workbook

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

the class TestBugs method bug56325a.

@Test
public void bug56325a() throws IOException {
    HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("56325a.xls");
    HSSFSheet sheet = wb1.cloneSheet(2);
    wb1.setSheetName(3, "Clone 1");
    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));
    wb1.setPrintArea(3, "$A$4:$C$10");
    sheet = wb1.cloneSheet(2);
    wb1.setSheetName(4, "Clone 2");
    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));
    wb1.setPrintArea(4, "$A$4:$C$10");
    wb1.removeSheetAt(2);
    Workbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    assertEquals(4, wb2.getNumberOfSheets());
    wb2.close();
    wb1.close();
}

Example 50 with Workbook

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

the class UserDefinedFunctionExample method main.

public static void main(String[] args) throws Exception {
    if (args.length != 2) {
        // e.g. src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls Sheet1!B4
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;
    }
    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);
    File workbookFile = new File(args[0]);
    Workbook workbook = WorkbookFactory.create(workbookFile, null, true);
    try {
        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };
        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);
        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);
        CellValue value = evaluator.evaluate(cell);
        System.out.println("returns value: " + value);
    } finally {
        workbook.close();
    }
}