Example usage for org.apache.poi.ss.usermodel Workbook addToolPack

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook addToolPack.

Prototype

void addToolPack(UDFFinder toopack);

Source Link

Document

Register a new toolpack in this workbook.

Usage

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/** Creates an instance of new {@link XSSFWorkbook}. */
public static Workbook newWorkbook() {
    Workbook book = new XSSFWorkbook();
    book.addToolPack(Functions.getUdfFinder());
    return book;//from  w ww .  j  a v a 2s .  co m
}

---

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/** Creates an instance of new {@link XSSFWorkbook} from {@link InputStream}. */
public static Workbook newWorkbook(InputStream original) {
    try {/*from ww w. j  a  v a 2  s. c o m*/
        Workbook book = new XSSFWorkbook(original);
        book.addToolPack(Functions.getUdfFinder());
        return book;
    } catch (IOException e) {
        throw new CalculationEngineException(e);
    }
}

---

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/**
 * For given {@link Workbook} does convert everything to new {@link DataModel} structure.
 * Does copy all supported fields (for supported fields see {@link DataModel} class.
 *///from  www.  j  a  va  2s. c  om
static IDataModel toDataModel(final Workbook workbook) {
    if (workbook == null) {
        return null;
    }

    //add custom functions information
    workbook.addToolPack(getUdfFinder());

    Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported
    if (s == null) {
        return null;
    }

    IDataModel dm = new DataModel(s.getSheetName());

    for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) {
        Row r = s.getRow(i);
        if (r == null) {
            continue;
        }

        DmRow row = new DmRow(i);
        dm.setRow(i, row);

        for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) {
            Cell c = r.getCell(j);
            if (c == null) {
                continue;
            }

            DmCell cell = new DmCell();
            row.setCell(j, cell);

            cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j)));
            cell.setContent(ConverterUtils.resolveCellValue(c));
        }
    }

    EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook);

    for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) {
        Name name = workbook.getNameAt(nIdx);

        String reference = name.getRefersToFormula();
        if (reference == null) {
            continue;
        }

        if (A1Address.isAddress(removeSheetFromNameRef(reference))) {
            dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference)));
        } else if (isFormula(reference, evaluationWbook)) {
            dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference));
        } else {
            dm.setNamedValue(name.getNameName(), CellValue.from(reference));
        }
    }

    return dm;
}

---

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Does the same logic as {@link #toDataModels(Workbook, String)}, but for each new {@link IDataModel} created
 * also created an instance of given {@link FunctionMeta}.
 *///  ww w.ja v a2  s.  c  o m
static <T extends FunctionMeta> Map<T, IDataModel> toMetaFunctions(Workbook book, Class<T> metaClass) {
    Map<T, IDataModel> map = new HashMap<>();

    book.addToolPack(Functions.getUdfFinder());
    final FormulaParsingWorkbook parsingBook = create((XSSFWorkbook) book);

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    for (Row r : s) {
        for (Cell c : r) {
            if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
                continue;
            }

            try {
                String formula = c.getCellFormula();
                String keyword = metaClass.getAnnotation(FunctionMeta.MetaFunctionKeyword.class).value();

                if (!formula.startsWith(keyword)) {
                    continue;
                }

                IDataModel dataModel = createDataModelFromCell(s, parsingBook,
                        fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
                T meta = createAttributeFunctionMeta(metaClass, formula, dataModel);

                map.put(meta, dataModel);
            } catch (Exception e) {
                log.debug("Warning while parsing custom excel formula. It is OK.", e);
            }
        }
    }

    return map;
}

---

From source file:com.wantdo.stat.excel.poi_src.formula.SettingExternalFunction.java

License:Apache License

public static void main(String[] args) throws IOException {

    Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook()

    // register the add-in
    wb.addToolPack(new BloombergAddIn());

    Sheet sheet = wb.createSheet();/*from  w w  w. j  a v a  2s  .c  om*/
    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();

}

---

From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java

License:Apache License

public static void main(String[] args) {

    if (args.length != 2) {
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;/*from   ww w  .j a  v a2s . c o m*/
    }

    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);

    File workbookFile = new File(args[0]);

    try {
        FileInputStream fis = new FileInputStream(workbookFile);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        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);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

---