How to read excel file, omitting the first two lines

Question

I have an excel file with 111 lines. I need to omit the first two lines of a worksheet and then read the file using java and POI.

Answer 1

You need to skip the first two lines using rownum() . Here is a sample code

 HSSFWorkbook workBook = new HSSFWorkbook (fileSystem); HSSFSheet sheet = workBook.getSheetAt (0); Iterator<HSSFRow> rows = sheet.rowIterator (); while (rows.hasNext ()) { HSSFRow row = rows.next (); // display row number in the console. System.out.println ("Row No.: " + row.getRowNum ()); if(row.getRowNum()==0 || row.getRowNum()==1){ continue; //just skip the rows if row number is 0 or 1 } } 

Here is a complete example

 import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.Iterator; public class POIExcelReader { /** Creates a new instance of POIExcelReader */ public POIExcelReader () {} @SuppressWarnings ("unchecked") public void displayFromExcel (String xlsPath) { InputStream inputStream = null; try { inputStream = new FileInputStream (xlsPath); } catch (FileNotFoundException e) { System.out.println ("File not found in the specified path."); e.printStackTrace (); } POIFSFileSystem fileSystem = null; try { fileSystem = new POIFSFileSystem (inputStream); HSSFWorkbook workBook = new HSSFWorkbook (fileSystem); HSSFSheet sheet = workBook.getSheetAt (0); Iterator<HSSFRow> rows = sheet.rowIterator (); while (rows.hasNext ()) { HSSFRow row = rows.next (); if(row.getRowNum()==0 || row.getRowNum()==1){ continue; //just skip the rows if row number is 0 or 1 } // once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator (); while (cells.hasNext ()) { HSSFCell cell = cells.next (); System.out.println ("Cell No.: " + cell.getCellNum ()); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType ()) { case HSSFCell.CELL_TYPE_NUMERIC : { // cell type numeric. System.out.println ("Numeric value: " + cell.getNumericCellValue ()); break; } case HSSFCell.CELL_TYPE_STRING : { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue (); System.out.println ("String value: " + richTextString.getString ()); break; } default : { // types other than String and Numeric. System.out.println ("Type not supported."); break; } } } } } catch (IOException e) { e.printStackTrace (); } } public static void main (String[] args) { POIExcelReader poiExample = new POIExcelReader (); String xlsPath = "c://test//test.xls"; poiExample.displayFromExcel (xlsPath); } } 

Answer 2

Apache POI provides two ways to access rows and cells in an Excel file. One of them is an iterator that gives you all the records, and the other - by index. (The POI will also tell you the start / end rows / columns). An iterator is often easier to use, but both are equally fast.

If you have special string selection requirements, I would suggest using the latter. Your code will look something like this:

 int FIRST_ROW_TO_GET = 2; // 0 based Sheet s = wb.getSheetAt(0); for (int i = FIRST_ROW_TO_GET; i < s.getLastRowNum(); i++) { Row row = s.getRow(i); if (row == null) { // The whole row is blank } else { for (int cn=row.getFirstCellNum(); cn<row.getLastCellNum(); cn++) { Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL); if (c == null) { // The cell is empty } else { // Process the cell } } } }