Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

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;
    }/*  ww  w .ja v  a  2 s  .  c  o  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  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 2  s  .  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.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 a va 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

/**
 * ??Excel??/*from www  .  j  a  v  a  2 s .co m*/
 */
private void readSingleCell(ExcelImportResult result, Sheet sheet, ImportParams params) {
    if (result.getMap() == null) {
        result.setMap(new HashMap<String, Object>());
    }
    for (int i = 0; i < params.getTitleRows() + params.getHeadRows() + params.getStartRows(); i++) {
        getSingleCellValueForRow(result, sheet.getRow(i), params);
    }

    for (int i = sheet.getLastRowNum() - params.getLastOfInvalidRow(); i < sheet.getLastRowNum(); i++) {
        getSingleCellValueForRow(result, sheet.getRow(i), params);

    }
}

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  w  w .j  a  v  a 2s .  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));
    }
}

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

License:Apache License

/**
 * /*from w  w  w  .jav a  2s . c o  m*/
 *
 * @param styles
 * @param sheet
 */
public void addStatisticsRow(CellStyle styles, Sheet sheet) {
    if (statistics.size() > 0) {
        Row row = sheet.createRow(sheet.getLastRowNum() + 1);
        Set<Integer> keys = statistics.keySet();
        createStringCell(row, 0, "?", styles, null);
        for (Integer key : keys) {
            createStringCell(row, key, DOUBLE_FORMAT.format(statistics.get(key)), styles, null);
        }
        statistics.clear();
    }

}

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

License:Apache License

/**
 * Sheet ?,?? ,??//from w  w w.  j  a va2  s. c  o m
 *
 * @param sheet
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook)
        throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // ??
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // ??
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // ??
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //?,?
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(),
            getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // ??
    mergeCells(sheet, excelParams, titleHeight);
}

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  ww . j a va2  s.  c  om
    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

/**
 * ,??/*from   w  ww  .ja  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() == 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);
                }
            }
        }
    }
}