Create a Worksheet and Write Values in Selenium with Python

We can create a worksheet and then write some values in it. Excel is a spreadsheet which is saved with the .xlsx extension. An excel workbook has multiple sheets and each sheet consists of rows and columns.

Out of all the worksheets, while we are accessing a particular sheet that is called as an active sheet. Each cell inside a sheet has a unique address which is a combination of row and column numbers.

The column number starts from alphabetic character A and row number starts from the number 1. A cell can contain numerous types of values and they are the main component of a worksheet.

To work with excel in Selenium with python, we need to take help of OpenPyXL library. This library is responsible for reading and writing operations on Excel, having the extensions like xlsx, xlsm, xltm, xltx.

To install OpenPyXL library, we have to execute the command pip install openpyxl. This is because OpenPyXL does not come by default with python. After this we should import openpyxl in our code and then we should be ready to interact with excel.

To create a sheet, first of all we need to have an object of the Workbook class and then hold it in a variable. Next with the help of create_sheet() method, a new sheet shall be created. This method takes the sheet name and location position as arguments. The position starts from 0.

To write some values inside that sheet, we need the help of row and column number and the cell method [which accepts the row and column number as arguments]. For example, to point to the cell corresponding to row 2 and column 3, we need to mention sheet.cell(row=2,column=3).

After identification of the cell, we need to use a value method to set the value inside the cell. Finally we need to save the changes to excel.

Syntax

wrkbk = openpyxl.Workbook()
# to create the sheet with name
sh = wrkbk.create_sheet("Details", 2)
# set the value in row 2 and column 3
sh.cell(row=2,column=3).value = "Tutorialspoint"

Example

Coding Implementation to create a sheet and write some values inside it.

import openpyxl
# store the object of Workbook class in a variable
wrkbk = openpyxl.Workbook()
# to create a new sheet
sh = wrkbk.create_sheet("Details", 2)
# to set the value in row 2 and column 3
sh.cell(row=2,column=3).value = "Tutorialspoint"
# to save the workbook
wrkbk.save("SeleniumPython.xlsx")