How to interact with excel file to run data driven automation testing?

Question

What are the methods to use to interact with excel files in data driven testing. Expleain POI API?

Answer 1

You can checkout my project here https://github.com/qamate/excel-parser

If you are using maven, you can simply add artifact as given below.

<dependency>

    <groupId>in.mayurshah</groupId>

    <artifactId>excel_parser</artifactId>

    <version>1.0.0</version>

</dependency>

Usage:

        ExcelFile xlsxFile = new ExcelFile("test-resources\\file1.xlsx");

        ExcelFile xlsFile = new ExcelFile("test-resources\\file1.xls");

        ExcelFile csvFile = new ExcelFile("test-resources\\file1.csv");

        System.out.println("XLSX file");

        PrintData(xlsxFile.getData());

        System.out.println("XLS file");

        PrintData(xlsFile.getData());

        System.out.println("CSV file");

        PrintData(csvFile.getData());

 

Above will return data in form of List<HashMap<String, String>>

to convert it to arrray of an Object[][] you can use below function from: Github

	 	public static Object[][] listHashMapToObject(

			List<HashMap<String, String>> mapList) {
		Object[][] data = new Object[mapList.size()][1];
		{
			for(int i=0;i<mapList.size();i++)
				data[i][0] = mapList.get(i);
		}
		return data;
	}

 

Hope that helps!