Read XLSX using Apache POI

Apache POI (Poor Obfuscation Implementation File System) is the Java API for Microsoft Documents such as XLS,XLSX,DOC and PPT as well.
HSSF (Horrible Spreadsheet Format)
Read or write an Excel file format – XLS
XSSF (XML Spreadhsheet Format)
Read or write an Excel file format – XLSX
To download the Apache POI Library access the below link -> https://poi.apache.org/download.html
Download the Binary Distribution

Linux : poi-bin-xx.tar.gz
Windows : poi-bin-xx.zip

Reading the XLSX in JAVA project
1. Create JAVA Project
2. Add the dependencies of the Apache POI to the Build Path of the project.

Java Project – Build Path

3. Create a XLSX file with rows and columns

Xlsx File

4. Write the logic to read the data from XLSX.


package total.qa.apache.poi.readxlsx;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadXLSX {
	
	public static void main(String args[]) throws IOException
	{
		File f = new File("TestCaseData.xlsx");
		FileInputStream fis = new FileInputStream(f);
		XSSFWorkbook excelWorkbook = new XSSFWorkbook(fis);
		XSSFSheet excelSheet = excelWorkbook.getSheetAt(0);
		int rows = excelSheet.getPhysicalNumberOfRows();//3
		int cols = excelSheet.getRow(0).getPhysicalNumberOfCells();//2
		String data[][]= new String[rows][cols];
		XSSFCell cell;
		for(int i =0 ; i< rows;i++)
		{
			for(int j=0;j<cols;j++)
			{
				cell = excelSheet.getRow(i).getCell(j);
				String cellContents=cell.getStringCellValue();
				data[i][j] = cellContents;
				System.out.println(data[i][j]);
			}
		}
		fis.close();	
	}
}

package total.qa.apache.poi.readxlsx;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadXLSX {

public static void main(String args[]) throws IOException

{

File f = new File("TestCaseData.xlsx");

FileInputStream fis = new FileInputStream(f);

XSSFWorkbook excelWorkbook = new XSSFWorkbook(fis);

XSSFSheet excelSheet = excelWorkbook.getSheetAt(0);

int rows = excelSheet.getPhysicalNumberOfRows();//3

int cols = excelSheet.getRow(0).getPhysicalNumberOfCells();//2

String data[][]= new String[rows][cols];

XSSFCell cell;

for(int i =0 ; i< rows;i++)

{

for(int j=0;j<cols;j++)

{

cell = excelSheet.getRow(i).getCell(j);

String cellContents=cell.getStringCellValue();

data[i][j] = cellContents;

System.out.println(data[i][j]);

}

fis.close();

}

Reading the XLSX in maven project
1. Create MAVEN Project
2. Add the following dependencies in the MAVEN pom.xml
https://mvnrepository.com/artifact/org.apache.poi/poi
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml

Maven Dependency- Apache POI

3. Write the logic to read the data from XLSX.


package total.qa.apache.poi.readxlsx;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadXLSX {
	
	public static void main(String args[]) throws IOException
	{
		File f = new File("TestCaseData.xlsx");
		FileInputStream fis = new FileInputStream(f);
		XSSFWorkbook excelWorkbook = new XSSFWorkbook(fis);
		XSSFSheet excelSheet = excelWorkbook.getSheetAt(0);
		int rows = excelSheet.getPhysicalNumberOfRows();//3
		int cols = excelSheet.getRow(0).getPhysicalNumberOfCells();//2
		String data[][]= new String[rows][cols];
		XSSFCell cell;
		for(int i =0 ; i< rows;i++)
		{
			for(int j=0;j<cols;j++)
			{
				cell = excelSheet.getRow(i).getCell(j);
				String cellContents=cell.getStringCellValue();
				data[i][j] = cellContents;
				System.out.println(data[i][j]);
			}
		}
		fis.close();	
	}
}