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

List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getNumericCellValue

Introduction

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

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

private Integer readIntColumn(int row, String requestedColumnName) {
    return (Integer) readColumn(row, requestedColumnName, new Function<Cell, Object>() {
        @Override//from www . j a v  a2  s  .  c o m
        public Object apply(Cell cell) {
            return new Double(cell.getNumericCellValue()).intValue();
        }
    });
}

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

private Long readLongColumn(int row, String requestedColumnName) {
    return (Long) readColumn(row, requestedColumnName, new Function<Cell, Object>() {
        @Override//from w ww.j a v a2s . c  o m
        public Object apply(Cell cell) {
            return new Double(cell.getNumericCellValue()).longValue();
        }
    });
}

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

private static String toString(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() + "";

    case Cell.CELL_TYPE_NUMERIC:
        return cell.getNumericCellValue() + "";

    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    case Cell.CELL_TYPE_ERROR:
        return "_ERROR_ " + cell.getErrorCellValue();

    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula() + "";
    default://  www  .  j a  va 2 s . co  m
        throw new RuntimeException("No toString is available for the cell type!");
    }
}

From source file:graphbuilder.ExcelParser.java

private static Object loadCellData(Cell cell) {
    Object result = null;//ww  w  . j av  a 2 s . c o m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            result = cell.getDateCellValue();
        } else {
            result = cell.getNumericCellValue();
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;
    }
    return result;
}

From source file:graphene.hts.file.ExcelXSSFToJSONConverter.java

License:Apache License

private List internalConvert(final Iterator<Row> rowIter) {
    final List<Map<String, String>> excelSheetConversion = new ArrayList<Map<String, String>>();
    final List<String> headerRow = new ArrayList<String>();
    if (rowIter.hasNext()) {
        final Row row = rowIter.next();
        final Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            final Cell cell = cellIterator.next();
            String cellString = cell.getStringCellValue();
            if (!ValidationUtils.isValid(cellString)) {
                cellString = "Column " + cell.getColumnIndex();
            }//from   w  w w  .jav a2s.  co m
            headerRow.add(cellString);
            System.out.println("Header Column: " + cellString);
        }

    }
    while (rowIter.hasNext()) {
        final Row row = rowIter.next();
        final Map<String, String> kvMap = new TreeMap<String, String>();
        final Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            final org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
            // System.out.println("Header Columns: " + headerRow);
            final int ci = cell.getColumnIndex();
            String key = "Column " + ci;
            if (ci < headerRow.size()) {
                key = headerRow.get(cell.getColumnIndex());
            }
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                if (ValidationUtils.isValid(key, cell.getBooleanCellValue())) {
                    kvMap.put(key, new Boolean(cell.getBooleanCellValue()).toString());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                if (ValidationUtils.isValid(key, cell.getStringCellValue())) {
                    kvMap.put(key, cell.getStringCellValue());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (ValidationUtils.isValid(key, cell.getNumericCellValue())) {
                    kvMap.put(key, new Double(cell.getNumericCellValue()).toString());
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                break;

            }

        }
        excelSheetConversion.add(kvMap);
    }
    logger.debug("Added sheet to conversion.");
    return excelSheetConversion;
}

From source file:helpers.Excel.ExcelDataFormat.java

public Object marshalAsArray(Iterator<Row> sheet) {

    ArrayList<ArrayList<Object>> results = new ArrayList<ArrayList<Object>>();

    for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) {
        ArrayList newrow = new ArrayList();
        results.add(newrow);//from  w ww. j  av a 2 s  . c  o  m
        Row row = rowIterator.next();

        for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
            Cell cell = cellIterator.next();
            logger.info("Cell type:" + cell.getCellType());
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //logger.info(cell.getCellType()+"="+cell.getDateCellValue());
                    newrow.add(cell.getDateCellValue());
                } else {
                    //logger.info(cell.getCellType()+"="+cell.getNumericCellValue());
                    newrow.add(cell.getNumericCellValue());
                }
                break;
            default:
                //logger.info(cell.getCellType()+"="+cell.getStringCellValue());
                newrow.add(cell.getStringCellValue());
                break;

            }

        }
    }
    return results;
}

From source file:helpers.Excel.ExcelDataFormat.java

public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) {
    logger.info("Evaluating formulas.");
    evaluator.evaluateAll();//from   w  w w  .j  ava  2  s  .com
    logger.info("Done...");
    OneExcelSheet onesheet = new OneExcelSheet();

    ArrayList<String> headers = null;

    for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) {
        Row row = rowIterator.next();

        if (headers == null) {
            headers = new ArrayList<String>();
            int coln = 0;
            for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                try {
                    Cell cell = cellIterator.next();
                    logger.info("Header:" + cell.getStringCellValue());
                    String headn = cell.getStringCellValue().replace(" ", "");
                    headers.add(headn);
                    OneExcelColumn col = new OneExcelColumn(headn, coln);
                    onesheet.columns.add(col);
                } catch (Exception e) {
                    logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e);
                }
                coln++;
            }
        } else {
            ArrayList<Object> newrow = new ArrayList<Object>();
            onesheet.data.add(newrow);

            int coln = 0;

            //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();)
            for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                //Cell cell=cellIterator.next();
                //logger.info("Cell type:"+cell.getCellType());

                switch (evaluator.evaluateInCell(cell).getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        //logger.info(cell.getCellType()+"="+cell.getDateCellValue());
                        newrow.add(cell.getDateCellValue());
                        if (onesheet.columns.size() > coln)
                            onesheet.columns.get(coln).columnTypes[9]++;
                    } else {
                        //logger.info(cell.getCellType()+"="+cell.getNumericCellValue());
                        newrow.add(cell.getNumericCellValue());
                        if (onesheet.columns.size() > coln)
                            onesheet.columns.get(coln).columnTypes[cell.getCellType()]++;
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:

                    int value = evaluator.evaluateFormulaCell(cell);
                    value = cell.getCachedFormulaResultType();

                    newrow.add(value);
                    if (onesheet.columns.size() > coln)
                        onesheet.columns.get(coln).columnTypes[0]++;
                    break;
                default:
                    //logger.info(cell.getCellType()+"="+cell.getStringCellValue());

                    String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8"));
                    newrow.add(cellstr);
                    if (onesheet.columns.size() > coln)
                        onesheet.columns.get(coln).columnTypes[cell.getCellType()]++;

                    break;

                }
                coln++;
            }
        }
    }

    return onesheet;
}

From source file:hjow.hgtable.util.XLSXUtil.java

License:Apache License

/**
 * <p>XLSX ? ?  ?? . ?  ?  ?? ?? , ? ? ?? ?  ?  ?? ?.</p>
 * /* w  w  w .  j a  v a 2  s  .  c o  m*/
 * @param file : XLSX ?
 * @return ?  ? 
 */
public static List<TableSet> toTableSets(File file) {
    List<TableSet> tableSets = new Vector<TableSet>();

    org.apache.poi.ss.usermodel.Workbook workbook = null;

    if (file == null)
        throw new NullPointerException(Manager.applyStringTable("Please select file !!"));
    if (!file.exists())
        throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " "
                + Manager.applyStringTable("is not exist"));

    boolean isHead = true;
    int rowNum = 0;
    int cellNum = 0;

    int cellCount = 0;

    FileInputStream fileStream = null;
    try {
        if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) {
            workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file);
        } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) {
            fileStream = new FileInputStream(file);
            workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream);
        }

        org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper()
                .createFormulaEvaluator();

        org.apache.poi.ss.usermodel.Sheet sheet = null;

        for (int x = 0; x < workbook.getNumberOfSheets(); x++) {
            TableSet newTableSet = new DefaultTableSet();
            newTableSet.setColumns(new Vector<Column>());

            sheet = workbook.getSheetAt(x);
            newTableSet.setName(sheet.getSheetName());

            rowNum = 0;
            isHead = true;

            String targetData = null;

            for (org.apache.poi.ss.usermodel.Row row : sheet) {
                cellNum = 0;
                for (org.apache.poi.ss.usermodel.Cell cell : row) {
                    try {
                        if (cellNum >= cellCount) {
                            throw new IndexOutOfBoundsException(
                                    Manager.applyStringTable("There are some cells not have their heads") + ", "
                                            + Manager.applyStringTable("Head count") + " : " + cellCount + ", "
                                            + Manager.applyStringTable("Cell Number") + " : " + cellNum);
                        }

                        switch (cell.getCellType()) {
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        cell.getRichStringCellValue().getString(), Column.TYPE_STRING));
                            } else {
                                targetData = cell.getRichStringCellValue().getString();
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE));
                                } else {
                                    targetData = String.valueOf(cell.getDateCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            } else {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                                } else {
                                    double values = cell.getNumericCellValue();
                                    double intPart = values - ((double) ((int) values));
                                    if (intPart == 0.0) {
                                        targetData = String.valueOf(((int) values));
                                        newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER);
                                    } else {
                                        targetData = String.valueOf(values);
                                        newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                    }
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN));
                            } else {
                                targetData = String.valueOf(cell.getBooleanCellValue());
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                            } else {
                                if (evals.evaluateFormulaCell(cell) == 0) {
                                    targetData = String.valueOf(cell.getNumericCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC);
                                } else if (evals.evaluateFormulaCell(cell) == 1) {
                                    targetData = String.valueOf(cell.getStringCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING);
                                } else if (evals.evaluateFormulaCell(cell) == 4) {
                                    targetData = String.valueOf(cell.getBooleanCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN);
                                } else {
                                    targetData = String.valueOf(cell.getCellFormula());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                targetData = "";
                                newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK);
                            }
                            break;
                        default:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                try {
                                    targetData = cell.getStringCellValue();
                                } catch (Exception e1) {
                                    e1.printStackTrace();
                                }
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        }

                        if (isHead) {
                            cellCount++;
                        } else {
                            while (rowNum > 0
                                    && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                            if (targetData != null)
                                newTableSet.getColumns().get(cellNum).getData().add(targetData);
                            else {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                        }
                    } catch (ArrayIndexOutOfBoundsException e1) {
                        StringBuffer err = new StringBuffer("");
                        for (StackTraceElement errEl : e1.getStackTrace()) {
                            err = err.append("\t " + errEl + "\n");
                        }

                        String cellObject = null;
                        try {
                            cellObject = cell.getStringCellValue();
                        } catch (Exception e2) {

                        }

                        throw new ArrayIndexOutOfBoundsException(
                                Manager.applyStringTable("Array index out of range") + " <- "
                                        + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName()
                                        + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On")
                                        + " " + Manager.applyStringTable("Row") + " " + rowNum + ", "
                                        + Manager.applyStringTable("Cell") + " " + cellNum + ", "
                                        + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject)
                                        + "\n " + Manager.applyStringTable("<-\n") + err + "\n "
                                        + Manager.applyStringTable("Original Message") + "...\n"
                                        + e1.getMessage() + "\n" + Manager.applyStringTable("End"));
                    }

                    cellNum++;
                }

                isHead = false;
                rowNum++;
            }

            fillTableSet(newTableSet);
            newTableSet.removeEmptyColumn(true);

            tableSets.add(newTableSet);
        }

        return tableSets;
    } catch (Throwable e) {
        if (Main.MODE >= DebuggingUtil.DEBUG)
            e.printStackTrace();
        Main.logError(e,
                Manager.applyStringTable("On reading xlsx") + " : " + file + "\n"
                        + Manager.applyStringTable("At rownum") + " " + rowNum + ", "
                        + Manager.applyStringTable("cellnum") + " " + cellNum);

        return null;
    } finally {
        try {
            workbook.close();
        } catch (Throwable e) {

        }
        try {
            if (fileStream != null)
                fileStream.close();
        } catch (Throwable e) {

        }
    }
}

From source file:hrytsenko.gscripts.io.XlsFiles.java

License:Apache License

private static String cellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        DecimalFormat format = new DecimalFormat("0.#");
        return format.format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    default://from w ww.j a v  a  2 s.  c o m
        return "";
    }
}

From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java

License:Apache License

private CellValue getCellValue(Row row, Cell cell, int column) {

    CellValue cellValue = null;//from  w  ww.  j a v  a 2 s  .  c  o m
    try {
        cellValue = evaluator.evaluate(cell);
    } catch (RuntimeException e) {
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = new CellValue(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = new CellValue(cell.getStringCellValue());
                break;
            default:
                System.err.format("  Cell[%d,%d] unknown cached formula type %s\n", row.getRowNum(), column,
                        cell.getCachedFormulaResultType());
            }
        }
    }
    return cellValue;
}