Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getStringCellValue.

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * /*from  ww w . j  a va  2  s.co  m*/
 *
 * @param cell
 * @param map
 * @param name
 * @throws Exception
 */
private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = name.contains(FOREACH_COL_VALUE);
    name = name.replace(FOREACH_COL_VALUE, EMPTY).replace(FOREACH_COL, EMPTY).replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    while (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        if (cell.getRow().getCell(cell.getColumnIndex() + colspan) == null) {
            cell.getRow().createCell(cell.getColumnIndex() + colspan);
        }
        cell = cell.getRow().getCell(cell.getColumnIndex() + colspan);
    }
    if (isCreate) {
        cell = cell.getRow().getCell(cell.getColumnIndex() - 1);
        cell.setCellValue(cell.getStringCellValue() + END_STR);
    }
}

---

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??//from  ww w.  jav a  2  s . com
 *
 * @param sheet
 * @param map
 * @throws Exception
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null
                    && (cell.getCellType() == CellType.STRING || cell.getCellType() == CellType.NUMERIC)) {
                cell.setCellType(CellType.STRING);
                String text = cell.getStringCellValue();
                if (text.contains(IF_DELETE)) {
                    if (Boolean.valueOf(
                            eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map)
                                    .toString())) {
                        PoiSheetUtil.deleteColumn(sheet, i);
                        i--;
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

---

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ?Cell??set//  w w w.  j av a 2  s.  c  o  m
 *
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
    CellType cellType = cell.getCellType();
    if (cellType != CellType.STRING && cellType != CellType.NUMERIC) {
        return;
    }
    String oldString;
    oldString = cell.getStringCellValue();
    if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
        // step 2. ???
        String params = null;
        boolean isNumber = false;
        if (isNumber(oldString)) {
            isNumber = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        boolean isStyleBySelf = false;
        if (isStyleBySelf(oldString)) {
            isStyleBySelf = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        Object obj = PoiPublicUtil.getRealValue(oldString, map);
        // ,// 
        if (obj instanceof ImageEntity) {
            ImageEntity img = (ImageEntity) obj;
            cell.setCellValue("");
            if (img.getRowspan() > 1 || img.getColspan() > 1) {
                img.setHeight(0);
                PoiMergeCellUtil.addMergedRegion(cell.getSheet(), cell.getRowIndex(),
                        cell.getRowIndex() + img.getRowspan() - 1, cell.getColumnIndex(),
                        cell.getColumnIndex() + img.getColspan() - 1);
            }
            createImageCell(cell, img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(),
                    img.getData());
        } else if (isNumber && StringUtils.isNotBlank(obj.toString())) {
            cell.setCellValue(Double.parseDouble(obj.toString()));
        } else {
            cell.setCellValue(obj.toString());
        }
    }
    //foreach ?
    if (oldString != null && oldString.contains(FOREACH)) {
        addListDataToExcel(cell, map, oldString.trim());
    }

}

---

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??//  ww w .ja  va  2  s  .c o  m
 *
 * @param cell
 * @param name
 * @param mergedRegionHelper
 * @return
 */
private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
    List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();
    cell.setCellValue("");
    columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));
    int rowspan = 1, colspan = 1;
    if (!name.contains(END_STR)) {
        int index = cell.getColumnIndex();
        //?col 
        int startIndex = cell.getColumnIndex();
        Row row = cell.getRow();
        while (index < row.getLastCellNum()) {
            int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan()
                    : 1;
            index += colSpan;

            for (int i = 1; i < colSpan; i++) {
                //??,???,,?
                columns.add(null);
                continue;
            }
            cell = row.getCell(index);
            //???
            if (cell == null) {
                //?,
                columns.add(null);
                continue;
            }
            String cellStringString;
            try {//?? ?
                cellStringString = cell.getStringCellValue();
                if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                    throw new ExcelExportException("for each ,?");
                } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                    //?,,?,?,?
                    columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));
                    continue;
                }
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);
            }
            //?cell 
            cell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell,
                        mergedRegionHelper));
                //cell(????)
                int lastCellColspan = columns.get(columns.size() - 1).getColspan();
                for (int i = 1; i < lastCellColspan; i++) {
                    //??,???,,?
                    columns.add(null);
                }
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - columns.get(columns.size() - 1).getColspan();
                row = row.getSheet().getRow(row.getRowNum() + 1);
                rowspan++;
            } else {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
            }
        }
    }
    colspan = 0;
    for (int i = 0; i < columns.size(); i++) {
        colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;
    }
    colspan = colspan / rowspan;
    return new Object[] { rowspan, colspan, columns };
}

---

From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java

License:Apache License

/**
 * ??// w ww .ja  v a 2  s  .  co  m
 *
 * @param cell
 * @param entity
 * @return
 */
private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) {
    if (cell == null) {
        return "";
    }
    Object result = null;
    if ("class java.util.Date".equals(classFullName) || "class java.sql.Date".equals(classFullName)
            || ("class java.sql.Time").equals(classFullName)
            || ("class java.time.Instant").equals(classFullName)
            || ("class java.time.LocalDate").equals(classFullName)
            || ("class java.time.LocalDateTime").equals(classFullName)
            || ("class java.sql.Timestamp").equals(classFullName)) {
        //FIX: ?yyyyMMdd cell.getDateCellValue() ?
        if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) {
            result = DateUtil.getJavaDate(cell.getNumericCellValue());
        } else {
            String val = "";
            try {
                val = cell.getStringCellValue();
            } catch (Exception e) {
                cell.setCellType(CellType.STRING);
                val = cell.getStringCellValue();
            }

            result = getDateData(entity, val);
            if (result == null) {
                return null;
            }
        }
        if (("class java.time.Instant").equals(classFullName)) {
            result = ((Date) result).toInstant();
        } else if (("class java.time.LocalDate").equals(classFullName)) {
            result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        } else if (("class java.time.LocalDateTime").equals(classFullName)) {
            result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        } else if (("class java.sql.Date").equals(classFullName)) {
            result = new java.sql.Date(((Date) result).getTime());
        } else if (("class java.sql.Time").equals(classFullName)) {
            result = new Time(((Date) result).getTime());
        } else if (("class java.sql.Timestamp").equals(classFullName)) {
            result = new Timestamp(((Date) result).getTime());
        }
    } else {
        switch (cell.getCellType()) {
        case STRING:
            result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString();
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                if ("class java.lang.String".equals(classFullName)) {
                    result = formateDate(entity, cell.getDateCellValue());
                }
            } else {
                result = readNumericCell(cell);
            }
            break;
        case BOOLEAN:
            result = Boolean.toString(cell.getBooleanCellValue());
            break;
        case BLANK:
            break;
        case ERROR:
            break;
        case FORMULA:
            try {
                result = readNumericCell(cell);
            } catch (Exception e1) {
                try {
                    result = cell.getRichStringCellValue() == null ? ""
                            : cell.getRichStringCellValue().getString();
                } catch (Exception e2) {
                    throw new RuntimeException("???", e2);
                }
            }
            break;
        default:
            break;
        }
    }
    return result;
}

---

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * ??/*w  w  w.  j  av  a 2s .  c o m*/
 */
public boolean verifyingDataValidity(Object object, Row row, ImportParams params, boolean isMap,
        StringBuilder fieldErrorMsg) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerify()) {
        String errorMsg = PoiValidationUtil.validation(object, params.getVerifyGroup());
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            }
            isAdd = false;
            verifyFail = true;
        }
    }
    if (params.getVerifyHandler() != null) {
        ExcelVerifyHandlerResult result = params.getVerifyHandler().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null) {
                cell = row.createCell(row.getLastCellNum());
            }
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            }
            isAdd = false;
            verifyFail = true;
        }
    }
    if ((params.isNeedVerify() || params.getVerifyHandler() != null) && fieldErrorMsg.length() > 0) {
        if (object instanceof IExcelModel) {
            IExcelModel model = (IExcelModel) object;
            model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                    + fieldErrorMsg.toString());
        }
        if (cell == null) {
            cell = row.createCell(row.getLastCellNum());
        }
        cell.setCellValue(
                (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                        + fieldErrorMsg.toString());
        isAdd = false;
        verifyFail = true;
    }
    if (cell != null) {
        cell.setCellStyle(errorCellStyle);
        failRow.add(row);
        if (isMap) {
            ((Map) object).put("excelErrorMsg", cell.getStringCellValue());
        }
    } else {
        successRow.add(row);
    }
    return isAdd;
}

---

From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java

License:Apache License

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;/* w  w w .j a  va 2 s .co m*/
    }
    case NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    default:
        cNew.setCellValue(cOld.getStringCellValue());
    }

}

---

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void hanlderSumCell(Sheet sheet) {
    for (TemplateSumHanlder.TemplateSumEntity sumEntity : templateSumHanlder.getDataList()) {
        Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());
        cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")",
                sumEntity.getValue() + ""));
    }/*from ww w  .j  av  a  2 s  .c o  m*/
}

---

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??/*  www  .j  av a 2 s  . c  om*/
 * @param sheet
 * @param map
 */
private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {
                    foreachCol(cell, map, text);
                }
            }
        }
    }
}

---

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * //from w  w w . j  a va  2  s  .  c  om
 * @param cell
 * @param map
 * @param name
 * @throws Exception
 */
private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = name.contains(FOREACH_COL_VALUE);
    name = name.replace(FOREACH_COL_VALUE, EMPTY).replace(FOREACH_COL, EMPTY).replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    while (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        cell = cell.getRow().getCell(cell.getColumnIndex() + colspan);
    }
    if (isCreate) {
        cell = cell.getRow().getCell(cell.getColumnIndex() - 1);
        cell.setCellValue(cell.getStringCellValue() + END_STR);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »