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

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

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

Introduction

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

Prototype

public HSSFRichTextString getRichStringCellValue()

Source Link

Document

get the value of the cell as a string - for numeric cells we throw an exception.

Usage

From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java

License:Apache License

public static ExcelMetadata instance(InputStream stream) throws IOException {
    ExcelMetadata metadata = new ExcelMetadata();
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream));

    List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>();

    for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) {
        ExcelSheetMetadata meta = new ExcelSheetMetadata();
        meta.setName(wb.getSheetName(sheetInd));
        sheets.add(meta);//from w  ww.  ja va2 s. c  o  m

        HSSFSheet worksheet = wb.getSheetAt(sheetInd);
        int lastRowNum = worksheet.getLastRowNum();

        StringBuilder buff = new StringBuilder();
        for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
            HSSFRow row = worksheet.getRow(rowNum);

            // Empty rows are returned as null
            if (row == null) {
                continue;
            }

            int lastCellNum = row.getLastCellNum();
            for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) {
                HSSFCell cell = row.getCell(cellNum);

                // Undefined cells are returned as null
                if (cell == null) {
                    continue;
                }

                /*
                 * Builds a string of body content from all string, numeric,
                 * and formula values in the body of each worksheet.
                 * 
                 *  This code currently duplicates the POI 3.1 ExcelExtractor behavior of
                 *  combining the body text from all worksheets into a single string.
                 */
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    buff.append(cell.getRichStringCellValue().getString());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    buff.append(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    buff.append(cell.getCellFormula());
                    break;
                }

                HSSFComment comment = cell.getCellComment();
                if (comment != null) {
                    // Filter out row delimiter characters from comment
                    String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' ');

                    buff.append(" [");
                    buff.append(commentText);
                    buff.append(" by ");
                    buff.append(comment.getAuthor());
                    buff.append(']');
                }

                if (cellNum < lastCellNum - 1) {
                    buff.append(CELL_DELIMITER_CHAR);
                } else {
                    buff.append(ROW_DELIMITER_CHAR);
                }
            }
        }
        meta.setText(buff.toString());
    }

    metadata.setSheets(sheets);
    metadata.setMetadata(wb.getSummaryInformation());
    return metadata;
}

From source file:org.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./*  ww  w  .  jav a  2 s.  c  o  m*/
 *
 * @param dctx the dispatch context
 * @param context the context
 * @return the result of the service execution
 */
public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx,
        Map<String, ? extends Object> context) {
    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 = FastList.newInstance();

    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 = FastList.newInstance();
        List<Map<String, Object>> inventoryItems = FastList.newInstance();
        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);
        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.openXpertya.print.export.AbstractExcelExporter.java

License:Open Source License

/**
 * Export to given stream/*from  ww  w. j av  a  2  s.  c  o m*/
 * @param out
 * @throws Exception
 */
private void export(OutputStream out) throws Exception {
    HSSFSheet sheet = createTableSheet();
    String sheetName = null;
    //
    short colnumMax = 0;
    for (int rownum = 0, xls_rownum = 1; rownum < getRowCount(); rownum++, xls_rownum++) {
        setCurrentRow(rownum);

        boolean isPageBreak = false;
        HSSFRow row = sheet.createRow(xls_rownum);
        //   for all columns
        short colnum = 0;
        for (int col = 0; col < getColumnCount(); col++) {
            if (colnum > colnumMax)
                colnumMax = colnum;
            //
            if (isColumnPrinted(col)) {
                HSSFCell cell = row.createCell(colnum);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16); // Bug-2017673 - Export Report as Excel - Bad Encoding

                // line row
                Object obj = getValueAt(rownum, col);
                int displayType = getDisplayType(rownum, col);
                if (obj == null)
                    ;
                else if (DisplayType.isDate(displayType)) {
                    Timestamp value = (Timestamp) obj;
                    cell.setCellValue(value);
                } else if (DisplayType.isNumeric(displayType)) {
                    double value = 0;
                    if (obj instanceof Number) {
                        value = ((Number) obj).doubleValue();
                    }
                    cell.setCellValue(value);
                } else if (DisplayType.YesNo == displayType) {
                    boolean value = false;
                    if (obj instanceof Boolean)
                        value = (Boolean) obj;
                    else
                        value = "Y".equals(obj);
                    cell.setCellValue(
                            new HSSFRichTextString(Msg.getMsg(getLanguage(), value == true ? "Y" : "N")));
                } else {
                    String value = fixString(obj.toString()); //   formatted
                    cell.setCellValue(new HSSFRichTextString(value));
                }
                //
                HSSFCellStyle style = getStyle(rownum, col);
                cell.setCellStyle(style);
                // Page break
                if (isPageBreak(rownum, col)) {
                    isPageBreak = true;
                    sheetName = fixString(cell.getRichStringCellValue().getString());
                }
                //
                colnum++;
            } //   printed
        } //   for all columns
        //
        // Page Break
        if (isPageBreak) {
            closeTableSheet(sheet, sheetName, colnumMax);
            sheet = createTableSheet();
            xls_rownum = 0;
            isPageBreak = false;
        }
    } //   for all rows
    closeTableSheet(sheet, sheetName, colnumMax);
    //
    m_workbook.write(out);
    out.close();
    //
    // Workbook Info
    if (CLogMgt.isLevelFine()) {
        log.fine("Sheets #" + m_sheetCount);
        log.fine("Styles used #" + m_styles.size());
    }
}

From source file:org.rti.zcore.dar.utils.PoiUtils.java

License:Apache License

/**
 * This utility is a version of HSSF.main that does not use deprecated methods.
 * It is helpful in figuring out what row a filed is on when outputting Excel files via POI.
 * @param pathExcelMaster/*from w  w w.  j a  v a2  s.  c o  m*/
 */
public static void testExcelOutput(String pathExcelMaster) {

    try {
        //HSSF hssf = new HSSF(args[ 0 ]);

        System.out.println("Data dump:\n");
        //HSSFWorkbook wb = hssf.hssfworkbook;
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            System.out.println("Sheet " + k);
            HSSFSheet sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();

            for (int r = 0; r < rows; r++) {
                //HSSFRow row   = sheet.getPhysicalRowAt(r);
                HSSFRow row = sheet.getRow(r);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("ROW " + row.getRowNum());
                    for (int c = 0; c < cells; c++) {
                        //HSSFCell cell  = row.getPhysicalCellAt(c);
                        HSSFCell cell = row.getCell(c);
                        String value = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {

                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = "FORMULA ";
                                value = "FORMULA " + cell.getCellFormula();
                                break;

                            case HSSFCell.CELL_TYPE_NUMERIC:
                                value = "NUMERIC value=" + cell.getNumericCellValue();
                                break;

                            case HSSFCell.CELL_TYPE_STRING:
                                //value = "STRING value=" + cell.getStringCellValue();
                                HSSFRichTextString str = cell.getRichStringCellValue();
                                value = "STRING value=" + str;
                                break;

                            default:
                            }
                            //System.out.println("CELL col=" + cell.getCellNum()  + " VALUE=" + value);
                            System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java

License:Apache License

protected void setupColumns(DfDataTable table, HSSFRow nameRow, HSSFRow valueRow) {
    for (int i = 0;; ++i) {
        final HSSFCell nameCell = nameRow.getCell(i);
        if (nameCell == null) {
            break;
        }//from   ww w  .ja  v  a  2 s . c  o m
        final HSSFRichTextString richStringCellValue = nameCell.getRichStringCellValue();
        if (richStringCellValue == null) {
            break;
        }
        final String columnName = richStringCellValue.getString().trim();
        if (columnName.length() == 0) {
            break;
        }
        HSSFCell valueCell = null;
        if (valueRow != null) {
            valueCell = valueRow.getCell(i);
        }
        if (valueCell != null) {
            table.addColumn(columnName, getColumnType(valueCell));
        } else {
            table.addColumn(columnName);
        }
    }
}

From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java

License:Apache License

protected Object processRichStringCellValue(DfDataTable table, int columnIndex, HSSFRow row, HSSFCell cell) {
    String str = cell.getRichStringCellValue().getString();
    str = rtrimCellValueIfNeeds(table, cell, str); // basically for compatible
    str = treatEmptyAsNullBasically(str); // empty means null basically
    str = treatNullAsEmptyIfTarget(table, columnIndex, str); // but empty if target
    str = treatCrLfAsLf(str); // remove CR
    if (isCellBase64Formatted(cell)) {
        return decodeAsBase64(str);
    }/*w w  w . j a  va  2  s  . c o  m*/
    // normal cell here
    return resolveLargeDataIfNeeds(table, columnIndex, row, str);
}

From source file:org.seasar.dbflute.helper.io.xls.DfXlsReader.java

License:Apache License

public Object getValue(int columnIndex, HSSFCell cell, DfDataTable table) {
    if (cell == null) {
        if (isEmptyStringTarget(columnIndex, table)) {
            return "\"\""; // for preventing trimming later
        } else {//from   w ww .  java2  s. c  om
            return null;
        }
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (isCellDateFormatted(cell)) {
            return DfTypeUtil.toTimestamp(cell.getDateCellValue());
        }
        final double numericCellValue = cell.getNumericCellValue();
        if (isInt(numericCellValue)) {
            return new BigDecimal((int) numericCellValue);
        }
        return new BigDecimal(Double.toString(numericCellValue));
    case HSSFCell.CELL_TYPE_STRING:
        String s = cell.getRichStringCellValue().getString();
        if (s != null) {
            if (isNotTrimTarget(cell, table)) {
                if (s.length() != s.trim().length()) {
                    s = "\"" + s + "\""; // for preventing trimming later
                }
            } else {
                s = Srl.rtrim(s);
            }
        }
        if ("".equals(s)) {
            s = null;
        }
        if (isEmptyStringTarget(columnIndex, table) && s == null) {
            s = "\"\""; // for preventing trimming later
        }
        if (isCellBase64Formatted(cell)) {
            return DfTypeUtil.decodeAsBase64(s);
        }
        return s;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.valueOf(b);
    default:
        if (isEmptyStringTarget(columnIndex, table)) {
            return "\"\"";
        } else {
            return null;
        }
    }
}

From source file:org.seasar.extension.dataset.impl.XlsReader.java

License:Apache License

/**
 * ????//w  ww  .jav a 2 s.  c o  m
 * 
 * @param table
 *            
 * @param sheet
 *            
 */
protected void setupColumns(DataTable table, HSSFSheet sheet) {
    HSSFRow nameRow = sheet.getRow(0);
    HSSFRow valueRow = sheet.getRow(1);
    for (int i = 0; i <= Short.MAX_VALUE; ++i) {
        HSSFCell nameCell = nameRow.getCell((short) i);
        if (nameCell == null) {
            break;
        }
        String columnName = nameCell.getRichStringCellValue().getString();
        if (columnName.length() == 0) {
            break;
        }
        HSSFCell valueCell = null;
        if (valueRow != null) {
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                valueCell = sheet.getRow(j).getCell((short) i);
                if (valueCell != null && !StringUtil.isEmpty(valueCell.toString())) {
                    break;
                }
            }
        }
        if (valueCell != null) {
            table.addColumn(columnName, getColumnType(valueCell));
        } else {
            table.addColumn(columnName);
        }
    }
}

From source file:org.seasar.extension.dataset.impl.XlsReader.java

License:Apache License

/**
 * ????/*from  www . j a v a 2s. co m*/
 * 
 * @param cell
 *            
 * @return ?
 */
public Object getValue(HSSFCell cell) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (isCellDateFormatted(cell)) {
            return TimestampConversionUtil.toTimestamp(cell.getDateCellValue());
        }
        final double numericCellValue = cell.getNumericCellValue();
        if (isInt(numericCellValue)) {
            return new BigDecimal((int) numericCellValue);
        }
        return new BigDecimal(Double.toString(numericCellValue));
    case HSSFCell.CELL_TYPE_STRING:
        String s = cell.getRichStringCellValue().getString();
        if (s != null) {
            s = StringUtil.rtrim(s);
            if (!trimString && s.length() > 1 && s.startsWith("\"") && s.endsWith("\"")) {
                s = s.substring(1, s.length() - 1);
            }
        }
        if ("".equals(s)) {
            s = null;
        }
        if (isCellBase64Formatted(cell)) {
            return Base64Util.decode(s);
        }
        return s;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.valueOf(b);
    default:
        return null;
    }
}

From source file:org.seasar.fisshplate.core.element.ElTest.java

License:Apache License

() throws Exception{
    HSSFWorkbook template = getTemplate("/ElTest.xls");
    WorkbookWrapper workbook = new WorkbookWrapper(template);

    Map<String, Object> data = new HashMap<String, Object>();
    data.put("code", "01234");
    data.put("num", new Integer(-1234));

    FPContext context = new FPContext(template.getSheetAt(0),data);

    CellWrapper cell0 = workbook.getSheetAt(0).getRow(0).getCell(0);//${code}
    CellWrapper cell1 = workbook.getSheetAt(0).getRow(0).getCell(1);//${num}

    el = new El(new GenericCell(cell0));
    el.merge(context);/*  ww  w. j av  a  2 s.  c o m*/
    el = new El(new GenericCell(cell1));
    el.merge(context);

    HSSFCell actual = template.getSheetAt(0).getRow(0).getCell(0);
    assertEquals("celltype",HSSFCell.CELL_TYPE_STRING, actual.getCellType());
    assertEquals("value", "01234", actual.getRichStringCellValue().getString());

    actual = template.getSheetAt(0).getRow(0).getCell(1);
    assertEquals("celltype",HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType());
    assertEquals("value",-1234D, actual.getNumericCellValue(),0D);
}