Geek Culture
Published in

Geek Culture

How to Read Column Wise Data from Excel File-Apache POI

Today someone on the Stack-overflow, asked me that he wanted to read the below Example Data using cell reference attribute.

The table in the sheet is maintained in column order.

For example, if the table is something like:

Then he wanted the script to run for:

And then in the second iteration wanted to run for:

And be accessible using the corresponding attributes (firstName, lastName, personalEmail) in the code for the ExcelReader class.

Here’s what he wanted to achieve:

  1. Is there a way to achieve this using Apache-poi extension for Java?
  2. What function libraries can I used from the apache-poi extension?
  3. What code should I use in my utilities package?

Now, let’s build a logic for the above problem:

Solution:

To solve this we need to reverse the data getting logic. So here we first need to get the column data and then traverse all its row.

ie. Nick -> Fury -> nick-fury@example.com and then moving to another column and fetching Jack -> Ryan -> jack-ryan@example.com

Screenshot:

Important Note:

This code is to fetch xls file data using POI, kindly change the code as per your requirement.

(1). HSSFWorkbook: This class has methods to read and write Microsoft Excel files in .xls format.

(2).XSSFWorkbook: This class has methods to read and write Microsoft Excel and OpenOffice xml files in .xls or .xlsx format.

Code:

@Test(dataProvider = "getExcelData")
public void testSheet(String firstName, String lastName, String personalEmail) {
System.out.println(firstName+" "+lastName+" "+personalEmail);
}
@DataProvider
public Object[][] getExcelData(){
String excelSheetPath = System.getProperty("user.dir")+"/data.xls";
String sheetName = "Sheet1";
return getExcelData(excelSheetPath, sheetName);
}
public Object[][] getExcelData(String excelSheetPath, String sheetName) {
Object[][] arrayExcelData = null;
try (
FileInputStream fileStream = new FileInputStream(excelSheetPath)
) {
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
HSSFSheet sheet = workbook.getSheet(sheetName);
Row row = sheet.getRow(0);
int lastRowIndex = sheet.getLastRowNum() + 1;
System.out.println("Last row index :" + lastRowIndex);
int totalNoOfCols = row.getLastCellNum() - 1;
System.out.println("Total columns :" + totalNoOfCols);
arrayExcelData = new Object[totalNoOfCols][lastRowIndex];
DataFormatter df = new DataFormatter();
for (int i = 1; i <= totalNoOfCols ; i++) {
for (int j = 0; j < lastRowIndex; j++) {
row = sheet.getRow(j);
Cell c = row.getCell(i);
String cellData = df.formatCellValue(c);
System.out.println(cellData);
arrayExcelData[i-1][j] = cellData;
}
System.out.println("-----------");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
return arrayExcelData;
}

Output:

--

--

--

A new tech publication by Start it up (https://medium.com/swlh).

Recommended from Medium

Docker Adoption Pathway — Part 1

Apache Server.

Want to run a successful Dreamforce booth? Check out these key tips.

Ansible AWX Installation

Late Binding Variables: It’s a Trap!

What is Flutter?

.NET CORE 3.1 Web API For Beginners. Part -01

How Do I Become a Science Writer?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Abhishek Dhoundiyal

Abhishek Dhoundiyal

Principal QA Manager at MakeMyTrip https://stackoverflow.com/users/5372079/abhishek-dhoundiyal

More from Medium

Beginners Guide to Java Programming

Version Controlling

Lists and Strings in Python: How to convert list into a string

Lists and Strings in Python: How to convert list into a string

Java 學習記錄126 — SQL Injection Attacks and Prepared Statements