Example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluate

List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluate

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. FormulaEvaluator
  6. evaluate

Introduction

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

Prototype

CellValue evaluate(Cell cell);

Source Link

Document

If cell contains a formula, the formula is evaluated and returned, else the CellValue simply copies the appropriate cell value from the cell and also its cell type.

Usage

From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java

License:Open Source License

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 *///from w  w w. j a va 2  s .  c o m
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            int xlsType = Cell.CELL_TYPE_STRING;

            try {
                xlsType = cell.getCellType() == Cell.CELL_TYPE_FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case Cell.CELL_TYPE_BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case Cell.CELL_TYPE_BLANK:
                currentType = BLANK;
                break;
            case Cell.CELL_TYPE_FORMULA:
            case Cell.CELL_TYPE_STRING:
                currentType = STRING.getName();
                break;
            case Cell.CELL_TYPE_ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}

From source file:org.talend.dataprep.schema.xls.XlsUtils.java

License:Open Source License

/**
 *
 * @param cell//from  w w w .j  a v  a 2s . c  om
 * @param formulaEvaluator
 * @return return the cell value as String (if needed evaluate the existing formula)
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case Cell.CELL_TYPE_ERROR:
        return "Cell Error type";
    case Cell.CELL_TYPE_FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case Cell.CELL_TYPE_NUMERIC:
        return getNumericValue(cell, null, false);
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * return cell value with format.//from   w w w  .j  a v a 2 s.co m
 * 
 * @param poiCell
 *            cell.
 * @param formulaEvaluator
 *            formula evaluator.
 * @param dataFormatter
 *            data formatter.
 * @return cell string value with format.
 */
@SuppressWarnings("deprecation")
public static String getCellValueWithFormat(final Cell poiCell, final FormulaEvaluator formulaEvaluator,
        final DataFormatter dataFormatter) {

    if (poiCell == null) {
        return null;
    }

    String result;
    try {
        CellType cellType = poiCell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            cellType = formulaEvaluator.evaluate(poiCell).getCellTypeEnum();
        }
        if (cellType == CellType.ERROR) {
            result = "";
        } else {
            result = dataFormatter.formatCellValue(poiCell, formulaEvaluator);
        }
    } catch (Exception e) {
        LOG.log(Level.SEVERE,
                "Web Form WebFormHelper getCellValue Error row = " + poiCell.getRowIndex() + " column = "
                        + poiCell.getColumnIndex() + " error = " + e.getLocalizedMessage()
                        + "; Change return result to blank",
                e);
        result = "";
    }

    return result;
}

From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java

License:Open Source License

@RequestMapping(method = RequestMethod.POST)
protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile,
        @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response)
        throws Exception {

    // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory?
    File file = multipartToFile(uploadFile);
    String mergedResultFileName = "merged_" + file.getName();
    FileInputStream fis = new FileInputStream(file);

    // Finds the workbook instance of XLSX file
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

    // number of sheets in the workbook
    int numberOfSheets = workbook.getNumberOfSheets();

    // contains all values of the merge column across all sheets
    ArrayList<String> mergeColumnValues = new ArrayList<String>();

    // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>>
    HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>();

    // maps sheet index to the headers in that sheet
    HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>();

    // how many copies of headers need to be created for each sheet
    HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>();

    // loop through the sheets in the workbook and populate the variables
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        int mergeColumnIndex = -1; // index of the merge column in this sheet
        int rowIteratorIndex = 0; // index of current row iteration

        // collect all of the merge column rows in each sheet
        Iterator<Row> rowIterator = sheet.rowIterator();

        int maxSheetRowCountForCurrentSheet = 0;
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();
            if (rowIteratorIndex == 0) {
                // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders
                // and add it to sheetIndexToSheetColumnHeaders
                ArrayList<String> sheetColumnHeaders = new ArrayList<String>();

                int rowCellIteratorIndex = 0;
                Iterator<Cell> topRowCellIterator = row.cellIterator();
                while (topRowCellIterator.hasNext()) {
                    Cell topRowCell = topRowCellIterator.next();
                    String topRowCellString = topRowCell.toString();
                    if (!topRowCellString.isEmpty()) {
                        sheetColumnHeaders.add(topRowCellString);
                    }//from  w  ww. j  a va2  s  . c  o m
                    if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) {
                        // this is the mergeColumn. Remember the column index
                        if (mergeColumnIndex == -1) {
                            mergeColumnIndex = rowCellIteratorIndex;
                        } else {
                            // there are multiple mergeColumnTitles in this sheet. Let the user know and exit
                            ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets");
                            mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle
                                    + "\" in worksheet #" + (sheetIndex + 1)
                                    + ". You can have only one merge column per worksheet. Please fix and try again.");
                            return mav;
                        }
                    }
                    rowCellIteratorIndex++;
                }
                sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders);
            } else {
                // for rows that are not the top row (header)
                // 1. get all the mergeColumnValues
                // 2. populate mergeColumnValueToSheetRows
                // 3. calculate sheetIndexToMaxSheetRowCount
                Cell mergeColumnValueCell = row.getCell(mergeColumnIndex);
                if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) {

                    objFormulaEvaluator.evaluate(mergeColumnValueCell);
                    String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell,
                            objFormulaEvaluator);

                    HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows
                            .get(mergeColumnValueString);
                    if (sheetIndexToSheetRows == null) {
                        sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>();
                        mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows);
                    }
                    ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex);
                    if (sheetRows == null) {
                        sheetRows = new ArrayList<>();
                        sheetIndexToSheetRows.put(sheetIndex, sheetRows);
                    }
                    sheetRows.add(row);
                    if (sheetRows.size() > maxSheetRowCountForCurrentSheet) {
                        maxSheetRowCountForCurrentSheet = sheetRows.size();
                    }

                    Iterator<Cell> rowCellIterator = row.cellIterator();
                    int rowCellIteratorIndex = 0;
                    while (rowCellIterator.hasNext()) {
                        Cell rowCell = rowCellIterator.next();
                        if (rowCellIteratorIndex == mergeColumnIndex) {
                            // this is a merge column cell, so add its value to mergeColumnValues
                            if (!rowCell.toString().isEmpty()) {
                                objFormulaEvaluator.evaluate(rowCell);
                                String rowCellValueString = objDefaultFormat.formatCellValue(rowCell,
                                        objFormulaEvaluator);
                                if (!mergeColumnValues.contains(rowCellValueString)) {
                                    mergeColumnValues.add(rowCellValueString);
                                }
                            }
                        }
                        rowCellIteratorIndex++;
                    }
                }
            }
            rowIteratorIndex++;
        }
        sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet);
    }

    // Now we are ready to make the merge sheet. We will be writing one row at a time.

    Workbook wb = new XSSFWorkbook(); // new output workbook
    Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet

    // make the header row
    Row headerRow = mergedSheet.createRow(0);

    // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues.
    Cell mergeColumnHeaderCell = headerRow.createCell(0);
    mergeColumnHeaderCell.setCellValue(mergeColumnTitle);

    // current column index "cursor" where we will be writing to
    int cellIndexWithoutMergeColumn = 1;

    // make the header row
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {

        Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
        ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        String sheetName = sheet.getSheetName();

        for (int i = 0; i < maxSheetRowCount; i++) {
            for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders
                    .size(); sheetColumnHeaderIndex++) {
                String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex);
                if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) {
                    String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) ";
                    Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn);
                    headerCell.setCellValue(newSheetColumnHeader);
                    cellIndexWithoutMergeColumn++;
                }
            }
        }
    }

    // now make all the non-header rows
    for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues
            .size(); mergeColumnValueIndex++) {
        String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex);
        HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows
                .get(mergeColumnValue);
        if (mergeColumnValueSheetRow == null) {
            System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: "
                    + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue);
            continue;
        }

        Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row;

        // reset current cursor as we make each row
        cellIndexWithoutMergeColumn = 0;

        // first column will be the merge column value
        Cell mergeColumnCell = row.createCell(0);
        mergeColumnCell.setCellValue(mergeColumnValue);
        cellIndexWithoutMergeColumn++;

        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex);
            int currentSheetSheetRowIndex = 0;
            ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);

            if (sheetRows != null) {
                for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) {
                    Row sheetRow = sheetRows.get(sheetRowIndex);
                    for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) {
                        String sheetHeader = sheetHeaders.get(sheetHeaderIndex);
                        if (!sheetHeader.equals(mergeColumnTitle)) {
                            Cell cell = sheetRow.getCell(sheetHeaderIndex);
                            Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                            objFormulaEvaluator.evaluate(cell);
                            String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                            exportCell.setCellValue(cellString);
                            cellIndexWithoutMergeColumn++;
                        }
                    }
                    currentSheetSheetRowIndex++;
                }
            }

            // some columns do not have any values to populate, so populate them with empty cells
            Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
            while (currentSheetSheetRowIndex < maxSheetRowCount) {
                for (int i = 0; i < sheetHeaders.size(); i++) {
                    String sheetHeader = sheetHeaders.get(i);
                    if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) {
                        Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                        exportCell.setCellValue("");
                        cellIndexWithoutMergeColumn++;
                    }
                }
                currentSheetSheetRowIndex++;
            }
        }
    }

    // write to response output
    response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\"");
    ServletOutputStream outputStream = response.getOutputStream();
    wb.write(outputStream);
    fis.close();

    return null;
}

From source file:se.sll.invoicedata.price.GeneratePriceList.java

License:Open Source License

private List<String> getServicePrice(Sheet sheet, int startRow, int serviceType) {
    FormulaEvaluator formulaEval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    List<String> priceList = new ArrayList<String>();

    for (int i = startRow; i < sheet.getPhysicalNumberOfRows(); i++) {
        Cell cell = sheet.getRow(i).getCell(serviceType);

        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                priceList.add(DECIMAL_FORMAT.format(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_FORMULA:
                double d = formulaEval.evaluate(cell).getNumberValue();
                priceList.add(DECIMAL_FORMAT.format(d));
                break;
            case Cell.CELL_TYPE_BLANK:
                priceList.add(DECIMAL_FORMAT.format(0));
                break;
            default:
                StringBuffer errorMsg = new StringBuffer("This type of cell is not handled by the program!");
                errorMsg.append(" cell type:").append(cell.getCellType());
                errorMsg.append(" cell row:").append(cell.getRowIndex());
                errorMsg.append(" cell column:").append(cell.getColumnIndex());
                errorMsg.append(" cell value:").append(cell.getStringCellValue());
                throw new IllegalStateException(errorMsg.toString());
            }//from  w w  w .  ja v  a  2  s .c o  m
        } else {
            priceList.add(DECIMAL_FORMAT.format(0));
        }
    }

    return priceList;
}

From source file:swift.selenium.WebHelper.java

License:Open Source License

/**
 * This method reads and returns data from each cell of a provided worksheet
 * /*from   w  w w .j  a v  a2  s. c  o  m*/
 * @param reqValue
 * @param reqSheet
 * @param rowIndex
 * @param inputHashTable
 * @return
 * @throws IOException
 */
@SuppressWarnings("null")
public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex,
        HashMap<String, Object> inputHashTable) throws IOException {
    HSSFCell reqCell = null;
    Object actualvalue = null;
    String req = "";
    DataFormatter fmt = new DataFormatter();

    if (inputHashTable.isEmpty() == true) {
        inputHashTable = getValueFromHashMap(reqSheet);
    }
    HSSFRow rowActual = reqSheet.getRow(rowIndex);
    if (inputHashTable.get(reqValue) == null) {

        TransactionMapping.report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet");
        TransactionMapping.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet");
    } else {
        actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString();         
        if (actualvalue != null) {
            int colIndex = Integer.parseInt(actualvalue.toString());
            reqCell = rowActual.getCell(colIndex);
            //TM 27-04-2015: Updated the code for formula in cells
            if (reqCell == null) {
                System.out.println(reqValue + " is Null");
            } else {
                HSSFWorkbook wb = reqCell.getSheet().getWorkbook(); //TM-05/05/2015: Get workbook instance from the worksheet
                HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); //TM-05/05/2015: To refresh all the formulas in the worksheet
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                CellValue cellValue = evaluator.evaluate(reqCell);
                int type = 0;
                if (cellValue != null) {
                    type = cellValue.getCellType();
                } else {
                    type = reqCell.getCellType();
                }

                switch (type) {
                case HSSFCell.CELL_TYPE_BLANK:
                    req = "";
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(reqCell)) {
                        SimpleDateFormat form = new SimpleDateFormat(
                                Automation.configHashMap.get("DATEFORMAT").toString());
                        req = form.format(reqCell.getDateCellValue());
                    } else
                        req = fmt.formatCellValue(reqCell, evaluator);
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    req = reqCell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    req = Boolean.toString(reqCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    req = "error";
                    break;
                }
            }
        }

        else {
            req = reqCell.getStringCellValue();
            System.out.println("null");
        }
    }
    return req;
}

From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java

License:Apache License

private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) {
    // generic part
    boolean costumFormatting = false;
    boolean formulae = false;
    boolean UDF = false;
    boolean hasComments = false;

    Set<String> udfs = new HashSet<String>();
    FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator();

    s.setAttribute("name", ss.getSheetName());
    s.setAttribute("firstRow", "" + ss.getFirstRowNum());
    s.setAttribute("lastRow", "" + ss.getLastRowNum());
    try {//from   w w  w .j  a va 2s  . c o m
        s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation());
    } catch (Throwable x) {
        //x.printStackTrace();
    }

    // shapes in detail? 
    Footer footer = ss.getFooter();
    if (footer != null) {
        s.setAttribute("footer", "true");
    }
    Header header = ss.getHeader();
    if (header != null) {
        s.setAttribute("header", "true");
    }
    PaneInformation paneInformation = ss.getPaneInformation();
    if (paneInformation != null) {
        s.setAttribute("panels", "true");
    }

    HSSFSheet hs = null;
    XSSFSheet xs = null;
    if (ss instanceof HSSFSheet) {
        hs = (HSSFSheet) ss;
        try {
            if (hs.getDrawingPatriarch() != null) {
                if (hs.getDrawingPatriarch().containsChart())
                    s.addContent(new Element("charts", sn));
                if (hs.getDrawingPatriarch().countOfAllChildren() > 0)
                    s.addContent(new Element("shapes", sn));
            }
        } catch (Exception x) {
            x.printStackTrace();
        }

        if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) {
            s.setAttribute("conditionalFormatting", "true");
        }
    }
    if (ss instanceof XSSFSheet) {
        xs = (XSSFSheet) ss;

    }
    Iterator<Row> rows = ss.rowIterator();

    int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0);
    int endColumn = 0;
    while (rows.hasNext()) {
        Row row = rows.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    s.setAttribute("firstColumn", "" + firstColumn);
    s.setAttribute("lastColumn", "" + endColumn);
    rows = ss.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row)
            if (cell != null) {
                try {
                    if (!cell.getCellStyle().getDataFormatString().equals("GENERAL"))
                        costumFormatting = true;
                } catch (Throwable t) {
                }

                if (cell.getCellComment() != null)
                    hasComments = true;
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    // System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //                        if (DateUtil.isCellDateFormatted(cell)) {
                    //                            // System.out.println(cell.getDateCellValue());
                    //                        } else {
                    //                            // System.out.println(cell.getNumericCellValue());
                    //                        }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    // System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // System.out.println(cell.getCellFormula());
                    formulae = true;
                    if (!UDF)
                        try {
                            evaluator.evaluate(cell);
                        } catch (Exception x) {
                            if (x instanceof NotImplementedException) {
                                Throwable e = x;

                                //e.printStackTrace();
                                while (e != null) {
                                    for (StackTraceElement c : e.getStackTrace()) {
                                        if (c.getClassName().contains("UserDefinedFunction")) {
                                            UDF = true;
                                            System.out.println("UDF " + e.getMessage());
                                            udfs.add(e.getMessage());
                                        }
                                    }
                                    e = e.getCause();
                                }

                            }
                        }
                    break;
                default:
                }

            }
    }
    if (costumFormatting) {
        Element cf = new Element("customisedFormatting", sn);
        s.addContent(cf);
    }
    if (formulae) {
        Element cf = new Element("formulae", sn);
        s.addContent(cf);
    }
    if (UDF) {
        Element cf = new Element("userDefinedFunctions", sn);
        for (String sss : udfs)
            cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss));
        s.addContent(cf);
    }
    if (hasComments) {
        Element cf = new Element("cellComments", sn);
        s.addContent(cf);
    }
}

From source file:xqt.adapters.csv.reader.RowBuilder.java

public static String[] createRowArray(Row row, FormulaEvaluator evaluator) {
    String[] cellValues = new String[row.getLastCellNum() + 1];
    for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
        CellValue cellValue = evaluator.evaluate(row.getCell(cellIndex));
        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            // what about the DATE type
            case Cell.CELL_TYPE_NUMERIC:
                //System.out.print(cellValue.getNumberValue() + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                //System.out.print(cellValue.getStringValue()  + "\t");
                cellValues[cellIndex] = cellValue.getStringValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.println(cellValue.getBooleanValue()  + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getBooleanValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // should not happen. It is evaluated by the evaluator
            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_ERROR:
                cellValues[cellIndex] = "";
                break;
            }/*from   ww  w  .ja  v a  2s  .  co m*/
        }
    }
    return cellValues;
}