How to Read Data From Excel File Using Apache POI
In test automation, there are several kinds of frameworks to choose from to implement in your project depending on your needs. One of which is the Data-Driven Framework. In this framework, test data are stored in a single or combination of data sources like .csv, .xls .xlsx, database, straight from google sheets, and many more. With that, you should have a utility to extract or read your data from these data sources. For this writing, we will discuss how to read data from excel files particularly with that of .xlsx extension.
To read data from .xlsx file, we will be using poi-ooxml provided by Apache Poi. To add this to your maven project, check their maven repository. Below are the classes we will use, linked to their documentations.
- XSSFWorkbook
- XSSFSheet
- Row
- Cell
- CellType (This is an Enum)
- DataFormatter
Here is a simple method to read all the data in the excel sheet. Read all, meaning empty cells are included too.
public static String[][] getDataFromSheet(String workbookLocation, String workSheetName) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(System.getProperty("user.dir") + "/" + workbookLocation);
XSSFSheet workSheet = workbook.getSheet(workSheetName);
int noOfRows = workSheet.getLastRowNum() + 1;
int noOfColumns = workSheet.getRow(0).getLastCellNum();
String[][] dataTable = new String[noOfRows][noOfColumns];
for (int i = workSheet.getFirstRowNum(); i < workSheet.getLastRowNum() + 1; i++) {
Row row = workSheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
dataTable[i][j] = cell.getStringCellValue();
}
}
workbook.close();
return dataTable;
}
First, we created an XSSFWorkbook object and named it workbook. Passing to its constructor the path to the location of our .xlsx file. Note that the XSSFWorkbook constructor throws an IOException. The workbook object is the representation of our .xlsx file containing the excel sheet where we will be reading our data from.
XSSFWorkbook workbook = new XSSFWorkbook(System.getProperty("user.dir") + "/" + workbookLocation);Second, using the getSheet() method accessible via the workbook object, we passed the name of the excel sheet to get the XSSFSheet from our workbook and named it workSheet. The workSheet represent our excel sheet.
XSSFSheet workSheet = workbook.getSheet(workSheetName);Third, we created a 2 dimensional array of strings and named it dataTable. Notice how we set the size of our dataTable to match the size of our excel sheet.
int noOfRows = workSheet.getLastRowNum() + 1;
int noOfColumns = workSheet.getRow(0).getLastCellNum();
String[][] dataTable = new String[noOfRows][noOfColumns];For the noOfRows, we used getLastRowNum(). This method returns the zero-based index of the last row in our sheet, empty rows are included. Say we have 10 rows, this will return 9 that’s why we added 1 to it.
For the noOfCoumns, we get the first row in our workSheet by passing the 0 to the getRow() method. getRow() returns a Row object. Then we get the number of the last cell in that row. The getLastCellNum(), according to the documentation, it already added 1 to the index of the last cell that’s why we did not add 1 just like what we did in noOfRows.
Fourth, we read the data from our workSheet and saved it to dataTable.
for (int i = workSheet.getFirstRowNum(); i < workSheet.getLastRowNum() + 1; i++) {
Row row = workSheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
dataTable[i][j] = getCellValueAsString(cell);
}
}What we are doing here is just iterating every row of our workSheet. Then on each row, we iterate again to get the value of each cell and save it to our dataTable. Notice the method call getCellValueAsString(cell). This is another method we need to create. This will just check the type of the value in the cell and will return the value as string. We could have done this like the code below.
dataTable[i][j] = cell.getStringCellValue();This is okay if the sheet contains only string values. But for example the cell contains a number, it will throw an exception.
Here is the code for our getCellValueAsString() method.
private static String getCellValueAsString(Cell cell) {
CellType cellType = cell.getCellType();
String val = "";
switch (cellType) {
case STRING:
val = cell.getStringCellValue();
break;
case NUMERIC:
DataFormatter dataFormatter = new DataFormatter();
val = dataFormatter.formatCellValue(cell);
break;
case BOOLEAN:
val = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
break;
}
return val;
}Lastly, once we are done reading the data from our workbook. We close the workbook and return our dataTable.
workbook.close();
return dataTable;Now let’s try to use our getDataFromSheet() method to read data from an Excel file.
Above image shows how we used the getDataFromSheet() method. We passed the path to the location of our excel file and the name of the sheet our data will be coming from. And below image shows the printed data we read from our excel file.
Here is the code to the print method we used to display the data.
public static void printData(String[][] table) {
for (String[] strings : table) {
for (String string : strings) {
if (string.equals("")) {
System.out.print("<empty cell>");
} else {
System.out.print(string);
}
System.out.print(" | ");
}
System.out.println(" ");
}
}The getDataFromSheet() method that we created is a simple and straight forward solution to our requirement which is to read our data from the excel file. Now that you are already familiar with the classes, some methods and already knows which documents to check. Creating your more complex method depending on what you need will be a challenge at first but once you get your hands dirty on it, it will be easy breezy.
🍻 🍻 🍻
