Introduction to Excel Data Driven Testing<\/b><\/h3>\n
To interact with excel in Python is a straightforward process. We have to use the openpyxl<\/b> library in Python which helps to read\/write in excel. This package is not available by default in the installed Python packages. We have to download by running the command pip install openpyxl <\/b>from the terminal.<\/p>\n Conclusion:<\/strong> Thus we have discussed the openpyxl library in Python. For more details, you can refer to the link:<\/p>\n https:\/\/courses.rahulshettyacademy.com\/courses\/learn-selenium-automation-in-easy-python-language\/lectures\/13248584<\/a><\/p>\n In the next section we shall discuss how to perform read\/write operations with excel in Python.<\/p>\n Let us take an excel workbook having name PythonExcel.xlsx<\/b> and populate the data within its Sheet1 as shown in the below image:<\/p>\n To work with excel workbook in Python we have to add the import openpyxl<\/b> package to our code. First of all we have to load the excel workbook with the help of the load_workbook<\/b> method. The path of the excel workbook shall be passed as argument to the method. The load_workbook method is obtained from the openpyxl package.<\/p>\n book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/p>\n <\/b><\/p>\n Thus the book <\/b>object has the location of the workbook. Next we shall need to know which sheet is active currently. We shall apply the active<\/b> method on the book object and store it in the object variable named sheet.<\/p>\n sheet = book.active<\/b><\/p>\n Next to pinpoint to a cell in the sheet we shall take the help of the cell <\/b>method. The row and column numbers shall be passed as arguments to the method.<\/p>\n cell = sheet.cell (row = 2, column = 1)<\/b><\/p>\n Here we are referring to a cell in the second row appearing in the first column. Finally to obtain the value out of that cell, we shall apply the value<\/b> method on the cell object.<\/p>\n import openpyxl<\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # load the workbook with load_workbook method<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to get active worksheet<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet = book.active<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to pinpoint a cell at 2nd row and first column<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cell = sheet.cell(row = 2, column = 1)<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to obtain the cell value<\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 print(cell.value)<\/b><\/p>\n<\/div>\n<\/div>\n<\/div>\n Now we shall discuss how to perform write operations. We have to access the cell on which we want to perform the write operation then set the value on to it.<\/p>\n sheet.cell (row = 1, column = 4).value = “Email”, <\/b>here we are setting the value Email <\/b>in the cell appearing at the first row and fourth column.<\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 import openpyxl<\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # load the workbook with load_workbook method<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to get active worksheet<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet = book.active<\/b><\/p>\n <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # set value with value method at 2nd row and 4th column<\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.cell (row = 1, column = 4).value = “Email”<\/b><\/p>\n<\/div>\n<\/div>\n<\/div>\n To get the maximum number of occupied rows in the active worksheet, we shall apply the max_row<\/b> method on the active worksheet object. To get the maximum number of occupied columns in the active worksheet, we shall apply the max_column<\/b> method on the active worksheet object.<\/p>\n rows = sheet.max_row<\/b><\/p>\n columns = sheet.max_column<\/b><\/p>\n Conclusion: <\/strong>Thus we have discussed how to interact with excel – read\/write operations in Python. For more details, you can refer to the link:<\/p>\n https:\/\/courses.rahulshettyacademy.com\/courses\/learn-selenium-automation-in-easy-python-language\/lectures\/13248589<\/a><\/p>\n In the next section, we shall discuss how to obtain values from an excel in Python.<\/p>\n To obtain all the data from the active worksheet, we have to iterate the rows from 1 to the sheet.max_row<\/b> with the help of for loop. In the inner for loop, we have to iterate from 1 to the sheet.max_column<\/b>. In this way, each of the occupied rows and columns get scanned.<\/p>\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0import openpyxl<\/span><\/b><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # load the workbook with load_workbook method<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to get active worksheet<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet = book.active<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to iterate through the rows<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for i in range ( 1, sheet.max_row + 1):<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0# to iterate through the columns<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0for j in range ( 1, s.max_column + 1):<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # to get the cell data and print<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print (sheet.cell (row=i, column=j). value)<\/span> <\/b><\/p>\n<\/div>\n<\/div>\n<\/div>\n Conclusion<\/strong><\/p>\n Thus we have discussed how to obtain values from an excel in Python. For more details, you can refer to the links:<\/p>\n
\n<\/ins>\n<\/div>\nOperations to Read and Write the Data from Excel<\/b><\/h3>\n
<\/p>\n
Code Implementation to perform read operation<\/h5>\n<\/div>\n
Code Implementation to perform write operation<\/h5>\n<\/div>\n
Build utility to retrieve values from sheet<\/b><\/h3>\n
Code Implementation to get all values in the excel<\/h5>\n<\/div>\n