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

Example 46 with Row

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

the class TestXSSFFormulaEvaluation method setCellFormula.

/**
     * @param row 0-based
     * @param column 0-based
     */
private void setCellFormula(Sheet sheet, int row, int column, String formula) {
    Row r = sheet.getRow(row);
    if (r == null) {
        r = sheet.createRow(row);
    }
    Cell cell = r.getCell(column);
    if (cell == null) {
        cell = r.createCell(column);
    }
    cell.setCellType(CellType.FORMULA);
    cell.setCellFormula(formula);
}

Example 47 with Row

use of org.apache.poi.ss.usermodel.Row 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 48 with Row

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

the class MemoryUsage method numberSpreadsheet.

/**
     * Generate a spreadsheet who's all cell values are numbers.
     * The data is generated until OutOfMemoryError. 
     * <p>
     * as compared to {@link #mixedSpreadsheet(org.apache.poi.ss.usermodel.Workbook, int)},
     * this method does not set string values and, hence, does not involve the Shared Strings Table.
     * </p>
     *
     * @param wb        the workbook to write to
     * @param numCols   the number of columns in a row
     */
public static void numberSpreadsheet(Workbook wb, int numCols) {
    System.out.println("Testing " + wb.getClass().getName());
    printMemoryUsage("before");
    int i = 0, cnt = 0;
    try {
        Sheet sh = wb.createSheet();
        for (i = 0; ; i++) {
            Row row = sh.createRow(i);
            for (int j = 0; j < numCols; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
                cnt++;
            }
        }
    } catch (OutOfMemoryError er) {
        System.out.println("Failed at row=" + i + ", objects : " + cnt);
    } catch (final Exception e) {
        System.out.println("Unable to reach an OutOfMemoryError");
        System.out.println(e.getClass().getName() + ": " + e.getMessage());
    }
    printMemoryUsage("after");
}

Example 49 with Row

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

the class TestBugs method bug44693.

/**
     * User reported the wrong number of rows from the
     *  iterator, but we can't replicate that
     */
@Test
public void bug44693() throws Exception {
    HSSFWorkbook wb = openSample("44693.xls");
    HSSFSheet s = wb.getSheetAt(0);
    // Rows are 1 to 713
    assertEquals(0, s.getFirstRowNum());
    assertEquals(712, s.getLastRowNum());
    assertEquals(713, s.getPhysicalNumberOfRows());
    // Now check the iterator
    int rowsSeen = 0;
    for (Iterator<Row> i = s.rowIterator(); i.hasNext(); ) {
        Row r = i.next();
        assertNotNull(r);
        rowsSeen++;
    }
    assertEquals(713, rowsSeen);
    wb.close();
}

Example 50 with Row

use of org.apache.poi.ss.usermodel.Row 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();
    }
}