Using apache poi for getting excel data in TestNG data provider format.

Excel is a truth of life! Whether you like it or not, a lot of people love it. So, we techies have to make peace with it. Often test data comes from business SMEs and guess what? They eat, drink, sleep, excel! With all due respect to their affinity, we need to find ways to read the data from excel.

I will attempt to present a function for reading excel data in HashMap (set of key, value pairs) format. But I will also make it compatible to TestNG data provider. Now why do we need TestNG data provider compatibility? It is because we automators are basically lazy and would let TestNG manage the ‘looping part’ for our data driven testing. Basically, for each row in excel your test can be made to repeat without you coding a single line for this looping. Then, why re-invent the wheel?

Before I go any further, let me clarify a few things.

  • There are many approaches of reading excel data. My approach is just on of the many.
  • You need not get the value as HashMap always. You can get each cell value as an argument variable to your test method. But when you are fetching too many values, your function definition’s argument list will look really long ugly. You will dearly need all the input data wrapped up in some way. Hence I would suggest a Map/ HashMap/ JSON etc. HashMap is just my pick. You may implement data type of your choice.
  • Let us agree that in the final HashMap that your test will receive, ‘key’ will be the ‘column header’ and ‘value’ will be the cell value (If there is a formula in the cell, we will pick the final evaluated ‘value’).

I will try to give as many comments as possible in the code. I hope it is clear enough. Feel free to post a comment if you need further clarity. I will try to respond as soon as possible.


package demoapp.testdata;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class Excel_TestDataProviderDemo {

	// relative path of the excel
	private static final String DEMO_APP_TEST_DATA_XLSX_PATH = "src/test/java/demoapp/testdata/DemoApp_TestData_Xlsx.xlsx";
	private final static int ENABLED_COL_NUM = 0;

	
	@Test(dataProvider = "DEMO_XLSX_HASHMAP")
	public void testNGTestUsingXLSXDataAsHashMap(HashMap data) {
		System.out.println((String)data.get("firstName"));
		System.out.println((String)data.get("lastName"));
		System.out.println((String)data.get("pin"));
		/*If you expect the value against the key to be something other than String, cast accordingly */
                System.out.println(data.toString());
		
	}
	
	@DataProvider(name = "DEMO_XLSX_HASHMAP")
	public Object[][] demoAppGetDataCreateAddressXLSXHashMap() {
		String excelAbsPath = new File(DEMO_APP_TEST_DATA_XLSX_PATH).getAbsolutePath();
		return getXLSXTableDataProviderAsHashMap(excelAbsPath, "CreateAddress", 1);
	}

	/*
	 * This function gets the excel data from a given worksheet in a given workbook
	 * in HashMap format. Where String is column Name and Object is
	 * the evaluated cell value. Final return type if Object[][] to make it
	 * compatible to TestNG data provider Essentially it returns something like
	 * {{HashMap row1},{HashMap
	 * row2},{HashMap row3},...} The benefit is that if there are
	 * too many columns required for test data, you get the data inside a HashMap.
	 */
	public static Object[][] getXLSXTableDataProviderAsHashMap(String excelFileToRead, String sheetName,
			int headerRowNum) {

		Object[][] excelTable = null;
		FileInputStream excelFile = null;
		List excelTableAsList = new ArrayList(); // This list will hold HashMap of each row as a
																		// list element
		int startRow = headerRowNum;
		int lastRow;

		try {
			excelFile = new FileInputStream(excelFileToRead);
			Workbook workBook = new XSSFWorkbook(excelFile);
			Sheet workSheet = workBook.getSheet(sheetName);
			lastRow = workSheet.getLastRowNum();

			for (int currRow = startRow; currRow <= lastRow; currRow++) {
				// Note - Every row will use same column name as key. So, for every
				// iteration/row same header row will be referred.
				Row headerRow = workSheet.getRow(headerRowNum - 1);
				Row row = workSheet.getRow(currRow);

				/*
				 * This is an additional check whether to skip the row. On a column number
				 * defined by ENABLED_COL_NUM user can enter TRUE or FALSE in excel only rows
				 * with TRUE in that column (say "isEnabled" column) the rows will be picked and
				 * returned
				 */
				if (row.getCell(ENABLED_COL_NUM).getBooleanCellValue() == false) {
					continue;
				}

				Cell cell, headerCell;
				HashMap rowHashMap = new HashMap();
				Iterator cells = row.cellIterator();
				Iterator headerCells = headerRow.cellIterator();

				FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

				while (cells.hasNext() && headerCells.hasNext()) {
					headerCell = (Cell) headerCells.next();
					cell = (Cell) cells.next();

					switch (formulaEvaluator.evaluateInCell(cell).getCellTypeEnum()) {
					case BOOLEAN:
						rowHashMap.put(headerCell.getStringCellValue(), cell.getBooleanCellValue());
						break;
					case STRING:
						rowHashMap.put(headerCell.getStringCellValue(), cell.getStringCellValue());
						break;
					case NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							rowHashMap.put(headerCell.getStringCellValue(), cell.getDateCellValue());
						} else {
							rowHashMap.put(headerCell.getStringCellValue(), cell.getNumericCellValue());
						}
						break;
					case ERROR:
						rowHashMap.put(headerCell.getStringCellValue(), cell.getErrorCellValue());
						break;
					case BLANK:
						rowHashMap.put(headerCell.getStringCellValue(), "");
						break;

					default:
						rowHashMap.put(headerCell.getStringCellValue(), cell.getStringCellValue());
						break;
					}

				}
				
				//Only to put the rowHashMap into Object[][] format it is required to add it to a single element oject array
				Object[] objRow = new Object[1];
				objRow[0] = rowHashMap;
				
				excelTableAsList.add(objRow);

			}
			workBook.close();
			excelFile.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//Finally convert the List<Object[] into Object[][] and return
		excelTable = excelTableAsList.toArray(new Object[excelTableAsList.size()][]);
		return excelTable;
	
	}

}


Here are some more details – screenshot of excel input and result:

This slideshow requires JavaScript.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s