Apache POI : Read excel

Maven Dependencies:
1- poi-ooxml 4.1.2
2- commons-compress 1.20
Note: 00xml comes with commons-compress 1.19, but it has compatibility issues, throws an error. So add it explicitly to 1.20


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Arrays;

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

public class excelReader{

//Option 1: Creating path of file
String stringPath="./src/main/java/com/test/TestData.xlsx";

//Option2: Using FileInputStream
File file = new File("./src/main/java/com/test/TestData.xlsx");
		FileInputStream fisPath = new FileInputStream(file);
		
//It takes either String path or InputStream path
// inputStream requires more memory than File so ideal way to user File path
		XSSFWorkbook workbook = new XSSFWorkbook(stringPath);
		
		//Optiona 1:Get Sheet by Name
		XSSFSheet sheet = workbook.getSheet("Sheet1");
		
		//Option 2 : get sheet by index
		XSSFSheet sheet2 = workbook.getSheetAt(0);
		
		//Option1: by getting last row num
		int rowCount = sheet.getLastRowNum();
		
		//Option 2: By getting actual row number
		int rowCount2 = sheet.getPhysicalNumberOfRows();
		
		System.out.println("Total Rows option1: "+rowCount);
		System.out.println("Total Rows option2: "+rowCount2);
		
		int totalColumns = sheet.getRow(0).getPhysicalNumberOfCells();
		int totalColumns2 = sheet.getRow(0).getLastCellNum();
		
		System.out.println("Total Columns option1: "+totalColumns);
		System.out.println("Total Columns option2: "+totalColumns2);
		
		// Read data from excel and store the same in the Object Array.
		Object obj[][] = new Object[rowCount][totalColumns];
		for (int i = 0; i < rowCount; i++) {
			obj[i][0] = sheet.getRow(i + 1).getCell(0).toString();
			obj[i][1] = sheet.getRow(i + 1).getCell(1).toString();
			
		}
		//To print 2D array
		System.out.println("Excel Data : "+Arrays.deepToString(obj));
				
		//Closing Workbook is a good practice to avoid file corruption and memory optimization
		workbook.close();

}

Leave a Reply