excel utility for selenium code example

Example 1: read excel selenium

# How to read & write to Excel file

import XL_utility # Importing from the second file
from selenium import webdriver
import unittest
import time


class Test_Excel(unittest.TestCase):

    def setUp(self):

        self.driver = webdriver.Chrome("path for browser driver...")
        self.driver.implicitly_wait(10)
        self.driver.get("web adress...")
        self.driver.maximize_window()

        return self.driver

    def test_read_excel_file(self):

        path = "file location for your excel file..."
        print(path.title())

        rows = XL_utility.getRowCount(path, "Sheet1")

        for r in range(2, rows + 1):
            username = XL_utility.readData(path, "Sheet1", r, 1)
            password = XL_utility.readData(path, "Sheet1", r, 2)

            self.driver.find_element_by_name("username").send_keys(username)
            self.driver.find_element_by_name("password").send_keys(password)

            self.driver.find_element_by_class_name("auth-form__submit").click()

            time.sleep(10)


            if self.driver.title == "title when you're logged in":
                print("Test is passed!")
                XL_utility.writeData(path, "Sheet1", r, 3, "Test passed")
            else:
                print("Test failed")
                XL_utility.writeData(path, "Sheet1", r, 3, "Test failed")

            self.driver.back()
            return self.driver



if __name__ == "__main__":
    unittest.main()
    
    
# This following code should be placed in a new python file...


import openpyxl


def getRowCount(file, sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return sheet.max_row


def getColumnCount(file, sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return sheet.max_column


def readData(file, sheetName, rownum, columnno):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return sheet.cell(row=rownum, column=columnno).value


def writeData(file, sheetName, rownum, columnno, data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    sheet.cell(row=rownum, column=columnno).value = data
    workbook.save(file)

Example 2: how do you test using excel files

How do you do test using excel files in Java?
I use Apache POI libraries to read and write
from excel file, I add the Apache POI
dependencies to my pom file. In order
to connect I use following classes.
 	-FileInputStream from Java. it is
    used to create connection to the file.
    We pass the file path as constructor to it.
 	-WorkBook is a class that represents
    the excel file.  We create Workbook object
    using the FileInputStream object.
 	-Sheet represents a single sheet from 
    the excel file. We create sheet using 
    Workbook object. We can create worksheet
    using the 0 based index.

	public String readExcel(String path, String sheetName,
                                         int rowNum, int colNum) {
        try {
            FileInputStream file = new FileInputStream(path);
            Workbook book = WorkbookFactory.create(file);
            Sheet sheet = book.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
	    	Cell cell = row.getCell(colNum);
            String cellData = cell.toString();
            return cellData;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

How to get row and column numbers:
    int rowCount = sheet.getLastRowNum()+1; ==> why we add '+1'? 
                                         Because row num starts from 0.
    int colCount = sheet.getRow(0).getLastCellNum();
    String sheetName = workSheet.getSheetName();