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:com.efficio.fieldbook.web.nursery.service.impl.ImportGermplasmFileServiceImpl.java

License:Open Source License

/**
 * Gets the cell string value./* w  ww .  j a  va2  s. c  om*/
 *
 * @param sheetNumber the sheet number
 * @param rowNumber the row number
 * @param columnNumber the column number
 * @param followThisPosition the follow this position
 * @return the cell string value
 */
private String getCellStringValue(Integer sheetNumber, Integer rowNumber, Integer columnNumber,
        Boolean followThisPosition) {
    if (followThisPosition) {
        currentSheet = sheetNumber;
        currentRow = rowNumber;
        currentColumn = columnNumber;
    }

    try {
        Sheet sheet = wb.getSheetAt(sheetNumber);
        Row row = sheet.getRow(rowNumber);
        Cell cell = row.getCell(columnNumber);
        return cell.getStringCellValue();
    } catch (IllegalStateException e) {
        Sheet sheet = wb.getSheetAt(sheetNumber);
        Row row = sheet.getRow(rowNumber);
        Cell cell = row.getCell(columnNumber);
        return String.valueOf(Integer.valueOf((int) cell.getNumericCellValue()));
    } catch (NullPointerException e) {
        return "";
    }
}

From source file:com.envisioncn.it.super_sonic.showcase.evaluation.utils.ExcelUtils.java

License:Open Source License

/**
 * Excel/*ww  w  . ja v  a  2s.c  o m*/
 * 
 * @param hssfCell
 *            Excel??
 * @return Excel??
 */
@SuppressWarnings("static-access")
public static String getValue(Cell Cell) {

    // 
    if (Cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

        return String.valueOf(Cell.getBooleanCellValue());
        // 
    } else if (Cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

        return String.valueOf(new BigDecimal(Cell.getNumericCellValue()));
        // 
    } else {

        return String.valueOf(Cell.getStringCellValue());
    }
}

From source file:com.epitech.oliver_f.astextexls.ReadXLSFiles.java

private List<ResultRow> parseAllFiles(List<Path> paths) {
    List<ResultRow> resultList = new ArrayList<ResultRow>();
    for (Path path : paths) {
        try {//from   w w  w.j a va2  s. co  m
            System.out.println("file : " + path.toAbsolutePath());
            FileInputStream file = new FileInputStream(path.toFile());
            Workbook wb = WorkbookFactory.create(file);
            Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            boolean found = false;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    String res = null;
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        double inte = cell.getNumericCellValue();
                        res = Double.toString(inte);
                    }
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        res = cell.getStringCellValue();
                    }
                    if (res != null && res.trim().toLowerCase().equals("login \nvaluateur")) {
                        found = true;
                    }
                }
                if (found) {
                    System.out.println("found ! ");
                    ResultRow rr = new ResultRow();
                    Row rowFound = rowIterator.next();
                    Iterator<Cell> c = rowFound.cellIterator();
                    while (c.hasNext()) {
                        Cell cel = c.next();
                        String res = null;
                        if (cel.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            double inte = cel.getNumericCellValue();
                            res = Double.toString(inte);
                        }
                        if (cel.getCellType() == Cell.CELL_TYPE_STRING) {
                            res = cel.getStringCellValue();
                        }
                        rr.result.add(res);
                    }
                    resultList.add(rr);
                    found = false;
                    break;
                }
            }
            file.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }
    return resultList;
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createContent(Workbook wb, Sheet s, String map) {
    Map<String, String> out = new HashMap<String, String>();

    int rownum = 2;
    int cellnum = 0;

    // create a merged list of domains.
    domains.clear();//from w  w  w  .  ja  va2 s.c  o  m
    for (String database : results.keySet()) {
        if (database.equals("baseline")) {
            Analyzer ra = results.get(database);
            Map<String, Integer> mapToUse = this.getMap(map, ra);

            for (String domain : mapToUse.keySet()) {
                if ((!domains.contains(domain)) && !exclusions.contains(domain)) {
                    domains.add(domain);
                    out.put(domain, "");
                }
            }
        }
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));
    s.setColumnWidth(0, 5000);

    for (String domain : domains) {
        cellnum = 0;

        Row r = s.createRow(rownum);
        Cell c = r.createCell(cellnum);
        c.setCellValue(domain);
        cellnum++;

        for (String database : results.keySet()) {
            Analyzer ra = results.get(database);

            Map<String, Integer> mapToUse = this.getMap(map, ra);

            c = r.createCell(cellnum);
            try {
                if (mapToUse.containsKey(domain)) {
                    c.setCellValue(mapToUse.get(domain));
                } else {
                    c.setCellValue(0);
                }
            } catch (Exception e) {
                c.setCellValue(0);
            }

            c.setCellStyle(numberStyle);

            cellnum++;
        }
        rownum++;
    }

    // Totals.
    rownum++;
    cellnum = 1;
    Row r = s.createRow(rownum);

    Cell c = r.createCell(0);
    c.setCellValue("Totals:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }

    // Delta/Reduction
    rownum++;
    cellnum = 1;
    r = s.createRow(rownum);

    c = r.createCell(0);
    c.setCellValue("Tracking Decrease:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

public void createSpreadSheet() throws Exception {
    int row = 2, cell = 0, sheet = 0;
    FileOutputStream file = new FileOutputStream(path + "analysis.xls");

    Workbook wb = new HSSFWorkbook();

    // content: total content length sheet.
    Sheet s = wb.createSheet();/*w  ww.j ava  2  s .  c o m*/
    wb.setSheetName(sheet, "Content Length");
    this.createHeader(wb, s, "Total Content Length in MB", 0);

    Row r = s.createRow(row);
    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        c.setCellValue(results.get(database).totalContentLength / 1024 / 1024);
        cell++;
    }

    row++;
    cell = 0;
    r = s.createRow(row);

    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        if (database.equals("baseline")) {
            c.setCellValue("Decrease:");

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), "0");
            decrease.put(database, contents);
        } else {
            c = r.createCell(cell);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)");

            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateFormulaCell(c);

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");
            decrease.put(database, contents);
        }
        cell++;
    }
    sheet++;

    // When content is created, baseline is used as a base for every entry. For example,
    // if baseline contained doubleclick.com, this will be output and each other analyzer's
    // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick.
    // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never
    // shown in the spreadsheet or any other results.

    // so this means if we have tracker/whatever URLs in a non-baseline profile
    // and these URLs are NOT in the baseline profile,
    // we wouldn't see those trackers/whatever in the final comparison.

    // content: HTTP Requests
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Requests");
    this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1);
    this.createContent(wb, s, "requestCountPerDomain");
    sheet++;

    // content: HTTP Set-Cookie Responses
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Set-Cookie Responses");
    this.createHeader(wb, s,
            "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1);
    this.createContent(wb, s, "setCookieResponses");
    sheet++;

    // content: Cookie Added - Cookie Deleted
    s = wb.createSheet();
    wb.setSheetName(sheet, "Cookies Added-Deleted");
    this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1);
    this.createContent(wb, s, "cookieTotals");
    sheet++;

    // content: Local Storage counts per domain
    s = wb.createSheet();
    wb.setSheetName(sheet, "Local Storage");
    this.createHeader(wb, s, "Local Storage counts per domain", 1);
    this.createContent(wb, s, "localStorageContents");
    sheet++;

    // content: Pretty Chart
    s = wb.createSheet();
    wb.setSheetName(sheet, "Overall");

    int rownum = 0, cellnum = 0;

    // Header
    r = s.createRow(rownum);
    Cell c = r.createCell(0);
    s.setColumnWidth(0, 8000);
    c.setCellValue(
            "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)");

    rownum++;
    r = s.createRow(rownum);

    cellnum++;

    for (String database : decrease.keySet()) {
        if (database.equals("baseline")) {
            continue;
        }

        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));

    // Content
    for (String type : decrease.get("baseline").keySet()) {
        cellnum = 0;
        rownum++;

        r = s.createRow(rownum);

        c = r.createCell(cellnum);
        c.setCellValue(type);
        cellnum++;

        for (String database : decrease.keySet()) {
            if (database.equals("baseline")) {
                continue;
            }

            c = r.createCell(cellnum);
            c.setCellStyle(numberStyle);

            double decreaseValue = Double.parseDouble(decrease.get(database).get(type));

            if (decreaseValue < 0)
                decreaseValue = 0;

            c.setCellValue(decreaseValue);
            cellnum++;
        }
    }

    /*
    for (String database : decrease.keySet()) {
       for (String type : decrease.get(database).keySet()) {
    System.out.println(database + "|" + type + "|" + decrease.get(database).get(type));
       }
    }
    */

    wb.write(file);
    file.close();
}

From source file:com.Excel.Excel.java

private void leerArchivo(int indiceHoja) {
    abrirArchivo();/*from w  w w .j a  v a 2 s . co m*/
    this.datos = new HashMap<>();
    HSSFSheet sheet = workbook.getSheetAt(indiceHoja);
    Iterator<Row> rowIterator = sheet.iterator();
    int fila = 0;
    Row row;

    while (rowIterator.hasNext()) {
        List<Object> datosFila = new ArrayList<>();
        Cell celda;
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Object dato = null;
            celda = cellIterator.next();
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celda)) {
                    System.out.print(celda.getDateCellValue());
                    dato = celda.getDateCellValue();
                } else {
                    System.out.print(celda.getNumericCellValue());
                    dato = celda.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(celda.getStringCellValue());
                dato = celda.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(celda.getBooleanCellValue());
                dato = celda.getBooleanCellValue();
                break;
            }
            datosFila.add(dato);
        }
        datos.put(fila++, datosFila);
        System.out.println("");
    }
    cerrarArchivo();
}

From source file:com.Excel.Excel2007.java

private void leerArchivo(int indiceHoja) {
    abrirArchivo();/*from w w w .j ava 2 s.  c o m*/
    this.datos = new HashMap<>();
    XSSFSheet sheet = workbook.getSheetAt(indiceHoja);
    Iterator<Row> rowIterator = sheet.iterator();
    int fila = 0;
    Row row;

    while (rowIterator.hasNext()) {
        List<Object> datosFila = new ArrayList<>();
        Cell celda;
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Object dato = null;
            celda = cellIterator.next();
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celda)) {
                    System.out.print(celda.getDateCellValue());
                    dato = celda.getDateCellValue();
                } else {
                    System.out.print(celda.getNumericCellValue());
                    dato = celda.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(celda.getStringCellValue());
                dato = celda.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(celda.getBooleanCellValue());
                dato = celda.getBooleanCellValue();
                break;
            }
            datosFila.add(dato);
        }
        datos.put(fila++, datosFila);
        System.out.println("");
    }
    cerrarArchivo();
}

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * purpose of this method to create ValueList along with types and column
 * name. Simple design followed : Just have ColumnMetaData object which
 * contains everything. For a Cell we will have one columnMetaData object
 * and it will have values across the/*from w  ww.  j  ava2 s.  co  m*/
 * 
 * @param row
 * @throws Exception
 */
private void readARow(Row row, int nbrColumnsInARow) throws ExilityException {
    Value[] columnValues = new Value[nbrColumnsInARow];
    Value aColumnValue = null;
    String rawValue = null;

    for (int c = 0; c < nbrColumnsInARow; c++) {
        Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);

        ColumnMetaData columnInfo = this.columnsData.get(new Integer(c));
        int xlsColumnDataType = columnInfo.getXlsDataType();
        DataValueType exilDataType = null;

        int cellType = cell.getCellType();
        if (xlsColumnDataType != XLSReader.UNKNOWN_TYPE) {
            cellType = xlsColumnDataType;
        }

        try {

            switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:

                if (DateUtil.isCellDateFormatted(cell)) {
                    rawValue = DateUtility.formatDate(cell.getDateCellValue());
                    /*
                     * returns yyyy-mm-dd hh:mm:ss.sss full date with time.
                     */

                    exilDataType = DataValueType.DATE;
                } else {
                    double decimalNumber = cell.getNumericCellValue();
                    rawValue = NumberToTextConverter.toText(decimalNumber);

                    boolean isDecimal = rawValue.contains(".");
                    if (isDecimal) {
                        exilDataType = DataValueType.DECIMAL;
                    } else {
                        exilDataType = DataValueType.INTEGRAL;
                    }
                }

                break;

            case Cell.CELL_TYPE_STRING:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_FORMULA:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_BLANK:

                rawValue = cell.getStringCellValue();
                exilDataType = DataValueType.NULL;
                columnInfo.setExilDataType(exilDataType);
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                rawValue = cell.getBooleanCellValue() ? BooleanValue.TRUE : BooleanValue.FALSE;
                exilDataType = DataValueType.BOOLEAN;
                break;
            default:
                String msg = columnInfo.getColumnName() + XLSReader.INVALID_COLUMN_TYPE + row.getRowNum();
                Spit.out(msg);

            }

        } catch (Exception e) {
            // Trying to set valueType value and expected valueType value
            // for column in row
            String[] params = { this.getXlsTypeAsText(cell.getCellType()), this.getXlsTypeAsText(cellType),
                    columnInfo.getColumnName(), "" + row.getRowNum() };

            String message = this.replaceMessageParams(XLSReader.DATATYPE_MISMATCH, params);
            throw new ExilityException(message);
        }

        if (xlsColumnDataType == XLSReader.UNKNOWN_TYPE && cellType != Cell.CELL_TYPE_BLANK) {
            columnInfo.setXlsDataType(cellType);
            columnInfo.setExilDataType(exilDataType);
        }

        exilDataType = columnInfo.getExilDataType();

        aColumnValue = Value.newValue(rawValue, exilDataType);

        columnValues[c] = aColumnValue;
        this.columnsData.put(new Integer(c), columnInfo);
    }

    this.rows.add(columnValues);
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get text value of cell irrespective of its content type
 * /*  ww  w.  ja  v a  2s  .  com*/
 * @param cell
 * @return
 */
private String getTextValue(Cell cell) {
    if (cell == null) {
        return EMPTY_STRING;
    }

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_BLANK) {
        return EMPTY_STRING;
    }

    if (cellType == Cell.CELL_TYPE_FORMULA) {
        cellType = cell.getCachedFormulaResultType();
    }

    if (cellType == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue().trim();
    }

    /*
     * dates are internally stored as decimal..
     */
    if (cellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtility.formatDate(cell.getDateCellValue());
        }

        return NumberFormat.getInstance().format(cell.getNumericCellValue());
    }

    if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        if (cell.getBooleanCellValue()) {
            return "1";
        }
        return "0";
    }
    return EMPTY_STRING;
}

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected Object[] readExcelData(Row excelDataRow) {
    Object[] data = new Object[_columnCount];
    String cellAddress = "";
    int dataIndex = 0;
    try {// w  w w .j av  a 2 s  .  c o m
        for (Cell cell : excelDataRow) {
            cellAddress = cell.getAddress().toString();
            String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
            int columnIndex = _columnAddress.indexOf(columnLetter);
            if (columnIndex == -1) {
                continue;
            }

            CellType ct = cell.getCellTypeEnum();
            if (ct == CellType.FORMULA)
                ct = cell.getCachedFormulaResultTypeEnum();
            switch (ct) {
            case STRING:
                data[dataIndex] = cell.getStringCellValue();
                break;
            case BOOLEAN:
                data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue())
                        : cell.getBooleanCellValue();
                break;
            case NUMERIC:
                data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue())
                        : cell.getNumericCellValue();
                break;
            default:
                data[dataIndex] = _allTypesStrings ? "" : null;
                break;
            }
            dataIndex++;
        }
        if (_addFilename)
            data[data.length - 1] = _filenameOnly;
    } catch (Exception ex) {
        throw new PieException(
                String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()),
                ex);
    }

    return data;
}