Example usage for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. getNumericCellValue

Introduction

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

Prototype

@Override
public double getNumericCellValue()

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:com.centurylink.mdw.drools.Excel2007Parser.java

License:Apache License

@SuppressWarnings("deprecation")
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }//from   ww  w. j a va 2  s .c  o m

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode()
                                ? String.valueOf(topLeft.getNumericCellValue())
                                : topLeft.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java

License:Apache License

private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }//  ww w.j a  v a2 s .  c  o  m

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:com.cn.util.ExcelImport.java

/**
* ?2007excel//from   ww w.  j  a va2 s  . co  m
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.esd.cs.common.XExcelSheetParser.java

License:Open Source License

private Object getCellString(XSSFCell cell) {
    // TODO Auto-generated method stub
    Object result = null;//from   www  .  jav  a2  s  . co m
    if (cell != null) {
        // ?Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
        int cellType = cell.getCellType();
        switch (cellType) {
        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getRichStringCellValue().getString();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            result = cell.getNumericCellValue();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            result = cell.getNumericCellValue();
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            result = cell.getBooleanCellValue();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            result = null;
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            result = null;
            break;
        default:
            break;
        }
    }
    return result;
}

From source file:com.excel.javafx.frames.MainFrame.java

public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
    if ((cell1 == null) && (cell2 == null)) {
        return true;
    } else if ((cell1 == null) || (cell2 == null)) {
        return false;
    }//from w w  w. j  a  v  a  2 s  .c om

    boolean equalCells = false;
    int type1 = cell1.getCellType();
    int type2 = cell2.getCellType();
    if (type1 == type2) {
        if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
            // Compare cells based on its type
            switch (cell1.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                    equalCells = true;
                }
                break;
            default:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
    return equalCells;
}

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSLX.java

License:Open Source License

public String convertCellToString(XSSFCell cell) {
    int type;//from w w  w.j av a  2 s . co  m
    if (cell == null) {
        type = Cell.CELL_TYPE_BLANK;
    } else {
        type = cell.getCellType();
    }
    Object result;
    switch (type) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        result = "";
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        result = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;

    default:
        throw new RuntimeException("There are no support fot the cell type");
    }
    return result.toString();
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid//ww w .ja v a2s .  com
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLSX(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    FileInputStream fs = new FileInputStream(filename);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            XSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                XSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLSX has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename//from w w w .  jav a 2  s .  c  o m
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                XSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    }
}

From source file:com.niles.excel2json.objects.ExcelFile.java

License:Open Source License

public void process() throws Exception {
    File excel = new File(path);
    FileInputStream fis = SystemTools.getFileInputStream(excel);

    XSSFWorkbook wb = null;/*w w w.  j  a v  a 2 s.c o  m*/

    try {
        wb = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage());
        return;
    }

    this.fileName = excel.getName().replace(StringConstants.XLSX, "");
    this.folderPath = fileName + " Files";

    folderPath = SystemTools.createFolder(folderPath);

    int sheetCount = wb.getNumberOfSheets();

    int currentSheetNumber = 0;
    int rowCount = 0;
    int columnCount = 0;

    HashMap<Integer, String> headers = new HashMap<Integer, String>();

    /*
     * Itterate through the Excel sheets here and convert them to JSON
     */
    while (currentSheetNumber < sheetCount) {
        XSSFSheet current = wb.getSheetAt(currentSheetNumber);
        sheetName = current.getSheetName();

        // System.out.println(sheetName);
        if (current.getRow(0) == null) {
            // logger.error("[{}] Sheet contains no data", sheetName);
        } else {
            //logger.info("[{}] Processing sheet", sheetName);
            rowCount = current.getLastRowNum() + 1;
            columnCount = current.getRow(0).getPhysicalNumberOfCells();

            // System.out.println("Col Count: " + columnCount);
            // System.out.println("Row Count: " + rowCount);
            JSONArray myJSONArray = new JSONArray();
            JSONObject currentJSONObject = null;
            for (int a = 0; a < rowCount; a++) {
                XSSFRow currentRow = current.getRow(a);

                if (a == 0) {
                    logger.info("[{}] Loading header information", sheetName);
                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String header = "Header" + currentCellNumber;

                        if (currentCell != null) {
                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                header = currentCell.getStringCellValue();
                            }

                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                header = currentCell.getNumericCellValue() + "";
                            }
                        }

                        headers.put(currentCellNumber, header.replaceAll(" ", ""));
                    }
                } else {
                    currentJSONObject = new JSONObject();

                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String value = "";

                        if (currentCell != null) {
                            if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR
                                    && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                    value = currentCell.getStringCellValue();
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                    value = currentCell.getNumericCellValue() + "";
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                                    value = currentCell.getBooleanCellValue() + "";
                                }

                                if (value == null) {
                                    value = "";
                                }
                            }
                        }

                        currentJSONObject.put(headers.get(currentCellNumber), value);
                    }
                    myJSONArray.add(currentJSONObject);
                }
            }
            writeToJson(myJSONArray);
        }
        currentSheetNumber++;
    }
}

From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java

private String getStringValue(XSSFCell cell) {

    if (cell != null) {

        switch (cell.getCellType()) {

        case XSSFCell.CELL_TYPE_STRING:
            return cell.toString();
        case XSSFCell.CELL_TYPE_NUMERIC:
            return this.formatDouble(cell.getNumericCellValue());
        case XSSFCell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case XSSFCell.CELL_TYPE_BLANK:
            return "";
        default://from ww  w.  j  a  va  2  s.co  m
            return "";
        }

    }

    return "";

}