Example usage for org.apache.poi.ss.usermodel DataFormatter formatCellValue

List of usage examples for org.apache.poi.ss.usermodel DataFormatter formatCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataFormatter
  6. formatCellValue

Introduction

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

Prototype

public String formatCellValue(Cell cell, FormulaEvaluator evaluator)

Source Link

Document

Returns the formatted value of a cell as a String regardless of the cell type.

Usage

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;/*from   ww  w  . j av  a  2 s.c om*/

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

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

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

        while (rowIterator.hasNext()) {
            rowCount++;
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.dua3.meja.model.poi.PoiCell.java

License:Apache License

@Override
public RichText getAsText() {
    if (getCellType() == CellType.TEXT) {
        return toRichText(poiCell.getRichStringCellValue());
    } else {/* ww  w . j  a va  2s.co m*/
        if (isEmpty()) {
            return RichText.emptyText();
        }

        // FIXME locale specific grouping separator does not work in POI
        // see https://bz.apache.org/bugzilla/show_bug.cgi?id=59638
        // TODO create and submit patch for POI
        DataFormatter dataFormatter = getWorkbook().getDataFormatter();
        try {
            FormulaEvaluator evaluator = getWorkbook().evaluator;
            return RichText.valueOf(dataFormatter.formatCellValue(poiCell, evaluator));
        } catch (Exception ex) {
            return RichText.valueOf(Cell.ERROR_TEXT);
        }
    }
}

From source file:com.dua3.meja.model.poi.PoiCell.java

License:Apache License

@Override
public String toString() {
    if (getCellType() == CellType.TEXT) {
        return poiCell.getStringCellValue();
    } else {/*from w  w  w.ja  v  a 2  s .c o  m*/
        if (isEmpty()) {
            return "";
        }

        // FIXME locale specific grouping separator does not work in POI
        // see https://bz.apache.org/bugzilla/show_bug.cgi?id=59638
        // TODO create and submit patch for POI
        DataFormatter dataFormatter = getWorkbook().getDataFormatter();
        try {
            FormulaEvaluator evaluator = getWorkbook().evaluator;
            return dataFormatter.formatCellValue(poiCell, evaluator);
        } catch (Exception ex) {
            return Cell.ERROR_TEXT;
        }
    }
}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Retrieve the cell value as String.//from   w w  w .j  a  v a 2  s.  c om
 *
 * @param rowIndex row-number of the cell
 * @param colIndex column-number of the cell
 * @return the converted cell-value and trimmed cell value if exists, empty
 *         string else
 */
public String getCellValueAsString(int rowIndex, int colIndex) {
    DataFormatter formatter = new DataFormatter();
    FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
    String value;
    Cell cell = getCellAt(rowIndex, colIndex);
    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
            value = ErrorConstants.getText(cell.getErrorCellValue());
        } else {
            value = formatter.formatCellValue(cell, eval);
        }
    } else {
        value = formatter.formatCellValue(cell);
    }
    return value.trim();
}

From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java

License:Open Source License

public Column buildStringColumn() {
    String[] colValues = new String[values.size()];
    boolean[] missing = new boolean[values.size()];
    Iterator<CellValue> it = values.iterator();
    Iterator<Cell> jt = cells.iterator();
    DataFormatter fmt = new DataFormatter();
    int counter = 0;
    while (it.hasNext()) {
        CellValue cv = it.next();/* w  w w  . j a  v a  2s. c om*/
        Cell cell = jt.next();
        if (cv == null) {
            missing[counter] = true;
        } else {
            switch (detectedTypes.get(counter)) {
            case Boolean:
            case Numeric:
                // format according to Excel format
                colValues[counter] = fmt.formatCellValue(cell, this.evaluator);
                break;
            case DateTime:
                // format according to dateTimeFormatter
                colValues[counter] = Workbook.dateTimeFormatter
                        .format(DateUtil.getJavaDate(cv.getNumberValue()), dateTimeFormat);
                break;
            case String:
                colValues[counter] = cv.getStringValue();
                break;
            default:
                throw new IllegalArgumentException("Unknown data type detected!");
            }
        }
        ++counter;
    }
    return new Column(colValues, missing, DataType.String);
}

From source file:nl.architolk.ldt.processors.ExcelConverter.java

License:Open Source License

public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException {

    try {/*  w  ww.jav a2  s  . c om*/
        // Read binary content of Excel file
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os);
        readInputAsSAX(context, INPUT_DATA, base64ContentHandler);
        final byte[] fileContent = os.toByteArray();
        final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent);

        // Create workbook
        XSSFWorkbook workbook = new XSSFWorkbook(bais);
        DataFormatter formatter = new DataFormatter();
        XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

        contentHandler.startDocument();
        contentHandler.startElement("", "workbook", "workbook", new AttributesImpl());

        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            XSSFSheet sheet = workbook.getSheetAt(s);
            AttributesImpl sheetAttr = new AttributesImpl();
            sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName());
            contentHandler.startElement("", "sheet", "sheet", sheetAttr);
            for (int r = 0; r <= sheet.getLastRowNum(); r++) {
                XSSFRow row = sheet.getRow(r);
                if (row != null) {
                    AttributesImpl rowAttr = new AttributesImpl();
                    rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r));
                    contentHandler.startElement("", "row", "row", rowAttr);
                    for (int c = 0; c < row.getLastCellNum(); c++) {
                        XSSFCell cell = row.getCell(c);
                        if (cell != null) {
                            try {
                                String cellvalue = formatter.formatCellValue(cell, evaluator);
                                if (cellvalue != "") {
                                    AttributesImpl columnAttr = new AttributesImpl();
                                    columnAttr.addAttribute("", "id", "id", "CDATA",
                                            Integer.toString(cell.getColumnIndex()));
                                    contentHandler.startElement("", "column", "column", columnAttr);
                                    contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length());
                                    contentHandler.endElement("", "column", "column");
                                }
                            } catch (Exception e) {
                            }
                        }
                    }
                    contentHandler.endElement("", "row", "row");
                }
            }
            contentHandler.endElement("", "sheet", "sheet");
        }

        contentHandler.endElement("", "workbook", "workbook");
        contentHandler.endDocument();

    } catch (IOException e) {
        throw new OXFException(e);
    }
}

From source file:org.cytoscape.tableimport.internal.ui.PreviewTablePanel.java

License:Open Source License

private PreviewTableModel parseExcel(final Sheet sheet, int startLine) throws IOException {
    int size = getPreviewSize();

    if (size == -1)
        size = Integer.MAX_VALUE;

    int maxCol = 0;
    final Vector<Vector<String>> data = new Vector<>();

    int rowCount = 0;
    int validRowCount = 0;
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    DataFormatter formatter = new DataFormatter();
    Row row;//from  w w  w  .  j  ava2 s  . c  om

    while (((row = sheet.getRow(rowCount)) != null) && (validRowCount < size)) {
        if (rowCount >= startLine) {
            final Vector<String> rowVector = new Vector<>();

            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();

            for (short j = 0; j < maxCol; j++) {
                Cell cell = row.getCell(j);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_ERROR
                        || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR)) {
                    rowVector.add(null);
                } else {
                    rowVector.add(formatter.formatCellValue(cell, evaluator));
                }
            }

            data.add(rowVector);
            validRowCount++;
        }

        rowCount++;
    }

    final boolean firstRowNames = importType == NETWORK_IMPORT || importType == TABLE_IMPORT;

    return new PreviewTableModel(data, new Vector<String>(), firstRowNames);
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

/** Resolves a formula or a normal cell and formats the result as it would be displayed in Excel 
 * @param cell the cell to resolve/*from  ww  w . java2s  .  c om*/
 * @param emptyMarker the string to interpret as empty field
 * @param nullMarker the string to interpret as null value
 * @param stringPreprocessor a preprocessor to apply to the raw field values
 * @return a string representation of the cell value */
public static String resolveCellValueAsString(Cell cell, String emptyMarker, String nullMarker,
        Converter<String, ?> stringPreprocessor) {
    if (cell == null)
        return null;
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        String content = cell.getRichStringCellValue().getString();
        if (content != null) {
            if (content.equals(emptyMarker) || content.equals("'"))
                content = "";
            else if (content.equals(nullMarker))
                content = null;
        }
        if (stringPreprocessor != null)
            content = ToStringConverter.convert(stringPreprocessor.convert(content), null);
        return content;
    } else {
        DataFormatter formatter = new DataFormatter();
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
            return formatter.formatCellValue(cell, createFormulaEvaluator(cell));
        else
            return formatter.formatCellValue(cell);
    }
}

From source file:org.generationcp.middleware.operation.parser.WorkbookParser.java

License:Open Source License

public static String getCellStringValue(final Workbook wb, final Cell cell) {
    if (cell == null) {
        return null;
    }// w  w  w  .  j a va2s  . c om
    final FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
    final DataFormatter formatter = new DataFormatter();
    return formatter.formatCellValue(cell, formulaEval);
}

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

License:MIT License

/**
 * return cell value with format./* w  w w.  j  ava2 s.c o 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;
}