Here is my excel file
I will read excel from top to down, and will start to read from row 4 and write data into database.
Here is my code snippet:
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 | // file extension validation String extension = StringUtils.upperCase(FilenameUtils.getExtension(fileName)); Integer startingRow = 3; Workbook workbook = null; Sheet sheet = null; if ("XLS".equals(extension)) { // Get the workbook instance for XLS file workbook = new HSSFWorkbook(inputStream); } else if ("XLSX".equals(extension)) { // Get the workbook instance for XLSX file workbook = new XSSFWorkbook(inputStream); } else { String errorMsg = "只接受副檔名為xls與xlsx的檔案, fileName = " + fileName; throw new RuntimeException(errorMsg); } // Get the specific sheet from the workbook sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); int rowCount = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowCount < startingRow) { //do nothing } else { //start to read and write data into database } rowCount++; } |
But, apply this approach it will read from row 5. Because of the first row is null, Apache POI will skip empty row.
Solution
Utilize for-loop instead of while-loop, then it will not skip empty row:
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 | // file extension validation String extension = StringUtils.upperCase(FilenameUtils.getExtension(fileName)); Integer startingRow = 3; Workbook workbook = null; Sheet sheet = null; if ("XLS".equals(extension)) { // Get the workbook instance for XLS file workbook = new HSSFWorkbook(inputStream); } else if ("XLSX".equals(extension)) { // Get the workbook instance for XLSX file workbook = new XSSFWorkbook(inputStream); } else { String errorMsg = "只接受副檔名為xls與xlsx的檔案, fileName = " + fileName; throw new RuntimeException(errorMsg); } // Get the specific sheet from the workbook sheet = workbook.getSheetAt(0); // get last row number int rowEnd = sheet.getLastRowNum(); for (int rowNum = startingRow; rowNum <= rowEnd; rowNum++) { Row row = sheet.getRow(rowNum); } |
Reference
[1] http://poi.apache.org/spreadsheet/quick-guide.html#Iterator
