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:com.cablelabs.fsm.SystemSettings.java

License:Open Source License

private String getXlsCellStringValue(HSSFCell cell) {
    int cellType = 0;
    String value = "";

    if (cell != null) {
        cellType = cell.getCellType();//from w w  w .  j  a  v  a 2 s  . c  o  m

        switch (cellType) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            Double doubleValue = cell.getNumericCellValue();
            value = Long.toString(doubleValue.longValue());
            break;
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            value = "";
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "ERROR";
            break;
        default:
        }
    }
    return value;
}

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

private String getCellValue(HSSFSheet sheet, int row, int column) {
    if (row > sheet.getLastRowNum())
        return "";
    HSSFRow theRow = sheet.getRow(row);//w  ww.  java 2 s. c om
    if (theRow == null)
        return "";
    HSSFCell cell = theRow.getCell(column);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    case HSSFCell.CELL_TYPE_NUMERIC: {
        Double v = cell.getNumericCellValue();
        if (v.doubleValue() == (v.longValue())) {
            return Long.toString(v.longValue());
        }
        return Double.toString(v);
    }
    case HSSFCell.CELL_TYPE_STRING:
        String v = cell.getRichStringCellValue().getString().trim();
        return v;

    default:
        return "";
    }
}

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) {
    if (src == null) {
        dest.setCellType(HSSFCell.CELL_TYPE_BLANK);
        return;//  w ww  .  ja va 2s.  c o  m
    }

    if (src.getCellComment() != null)
        dest.setCellComment(src.getCellComment());
    if (src.getCellStyle() != null) {
        HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb);
        if (nstyle == null) {
            nstyle = destwb.createCellStyle();
            copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle());
        }
        dest.setCellStyle(nstyle);
    }
    dest.setCellType(src.getCellType());

    switch (src.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:

        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        dest.setCellValue(src.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        dest.setCellFormula(src.getCellFormula());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        dest.setCellErrorValue(src.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        dest.setCellValue(src.getNumericCellValue());
        break;
    default:
        dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString()));
        break;
    }
}

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

License:Open Source License

private Object getCellString(HSSFCell cell) {
    Object result = null;/*from  w  w  w  . j av a2s  .com*/
    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:
            logger.info("");
            break;
        }
    }
    return result;
}

From source file:com.glaf.base.modules.todo.TodoXlsReader.java

License:Apache License

public List<Todo> readXls(java.io.InputStream inputStream) {
    List<Todo> todos = new java.util.ArrayList<Todo>();
    HSSFWorkbook wb = null;//  w ww . j a  v a  2  s .c o  m
    try {
        wb = new HSSFWorkbook(inputStream);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(1);
    Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>();
    Map<String, Object> dataMap = new java.util.HashMap<String, Object>();
    int cells = row.getPhysicalNumberOfCells();
    for (int colIndex = 0; colIndex < cells; colIndex++) {
        HSSFCell cell = row.getCell(colIndex);
        keyMap.put(colIndex, cell.getStringCellValue());
    }
    Set<String> keys = new HashSet<String>();
    for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
        HSSFRow rowx = sheet.getRow(rowIndex);
        if (rowx == null) {
            continue;
        }
        // System.out.println();
        dataMap.clear();
        for (int colIndex = 0; colIndex < cells; colIndex++) {
            String fieldName = keyMap.get(colIndex);
            HSSFCell cell = rowx.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            Object cellValue = null;
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) {
                    cellValue = cell.getRichStringCellValue().getString();
                }
                break;
            default:
                if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                    cellValue = cell.getStringCellValue();
                }
                break;
            }
            if (cellValue != null) {
                dataMap.put(fieldName, cellValue);
                if ("id".equals(fieldName)) {
                    cellValue = cellValue.toString().substring(0, cellValue.toString().indexOf("."));
                    dataMap.put(fieldName, cellValue);
                }
            }
        }

        if (dataMap.get("code") != null) {

            String id = ParamUtils.getString(dataMap, "id");
            if (!keys.contains(ParamUtils.getString(dataMap, "code"))) {
                if (id != null && StringUtils.isNotEmpty(id)) {
                    Todo model = new Todo();
                    Tools.populate(model, dataMap);
                    if (ParamUtils.getInt(dataMap, "limitDay") > 0) {
                        model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay"));
                    }
                    todos.add(model);
                    keys.add(model.getCode());
                }
            }
        }
    }

    return todos;
}

From source file:com.glaf.core.todo.util.TodoXlsReader.java

License:Apache License

public List<Todo> readXls(java.io.InputStream inputStream) {
    List<Todo> todos = new java.util.ArrayList<Todo>();
    HSSFWorkbook wb = null;/*from  w  w w .ja  v  a 2  s. com*/
    try {
        wb = new HSSFWorkbook(inputStream);

        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(1);
        Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>();
        Map<String, Object> dataMap = new java.util.HashMap<String, Object>();
        int cells = row.getPhysicalNumberOfCells();
        for (int colIndex = 0; colIndex < cells; colIndex++) {
            HSSFCell cell = row.getCell(colIndex);
            keyMap.put(colIndex, cell.getStringCellValue());
        }
        int sortNo = 1;
        Set<String> keys = new HashSet<String>();
        for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
            HSSFRow rowx = sheet.getRow(rowIndex);
            if (rowx == null) {
                continue;
            }
            // System.out.println();
            dataMap.clear();
            for (int colIndex = 0; colIndex < cells; colIndex++) {
                String fieldName = keyMap.get(colIndex);
                HSSFCell cell = rowx.getCell(colIndex);
                if (cell == null) {
                    continue;
                }
                Object cellValue = null;
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_FORMULA:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    cellValue = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) {
                        cellValue = cell.getRichStringCellValue().getString();
                    }
                    break;
                default:
                    if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                        cellValue = cell.getStringCellValue();
                    }
                    break;
                }
                if (cellValue != null) {
                    dataMap.put(fieldName, cellValue);
                    // System.out.print("\t" + fieldName + "=" + cellValue);
                }
            }

            if (dataMap.get("code") != null) {
                String id = ParamUtils.getString(dataMap, "id");
                Todo model = new Todo();
                dataMap.remove("id");
                Tools.populate(model, dataMap);

                if (!keys.contains(model.getCode())) {
                    model.setSortNo(sortNo++);
                    if (id != null) {
                        model.setId(Long.parseLong(id));
                    }
                    if (ParamUtils.getDouble(dataMap, "limitDay") > 0) {
                        model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay"));
                    }
                    todos.add(model);
                    keys.add(model.getCode());
                }
            }
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        if (wb != null) {
            try {
                wb.close();
                wb = null;
            } catch (IOException e) {
            }
        }
    }

    return todos;
}

From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTable.java

License:Open Source License

/**
 * Gets cell values from row/* www .java 2s. c o m*/
 * 
 * @param relativRowIndex
 *            to the description and cell headers
 * @return cell values for the given row
 */
public List<Object> getRow(int relativRowIndex) {
    List<Object> result = new ArrayList<Object>();
    HSSFRow row = sheet.getRow(startingRowIndex + 2 + relativRowIndex);
    for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
        HSSFCell cell = row.getCell(cellnum);

        try {
            int cellType = cell.getCellType();
            switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC: {
                result.add(cell.getNumericCellValue());
                continue;
            }
            case HSSFCell.CELL_TYPE_FORMULA: {
                result.add(cell.getNumericCellValue());
                continue;
            }
            case HSSFCell.CELL_TYPE_STRING: {
                result.add(cell.getRichStringCellValue().getString());
                continue;
            }
            default: {
                throw new IllegalStateException("can't handle value");
            }
            }

        } catch (Exception e) {
            throw new ExcelTableCellReadException(row.getRowNum() + 1, cellnum, cell, e);
        }
    }
    return result;
}

From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTables.java

License:Open Source License

public ExcelExampleTable getTable(String tableDescription) {

    HSSFSheet sheet = workbook.getSheetAt(0);
    int rowIndex = -1;
    int cellnum = -1;
    HSSFCell cell = null;
    try {/*  w  w w . jav  a 2s.  com*/
        for (rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            if (null == row) {
                continue;
            }
            for (cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
                cell = row.getCell(cellnum);
                if ((HSSFCell.CELL_TYPE_STRING == cell.getCellType())
                        && tableDescription.equals(cell.getRichStringCellValue().getString())) {
                    return new ExcelExampleTable(sheet, rowIndex, cellnum);
                }
            }
        }
    } catch (Exception e) {
        throw new IllegalStateException("Problem with cell (row=" + rowIndex + ",cellnum=" + cellnum + ",val="
                + cell + ") getting table [" + tableDescription + "] from [" + xlsFilePath + "]", e);

    }
    throw new IllegalArgumentException("Can't find [" + tableDescription + "] in [" + xlsFilePath + "]");
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * copies template cell to result row into result column. Fills this cell with data from band
 *
 * @param templateCell - template cell//  ww  w. ja  va  2 s  .  com
 * @param resultRow    - result row
 * @param resultColumn - result column
 * @param band         - band
 */
private HSSFCell copyCellFromTemplate(HSSFCell templateCell, HSSFRow resultRow, int resultColumn,
        BandData band) {
    if (templateCell == null)
        return null;

    HSSFCell resultCell = resultRow.createCell(resultColumn);

    HSSFCellStyle templateStyle = templateCell.getCellStyle();
    HSSFCellStyle resultStyle = copyCellStyle(templateStyle);
    resultCell.setCellStyle(resultStyle);

    String templateCellValue = "";
    int cellType = templateCell.getCellType();

    if (cellType != HSSFCell.CELL_TYPE_FORMULA && cellType != HSSFCell.CELL_TYPE_NUMERIC) {
        HSSFRichTextString richStringCellValue = templateCell.getRichStringCellValue();
        templateCellValue = richStringCellValue != null ? richStringCellValue.getString() : "";

        templateCellValue = extractStyles(templateCell, resultCell, templateCellValue, band);
    }

    if (cellType == HSSFCell.CELL_TYPE_STRING && containsJustOneAlias(templateCellValue)) {
        updateValueCell(rootBand, band, templateCellValue, resultCell,
                drawingPatriarchsMap.get(resultCell.getSheet()));
    } else {
        String cellValue = inlineBandDataToCellString(templateCell, templateCellValue, band);
        setValueToCell(resultCell, cellValue, cellType);
    }

    return resultCell;
}

From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java

License:Open Source License

public String extractText(String filepath) throws Exception {

    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    String resultText = "";
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText;
    }//www . j  ava  2 s  . c om

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {
                        /*
                         * if(HSSFDateUtil.isCellDateFormatted(cell) ==
                         * true) { resultText +=
                         * cell.getDateCellValue().toString() + " "; } else
                         */
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            resultText += "           ";

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            resultText += cell.getRichStringCellValue().toString() + " ";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            resultText += d.toString() + " ";
                        }

                        /*
                         * else if(cell.getCellType() ==
                         * HSSFCell.CELL_TYPE_FORMULA){ resultText +=
                         * cell.getCellFormula() + " "; }
                         */
                    }
                } catch (Exception ex) {

                }
            }
            resultText += "\n";
        }
    }
    if (input != null) {
        input.close();
    }
    return resultText;
}