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

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellValue

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell//from w  w w. j a  v  a 2  s . c o  m
 */
public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    cell.setCellType(CellType.NUMERIC);
    if (text != null && text.length() > 0) {
        try {
            cell.setCellValue(Double.parseDouble(text));
        } catch (NumberFormatException e) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(text);
        }
    }

    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

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

License:Apache License

private void handlerSumCell(Sheet sheet) {
    for (TemplateSumEntity sumEntity : templateSumHandler.getDataList()) {
        Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());
        if (cell.getStringCellValue().contains(sumEntity.getSumKey())) {
            cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")",
                    sumEntity.getValue() + ""));
        } else {/*from   w  w  w .  ja  v a 2s  .c om*/
            cell.setCellValue(cell.getStringCellValue() + sumEntity.getValue());
        }
    }
}

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

License:Apache License

/**
 * //from   ww w . j  a v  a2 s .com
 *
 * @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 w w w  .j  a  va 2s.c o  m
 *
 * @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//from   ww  w .j ava  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

/**
 * ??// w w  w.  ja  va  2s. 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.ExcelImportService.java

License:Apache License

/**
 * ??//  ww  w .ja va 2 s .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  av  a  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.base.ExcelExportBase.java

License:Apache License

/**
 * Cell//from   w  w  w  .j a v  a2  s  .  c  o  m
 *
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 */
public void createStringCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {
        cell.setCellValue(Double.parseDouble(text));
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else {
        RichTextString Rtext;
        if (type.equals(ExcelType.HSSF)) {
            Rtext = new HSSFRichTextString(text);
        } else {
            Rtext = new XSSFRichTextString(text);
        }
        cell.setCellValue(Rtext);
    }
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell/*w  w w .  ja  v a2 s  .c om*/
 *
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 */
public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (text != null && text.length() > 0) {
        cell.setCellValue(Double.parseDouble(text));
    } else {
        cell.setCellValue(-1);
    }
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}