How to read excel file omitting first two rows

Question

I have an excel file of 111 rows. I need to omit first two rows of the sheet and then read the file using java and POI.

Answer 1

You have to skip first two rows using rownum().Here is the 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 the 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 the rows and cells in an Excel file. One is an iterator that gives you all the entries, the other is to loop up by index. (POI will also tell you the start/end rows/columns). The iterator is often simpler to use, but both are equally as fast.

If you have specific requirements on rows to fetch, I'd suggest you use the latter. Your code would want to be something like:

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
         }
      }
   }
}