Example usage for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getNumericCellValue

Introduction

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

Prototype

public double getNumericCellValue()

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:net.intelliant.tests.ContactListImportTest.java

License:Open Source License

private String getStringData(HSSFCell cell) {
    if (cell != null) {
        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            return String.valueOf((int) cell.getNumericCellValue());
        }/*from ww  w . j  a v  a 2  s  . co  m*/
        return cell.toString();
    }
    return "";
}

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java

License:Open Source License

private void loadExcel(final String file) {

    final File fil = new File(file);
    if (fil.exists()) {
        canRead = true;//from w ww .jav a  2  s  .  co  m
        if (grid != null) {
            try {
                InputStream inp = new FileInputStream(file);
                try {
                    wb = new HSSFWorkbook(inp);
                } catch (Exception e) {
                    MsgDialog.message("Wrong format!\nOnly Excel 97-2007 is supported!");
                    canRead = false;
                }
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            if (canRead) {
                for (s = 0; s < wb.getNumberOfSheets(); s++) {
                    Display display = PlatformUI.getWorkbench().getDisplay();
                    display.syncExec(new Runnable() {

                        public void run() {

                            String name = fil.getName();
                            grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name);
                            SampleView.setGrid(grid);
                            HSSFSheet sheet = wb.getSheetAt(s);
                            int colCount = grid.getColumnCount();
                            int rowCount = grid.getItemCount();
                            int exelRow = endOfRow(sheet);
                            int exelColumn = endOfColumn(sheet);
                            // System.out.println(exelRow + " " + exelColumn
                            // + "---" + sheet.getPhysicalNumberOfRows() +
                            // " " +
                            // sheet.getRow(0).getPhysicalNumberOfCells());
                            if (colCount < exelColumn) {
                                int diff = exelColumn - colCount;
                                for (int i = 0; i < diff; i++) {
                                    GridColumn column = new GridColumn(grid, SWT.NONE);
                                    column.setText("C " + (i + 1 + colCount));
                                    column.setWidth(50);
                                }
                            }
                            if (rowCount < exelRow) {
                                int diff = exelRow - rowCount;
                                for (int i = 0; i < diff; i++) {
                                    new GridItem(grid, SWT.NONE).setHeight(16);
                                }
                            }
                            // Iterate over each row in the sheet
                            int rows = sheet.getPhysicalNumberOfRows();
                            for (int i = 0; i < exelRow; i++) {
                                HSSFRow row = sheet.getRow(i);
                                if (row == null) {
                                    for (int u = 0; u < exelColumn; u++) {
                                        grid.getItem(i).setText(u, " ");
                                    }
                                } else {
                                    for (int u = 0; u < exelColumn; u++) {
                                        HSSFCell cell = row.getCell(u);
                                        if (cell != null) {
                                            switch (cell.getCellType()) {
                                            case HSSFCell.CELL_TYPE_NUMERIC:
                                                String val = String.valueOf(cell.getNumericCellValue());
                                                grid.getItem(i).setText(u, val);
                                                break;
                                            case HSSFCell.CELL_TYPE_STRING:
                                                HSSFRichTextString st = cell.getRichStringCellValue();
                                                String val2 = st.getString();
                                                grid.getItem(i).setText(u, val2);
                                                break;
                                            case HSSFCell.CELL_TYPE_FORMULA:
                                                try {
                                                    String val3 = String.valueOf(cell.getNumericCellValue());
                                                    grid.getItem(i).setText(u, val3);
                                                } catch (Exception e) {
                                                    String s2 = cell.getCellFormula();
                                                    grid.getItem(i).setText(u, s2);
                                                }
                                                break;
                                            case HSSFCell.CELL_TYPE_BLANK:
                                                grid.getItem(i).setText(u, " ");
                                                break;
                                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                                boolean s4 = cell.getBooleanCellValue();
                                                if (s4) {
                                                    grid.getItem(i).setText(u, "TRUE");
                                                } else {
                                                    grid.getItem(i).setText(u, "FALSE");
                                                }
                                                break;
                                            default:
                                                break;
                                            }
                                        } else {
                                            grid.getItem(i).setText(u, " ");
                                        }
                                    }
                                }
                            }
                        }
                    });
                }
                wb = null;
            }
        }
    } else {
        MsgDialog.message("File not found!");
    }
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei./*from   w ww. jav a  2  s.c  om*/
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(File excelSheet) throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    int numberOfSheets = wb.getNumberOfSheets();
    List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();

        int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows][columns];
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    return sheetList;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//w  ww  .  j  av  a2 s  .c om
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    return sl;
}

From source file:no.abmu.abmstatistikk.annualstatistic.util.ExcelWithLibraryInformationAndDataParser.java

License:Open Source License

/**
 * Returns the value of the specified column as a String.
 *
 * @param row/*from  w w  w  .  j a  v a2s  . c  o m*/
 * @param columnIndex
 * @return
 */
protected String getStringValue(HSSFRow row, Short columnIndex) {
    HSSFCell cell;
    if (columnIndex == null) {
        return null;
    }

    cell = row.getCell(columnIndex.shortValue());

    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_NUMERIC:
        /* For now - convert to long */
        return Long.toString(new Double(cell.getNumericCellValue()).longValue());
    case HSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:
        return null;
    }
}

From source file:no.abmu.abmstatistikk.annualstatistic.util.ExcelWithLibraryInformationAndDataParser.java

License:Open Source License

/**
 * Return the value of the cell as a long.
 *
 * @param cell//ww  w . ja  v  a2s  .co  m
 * @return
 */
protected Long getLongValue(HSSFRow row, Short columnIndex) {
    String value;
    HSSFCell cell;

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

    cell = row.getCell(columnIndex.shortValue());

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

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_NUMERIC:
        return new Long(new Double(cell.getNumericCellValue()).longValue());
    case HSSFCell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        if (value == null) {
            return null;
        } else {
            try {
                return new Long(value);
            } catch (NumberFormatException nfe) {
            }
        }
    default:
        return null;
    }
}

From source file:no.abmu.common.excel.BaseExcelParserImpl.java

License:Open Source License

/**
 * Returns the value of the specified column as a String.
 *
 * @param row/*from  w  ww . j  ava  2  s .c o m*/
 * @param columnIndex
 * @return
 */
protected String getStringValue(HSSFRow row, Short columnIndex) {

    HSSFCell cell;

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

    cell = row.getCell(columnIndex.shortValue());

    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_NUMERIC:
        //           System.out.println("cell:"+cell.getNumericCellValue()+", %1:"+cell.getNumericCellValue() % 1+", "+(cell.getNumericCellValue() % 1 == 0));
        if (cell.getNumericCellValue() % 1 == 0) {//is integer
            /* For now - convert to long */
            return Long.toString(new Double(cell.getNumericCellValue()).longValue());
        } else {//decimal
            Locale locale = LocaleTypeNameConst.BOKMAAL;
            NumberFormat numberFormat = NumberFormat.getInstance(locale);
            return numberFormat.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:
        return null;

    }

}

From source file:no.abmu.common.excel.BaseExcelParserImpl.java

License:Open Source License

/**
 * Return the value of the cell as a long.
 *
 * @return//from w w  w  .  ja  v a2  s .c o  m
 */
protected Long getLongValue(HSSFRow row, Short columnIndex) {
    String value;
    HSSFCell cell;

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

    cell = row.getCell(columnIndex.shortValue());

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

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_NUMERIC:
        return new Long(new Double(cell.getNumericCellValue()).longValue());
    case HSSFCell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        if (value == null) {
            return null;
        } else {
            try {
                return new Long(value);
            } catch (NumberFormatException nfe) {
                if (logger.isDebugEnabled()) {
                    logger.debug("NumberFormatException: " + nfe);
                }
            }
        }
    default:
        return null;
    }
}

From source file:no.abmu.common.excel.BaseExcelParserImpl.java

License:Open Source License

/**
 * Return the value of the cell as a long.
 *
 * @return/*  w  w w  . j av a  2  s . co m*/
 */
protected Date getDateValue(HSSFRow row, Short columnIndex, DateFormat customDateFormat) {
    Assert.checkRequiredArgument("row", row);
    Assert.checkRequiredArgument("columnIndex", columnIndex);

    HSSFCell cell = row.getCell(columnIndex.shortValue());

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

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())) {
            return cell.getDateCellValue();
        } else {
            return null;
        }
    case HSSFCell.CELL_TYPE_STRING:
        String value = cell.getStringCellValue();
        if (StringUtil.isEmpty(value)) {
            return null;
        }
        // ISO data format are default date format
        DateFormat dateFormat = DateUtil.ISO_DATEFORMAT;
        if (customDateFormat != null) {
            dateFormat = customDateFormat;
        }
        Date returnValue = null;
        try {
            returnValue = DateUtil.parseDateString(dateFormat, value);
        } catch (IllegalArgumentException e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[getDateValue] IllegalArgumentException: " + e);
            }
        }
        return returnValue;

    default:
        return null;
    }

}

From source file:no.abmu.common.excel.BaseExcelParserImpl.java

License:Open Source License

/**
 * Return the value of the cell as a long.
 *
 * @return//from  www  .j  a  v  a 2  s  .co  m
 */
protected Double getDoubleValue(HSSFRow row, Short columnIndex) {
    Assert.checkRequiredArgument("row", row);
    Assert.checkRequiredArgument("columnIndex", columnIndex);

    HSSFCell cell = row.getCell(columnIndex.shortValue());

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

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_NUMERIC:
        return cell.getNumericCellValue();
    case HSSFCell.CELL_TYPE_STRING:
        String value = cell.getStringCellValue();
        if (StringUtil.isEmpty(value)) {
            return null;
        } else {
            Double returnValue = null;
            try {
                returnValue = new Double(value);
            } catch (NumberFormatException nfe) {
                if (logger.isDebugEnabled()) {
                    logger.debug("NumberFormatException: " + nfe);
                }
            }
            return returnValue;
        }
    default:
        return null;
    }
}