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

Example 6 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project Robot-Scouter by SUPERCILEX.

the class SpreadsheetExporter method buildTeamSheet.

@AddTrace(name = "buildTeamSheet")
private void buildTeamSheet(TeamHelper teamHelper, Sheet teamSheet) {
    List<Scout> scouts = mScouts.get(teamHelper);
    if (scouts.isEmpty()) {
        Workbook workbook = teamSheet.getWorkbook();
        workbook.removeSheetAt(workbook.getSheetIndex(teamSheet));
        return;
    }
    Row header = teamSheet.createRow(0);
    // Create empty top left corner cell
    header.createCell(0);
    List<Metric<?>> orderedMetrics = scouts.get(scouts.size() - 1).getMetrics();
    for (int i = 0; i < orderedMetrics.size(); i++) {
        Metric metric = orderedMetrics.get(i);
        Row row = teamSheet.createRow(i + 1);
        setupRow(row, teamHelper, metric);
    }
    for (int i = 0, column = 1; i < scouts.size(); i++, column++) {
        Scout scout = scouts.get(i);
        List<Metric<?>> metrics = scout.getMetrics();
        Cell cell = header.getCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
        String name = scout.getName();
        cell.setCellValue(TextUtils.isEmpty(name) ? "Scout " + column : name);
        cell.setCellStyle(mCache.getColumnHeaderStyle());
        columnIterator: for (int j = 0, rowNum = 1; j < metrics.size(); j++, rowNum++) {
            Metric metric = metrics.get(j);
            Row row = teamSheet.getRow(rowNum);
            if (row == null) {
                setupRowAndSetValue(teamSheet.createRow(rowNum), teamHelper, metric, column);
            } else {
                List<Row> rows = getAdjustedList(teamSheet);
                for (Row row1 : rows) {
                    Cell cell1 = row1.getCell(0);
                    if (TextUtils.equals(mCache.getMetricKey(row1), metric.getRef().getKey())) {
                        setRowValue(column, metric, row1);
                        if (TextUtils.isEmpty(cell1.getStringCellValue())) {
                            cell1.setCellValue(metric.getName());
                        }
                        continue columnIterator;
                    }
                }
                setupRowAndSetValue(teamSheet.createRow(teamSheet.getLastRowNum() + 1), teamHelper, metric, column);
            }
        }
    }
    if (scouts.size() > SINGLE_ITEM) {
        buildAverageColumn(teamSheet, teamHelper);
    }
}

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

Example 8 with Workbook

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

the class SettingExternalFunction method main.

public static void main(String[] args) throws IOException {
    // or new HSSFWorkbook()
    Workbook wb = new XSSFWorkbook();
    // register the add-in
    wb.addToolPack(new BloombergAddIn());
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
    row.createCell(1).setCellFormula("BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") ");
    row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") ");
    FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx");
    wb.write(out);
    out.close();
    wb.close();
}

Example 9 with Workbook

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

the class SpreadsheetHandler method handleWorkbook.

public void handleWorkbook(Workbook wb) throws IOException {
    // try to access some of the content
    readContent(wb);
    // write out the file
    writeToArray(wb);
    // access some more content (we had cases where writing corrupts the data in memory)
    readContent(wb);
    // write once more
    ByteArrayOutputStream out = writeToArray(wb);
    // read in the written file
    Workbook read;
    try {
        read = WorkbookFactory.create(new ByteArrayInputStream(out.toByteArray()));
    } catch (InvalidFormatException e) {
        throw new IllegalStateException(e);
    }
    assertNotNull(read);
    readContent(read);
    extractEmbedded(read);
    modifyContent(read);
    read.close();
}

Example 10 with Workbook

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

the class CellUtil method setFont.

/**
     * Take a cell, and apply a font to it
     *
     * @param cell the cell to set the alignment for
     * @param font The Font that you want to set.
     * @throws IllegalArgumentException if <tt>font</tt> and <tt>cell</tt> do not belong to the same workbook
     */
public static void setFont(Cell cell, Font font) {
    // Check if font belongs to workbook
    Workbook wb = cell.getSheet().getWorkbook();
    final short fontIndex = font.getIndex();
    if (!wb.getFontAt(fontIndex).equals(font)) {
        throw new IllegalArgumentException("Font does not belong to this workbook");
    }
    // Check if cell belongs to workbook
    // (checked in setCellStyleProperty)
    setCellStyleProperty(cell, FONT, fontIndex);
}