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