Site Loader
Auckland, New Zealand
In the last post we discussed how we can write code using Page Object Model with appium and in this post we will continue to work with same code but will add code related to Data Driven testing using JXL. This post inherits some of the previous posts we discussed in executeautomation like Here is the video of the above discuss Here is the complete code we discussed in video

Test Method

@Test
	public void SimpleTest() throws BiffException, IOException {

		ExcelSheetLibrary excel = new ExcelSheetLibrary("f:\\data.xls");

		CalcAppPage calPage = new CalcAppPage(driver);

		calPage.Add(excel.ReadCell(excel.GetCell("Number1"), 1),
				excel.ReadCell(excel.GetCell("Number2"), 1));

		if (calPage.VerifyResult(excel.ReadCell(excel.GetCell("Result"), 1)))
			System.out.println("PASSED Test");
		else
			System.out.println("FAILED Test");

	}

Excel Library

package com.example.lib;

import java.io.File;
import java.io.IOException;
import java.util.Hashtable;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelSheetLibrary {

	static Sheet wrksheet;
	static Workbook wrkbook = null;
	static Hashtable dict = new Hashtable();

	// Create a Constructor
	public ExcelSheetLibrary(String ExcelSheetPath) throws BiffException,
			IOException {
		// Initialize
		wrkbook = Workbook.getWorkbook(new File(ExcelSheetPath));
		// For Demo purpose the excel sheet path is hardcoded, but not
		// recommended :)
		wrksheet = wrkbook.getSheet("Sheet1");

		ColumnDictionary();
	}

	// Returns the Number of Rows
	public static int RowCount() {
		return wrksheet.getRows();
	}

	// Returns the Cell value by taking row and Column values as argument
	public static String ReadCell(int column, int row) {
		return wrksheet.getCell(column, row).getContents();
	}

	// Create Column Dictionary to hold all the Column Names
	public static void ColumnDictionary() {
		// Iterate through all the columns in the Excel sheet and store the
		// value in Hashtable
		for (int col = 0; col < wrksheet.getColumns(); col++) {
			dict.put(ReadCell(col, 0), col);
		}
	}

	// Read Column Names
	public static int GetCell(String colName) {
		try {
			int value;
			value = ((Integer) dict.get(colName)).intValue();
			return value;
		} catch (NullPointerException e) {
			return (0);

		}
	}

}
Thanks for reading the post and watching the video !!! Please leave your comments and let me know if there is anything I should update in this post. Thanks, Karthik KK

Post Author: Karthik kk

5 Replies to “Data Driven Testing with Appium using JXL”

  1. Nice and very helpful tutorials… Please add some more videos for automation selenium code on Android and iOS native apps using appium together.

    Thanks a lot Karthik…

  2. On following all the steps , It throws following error:

    “”The constructor ExcelSheetLibrary(String) is undefined””

    NOTE: I am new to Java+Appium, Hence getting some issues troubleshooting this error.

  3. Hi,

    Great videos, so that you for doing them? Quick question, I followed the example for custom test.xml file, but I cannot get the created file to include the method / include parameter.
    Below is my code, can someone please tell me what I am missing? – Thanks.

    // Initization of drivers.
    DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
    DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
    Document document = documentBuilder.newDocument();

    String xlFilePath = null;

    xlFilePath = “/IdeaProjects/PushAppiumTests/Resources/Initial.xls”;
    ExcelLib excel = new ExcelLib(xlFilePath);

    //excel.ColumnDictionary();

    // Get the flagged cell with value = “Y” from excel file
    excel.getFlaggedMethods(“Flag”);

    // Get the number of parameters to be created in the XML
    int totalNoOfElementsFlagsToRun = excel.flaggedMethod.size();

    // Type the suite tag element in the XML file
    Element rootElementSuite = document.createElement(“suite”);

    // Type the parameter set of lines in the XML file.
    for (int elementCounter = 1; elementCounter <= totalNoOfElementsFlagsToRun ; elementCounter++ ) {
    Element rootElementParameter = document.createElement("parameter");

    String[] flagElement = excel.flaggedMethod.get(elementCounter).toString().split(";");

    rootElementParameter.setAttribute("name", flagElement[0]);
    rootElementParameter.setAttribute("value", flagElement[1]);
    rootElementSuite.appendChild(rootElementParameter);
    }

    // Type the root element in the XML file
    Element rootElementTest = document.createElement("test");
    Element rootElementClasses = document.createElement("classes");
    Element childElementClass = document.createElement("class");

    // Assign attribute to the root elements
    childElementClass.setAttribute("name", "com.Push.Tests.MainScreenLabelTests");

    Element rootElementGroups = document.createElement("methods");

    // Assign attribute to the root elements
    rootElementSuite.setAttribute("name", "Selenium framework");
    rootElementTest.setAttribute("name", "labels");

    // Append values to root element
    rootElementSuite.appendChild(rootElementTest);
    rootElementTest.appendChild(rootElementClasses);
    rootElementClasses.appendChild(childElementClass);
    childElementClass.appendChild(rootElementGroups);
    document.appendChild(rootElementSuite);

    // Obtain the column value flag = "Y" in a loop
    for (int elementCounter = 1; elementCounter <= totalNoOfElementsFlagsToRun; elementCounter++) {

    String element = "include";
    Element em = document.createElement(element);

    String[] flagElement = excel.flaggedMethod.get(elementCounter).toString().split(";");

    em.setAttribute("name", flagElement[0]);
    rootElementGroups.appendChild(em);

    }

    // Generate the file
    FileWriter fstream = new FileWriter("/IdeaProjects/PushAppiumTests/Resources/autotesting.xml");
    BufferedWriter out = new BufferedWriter(fstream);

    // Generate the required XML
    TransformerFactory transformerFactory = TransformerFactory
    .newInstance();
    Transformer transformer = transformerFactory.newTransformer();
    DOMSource source = new DOMSource(document);

    // Print all the generated XML code in the file object.
    StreamResult result = new StreamResult(fstream);
    transformer.transform(source, result);

    // Close the generated file
    out.close();
    } catch (DOMException e) {
    e.printStackTrace();
    }
    }

  4. Hi Karthik,

    Thank you for the great work you are doing.

    How can we do data driven testing without hard-coding the row number in the Test Method.
    This code is great but it is missing that final step to loop through all rows in an Excel sheet.

Leave a Reply to Paul Lemelle Cancel reply

Your email address will not be published. Required fields are marked *