Example usage for org.apache.poi.ss.usermodel Row 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:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;/*from  w w  w  . j av a 2  s  . c  o m*/
    i18nHandler = params.getI18nHandler();
    boolean isMap = Map.class.equals(pojoClass);
    if (!isMap) {
        Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection, excelParams);
    checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    int readRow = 1;
    //
    for (int i = 0; i < params.getStartRows(); i++) {
        rows.next();
    }
    //index ?,?
    if (excelCollection.size() > 0 && params.getKeyIndex() == null) {
        params.setKeyIndex(0);
    }
    if (params.isConcurrentTask()) {
        ForkJoinPool forkJoinPool = new ForkJoinPool();
        int endRow = sheet.getLastRowNum() - params.getLastOfInvalidRow();
        if (params.getReadRows() > 0) {
            endRow = params.getReadRows();
        }
        ExcelImportForkJoinWork task = new ExcelImportForkJoinWork(
                params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params,
                pojoClass, this, targetId, titlemap, excelParams);
        ExcelImportResult forkJoinResult = forkJoinPool.invoke(task);
        collection = forkJoinResult.getList();
        failCollection = forkJoinResult.getFailList();
    } else {
        StringBuilder errorMsg;
        while (rows.hasNext()
                && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
            if (params.getReadRows() > 0 && readRow > params.getReadRows()) {
                break;
            }
            row = rows.next();
            // Fix row
            if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) {
                break;
            }
            /* ?? */
            if (row.getLastCellNum() < 0) {
                continue;
            }
            if (isMap && object != null) {
                ((Map) object).put("excelRowNum", row.getRowNum());
            }
            errorMsg = new StringBuilder();
            // ???,?,?
            // keyIndex ??,??
            if (params.getKeyIndex() != null
                    && (row.getCell(params.getKeyIndex()) == null
                            || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))
                    && object != null) {
                for (ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                }
            } else {
                object = PoiPublicUtil.createObject(pojoClass, targetId);
                try {
                    Set<Integer> keys = titlemap.keySet();
                    for (Integer cn : keys) {
                        Cell cell = row.getCell(cn);
                        String titleString = (String) titlemap.get(cn);
                        if (excelParams.containsKey(titleString) || isMap) {
                            if (excelParams.get(titleString) != null && excelParams.get(titleString)
                                    .getType() == BaseEntityTypeConstants.IMAGE_TYPE) {
                                picId = row.getRowNum() + "_" + cn;
                                saveImage(object, picId, excelParams, titleString, pictures, params);
                            } else {
                                try {
                                    saveFieldValue(params, object, cell, excelParams, 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());
                                    }
                                }
                            }
                        }
                    }
                    //for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {

                    //}
                    if (object instanceof IExcelDataModel) {
                        ((IExcelDataModel) object).setRowNum(row.getRowNum());
                    }
                    for (ExcelCollectionParams param : excelCollection) {
                        addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                    }
                    if (verifyingDataValidity(object, row, params, isMap, errorMsg)) {
                        collection.add(object);
                    } else {
                        failCollection.add(object);
                    }
                } catch (ExcelImportException e) {
                    LOGGER.error("excel import error , row num:{},obj:{}", readRow,
                            ReflectionToStringBuilder.toString(object));
                    if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                        throw new ExcelImportException(e.getType(), e);
                    }
                } catch (Exception e) {
                    LOGGER.error("excel import error , row num:{},obj:{}", readRow,
                            ReflectionToStringBuilder.toString(object));
                    throw new RuntimeException(e);
                }
            }
            readRow++;
        }
    }
    return collection;
}

---

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

License:Apache License

private void getSingleCellValueForRow(ExcelImportResult result, Row row, ImportParams params) {
    for (int j = row.getFirstCellNum(), le = row.getLastCellNum(); j < le; j++) {
        String text = PoiCellUtil.getCellValue(row.getCell(j));
        if (StringUtils.isNoneBlank(text) && text.endsWith(params.getKeyMark())) {
            if (result.getMap().containsKey(text)) {
                if (result.getMap().get(text) instanceof String) {
                    List<String> list = new ArrayList<String>();
                    list.add((String) result.getMap().get(text));
                    result.getMap().put(text, list);
                }//  ww  w .  j a v  a  2  s  .  c  o  m
                ((List) result.getMap().get(text)).add(PoiCellUtil.getCellValue(row.getCell(++j)));
            } else {
                result.getMap().put(text, PoiCellUtil.getCellValue(row.getCell(++j)));
            }

        }

    }
}

---

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

License:Apache License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * /*w  ww  .ja  va2s  .c om*/
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param columnToDelete
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null) {
            continue;
        }

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn) {
            maxColumn = lastColumn;
        }

        if (lastColumn < columnToDelete) {
            continue;
        }

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null) {
                row.removeCell(oldCell);
            }

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = columnToDelete; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

---

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

License:Apache License

/**
 *  ? Cells/* www  .ja va 2 s .c  om*/
 *
 * @param patriarch
 * @param index
 * @param t
 * @param excelParams
 * @param sheet
 * @param workbook
 * @param rowHeight
 * @return
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet,
        Workbook workbook, short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    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 listC = 0;
            if (list != null && list.size() > 0) {
                for (Object obj : list) {
                    createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
                    listC++;
                }
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == BaseEntityTypeConstants.StringType) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                        index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) {
                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(dataHanlder.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            } else {
                createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t);
            }
        }
    }
    // ????
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}

---

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

License:Apache License

/**
 * List??Cells/*from w ww.  j a v a2  s . c  o m*/
 *
 * @param patriarch
 * @param index
 * @param cellNum
 * @param obj
 * @param excelParams
 * @param sheet
 * @param workbook
 * @throws Exception
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
        List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook) throws Exception {
    ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        row.setHeight(getRowHeight(excelParams));
    } else {
        row = sheet.getRow(index);
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == BaseEntityTypeConstants.StringType) {
            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(dataHanlder.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) {
            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(dataHanlder.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else {
            createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
        }
    }
}

---

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

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    deleteCell(sheet, map);/*from   w  w  w  . ja va2  s  . c o  m*/
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHanlder = new TemplateSumHanlder(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);
            }
        }
    }

    //??
    hanlderSumCell(sheet);
}

---

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

License:Apache License

/**
 * ,??/* w w w .  j a  v  a2s  .  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() == 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

/**
 * ,??//  ww  w  .  j  a v 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() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_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())) {
                        PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

---

From source file:cn.bzvs.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??/*www  .  java2 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;//cell??
        row.setHeight(columns.get(0 * colspan).getHeight());
        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(columns.get(i).getCellStyle());
                ci = ci + columns.get(i).getColspan();
                continue;
            }
            String val = 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);
                val = eval(tempStr, map).toString();
            }
            if (isNumber && StringUtils.isNotEmpty(val)) {
                row.getCell(ci).setCellValue(Double.parseDouble(val));
                row.getCell(ci).setCellType(Cell.CELL_TYPE_NUMERIC);
            } else {
                try {
                    row.getCell(ci).setCellValue(val);
                } catch (Exception e) {
                    e.getMessage();
                }
            }
            row.getCell(ci).setCellStyle(columns.get(i).getCellStyle());
            //??
            if (params.isNeedSum()) {
                templateSumHanlder.addValueOfKey(params.getName(), val);
            }
            //??,?????
            setMergedRegionStyle(row, ci, columns.get(i));
            //??
            if ((params.getRowspan() != 1 || params.getColspan() != 1)
                    && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)) {
                row.getSheet().addMergedRegion(new CellRangeAddress(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.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ???//  www . j  a  va2 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++) {
        row.getCell(ci + i).setCellStyle(params.getCellStyle());
    }
    for (int i = 1; i < params.getRowspan(); i++) {
        for (int j = 0; j < params.getColspan(); j++) {
            row.getCell(ci + j).setCellStyle(params.getCellStyle());
        }
    }
}

---

• «
• 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
• »