1. Apache POI library v3.8 or above (download)

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();

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