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.
3. Create a XLSX file with rows and columns
4. Write the logic to read the data from XLSX.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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
3. Write the logic to read the data from XLSX.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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(); } } |
Conclusion:
Reading the XLSX is easy using the Apache POI and Java API.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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;
Output