How can I refer to Excel columns by their header names rather than indexes/keys in Selenium Webdriver with Java?

Question

I just recently started a job writing automated tests using Selenium WebDriver and Java, but my previous experience was with Telerik Test Studio and C#. The transition is going very well for the most part, but I'm trying to find a way to set up data-driven tests in Selenium WebDriver similarly to how they can be set up using Test Studio's Local Data tab (minus the built-in UI, obviously).

I've set up my framework with Apache POI so that I can use Excel files for my data with each row representing a scenario made of different variables represented by the columns. The problem is that since columns tend to get added or reordered over time, I need a way to refer to any column by the name string in its top row header rather than by its key or index.

Test Studio makes this extremely easy in that once you've copied your spreadsheet to the Local Data tab, you can easily refer to the columns by their header name with something along the lines of...

if (...LocalData.PaymentMethod = "CreditCard")...

...where PaymentMethod is the name of the column and "CreditCard" is a potential value in a cell of that column.

Does anyone know of a way to refer to columns by their header name strings using Java in Selenium WebDriver? Any help would be greatly appreciated. Thanks!

Answer 1

I think you are messing up Selenium with data reading from excel sheet. Selenium can't do it - Apache POI does. So, I'll answer regarding POI usage.

You can retrieve column headers using CellReference utility class and column index:

String columnName = CellReference.convertNumToColString(columnIdex);

When you've found necessary column, you can read its cells:

for(Row r : sheet) {
   Cell c = r.getCell(columnIdex);
   // do what you need

}