In the previous article we have seen Introduction about POI and how to Configure POI. Now in this article we will see how to read xls file.

We can also read xls files using JExcel library. Refer this link to 'How to read Excel file using JExcel'

Using Apache POI, To read an Excel '97-2007' (.xls) file format, we need to use HSSF (Horrible Spreadsheet Format). Similarly to work with Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format)

We will use the below classes to work with excel files (.xls) formats , And we need to import the below statements to work with all the above classes

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

// We will pass the excel file
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);

// We will pass the name / index of the sheet which starts from '0'.
HSSFSheet sheet = workbook.getSheet("Sheet1");
or
HSSFSheet sheet=wb.getSheetAt(0);

//We will iterate all the rows in the sheet
Iterator rows = sheet.rowIterator();

//We will be iterating all the cells of the current row
Iterator cells = row.cellIterator();

Now lets us take an example and the read the excel file content which looks like below

The above excel file has 2 columns 'Username' and 'Password' with 5 rows . We should be able to read the content using below example.

package com.read;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ReadXls {

	public void readXLSFile(String fileName) {
		InputStream ExcelFileToRead = null;
		HSSFWorkbook workbook = null;
		try {
			ExcelFileToRead = new FileInputStream(fileName);
			
			//Getting the workbook instance for xls file
			workbook = new HSSFWorkbook(ExcelFileToRead);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		//getting the first sheet from the workbook using sheet name. 
		// We can also pass the index of the sheet which starts from '0'.
		HSSFSheet sheet = workbook.getSheet("Sheet1");
		HSSFRow row;
		HSSFCell cell;
		
		//Iterating all the rows in the sheet
		Iterator rows = sheet.rowIterator();

		while (rows.hasNext()) {
			row = (HSSFRow) rows.next();
			
			//Iterating all the cells of the current row
			Iterator cells = row.cellIterator();

			while (cells.hasNext()) {
				cell = (HSSFCell) cells.next();
				if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
					System.out.print(cell.getStringCellValue() + " ");
				} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
					System.out.print(cell.getNumericCellValue() + " ");
				} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
					System.out.print(cell.getBooleanCellValue() + " ");
				} else { // //Here if require, we can also add below methods to
							// read the cell content
							// HSSFCell.CELL_TYPE_BLANK
							// HSSFCell.CELL_TYPE_FORMULA
							// HSSFCell.CELL_TYPE_ERROR
				}

			}
			System.out.println();
			try {
				ExcelFileToRead.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		ReadXls readXls = new ReadXls();
		readXls.readXLSFile("C:/testRead.xls");
	}
}

Output of the above should look like below:

Username Password 
test1 1234.0 
test2 test123 
test3 test456 
test4 test789 
test5 test012