If test automation on a complex data set took 2 hours in 2019, it takes us only an hour and half today to finish a significantly larger volume of work in the same timeframe today ✅
This is all thanks to ‘Excel POJO Mapper’.
Tl;dr: We created a JAVA library to map complex data sets from Excel to a POJO (plain old JAVA object) with just a few lines of code. This helps us avoid writing the boilerplate code at every instance to read and create JAVA objects.
Adopting the Excel POJO Mapper has helped us cut down our test data development & maintenance time by nearly 20% over the past two years.
Oh, and now, you can adapt the mapper to your needs — we’re open-sourcing the code ☺️
What led to the mapper’s creation?
Storing test data in a simple Excel sheet is fast and convenient for us. However, mapping individual variables from the sheet to the code is a tedious process — especially for huge data sets (say, more than 100 columns).
It was a straightforward problem statement: how do we reduce the time spent by our engineering team by automating this mapping process?
Challenges with the conventional approach
Of course, we weren’t the first engineering team to deal with such a challenge. There have been multiple solutions to automating this process, but here are some of the issues with them:
- Often, test automation scripts require that data to be converted into different formats such as XML, JSON, JAVA objects, etc. The cognitive complexity of conversion code increases with the complexity of data.
- The process of mapping the data with the automation script introduces an additional code that needs an update every single time the structure of the data changes, defeating the original purpose.
- It presents a bottleneck for existing automation scripts, which take input from the newly structured Excel data. Again, the code for data mapping needs to be modified at every instance.
- A similar code for mapping introduces large classes and utilities in projects, which makes readability and maintenance difficult in the long run.
Lastly: any minor mapping issue will require the code to be reviewed in its entirety — using these methods could potentially increase our workload instead of doing the opposite!
However, we run into familiar problems here as well 😪
Over the past 6 years, Meesho has grown by leaps and bounds — and so has our data + test coverage. The boilerplate code needs to be modified accordingly, adding to our work.
Additionally, the relationship between the Excel data and the automation script can become difficult to work within the long run — again, problems faced earlier.
If you have ever been in my shoes and worked in test automation, you’ll probably relate to these challenges that contribute to a sub-par experience!
This is where our Excel POJO Mapper proves to be a valuable library and can be added as a dependency in automation projects.
How does our Mapper work?
Generating Excel template
As an initial requirement, you’ll need to have POJO classes in your project.
The generator class present in the library generates Excel templates by reading POJO classes.
You’ll need to add the data to the Excel template, after which it should be smooth sailing ⛵️
Note: The column fields in the template are represented as the corresponding POJO classes.
Entering your data in the generated template
Once the Excel template is generated using the POJO classes, we can fill in the sheet with relevant data as described below:
You can map single or multiple rows of data with a key which will later be used to fetch data as a list of Java objects.
Our Excel POJO Mapper library supports the mapping of complex object hierarchies, which includes nested objects, arrays, and lists!
Final step: Getting the JAVA object
Now that the Excel template is filled with data, let’s fetch those objects into our code. Here, the ExcelObjectMapperHelper class provides a method to fetch data from Excel sheets as JAVA objects by the key mapped to it.
Testing stage
Prior to adopting it across our engineering functions, we had to conduct a small test run to see if our solution runs into the same problems as earlier ones 🧪
We developed a tiny JAVA library to map the test case data from an Excel sheet.
And voila! Our work was done for us!
By simply adding this library as a dependency, one can create Excel templates using POJO classes, as well as fetch the list of rows as JAVA objects using minimal lines of code!
Here’s how the entire flow works:
Some more advantages
That’s not even the best part of this entire project: our POJO Mapper generates Excel templates using POJO fields as columns, which makes retrieving the data much simpler 😌
To find it, simply fill the Excel rows and map each row (single/multiple rows combined) with a key — easy as pie!
The POJO Mapper also supports data representation & mapping of complex java objects with help of simple and easily understandable delimiters (like blank row or END keyword), which need to be taken care of while entering data.
If you want to use TestNG, that’s possible too: TestNG’s data provider and our library can run tests for multiple objects pretty easily.
Having a common data provider for Excel containing test case and data mapping will automatically identify the relevant data set for test cases. This avoids the repetitive task of creating a data provider for every test case.
Additionally, to fulfil custom mapping requirements on other automation tasks, we can simply fetch data using the keys mapped to the data.
We understood this niche area where mapping data from an Excel sheet in to JAVA objects can become tricky when a huge amount of test data needs to be maintained in Excel.
We’re open-sourcing the Excel-POJO-Mapper library so you can re-use existing code, shortening your development timelines significantly!
ICYMI, Excel POJO Mapper is hosted on GitHub:
https://github.com/Meesho/excel-pojo-mapper
Give it a spin, tell us what you think. Suggestions, feedback? Drop a comment 😄
Want to work with me to solve our engineering problems? Head to meesho.io to check our openings!
