Posted in Others

Apache POI : Read excel

Maven Dependencies:
1- poi-ooxml 4.1.2
2- commons-compress 1.20
Note: ooxml 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, j = 0; i < totalRows; i++) {
			while (j < totalColums) {
				sheet.getRow(i + 1).getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
				obj[i][j] = sheet.getRow(i + 1).getCell(j).toString()
						.trim();
                                System.out.println(obj[i][j]);
				j++;
			}
			j = 0;
		}
		//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 comment