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

Introduction

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

Prototype

int getFirstRowNum();

Source Link

Document

Gets the first row on the sheet.

Usage

From source file:net.sf.excelutils.ExcelUtils.java

License:Apache License

/**
 * parse Excel Template File//  w  w w .  j a va  2s  . c  o  m
 * 
 * @param context datasource
 * @param sheet Workbook sheet
 */
public static void parseSheet(Object context, Workbook wb, Sheet sheet) throws ExcelException {
    try {
        ExcelParser.parse(context, wb, sheet, sheet.getFirstRowNum(), sheet.getLastRowNum());
        try {
            // remove the last #page
            int breaks[] = sheet.getRowBreaks();
            if (breaks.length > 0) {
                sheet.removeRowBreak(breaks[breaks.length - 1]);
            }
        } catch (Exception ne) {
        }
    } catch (Exception e) {
        throw new ExcelException("parseSheet error: ", e);
    } finally {
        ExcelUtils.context.set(null);
    }
}

---

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

/**
 * Set Print Area/*from ww w  . jav  a 2 s. c  om*/
 *
 * @param wb
 * @param sheetIndex
 */
public static void setPrintArea(Workbook wb, int sheetIndex) {
    // sheet
    Sheet sheet = wb.getSheetAt(sheetIndex);
    if (null != sheet) {
        // #endRow
        Row endRow = sheet.getRow(sheet.getLastRowNum());
        if (null != endRow) {
            Cell cell = WorkbookUtils.getCell(endRow, 0);
            String cellStr = cell.getStringCellValue();
            cellStr = cellStr == null ? "" : cellStr.trim();
            if (cellStr.startsWith(EndRowTag.KEY_ENDROW)) {
                // search #endColumn
                int endColumn = endRow.getLastCellNum();
                for (int i = endRow.getLastCellNum(); i >= endRow.getFirstCellNum(); i--) {
                    Cell endCell = WorkbookUtils.getCell(endRow, i);
                    String endCellStr = endCell.getStringCellValue();
                    endCellStr = endCellStr == null ? "" : endCellStr.trim();
                    if (endCellStr.startsWith(EndRowTag.KEY_ENDCOLUMN)) {
                        endColumn = i;
                        break;
                    }
                }
                wb.setPrintArea(sheetIndex, endRow.getFirstCellNum(), endColumn, sheet.getFirstRowNum(),
                        sheet.getLastRowNum() - 1);
                sheet.removeRow(endRow);
            }
        }
    }
}

---

From source file:nu.mine.kino.projects.utils.POITest.java

License:Open Source License

public static void main(String[] args) {
    Workbook workbook = null;/*from   ww w .j  a  va2s.co m*/
    FileInputStream in = null;
    try {
        in = new FileInputStream(new java.io.File("project_management_tools.xls"));
        workbook = WorkbookFactory.create(in);
        Sheet sheet = workbook.getSheetAt(0);
        // int[] rowBreaks = sheet.getRowBreaks();

        Row row = sheet.getRow(25);
        Cell cell = row.getCell(24);
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getStringCellValue());
            break;
        default:
            System.out.println("cellType=" + cell.getCellType());
            break;
        }
        System.out.println("value: " + cell);
        System.out.println(sheet.getRow(46).getCell(4));

        final int rowMin = sheet.getFirstRowNum();
        System.out.println(rowMin);
        final int rowMax = sheet.getLastRowNum();
        System.out.println(rowMax);

    } catch (InvalidFormatException e) {
        // TODO ?? catch u?bN
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        // TODO ?? catch u?bN
        e.printStackTrace();
    } catch (IOException e) {
        // TODO ?? catch u?bN
        e.printStackTrace();
    } finally {
        if (in != null)
            try {
                in.close();
            } catch (IOException e) {
                // TODO ?? catch u?bN
                e.printStackTrace();
            }
    }

}

---

From source file:org.apache.any23.plugin.officescraper.ExcelExtractor.java

License:Apache License

private void writeSheetMetadata(URI sheetURI, Sheet sheet, ExtractionResult er) {
    final String sheetName = sheet.getSheetName();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    er.writeTriple(sheetURI, excel.sheetName, RDFUtils.literal(sheetName));
    er.writeTriple(sheetURI, excel.firstRow, RDFUtils.literal(firstRowNum));
    er.writeTriple(sheetURI, excel.lastRow, RDFUtils.literal(lastRowNum));
}

---

From source file:org.bbreak.excella.core.SheetParser.java

License:Open Source License

/**
 * ??/*ww w .ja va 2s .  c o  m*/
 * 
 * @param sheet ?
 * @param data BookController?parseBook(), parseSheet()?
 *              SheetParser?parseSheet?????
 *              TagParser??????
 * @return ??
 * @throws ParseException ??????Throw?
 */
public SheetData parseSheet(Sheet sheet, Object data) throws ParseException {
    // ??
    String sheetName = PoiUtil.getSheetName(sheet);
    SheetData sheetData = new SheetData(sheetName);

    int firstRowNum = sheet.getFirstRowNum();

    // ?
    for (int rowCnt = firstRowNum; rowCnt <= sheet.getLastRowNum(); rowCnt++) {
        // ??
        Row row = sheet.getRow(rowCnt);
        if (row == null) {
            continue;
        }
        if (row != null) {
            for (int columnIdx = 0; columnIdx < row.getLastCellNum(); columnIdx++) {
                // ??
                Cell cell = row.getCell(columnIdx);
                if (cell == null) {
                    continue;
                }
                if (parseCell(sheet, data, sheetData, cell, row, columnIdx)) {
                    // ?
                    return sheetData;
                }
            }
        }
    }

    return sheetData;
}

---

From source file:org.bbreak.excella.reports.listener.BreakAdapter.java

License:Open Source License

/**
 * @see org.bbreak.excella.reports.listener.ReportProcessAdaptor#postParse(org.apache.poi.ss.usermodel.Sheet, org.bbreak.excella.core.SheetParser, org.bbreak.excella.core.SheetData)
 *//*  w  w  w.  j  av  a 2 s .c om*/
@Override
public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException {
    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            parseRow(sheet, sheetParser, sheetData, row, rowIndex);
        }
    }
}

---

From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java

License:Open Source License

@Override
public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {

        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            int firstColNum = row.getFirstCellNum();
            int lastColNum = row.getLastCellNum() - 1;
            boolean isRowFlag = false;

            for (int colIndex = firstColNum; colIndex <= lastColNum; colIndex++) {
                Cell cell = row.getCell(colIndex);
                if (cell != null) {
                    if (cell.getCellTypeEnum() == CellType.STRING
                            && cell.getStringCellValue().contains(RemoveParamParser.DEFAULT_TAG)) {
                        // ??
                        String[] paramArray = getStrParam(sheet, rowIndex, colIndex);

                        // ??
                        String removeUnit = paramArray[0];
                        // ??
                        row.removeCell(cell);

                        // ????
                        if (removeUnit.equals("") || removeUnit.equals(ROW)) {
                            removeRegion(sheet, rowIndex, -1);
                            removeControlRow(sheet, rowIndex);
                            isRowFlag = true;
                            break;
                        } else if (removeUnit.equals(CELL) || removeUnit.equals(COLUMN)) {
                            // ???????
                            removeCellOrCol(paramArray, removeUnit, sheet, row, cell, rowIndex, colIndex);
                        }//from   ww  w .  j av a 2 s  .  co  m
                        lastColNum = row.getLastCellNum() - 1;
                        colIndex--;
                    }
                    // ??
                    if (isControlRow(sheet, sheetParser, row, cell)) {
                        removeControlRow(sheet, rowIndex);
                        isRowFlag = true;
                        break;
                    }
                }
            }
            // ???
            if (isRowFlag) {
                lastRowNum = sheet.getLastRowNum();
                rowIndex--;
            }
        }
    }
}

---

From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD
    // by/*from  ww  w.  ja  v a 2 s  . com*/
    // "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

---

From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD
    // by/*from w w w.  ja v  a 2 s  .c  om*/
    // "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

---

From source file:org.codelabor.example.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);/*from  w  ww . j  a va2 s .  com*/
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• »