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)