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:org.apache.ofbiz.product.spreadsheetimport.ImportProductServices.java

License:Apache License

/**
 * This method is responsible to import spreadsheet data into "Product" and
 * "InventoryItem" entities into database. The method uses the
 * ImportProductHelper class to perform its operation. The method uses "Apache
 * POI" api for importing spreadsheet (xls files) data.
 *
 * Note : Create the spreadsheet directory in the ofbiz home folder and keep
 * your xls files in this folder only.//from  w  w  w .  j  a  v a 2  s . c  o  m
 *
 * @param dctx the dispatch context
 * @param context the context
 * @return the result of the service execution
 * @throws IOException 
 */
public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx,
        Map<String, ? extends Object> context) throws IOException {
    Delegator delegator = dctx.getDelegator();
    Locale locale = (Locale) context.get("locale");
    // System.getProperty("user.dir") returns the path upto ofbiz home
    // directory
    String path = System.getProperty("user.dir") + "/spreadsheet";
    List<File> fileItems = new LinkedList<File>();

    if (UtilValidate.isNotEmpty(path)) {
        File importDir = new File(path);
        if (importDir.isDirectory() && importDir.canRead()) {
            File[] files = importDir.listFiles();
            // loop for all the containing xls file in the spreadsheet
            // directory
            for (int i = 0; i < files.length; i++) {
                if (files[i].getName().toUpperCase().endsWith("XLS")) {
                    fileItems.add(files[i]);
                }
            }
        } else {
            return ServiceUtil.returnError(
                    UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale));
        }
    } else {
        return ServiceUtil.returnError(
                UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale));
    }

    if (fileItems.size() < 1) {
        return ServiceUtil.returnError(
                UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale)
                        + path);
    }

    for (File item : fileItems) {
        // read all xls file and create workbook one by one.
        List<Map<String, Object>> products = new LinkedList<Map<String, Object>>();
        List<Map<String, Object>> inventoryItems = new LinkedList<Map<String, Object>>();
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        try {
            fs = new POIFSFileSystem(new FileInputStream(item));
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            Debug.logError("Unable to read or create workbook from file", module);
            return ServiceUtil.returnError(UtilProperties.getMessage(resource,
                    "ProductProductImportCannotCreateWorkbookFromFile", locale));
        }

        // get first sheet
        HSSFSheet sheet = wb.getSheetAt(0);
        wb.close();
        int sheetLastRowNumber = sheet.getLastRowNum();
        for (int j = 1; j <= sheetLastRowNumber; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row != null) {
                // read productId from first column "sheet column index
                // starts from 0"
                HSSFCell cell2 = row.getCell(2);
                cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
                String productId = cell2.getRichStringCellValue().toString();
                // read QOH from ninth column
                HSSFCell cell5 = row.getCell(5);
                BigDecimal quantityOnHand = BigDecimal.ZERO;
                if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                    quantityOnHand = new BigDecimal(cell5.getNumericCellValue());

                // check productId if null then skip creating inventory item
                // too.
                boolean productExists = ImportProductHelper.checkProductExists(productId, delegator);

                if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) {
                    products.add(ImportProductHelper.prepareProduct(productId));
                    if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0)
                        inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand,
                                delegator.getNextSeqId("InventoryItem")));
                    else
                        inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO,
                                delegator.getNextSeqId("InventoryItem")));
                }
                int rowNum = row.getRowNum() + 1;
                if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) {
                    Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module);
                }
            }
        }
        // create and store values in "Product" and "InventoryItem" entity
        // in database
        for (int j = 0; j < products.size(); j++) {
            GenericValue productGV = delegator.makeValue("Product", products.get(j));
            GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j));
            if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) {
                try {
                    delegator.create(productGV);
                    delegator.create(inventoryItemGV);
                } catch (GenericEntityException e) {
                    Debug.logError("Cannot store product", module);
                    return ServiceUtil.returnError(UtilProperties.getMessage(resource,
                            "ProductProductImportCannotStoreProduct", locale));
                }
            }
        }
        int uploadedProducts = products.size() + 1;
        if (products.size() > 0)
            Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module);
    }
    return ServiceUtil.returnSuccess();
}

From source file:org.apache.slide.extractor.MSExcelExtractor.java

License:Apache License

public Reader extract(InputStream content) throws ExtractorException {
    try {//from   ww w  . j  a va 2  s  .co m
        CharArrayWriter writer = new CharArrayWriter();

        POIFSFileSystem fs = new POIFSFileSystem(content);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0)
                            writer.write(num + " ");
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0)
                            writer.write(text + " ");
                        break;
                    }
                }
            }
        }

        return new CharArrayReader(writer.toCharArray());
    } catch (Exception e) {
        throw new ExtractorException(e.getMessage());
    }
}

From source file:org.apdplat.platform.generator.ModelGenerator.java

License:Open Source License

/**
 * ???EXCEL/* ww  w. j  ava2s.  c  om*/
 * ?EXCELJAVA
 * @param inputStream ??EXCEL
 * @return JAVA
 */
private static List<ModelInfo> readModelInfos(InputStream inputStream) {
    List<ModelInfo> models = new ArrayList<>();
    try {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);
            try {
                HSSFRow row = sheet.getRow(2);
                if (row == null) {
                    LOG.info("???" + sheet.getSheetName());
                    continue;
                }
                HSSFCell cell = row.getCell(1);
                //??
                String modelPackage = cell.getStringCellValue();
                row = sheet.getRow(3);
                cell = row.getCell(1);
                //??
                String modelEnglish = cell.getStringCellValue();
                row = sheet.getRow(4);
                cell = row.getCell(1);
                //??
                String modelChinese = cell.getStringCellValue();

                ModelInfo modelInfo = new ModelInfo();
                modelInfo.setModelPackage(modelPackage);
                modelInfo.setModelEnglish(modelEnglish);
                modelInfo.setModelChinese(modelChinese);

                int rows = sheet.getPhysicalNumberOfRows();
                //8??
                for (int rowNumber = 7; rowNumber < rows; rowNumber++) {
                    HSSFRow oneRow = sheet.getRow(rowNumber);

                    if (oneRow == null) {
                        continue;
                    }
                    Attr attr = new Attr();
                    //??
                    HSSFCell oneCell = oneRow.getCell(0);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setDes(cellValue);
                        } else {
                            continue;
                        }
                    }
                    //??
                    oneCell = oneRow.getCell(1);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setName(cellValue);
                        } else {
                            continue;
                        }
                    }
                    //
                    oneCell = oneRow.getCell(2);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setType(AttrType.validType(cellValue));
                        } else {
                            attr.setType(AttrType.validType("String"));
                        }
                    }
                    //?string
                    oneCell = oneRow.getCell(3);
                    if (oneCell != null) {
                        if (oneCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            String cellValue = oneCell.getStringCellValue();
                            if (cellValue != null && !"".equals(cellValue.trim())
                                    && !"null".equals(cellValue.trim().toLowerCase())) {
                                try {
                                    int length = Integer.parseInt(cellValue);
                                    attr.setLength(length);
                                } catch (Exception e) {
                                    LOG.error("?" + cellValue);
                                }
                            }
                        }
                        if (oneCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            double length = oneCell.getNumericCellValue();
                            attr.setLength((int) length);
                        }
                    }
                    //??
                    oneCell = oneRow.getCell(4);
                    if (oneCell != null) {
                        boolean cellValue = oneCell.getBooleanCellValue();
                        attr.setSearchable(cellValue);
                    }
                    //??
                    oneCell = oneRow.getCell(5);
                    if (oneCell != null) {
                        boolean cellValue = oneCell.getBooleanCellValue();
                        attr.setRenderIgnore(cellValue);
                    }
                    //??
                    oneCell = oneRow.getCell(6);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setDic(DicType.validType(cellValue));
                        }
                    }
                    //???
                    oneCell = oneRow.getCell(7);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setDicName(cellValue);
                        }
                    }
                    //
                    oneCell = oneRow.getCell(8);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setMap(MapType.validType(cellValue));
                        }
                    }
                    //
                    oneCell = oneRow.getCell(9);
                    if (oneCell != null) {
                        String cellValue = oneCell.getStringCellValue();
                        if (cellValue != null && !"".equals(cellValue.trim())
                                && !"null".equals(cellValue.trim().toLowerCase())) {
                            attr.setAttrRef(cellValue);
                        }
                    }
                    if ("Date".equals(attr.getType()) || "Time".equals(attr.getType())) {
                        modelInfo.setHasDateTime(true);
                    }
                    if ("DicItem".equals(attr.getType())) {
                        modelInfo.setHasDicItem(true);
                        //DicItemdicSimpleDic
                        if (!"SimpleDic".equals(attr.getDic()) && !"TreeDic".equals(attr.getDic())) {
                            attr.setDic("SimpleDic");
                        }
                        if (attr.getDicName() == null || "".equals(attr.getDicName())) {
                            attr.setDicName(attr.getName());
                        }
                    }
                    modelInfo.addAttr(attr);
                }
                models.add(modelInfo);
            } catch (Exception e) {
                LOG.error("?:" + sheet.getSheetName() + " ", e);
            }
        }
    } catch (IOException e) {
        LOG.error("?MODEL", e);
    }
    return models;
}

From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java

License:Open Source License

/**
 * @see ?cell??//  w w  w . jav a 2  s . com
 * @param cell
 * @param objClass
 * @return
 */
private String getCelValue(HSSFCell cell) {
    if ((cell == null) || (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK))
        return "";
    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return cell.getRichStringCellValue().getString();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return numberFormat.format(cell.getNumericCellValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
        if (cell.getBooleanCellValue())
            return "true";
        else
            return "false";
    } else {
        return "";
    }
}

From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * <p>/*from   w  w w . ja  v a2  s.c om*/
 * getCellValue.
 * </p>
 * 
 * @see ?cell??
 * @param cell a {@link org.apache.poi.hssf.usermodel.HSSFCell} object.
 * @return a {@link java.lang.Object} object.
 */
public static Object getCellValue(HSSFCell cell) {
    if ((cell == null))
        return null;
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_STRING:
        return Strings.trim(cell.getRichStringCellValue().getString());
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return numberFormat.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE;
    default:
        // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA
        return null;
    }
}

From source file:org.beangle.model.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * @see ?cell??//from www  .jav  a 2 s  .  c  om
 * @param cell
 * @param objClass
 * @return
 */
public static Object getCellValue(HSSFCell cell) {
    if ((cell == null))
        return null;
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getRichStringCellValue().getString());
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return numberFormat.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE;
    default:
        // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA
        return null;
    }
}

From source file:org.databene.platform.xls.XLSEntityExporterTest.java

License:Open Source License

private static void checkCells(HSSFRow row, Object... values) {
    if (ArrayUtil.isEmpty(values))
        assertNull(row);/*www.  j a  v a 2 s  .  co m*/
    for (int i = 0; i < values.length; i++) {
        HSSFCell cell = row.getCell(i);
        Object expectedContent = values[i];
        if (expectedContent == null)
            assertNull(cell);
        else if (expectedContent instanceof String) {
            assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCellType());
            assertEquals(expectedContent, cell.getStringCellValue());
        } else if (expectedContent instanceof Number) {
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCellType());
            assertEquals(((Number) expectedContent).doubleValue(), cell.getNumericCellValue());
        } else if (expectedContent instanceof Boolean) {
            assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cell.getCellType());
            assertEquals(expectedContent, cell.getBooleanCellValue());
        } else if (expectedContent instanceof Date) {
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCellType());
            assertEquals(((Date) expectedContent).getTime() / 1000, cell.getDateCellValue().getTime() / 1000); // cut off milliseconds
        } else
            throw new RuntimeException("Type not supported: " + expectedContent.getClass());
    }
}

From source file:org.dbunit.dataset.excel.XlsTable.java

License:Open Source License

protected Object getDateValueFromJavaNumber(HSSFCell cell) {
    logger.debug("getDateValueFromJavaNumber(cell={}) - start", cell);

    double numericValue = cell.getNumericCellValue();
    BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue));
    numericValueBd = stripTrailingZeros(numericValueBd);
    return new Long(numericValueBd.longValue());
    //        return new Long(numericValueBd.unscaledValue().longValue());
}

From source file:org.dbunit.dataset.excel.XlsTable.java

License:Open Source License

protected Object getDateValue(HSSFCell cell) {
    logger.debug("getDateValue(cell={}) - start", cell);

    double numericValue = cell.getNumericCellValue();
    Date date = HSSFDateUtil.getJavaDate(numericValue);
    // Add the timezone offset again because it was subtracted automatically by Apache-POI (we need UTC)
    long tzOffset = TimeZone.getDefault().getOffset(date.getTime());
    date = new Date(date.getTime() + tzOffset);
    return new Long(date.getTime());

    //TODO use a calendar for XLS Date objects when it is supported better by POI
    //        HSSFCellStyle style = cell.getCellStyle();
    //        HSSFDataFormatter formatter = new HSSFDataFormatter();
    //        Format f = formatter.createFormat(cell);
    //      String formatted = fomatter.formatCellValue(cell);
    //System.out.println("###"+formatted);
    //        Date dateValue = cell.getDateCellValue();
}

From source file:org.dbunit.dataset.excel.XlsTable.java

License:Open Source License

protected BigDecimal getNumericValue(HSSFCell cell) {
    logger.debug("getNumericValue(cell={}) - start", cell);

    String formatString = cell.getCellStyle().getDataFormatString();
    String resultString = null;//from w  ww . j  a v a  2  s  .  c  o m
    double cellValue = cell.getNumericCellValue();

    if ((formatString != null)) {
        if (!formatString.equals("General") && !formatString.equals("@")) {
            logger.debug("formatString={}", formatString);
            DecimalFormat nf = new DecimalFormat(formatString, symbols);
            resultString = nf.format(cellValue);
        }
    }

    BigDecimal result;
    if (resultString != null) {
        try {
            result = new BigDecimal(resultString);
        } catch (NumberFormatException e) {
            logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString);
            // Probably was not a BigDecimal format retrieved from the excel. Some
            // date formats are not yet recognized by HSSF as DateFormats so that
            // we could get here.
            result = toBigDecimal(cellValue);
        }
    } else {
        result = toBigDecimal(cellValue);
    }
    return result;
}