Example usage for org.apache.poi.ss.usermodel Cell getCellFormula

Introduction

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

Prototype

String getCellFormula();

Source Link

Document

Return a formula for the cell, for example, SUM(C4:E4)

Usage

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Called by the TemplateReader.//from   w  w w . j a  va2s .  c om
 *
 * @param sheet
 *            the sheet
 * @param templateSheet
 *            the template sheet
 * @throws Exception
 *             the exception
 */
private void populateColumns(Sheet sheet, TemplateSheet templateSheet) throws Exception {
    Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap();
    Row headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new Exception("No header row found! Please create one.");
    }

    Row styleRow = sheet.getRow(1);
    if (styleRow == null) {
        throw new Exception(
                "Sheet name " + templateSheet.getSheetName() + ": No style row found! Please create one.");
    }

    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        TemplateColumn column = new TemplateColumn();
        Cell headerCell = headerRow.getCell(i);
        Cell styleCell = styleRow.getCell(i);

        if (headerCell == null) {
            throw new Exception("The following column appears to be empty: " + i);
        }

        if (styleCell == null) {
            throw new Exception("The following style position is not defined: " + i);
        }

        String columnName = headerCell.getStringCellValue();
        // We want to use the style cell (row below) as the header will
        // always be text.
        Integer cellType = styleCell.getCellType();

        column.setColumnPos(i);
        column.setCellStyle(styleCell.getCellStyle());
        column.setColumnName(columnName);
        column.setCellType(cellType);
        if (cellType == Cell.CELL_TYPE_FORMULA) {
            column.setCellFormula(styleCell.getCellFormula());
        }

        columnMap.put(columnName, column);
    }

    populateColumnMappings(columnMap);
}

---

From source file:com.blackducksoftware.tools.commonframework.standard.workbook.CsvWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {

    int numSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {

        File curOutputFile = getCurrentOutputFile(filePath, numSheets, i);

        CSVWriter pw = new CSVWriter(new OutputStreamWriter(new FileOutputStream(curOutputFile)),
                CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r\n");

        try {/*from w  w  w  .ja va 2  s. c  o m*/
            Sheet sheet = wb.getSheetAt(i);
            for (Row row : sheet) {
                List<String> cells = new ArrayList<String>();
                String cellValue = "";
                for (Cell cell : row) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean cellValueBoolean = cell.getBooleanCellValue();
                        cellValue = cellValueBoolean ? "true" : "false";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "<error: " + cell.getErrorCellValue() + ">";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double cellValueDouble = cell.getNumericCellValue();
                        cellValue = Double.toString(cellValueDouble);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    default:
                        break;
                    }

                    cells.add(cellValue);
                }
                String[] typeExample = new String[cells.size()];
                String[] cellArray = cells.toArray(typeExample);
                pw.writeNext(cellArray);
            }
        } finally {
            pw.close();
        }
    }
}

---

From source file:com.canoo.webtest.plugins.exceltest.ExcelCellUtils.java

License:Open Source License

public static String getCellValueAt(final Cell cell) {
    if (null == cell) {
        return "";
    }/*w  w w .  j a  va2 s.co  m*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        return asStringTrimInts(cell.getNumericCellValue());
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_ERROR:
        return "Error Code " + String.valueOf(cell.getErrorCellValue() & 0xFF);
    ///CLOVER:OFF there are currently no other types.  Potentially more in future?
    default:
        LOG.warn("Cell Type not supported: " + cell.getCellType());
        return "";
    ///CLOVER:ON
    }
}

---

From source file:com.canoo.webtest.plugins.exceltest.ExcelVerifyCellSum.java

License:Open Source License

private void checkFormula(final Cell excelCell) {
    if (excelCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        final String expectedValue = "SUM(" + getRange() + ")".toUpperCase();
        final String actualValue = excelCell.getCellFormula().toUpperCase();
        if (verifyStrings(expectedValue, actualValue)) {
            return;
        }/* w  w  w  . j a  v  a  2s .  co m*/
        throw new StepFailedException("Unexpected formula in cell " + getCellReferenceStr(), expectedValue,
                actualValue);
    } else if (excelCell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
        throw new StepFailedException(
                "Cell " + getCellReferenceStr() + " does not contain a formula or a numeric value.");
    }
}

---

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

private Text getCellValue(Cell cell) {
    Text out = new Text();
    CellType cellType = cell.getCellTypeEnum();

    if (cellType == CellType.STRING) {
        out.set(cell.getStringCellValue());
    } else if (cellType == CellType.NUMERIC) {
        out.set(String.valueOf(cell.getNumericCellValue()));
    } else if (cellType == CellType.FORMULA) {
        out.set(cell.getCellFormula());
    } else if (cellType == CellType.ERROR) {
        out.set(String.valueOf(cell.getErrorCellValue()));
    } else if (cellType == CellType.BOOLEAN) {
        out.set(String.valueOf(cell.getBooleanCellValue()));
    } else {/*from   w w w.j a  v  a 2 s. c  o  m*/
        out.set("");
    }

    return out;
}

---

From source file:com.compassplus.gui.MainForm.java

private boolean analyzeCell(Workbook wb, Sheet sheet, Row row, Cell cell, ScriptEngine engine,
        Bindings bindings) {/* w  w  w . j a v  a  2 s  .  c o m*/
    try {
        String formula = cell.getCellFormula();
        cell.setCellFormula(formula);
    } catch (Exception e) {
    }
    try {
        String expr = cell.getStringCellValue();
        short type = 0;
        if (expr.contains("__REMOVE")) {
            type = __REMOVE;
        } else if (expr.contains("__INSERT")) {
            type = __INSERT;
        }
        if (type > 0) {
            try {
                expr = expr.substring(expr.indexOf("(") + 1);
                expr = expr.substring(0, expr.lastIndexOf(")"));
                expr = expr.replaceAll("\\s", "");

                if (type == __REMOVE) {
                    cell.setCellValue("");
                    Object val = null;
                    val = engine.eval(expr, bindings);
                    if (val instanceof Boolean) {
                        return (Boolean) val;
                    } else {
                        throw new Exception("result is not boolean");
                    }
                } else if (type == __INSERT) {
                    cell.setCellValue("");
                    Object val = null;
                    val = engine.eval(expr, bindings);

                    if (!(val instanceof String && ((String) val).equals(""))) {
                        CellStyle cs = wb.createCellStyle();
                        CellStyle csT = cell.getCellStyle();
                        cs.cloneStyleFrom(csT);
                        String format = (getCurrentProposalForm().getProposal().getCurrency()
                                .getSymbol() != null ? "\""
                                        + getCurrentProposalForm().getProposal().getCurrency().getSymbol()
                                        + "\" " : "")
                                + "#,##0"
                                + (getCurrentProposalForm().getProposal().getCurrency().getSymbol() == null
                                        ? " \"" + getCurrentProposalForm().getProposal().getCurrency().getName()
                                                + "\""
                                        : "");
                        if (expr.contains("$PRICE") || expr.contains("$MAN-DAY-RATE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(format));
                        } else if (expr.contains("$SUPPORT_RATE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                        } else if (expr.contains("$VALUE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("#,##0"));
                        }
                        cell.setCellStyle(cs);
                    }
                    if (val instanceof Boolean) {
                        cell.setCellValue((Boolean) val);
                    } else if (val instanceof Number) {
                        cell.setCellValue(((Number) val).doubleValue());
                    } else if (val instanceof String) {
                        cell.setCellValue((String) val);
                    } else {
                        throw new Exception("result type is unknown");
                    }
                }

            } catch (Exception e) {
                log.error("Bad" + (type == __REMOVE ? " __REMOVE" : (type == __INSERT ? " __INSERT" : ""))
                        + " expression: " + expr);
                cell.setCellValue("");
            }
        }
    } catch (Exception e) {
    }
    return false;
}

---

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Read records from Excel file/*from w ww.  j  ava  2s  .co m*/
 *
 * @param vcConfig The validator configuration object.
 * @param bUseTupleOld
 * @param filename Name of the Excel file.
 * @param doc Document conatins the request.
 * @param iResponsenode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param startcolumn column index from which data to be read.
 * @param endcolumn column index upto which data to be read.
 */
public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc,
        int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn)
        throws FileException {

    Workbook book = null;
    Sheet sheet;
    Cell cell;
    Row row;
    FileInputStream fileinp = null;
    String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
    try {
        int iRow, iCol, sheetindex, noofsheets;
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                book = (Workbook) new HSSFWorkbook(fileinp);
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                book = new XSSFWorkbook(fileinp);
            } else {
                //ERROR
                fileinp.close();
            }
        } else {
            //ERROR
            fileinp.close();
        }

        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        for (; sheetindex < noofsheets; sheetindex++) {
            sheet = book.getSheetAt(sheetindex);

            if (endrow == -1) {
                endrow = sheet.getLastRowNum();
                if (startrow == -1) {
                    startrow = 0;
                }
            } else {
                endrow = startrow + endrow - 1;
                if (endrow > sheet.getLastRowNum()) {
                    endrow = sheet.getLastRowNum();
                }
            }

            if (endcolumn == -1) {
                endcolumn = 30;
                if (startcolumn == -1) {
                    startcolumn = 0;
                }
            }
            for (int i = startrow; i <= endrow; i++) {

                row = sheet.getRow(i);

                if (row == null) {
                    int iTup = doc.createElement("tuple", iResponsenode);

                    if (bUseTupleOld) {
                        iTup = doc.createElement("old", iTup);
                    }
                    iRow = doc.createElement(sRecordName, iTup);
                    //Node.setAttribute(iRow, "id", "" + i);
                    ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                            .listIterator();
                    while (fieldslist.hasNext()) {
                        FieldType excelfields = (FieldType) fieldslist.next();
                        String sColumnName = excelfields.sFieldName;

                        iCol = doc.createTextElement(sColumnName, "", iRow);
                    }
                    continue;
                }
                int iTup = doc.createElement("tuple", iResponsenode);
                if (bUseTupleOld) {
                    iTup = doc.createElement("old", iTup);
                }
                iRow = doc.createElement(sRecordName, iTup);
                ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                        .listIterator();
                while (fieldslist.hasNext()) {
                    FieldType excelfields = (FieldType) fieldslist.next();
                    int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex);
                    cell = row.getCell(iColumnIndex);
                    String sColumnName = excelfields.sFieldName;
                    if (cell == null) {
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        continue;
                    }
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow);

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpledateformat = new SimpleDateFormat(
                                    "yyyy-MM-dd 'T' HH:mm:ss.S");
                            iCol = doc.createTextElement(sColumnName,
                                    "" + simpledateformat.format(cell.getDateCellValue()), iRow);

                        } else {
                            iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow);
                        break;
                    default:
                        System.out.println("default");
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        throw new FileException(e, LogMessages.FILE_NOT_FOUND);
    } catch (IOException e) {
        throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename);
    } finally {
        try {
            fileinp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

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

License:Apache License

/** Returns the new {@link CellValue} from provided {@link Cell}. */
public static ICellValue resolveCellValue(Cell c) {
    if (c == null) {
        return CellValue.BLANK;
    }/*from   ww  w. jav a2 s.c  o m*/

    switch (c.getCellType()) {
    case CELL_TYPE_NUMERIC: {
        return CellValue.from(c.getNumericCellValue());
    }
    case CELL_TYPE_STRING: {
        return CellValue.from(c.getStringCellValue());
    }
    case CELL_TYPE_BOOLEAN: {
        return CellValue.from(c.getBooleanCellValue());
    }
    case CELL_TYPE_ERROR: {
        return CellValue.from(forInt(c.getErrorCellValue()).getString());
    }
    case CELL_TYPE_BLANK: {
        return CellValue.BLANK;
    }
    case CELL_TYPE_FORMULA: {
        return CellValue.from(String.format("%s%s", FORMULA_PREFIX, c.getCellFormula()));
    }
    default: {
        throw new CalculationEngineException(
                String.format("Cell's type %s is not supported.", c.getCellType()));
    }
    }
}

---

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

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} for given function name.
 * Useful for formula with particular functions. Does scan IDataModel for exact formula use and create new 
 * {@link IDataModel} for every formula found.
 *///w  ww  . j a v a 2  s . co m
static List<IDataModel> toDataModels(final Workbook book, final String function) {
    if (book == null || function == null) {
        return emptyList();
    }
    List<IDataModel> list = new LinkedList<>();

    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 {
                if (ConverterUtils.isFunctionInFormula(c.getCellFormula(), function)) {
                    list.add(createDataModelFromCell(s, parsingBook,
                            fromRowColumn(c.getRowIndex(), c.getColumnIndex())));
                }
            } catch (FormulaParseException e) {
                log.warn("Warning while parsing excel formula. Probably this is OK.", e);
            }
        }
    }

    return list;
}

---

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}.
 *//*from  w  w  w.  j a  v a  2 s.  c om*/
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;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• »