From Spreadsheets to Scenarios: Designing an Excel Reader for My First Cucumber BDD Project

It was the time when I started my DS algo project in March 2025.

When I started my first automation project using Cucumber BDD, one of the earliest challenges I faced was test data management. Hardcoding test values inside feature files or step definitions felt wrong and unscalable. I needed a clean, reusable, and flexible way to read test data — excel was the best choice to read test data.

This blog explains why I designed my Excel reader the way I did, based on real project requirements, and how this design fits well with Cucumber BDD.

The Problem I Needed to Solve

Before writing any code, I listed down my actual requirements.

My key requirements were:

1. Single Excel file with multiple sheets

   • Each sheet represents a module or feature

   • Example: Login, Register, Queue, Graph etc.

2. Column headers can vary

   • Not all sheets have the same columns

   • Some sheets may have test case id, Username, Password

   • Others may have Input code, Actual result, Expected result etc.

3. Each sheet can have multiple rows

   • One sheet may have 10–20 test scenarios

   • All rows should be usable for test execution

4. Cucumber-friendly data access

   • I wanted to access data using meaningful names, not column indexes

   • Example: data.get("Username") instead of getCell(1)

5. Beginner-friendly but scalable

   • This was my first project, but I didn’t want to rewrite everything later.

Why I Chose List<Map<String, String>>

This was the most important design decision.

Why not arrays?

• Arrays depend on column order → break easily

  
  

Why Map<String, String>

Because:

• Key = Column Header

• Value = Cell Data

  
  

This means:

data.get("Username");
data.get("Password");
data.get("ExpectedResult");

No matter where the column is placed, the test still works. And since each row is a separate test case, I wrapped it as:

List<Map<String, String>>

• One map = one test scenario

• One list = all scenarios in that sheet

Why I Read the Header Row First

Row headerRow = sheet.getRow(0);

The first row defines the meaning of every column.

By reading headers first:

• I dynamically map column names

• I don’t care if columns move left or right

• Different sheets can have different headers

This design makes the user friendly for future reference.

Why I Loop Through All Rows

for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++)

Each row represents a separate test scenario. Instead of reading a single row:

• I fetch all rows

• Return them as a collection

• Let the test framework decide how to execute them

This works perfectly with:

• Cucumber scenario outlines

• TestNG DataProviders

• Sequential or parallel execution

Why I Included Empty Rows Safely

Row.MissingCellPolicy.CREATE_NULL_AS_BLANK

Excel files are rarely perfect:

• Someone might leave a cell empty

• Someone might add spacing or formatting

Instead of failing the test:

• Empty cells return empty strings

• Execution continues smoothly

This makes the framework robust and easy to use as a Beginner.

Why I Handle Different Cell Types

Excel cells are tricky:

• Some are strings

• Some are numbers

• Some are Booleans

• Some have formulas

That’s why I added a helper method:

private static String getCellValue(Cell cell)

This ensures:

• All values are converted to strings

• Tests don’t break due to cell type mismatch

• Step definitions remain simple

Why I Used a Single Utility Class

ExcelReader.getAllRows("Login");

All Excel logic is:

• Isolated

• Reusable

• Independent of test logic

This follows a core automation principle:

Test logic should never care how data is read.

If tomorrow I switch from Excel to JSON:

• Only this class changes

• Tests remain untouched

  
  

How This Fits Perfectly with Cucumber BDD

In Cucumber:

• Feature files define behavior

• Step definitions define actions

• Excel defines test data

  
  

This ExcelReader acts as a bridge between business-readable tests and technical execution.

Example usage:

List<Map<String, String>> testData = ExcelReader.getAllRows("Login");

Each row can drive one scenario or one iteration.

Final Thoughts

This ExcelReader may look simple, but it solves real-world automation problems:

• Multiple sheets

• Dynamic headers

• Scalable test data

• Cucumber-friendly design

• Beginner-safe implementation

Most importantly, it helped me focus on test behavior.

If you’re building your first automation framework, designing your data layer thoughtfully will save you months of refactoring later.