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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:ch.swissbytes.Service.business.Spreadsheet.ToCSV.java

License:Apache License

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file./*  ww  w  . j  a  v a  2s  .  c om*/
 *
 * @param row An instance of either the HSSFRow or XSSFRow classes that
 *            encapsulates information about a row of cells recovered from
 *            an Excel workbook.
 */
private void rowToCSV(Row row) {
    Cell cell = null;
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the csvLine ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                    csvLine.add(this.formatter.formatCellValue(cell));
                } else {
                    csvLine.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }
        // Make a note of the index number of the right most cell. This value
        // will later be used to ensure that the matrix of data in the CSV file
        // is square.
        if (lastCellNum > this.maxRowWidth) {
            this.maxRowWidth = lastCellNum;
        }
    }
    this.csvData.add(csvLine);
}

---

From source file:cherry.goods.excel.ExcelReader.java

License:Apache License

/**
 * ?<br />//  w  w w .j  a  va 2 s .c om
 * ???1(1)
 * 
 * @return 1(1)
 */
public String[] read() {
    if (!rowIterator.hasNext()) {
        return null;
    }
    Row row = rowIterator.next();
    int lastCellNum = row.getLastCellNum();
    if (lastCellNum < 0) {
        return new String[0];
    }
    String[] record = new String[lastCellNum];
    for (Cell cell : row) {
        record[cell.getColumnIndex()] = getCellValueAsString(cell);
    }
    return record;
}

---

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;
    }/*  w  w w. j av a2 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  w w.  j a  va2 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 ww  . j a va2s.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

/**
 * ??/*  w  ww. j  a  va2s  .  co 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

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  ww.j  a  v  a 2  s .  com*/
    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

/**
 * ??//from  w  w  w . jav  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.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);
                }/*w  ww .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.
 * /*  ww w.  j ava2 s . com*/
 * 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));
    }
}

---

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