read excel selenium

1# How to read & write to Excel file
2
3import XL_utility # Importing from the second file
4from selenium import webdriver
5import unittest
6import time
7
8
9class Test_Excel(unittest.TestCase):
10
11    def setUp(self):
12
13        self.driver = webdriver.Chrome("path for browser driver...")
14        self.driver.implicitly_wait(10)
15        self.driver.get("web adress...")
16        self.driver.maximize_window()
17
18        return self.driver
19
20    def test_read_excel_file(self):
21
22        path = "file location for your excel file..."
23        print(path.title())
24
25        rows = XL_utility.getRowCount(path, "Sheet1")
26
27        for r in range(2, rows + 1):
28            username = XL_utility.readData(path, "Sheet1", r, 1)
29            password = XL_utility.readData(path, "Sheet1", r, 2)
30
31            self.driver.find_element_by_name("username").send_keys(username)
32            self.driver.find_element_by_name("password").send_keys(password)
33
34            self.driver.find_element_by_class_name("auth-form__submit").click()
35
36            time.sleep(10)
37
38
39            if self.driver.title == "title when you're logged in":
40                print("Test is passed!")
41                XL_utility.writeData(path, "Sheet1", r, 3, "Test passed")
42            else:
43                print("Test failed")
44                XL_utility.writeData(path, "Sheet1", r, 3, "Test failed")
45
46            self.driver.back()
47            return self.driver
48
49
50
51if __name__ == "__main__":
52    unittest.main()
53    
54    
55# This following code should be placed in a new python file...
56
57
58import openpyxl
59
60
61def getRowCount(file, sheetName):
62    workbook = openpyxl.load_workbook(file)
63    sheet = workbook.get_sheet_by_name(sheetName)
64    return sheet.max_row
65
66
67def getColumnCount(file, sheetName):
68    workbook = openpyxl.load_workbook(file)
69    sheet = workbook.get_sheet_by_name(sheetName)
70    return sheet.max_column
71
72
73def readData(file, sheetName, rownum, columnno):
74    workbook = openpyxl.load_workbook(file)
75    sheet = workbook.get_sheet_by_name(sheetName)
76    return sheet.cell(row=rownum, column=columnno).value
77
78
79def writeData(file, sheetName, rownum, columnno, data):
80    workbook = openpyxl.load_workbook(file)
81    sheet = workbook.get_sheet_by_name(sheetName)
82    sheet.cell(row=rownum, column=columnno).value = data
83    workbook.save(file)
84