Example usage for org.apache.poi.hssf.usermodel HSSFCell 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:blueprint.sdk.experimental.util.XlsReader.java

License:Open Source License

/**
 * returns effective cells from sheetIdx starting from rowIdx, cellIdx
 *
 * @param sheetIdx index of target sheet
 * @param rowIdx index of target row/*from   www.ja v a  2 s  .  c  o  m*/
 * @return cells on target row
 * @throws IOException cell type error
 */
public List<String> getCellsAt(final int sheetIdx, final int rowIdx) throws IOException {
    List<String> result = new ArrayList<>();

    HSSFRow row = wbook.getSheetAt(sheetIdx).getRow(rowIdx);
    int lastCell = row.getLastCellNum();
    cell_loop: for (int i = 0; i < cells[sheetIdx].length && i <= lastCell; i++) {
        HSSFCell cell = row.getCell(i);

        if (cell == null) {
            result.add("");
        } else {
            try {
                switch (cells[sheetIdx][i]) {
                case TYPE_SKIP:
                    break;
                case TYPE_TEXT_SKIP:
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String val = cell.getRichStringCellValue().getString();
                        result.add(val == null ? "" : val);
                    } else {
                        break cell_loop;
                    }
                    break;
                case TYPE_TEXT:
                    String textVal = cell.getRichStringCellValue().getString();
                    result.add(textVal == null ? "" : textVal);
                    break;
                case TYPE_TIME:
                    String timeVal = getTimeValue(cell);
                    result.add(timeVal == null ? "" : timeVal);
                    break;
                case TYPE_DATE:
                    String dateVal = getDateValue(cell);
                    result.add(dateVal == null ? "" : dateVal);
                    break;
                case TYPE_NUM_TEXT:
                    String numTextVal = getTextValue(cell);
                    result.add(numTextVal == null ? "" : numTextVal);
                    break;
                default:
                    throw new IOException(createTypeErrMsg(cell));
                }
            } catch (IllegalStateException e) {
                e.printStackTrace();
                throw new IOException(createTypeErrMsg(cell));
            }
        }
    }

    return result;
}

---

From source file:blueprint.sdk.experimental.util.XlsReader.java

License:Open Source License

/**
 * returns numeric/string value//from  ww  w . j a  v  a 2  s  . c  om
 *
 * @param cell target cell
 * @return text value
 * @throws IOException cell type error
 */
protected String getTextValue(final HSSFCell cell) throws IOException {
    String result;

    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            result = Long.toString((long) cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                result = Long.toString((long) cellValue.getNumberValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                throw new IOException(createTypeErrMsg(cell));
            }
            break;
        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getRichStringCellValue().getString();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            result = "";
            break;
        default:
            throw new IOException(createTypeErrMsg(cell));
        }
    } catch (IllegalStateException e) {
        e.printStackTrace();
        throw new IOException(createTypeErrMsg(cell));
    }

    return result;
}

---

From source file:cdc.impl.datasource.office.ExcelDataSource.java

License:LGPL

private static String decodeValue(HSSFCell cell, HSSFFormulaEvaluator evaluator) throws RJException {
    if (cell == null) {
        return "";
    }//  ww  w.  j a  v  a  2s .  c  o  m
    switch (evaluator.evaluateInCell(cell).getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_ERROR:
        return "";
    case HSSFCell.CELL_TYPE_FORMULA:
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return cell.toString();
        } else {
            return formatter.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    }
    throw new RJException("Error reading data from Excel input file");
}

---

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Creates a new instance of PoiTest */
public void replaceHolders(java.util.Map<String, Object> models)
//throws Exception
{
    for (int k = 0; k < wb.getNumberOfSheets(); k++) {
        HSSFSheet sheet = wb.getSheetAt(k);

        // Iterate through all rows and cols of the sheet
        int prows = sheet.getPhysicalNumberOfRows();
        int pr = 0;
        for (int r = 0; pr < prows; r++) {
            System.out.println(r + ", " + pr + ", " + prows);
            HSSFRow row = sheet.getRow(r);
            if (row == null)
                continue;
            ++pr;/*from  www  .  ja  va2  s.  c o  m*/
            int pcells = row.getPhysicalNumberOfCells();
            int pc = 0;
            for (int c = 0; pc < pcells; ++c) {
                HSSFCell cell = row.getCell((short) c);
                if (cell == null)
                    continue;
                ++pc;

                // Look for cells like ${var}
                if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING)
                    continue;

                String value = cell.getRichStringCellValue().getString().trim();
                if (!value.startsWith("${"))
                    continue;
                String rsname = value.substring(2, value.length() - 1);

                int n = replaceOneHolder(sheet, r, c, models, rsname);
                if (n != NOROWCHANGE) {
                    r += n;
                    break; // We just deleted the whole line!
                }
            }
        }
    }
}

---

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

void copyCell(HSSFCell c0, HSSFCell c1) {
    copyCellFormatting(c0, c1);//  w w w  .  j a  v  a2  s . c  o m
    c1.setCellType(c0.getCellType());
    switch (c0.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        c1.setCellValue(c0.getRichStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        c1.setCellValue(c0.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        c1.setCellFormula(c0.getCellFormula());
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        c1.setCellValue(c0.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        c1.setCellErrorValue(c0.getErrorCellValue());
        break;
    }
}

---

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow, HSSFWorkbook workbook) {
    logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);

    List<Column> columnList = new ArrayList<Column>();
    List<String> primaryKeyList = new ArrayList<String>();
    for (int i = 0;; i++) {
        HSSFCell cell = sampleRow.getCell(i);
        if (cell == null) {
            break;
        }/* w w w. jav  a2  s  .co m*/

        String columnName = cell.getRichStringCellValue().getString();
        if (columnName != null) {
            columnName = columnName.trim();
        }

        // Bugfix for issue ID 2818981 - if a cell has a formatting but no
        // name also ignore it
        if (columnName.length() <= 0) {
            logger.debug(
                    "The column name of column # {} is empty - will skip here assuming the last column was reached",
                    String.valueOf(i));
            break;
        }

        Column column = new Column(columnName, DataType.UNKNOWN);
        columnList.add(column);

        // Unique identification key
        byte underline = cell.getCellStyle().getFont(workbook).getUnderline();
        if (underline == 1) {
            primaryKeyList.add(columnName);
        }

    }
    Column[] columns = columnList.toArray(new Column[0]);

    if (!primaryKeyList.isEmpty()) {
        return new DefaultTableMetaData(tableName, columns,
                primaryKeyList.toArray(new String[primaryKeyList.size()]));
    } else {
        return new DefaultTableMetaData(tableName, columns);
    }

}

---

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);//from w w w  .ja v  a 2 s.c o m

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
        }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);

    case HSSFCell.CELL_TYPE_BLANK:
        return null;

    case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

    case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row + ", column=" + column);

    default:
        throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column);
    }
}

---

From source file:cn.fql.template.poi.PoiTest.java

License:Open Source License

public static void writeEffort(int index, String sheetName) {
    HSSFSheet templateSheet = templateWbk.getSheet(sheetName);
    String lastCellValue = null;//from   w w  w  .  ja  v  a2  s .  c om
    for (int i = 1118; i < 1232; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) index);
            if (lastCellValue == null) {
                lastCellValue = cell.getRichStringCellValue().getString();
            } else {
                String newCellValue = cell.getRichStringCellValue().getString();
                if (cell != null) {
                    if (lastCellValue.equals(newCellValue)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) index);
                        region.setColumnTo((short) index);
                        templateSheet.addMergedRegion(region);
                    } else {
                        lastCellValue = newCellValue;
                    }
                }
            }
        }
    }
}

---

From source file:cn.vlabs.umt.common.xls.UserXLSParser.java

License:Apache License

private String readCellValue(HSSFCell cell) {
    if (cell != null) {
        String cellvalue = null;/*  www .  j a va 2s .  c o m*/
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            cellvalue = Integer.toString((int) cell.getNumericCellValue());
            break;
        default:
            cellvalue = cell.getRichStringCellValue().getString();
            break;
        }
        return cellvalue;
    } else {
        return null;
    }
}

---

From source file:com.beyondb.io.ExcelReader.java

/**
 * ?HSSFCell?//from ww w  . j a  v a  2 s. c om
 * @param cell
 * @return
 */
private String getCellFormatValue(HSSFCell cell) {
    String cellvalue = "";
    if (cell != null) {
        // ?CellType
        switch (cell.getCellType()) {
        // ?CellTypeNUMERIC
        case HSSFCell.CELL_TYPE_NUMERIC:
        case HSSFCell.CELL_TYPE_FORMULA: {
            // ?cell?Date
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // DateData?

                //1?data?2011-10-12 0:00:00
                //cellvalue = cell.getDateCellValue().toLocaleString();

                //2?data??2011-10-12
                Date date = cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                cellvalue = sdf.format(date);

            }
            // 
            else {
                // ??Cell
                cellvalue = String.valueOf(cell.getNumericCellValue());
            }
            break;
        }
        // ?CellTypeSTRIN
        case HSSFCell.CELL_TYPE_STRING:
            // ??Cell
            cellvalue = cell.getRichStringCellValue().getString();
            break;
        // Cell
        default:
            cellvalue = " ";
        }
    } else {
        cellvalue = "";
    }
    return cellvalue;

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• »