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:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ?/*from   w w  w.j av  a2  s.  c  om*/
 * @param rowIndex
 * @param colIndex
 * @return
 */
protected boolean cellExist(int rowIndex, int colIndex) {
    Sheet sheet = excelSheet.getSheet();
    if (rowIndex < 0 || rowIndex > sheet.getLastRowNum())
        return false;
    Row row = sheet.getRow(rowIndex);
    Cell cell = row.getCell(colIndex);
    return cell != null;
}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

@SuppressWarnings({ "unused", "deprecation" })
public void addItemsToSheet() {
    // ?//w w w  .  j  av a  2  s  . c o m
    Class clazz = excelSheet.getBeanClass();
    if (clazz == null) {
        return;
    }
    List<String> fieldnames = null;
    fieldnames = excelSheet.getFieldList();
    if (fieldnames == null)
        return;
    List<?> items = null;
    items = excelSheet.getDataList();
    if (items == null)
        return;
    Sheet sheet = excelSheet.sheet;
    Row row = null;
    Cell cell = null;
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 0; i < items.size(); i++) {
        row = sheet.createRow(lastRowNum + 1 + i);
        for (int j = 0; j < fieldnames.size(); j++) {
            try {
                Field field = clazz.getDeclaredField(fieldnames.get(j));
                field.setAccessible(true);
                cell = row.createCell(j);
                Class fieldType = field.getType();
                //   cell.setCellType(Cell.);
                Object value = field.get(items.get(i));
                setCellValue(cell, value);

            } catch (Exception ex) {
                if (logger.isEnabledFor(Priority.ERROR)) {
                    logger.error("" + (i + 1) + "" + j + "", ex);
                }
            }
        }
    }

}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ??//from w  ww.j  a v a 2s .  c  om
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public boolean setColumnStyle(int columnIndex, int rowFirstIndex, int rowLastIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex > rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); //   ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetReader.java

License:Apache License

private List read() {
    List list = null;//www.j a  v  a 2s  .  c o m
    int firstIndex = excelSheet.getFirstDataRowIndex();
    Sheet sheet = excelSheet.getSheet();
    Row row = null;
    int lastRowIndex = sheet.getLastRowNum();

    if (firstIndex > lastRowIndex)
        return null;
    list = new ArrayList();

    for (int i = firstIndex; i <= lastRowIndex; i++) {
        row = excelSheet.sheet.getRow(i);
        Object obj = getBean(excelSheet.getBeanClass(), row, excelSheet.getFieldList());
        list.add(obj);
    }
    return list;
}

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java./*from  w ww.  ja  va2s . com*/
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, List<String> titleList, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);

    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(0);
    int colNum = titleRow.getLastCellNum();

    for (int i = 2; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();

        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titleList.get(j));
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//from  w  ww  .j av  a  2s.  co m
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, int titlerow, int datarow, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);
    PropertieDescription rowidField = classInfo.getPropertyDescriptor("rowid");
    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(titlerow);

    int colNum = titleRow.getLastCellNum();
    String[] titles = new String[colNum];
    for (int i = 0; i < colNum; i++) {
        titles[i] = titleRow.getCell(i).getStringCellValue().trim();
    }

    for (int i = datarow; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();
        if (rowidField != null)
            rowidField.setValue(retObject, i);
        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titles[j]);
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException {

    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row row = sheet.getRow(offsetLine);//from  ww  w .  ja  v a  2  s  .  c o m
    List<T> list = new ArrayList<>();
    Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
    if (maps == null || maps.size() <= 0)
        throw new Excel4jReadException(
                "The Excel format to read is not correct, and check to see if the appropriate rows are set");
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();

    for (int i = offsetLine + 1; i <= maxLine; i++) {
        row = sheet.getRow(i);
        if (null == row)
            continue;
        T obj;
        try {
            obj = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new Excel4JException(e);
        }
        for (Cell cell : row) {
            int ci = cell.getColumnIndex();
            ExcelHeader header = maps.get(ci);
            if (null == header)
                continue;
            String val = Utils.getCellValue(cell);
            Object value;
            String filed = header.getFiled();
            // ??
            if (null != header.getReadConverter()
                    && header.getReadConverter().getClass() != DefaultConvertible.class) {
                value = header.getReadConverter().execRead(val);
            } else {
                // ?
                value = Utils.str2TargetClass(val, header.getFiledClazz());
            }
            Utils.copyProperty(obj, filed, value);
        }
        list.add(obj);
    }
    return list;
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine,
        int sheetIndex) {

    List<List<String>> list = new ArrayList<>();
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();/*w w  w. java  2 s.co m*/
    for (int i = offsetLine; i <= maxLine; i++) {
        List<String> rows = new ArrayList<>();
        Row row = sheet.getRow(i);
        if (null == row)
            continue;
        for (Cell cell : row) {
            String val = Utils.getCellValue(cell);
            rows.add(val);
        }
        list.add(rows);
    }
    return list;
}

From source file:com.github.cutstock.utils.ProfileUtils.java

License:Apache License

public static Profiles parseFile(String filePath) {
    Workbook workbook = null;/*ww w.ja  va  2 s  . c  om*/
    Sheet sheet = null;
    // CutStockPlugin.getLogger().log(new Status(0,
    // CutStockPlugin.PLUGIN_ID, filePath));
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        if (filePath.toLowerCase().endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }

    } catch (IOException e) {
        MessageDialog.openError(null, "ERROR",
                "?EXCEL,?????Excel");
    } finally {
        try {
            if (is != null) {
                is.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    sheet = workbook.getSheetAt(0);
    int sheetRowNum = sheet.getLastRowNum();
    Profiles proflies = new Profiles();
    for (int i = 1; i <= sheetRowNum; i++) {
        Row currentRow = sheet.getRow(i);
        if (currentRow != null) {
            Cell cell = currentRow.getCell(ColumnType.PROFILE_NAME);
            String name = (String) getCellValue(cell);
            if (StringUtil.Empty(name)) {
                break;
            }
            cell = currentRow.getCell(ColumnType.PROFILE_CODE_DATA);
            String codeData = (String) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_COLOR);
            String color = (String) getCellValue(cell);

            cell = currentRow.getCell(ColumnType.PROFILE_WIDTH);
            double width = (Double) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_AMOUNT);
            int amount = ((Double) getCellValue(cell)).intValue();

            proflies.add(name, codeData, color, new BigDecimal(width), amount);
        }
    }
    return proflies;
}

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * Format a table of worksheet//w w w  .j a v  a2s  .  c  om
 *
 * @param sheet
 *            Name of sheet
 * @param withHeader
 *            <code>true</code> for create auto filter and freeze pane in
 *            first row, otherwise <code>false</code>
 */
public void setAutoSizeColumns(Sheet sheet, boolean withHeader) {
    if (sheet.getLastRowNum() > 0) {
        if (withHeader) {
            int x = sheet.getRow(sheet.getLastRowNum()).getLastCellNum();
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, x - 1);
            sheet.setAutoFilter(range);
            sheet.createFreezePane(0, 1);
        }
        // auto-sizing columns
        if (columnWidth.containsKey(sheet.getSheetName())) {
            Map<Integer, Integer> width = columnWidth.get(sheet.getSheetName());
            for (Map.Entry<Integer, Integer> entry : width.entrySet()) {
                sheet.setColumnWidth(entry.getKey(), entry.getValue());
            }
        }
    }
}