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 # load the workbook with load_workbook method<\/b><\/p>\n <\/b><\/p>\n book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/p>\n <\/b><\/p>\n # to get active worksheet<\/b><\/p>\n <\/b><\/p>\n sheet = book.active<\/b><\/p>\n <\/b><\/p>\n # to pinpoint a cell at 2nd row and first column<\/b><\/p>\n <\/b><\/p>\n cell = sheet.cell(row = 2, column = 1)<\/b><\/p>\n <\/b><\/p>\n # to obtain the cell value<\/b><\/p>\n <\/b><\/p>\n print(cell.value)<\/b><\/p>\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 import openpyxl<\/b><\/p>\n # load the workbook with load_workbook method<\/b><\/p>\n <\/b><\/p>\n book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/p>\n <\/b><\/p>\n # to get active worksheet<\/b><\/p>\n <\/b><\/p>\n sheet = book.active<\/b><\/p>\n <\/b><\/p>\n # set value with value method at 2nd row and 4th column<\/b><\/p>\n <\/b><\/p>\n sheet.cell (row = 1, column = 4).value = “Email”<\/b><\/p>\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 import openpyxl<\/span><\/b><\/p>\n # load the workbook with load_workbook method<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n book = openpyxl.load_workbook(<\/b>“C:\\Work\\PythonExcel.xlsx”)<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n # to get active worksheet<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n sheet = book.active<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n # to iterate through the rows<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n for i in range ( 1, sheet.max_row + 1):<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n # to iterate through the columns<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n for j in range ( 1, s.max_column + 1):<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n # to get the cell data and print<\/b><\/span><\/p>\n <\/b><\/span><\/p>\n print (sheet.cell (row=i, column=j). value)<\/span> <\/b><\/p>\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>\nOperations to Read and Write the Data from Excel<\/b><\/h3>\n
<\/p>\n
Code Implementation to perform read operation<\/h5>\n
Code Implementation to perform write operation<\/h5>\n
Build utility to retrieve values from sheet<\/b><\/h3>\n
Code Implementation to get all values in the excel<\/h5>\n