How to read an Excel 2007 (.xlsx) file using Apache POI

As we know the main difference with Java JXL which does not support the Excel 2007 ".xlsx" file format. It only supports the old BIFF (binary) ".xls" format. Where as Apache POI supports both xls and xlsx file formats.

To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Will get the workbook instance for XLS and takes excel file to read

XSSFWorkbook workbook = new XSSFWorkbook(XlsxFileToRead);

// We will pass the name / index of the sheet which starts from '0'.

XSSFSheet sheet = workbook.getSheet("Sheet1");
	or
XSSFSheet 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 same example which was used to read xls file and the read the xlsx file content.
Apache POI 2003

The above xlsx 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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadXlsx {
	
	public void readXLSXFile(String fileName) {
		InputStream XlsxFileToRead = null;
		XSSFWorkbook workbook = null;
		try {
			XlsxFileToRead = new FileInputStream(fileName);
			
			//Getting the workbook instance for xlsx file
			workbook = new XSSFWorkbook(XlsxFileToRead);
		} 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'.
		XSSFSheet sheet = workbook.getSheet("Sheet1");
		XSSFRow row;
		XSSFCell cell;
		
		//Iterating all the rows in the sheet
		Iterator rows = sheet.rowIterator();

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

			while (cells.hasNext()) {
				cell = (XSSFCell) cells.next();

				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					System.out.print(cell.getStringCellValue() + " ");
				} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
					System.out.print(cell.getNumericCellValue() + " ");
				} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
					System.out.print(cell.getBooleanCellValue() + " ");

				} else { // //Here if require, we can also add below methods to
							// read the cell content
							// XSSFCell.CELL_TYPE_BLANK
							// XSSFCell.CELL_TYPE_FORMULA
							// XSSFCell.CELL_TYPE_ERROR
				}
			}
			System.out.println();
			try {
				XlsxFileToRead.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) {
		ReadXlsx readXlsx = new ReadXlsx();
		readXlsx.readXLSXFile("C:/testXlsxRead.xlsx");	
	}

}

Make sure to add all the required jars to the class path. Below are the exception you generally observe if we don't add jars.

You may see below exception if you don't add 'xmlbeans-2.x.x'
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject

And you may also come across the below exception if you don't add jar 'poi-ooxml-schemas-3.x-xxx'
Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet

The below is the other exception that you get, if we don't add jar 'dom4j-1.x.x '
Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException

After adding all the jars the program will get executed successfully.

Output of the above program should look like below:

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

Hope the article helps you, Please feel free to comment, if you have any questions or issues with the above code.

Excel API: 

Add new comment

Image CAPTCHA