Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:cherry.parser.worksheet.SheetBasedParser.java

License:Apache License

private TypeDef parseSheet(Sheet sheet) {

    State state = State.HEAD;
    int coldefFirstCellNum = -1;
    Map<Integer, String> coldef = new TreeMap<Integer, String>();

    TypeDef typeDef = new TypeDef();
    typeDef.setSheetName(sheet.getSheetName());
    for (Row row : sheet) {

        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum < 0) {
            continue;
        }//from  ww  w .  j  av a2 s . c o m

        if (state == State.HEAD) {

            String directive = getCellValueAsString(row.getCell(firstCellNum));
            if ("##FQCN".equals(directive)) {

                String fqcn = getCellValueAsString(row.getCell(firstCellNum + 1));
                typeDef.setFullyQualifiedClassName(fqcn);
            } else if ("##ATTR".equals(directive)) {

                String key = getCellValueAsString(row.getCell(firstCellNum + 1));
                String value = getCellValueAsString(row.getCell(firstCellNum + 2));
                typeDef.put(key, value);
            } else if ("##COLDEF".equals(directive)) {

                for (Cell cell : row) {
                    if (cell.getColumnIndex() == firstCellNum) {
                        continue;
                    }
                    if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        continue;
                    }
                    coldef.put(cell.getColumnIndex(), getCellValueAsString(cell));
                }

                coldefFirstCellNum = firstCellNum;
                state = State.ITEM;
            } else {
                // IGNORE UNKNOWN DIRECTIVES
            }
        } else if (state == State.ITEM) {

            ItemDef item = null;
            for (Cell cell : row) {

                if (cell.getColumnIndex() == coldefFirstCellNum) {
                    item = new ItemDef();
                    continue;
                }
                if (item == null) {
                    continue;
                }
                String key = coldef.get(cell.getColumnIndex());
                if (key == null) {
                    continue;
                }

                String value = getCellValueAsString(cell);
                if (value != null) {
                    item.put(key, value);
                }
            }

            if (item != null) {
                typeDef.getItemDef().add(item);
            }
        } else {
            // IGNORE UNKNOWN STATE
        }
    }

    return typeDef;
}

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

License:Apache License

/**
 *  ? Cells//from www.  jav a  2  s .  c  om
 */
public int[] createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams,
        Sheet sheet, Workbook workbook, short rowHeight, int cellNum) {
    try {
        ExcelExportEntity entity;
        Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
        int maxHeight = 1, listMaxHeight = 1;
        // ????
        int margeCellNum = cellNum;
        int indexKey = createIndexCell(row, index, excelParams.get(0));
        cellNum += indexKey;
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            if (entity.getList() != null) {
                Collection<?> list = getListCellValue(entity, t);
                int listIndex = 0, tmpListHeight = 0;
                if (list != null && list.size() > 0) {
                    int tempCellNum = 0;
                    for (Object obj : list) {
                        int[] temp = createCells(patriarch, index + listIndex, obj, entity.getList(), sheet,
                                workbook, rowHeight, cellNum);
                        tempCellNum = temp[1];
                        tmpListHeight += temp[0];
                        listIndex++;
                    }
                    cellNum = tempCellNum;
                    listMaxHeight = Math.max(listMaxHeight, tmpListHeight);
                }
            } else {
                Object value = getCellValue(entity, t);

                if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                    createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);

                } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                    createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                } else {
                    createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(),
                            t);
                }
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            }
        }
        maxHeight += listMaxHeight - 1;
        if (indexKey == 1 && excelParams.get(1).isNeedMerge()) {
            excelParams.get(0).setNeedMerge(true);
        }
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            if (entity.getList() != null) {
                margeCellNum += entity.getList().size();
            } else if (entity.isNeedMerge() && maxHeight > 1) {
                for (int i = index + 1; i < index + maxHeight; i++) {
                    if (sheet.getRow(i) == null) {
                        sheet.createRow(i);
                    }
                    sheet.getRow(i).createCell(margeCellNum);
                    sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity));
                }
                PoiMergeCellUtil.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum,
                        margeCellNum);
                margeCellNum++;
            }
        }
        return new int[] { maxHeight, cellNum };
    } catch (Exception e) {
        LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);
    }

}

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

License:Apache License

/**
 * List??Cells/*w  ww .j  av  a2s  . c o  m*/
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
        List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
    } else {
        row = sheet.getRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
            createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
            createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                    index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else {
            createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
        }
    }
}

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

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    if (sheet.getWorkbook() instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }//  www . j a v a  2 s .  co m
    deleteCell(sheet, map);
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHandler = new TemplateSumHandler(sheet);
    if (colForeach) {
        colForeach(sheet, map);
    }
    Row row = 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++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }

    //??
    handlerSumCell(sheet);
}

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

License:Apache License

/**
 * ,??/*from  w ww .  j  a v a  2  s .  c o m*/
 *
 * @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() == CellType.STRING || cell.getCellType() == CellType.NUMERIC)) {
                String text = PoiCellUtil.getCellValue(cell);
                if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {
                    foreachCol(cell, map, text);
                }
            }
        }
    }
}

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

License:Apache License

/**
 * ,??/*from   w w  w. j av  a  2s  .c om*/
 *
 * @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

private void setForEeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t,
        List<ExcelForEachParams> columns, Map<String, Object> map, int rowspan, int colspan,
        MergedRegionHelper mergedRegionHelper) throws Exception {
    //cell??/*  ww w.  j ava2  s. c o m*/
    for (int i = 0; i < rowspan; i++) {
        int size = columns.size();//?
        for (int j = columnIndex, max = columnIndex + colspan; j < max; j++) {
            if (row.getCell(j) == null) {
                row.createCell(j);
                CellStyle style = row.getRowNum() % 2 == 0
                        ? getStyles(false, size <= j - columnIndex ? null : columns.get(j - columnIndex))
                        : getStyles(true, size <= j - columnIndex ? null : columns.get(j - columnIndex));
                //styler??,?Excel,??Excel?
                if (style != null) {
                    row.getCell(j).setCellStyle(style);
                }
            }

        }
        if (i < rowspan - 1) {
            row = row.getSheet().getRow(row.getRowNum() + 1);
        }
    }
    //?
    ExcelForEachParams params;
    row = row.getSheet().getRow(row.getRowNum() - rowspan + 1);
    for (int k = 0; k < rowspan; k++) {
        int ci = columnIndex;
        short high = columns.get(0).getHeight();
        int n = k;
        while (n > 0) {
            if (columns.get(n * colspan).getHeight() == 0) {
                n--;
            } else {
                high = columns.get(n * colspan).getHeight();
                break;
            }
        }
        row.setHeight(high);
        for (int i = 0; i < colspan && i < columns.size(); i++) {
            boolean isNumber = false;
            params = columns.get(colspan * k + i);
            tempCreateCellSet.add(row.getRowNum() + "_" + (ci));
            if (params == null) {
                continue;
            }
            if (StringUtils.isEmpty(params.getName()) && StringUtils.isEmpty(params.getConstValue())) {
                row.getCell(ci).setCellStyle(params.getCellStyle());
                ci = ci + params.getColspan();
                continue;
            }
            String val = null;
            Object obj = null;
            //??
            if (StringUtils.isEmpty(params.getName())) {
                val = params.getConstValue();
            } else {
                String tempStr = new String(params.getName());
                if (isNumber(tempStr)) {
                    isNumber = true;
                    tempStr = tempStr.replaceFirst(NUMBER_SYMBOL, "");
                }
                map.put(teplateParams.getTempParams(), t);
                obj = eval(tempStr, map);
                val = obj.toString();
            }
            if (obj != null && obj instanceof ImageEntity) {
                ImageEntity img = (ImageEntity) obj;
                row.getCell(ci).setCellValue("");
                if (img.getRowspan() > 1 || img.getColspan() > 1) {
                    img.setHeight(0);
                    row.getCell(ci).getSheet()
                            .addMergedRegion(new CellRangeAddress(row.getCell(ci).getRowIndex(),
                                    row.getCell(ci).getRowIndex() + img.getRowspan() - 1,
                                    row.getCell(ci).getColumnIndex(),
                                    row.getCell(ci).getColumnIndex() + img.getColspan() - 1));
                }
                createImageCell(row.getCell(ci), img.getHeight(), img.getRowspan(), img.getColspan(),
                        img.getUrl(), img.getData());
            } else if (isNumber && StringUtils.isNotEmpty(val)) {
                row.getCell(ci).setCellValue(Double.parseDouble(val));
            } else {
                try {
                    row.getCell(ci).setCellValue(val);
                } catch (Exception e) {
                    LOGGER.error(e.getMessage(), e);
                }
            }
            if (params.getCellStyle() != null) {
                row.getCell(ci).setCellStyle(params.getCellStyle());
            }
            //??
            if (params.isNeedSum()) {
                templateSumHandler.addValueOfKey(params.getName(), val);
            }
            //??,?????
            setMergedRegionStyle(row, ci, params);
            //??
            if ((params.getRowspan() != 1 || params.getColspan() != 1)
                    && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)
                    && PoiCellUtil.isMergedRegion(row.getSheet(), row.getRowNum(), ci)) {
                PoiMergeCellUtil.addMergedRegion(row.getSheet(), row.getRowNum(),
                        row.getRowNum() + params.getRowspan() - 1, ci, ci + params.getColspan() - 1);
            }
            ci = ci + params.getColspan();
        }
        row = row.getSheet().getRow(row.getRowNum() + 1);
    }

}

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

License:Apache License

/**
 * ???//from   w  ww  . j  a  v  a  2 s. c o m
 *
 * @param row
 * @param ci
 * @param params
 */
private void setMergedRegionStyle(Row row, int ci, ExcelForEachParams params) {
    //?
    for (int i = 1; i < params.getColspan(); i++) {
        if (params.getCellStyle() != null) {
            row.getCell(ci + i).setCellStyle(params.getCellStyle());
        }
    }
    for (int i = 1; i < params.getRowspan(); i++) {
        for (int j = 0; j < params.getColspan(); j++) {
            if (params.getCellStyle() != null) {
                row.getCell(ci + j).setCellStyle(params.getCellStyle());
            }
        }
    }
}

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

License:Apache License

/**
 * ??/*from w  w w . j a v a  2s.  com*/
 *
 * @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

/***
 * ?List?/*from  w w  w  . j a va 2s  .  c  o  m*/
 *
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
public void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap,
        String targetId, Map<String, PictureData> pictures, ImportParams params, StringBuilder errorMsg)
        throws Exception {
    Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object,
            param.getName());
    Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
    if (entity instanceof IExcelDataModel) {
        ((IExcelDataModel) entity).setRowNum(row.getRowNum());
    }
    String picId;
    // ??
    boolean isUsed = false;
    for (int i = row.getFirstCellNum(); i < titlemap.size(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == BaseEntityTypeConstants.IMAGE_TYPE) {
                picId = row.getRowNum() + "_" + i;
                saveImage(entity, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                try {
                    saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
                } catch (ExcelImportException e) {
                    // ?,,
                    if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {
                        errorMsg.append(" ").append(titleString)
                                .append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());
                    }
                }
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}