Complete guide to Python library Openpyxl includes installation, how to create a new worksheet, read/write/delete data from the workbook:

Excel is the most popular spreadsheet application used to better organize the information, perform calculations, produce output charts, and much more. In the testing world, verifying the application with various sets of data is crucial to get a high-quality product and good test coverage.

Openpyxl is one such library that allows us to use Excel to perform data driven testing and even to prepare test result reports, etc.

In this tutorial we will learn what is Openpyxl, its installation, creating a new workbook, reading data from the spreadsheet, and also write data in it. We will also learn how to upload and download the same from a web page.

What Is Openpyxl

Openpyxl is a Python library used to read and write Excel files (xlsx/xlsm/xltx/xltm files). This module allows the Python programs to read and modify the spreadsheet.

 XLSX file is the default file format for Microsoft Excel. It is based on the Office Open XML standard.

 XLSM file is a Macro-enabled spreadsheet file.

XLTX file is an Excel Template file that preserves user-defined settings.

XLTM file is a Macro-Enabled template file that is created with Microsoft Excel.

How To Install Openpyxl

When you install Python, Openpyxl libraries are not installed by default. We have to execute a command to get the libraries into our Python. For this, you need to open the command prompt and change the directory to the folder where your Python is placed and execute the below command.

pip install openpyxl

Once you execute the command, the libraries will be downloaded and installed. This can be verified by using pip list command as shown in the image below.

Configuration Of PyCharm IDE

There are 2 ways to configure Openpyxl libraries for a project in PyCharm.

#1) Using available packages option in PyCharm

Click on File-> New Project. Click on Create.

Your project will be created successfully. To verify if the libraries are configured, go to File -> Settings. In the setting page, go to Project – > Project Interpreter.

Under Packages, you will then see Openpyxl package. If that is missing, Hit on the “+” button on the right corner. Under Available Packages search for Openpyxl and Hit Install Package, as shown in the image below. After the installation is complete, you will get “package ‘openpyxl’ installed successfully”

Verify whether the package is successfully installed.

#2) Using Inherit from global site-packages option

This method is fairly simple. Go to File-> New Project. While creating a new project select “Inherit global site- packages” checkbox. After the project is created, navigate to File -> Settings-> Project – > Project interpreter, you will see the Openpyxl package is installed already.

Create A New Excel Workbook

We will start by creating a new workbook. An Excel file is called Workbook that contains a minimum of one Sheet. In order to create a workbook, we first have to import the workbook from the Openpyxl library using the command below:

from openpyxl import Workbook

Let’s create a workbook object. We can use this object for handling all the actions which need to be performed like read, write, etc.

wb = Workbook()

Set the filepath where you want to create the workbook, as shown below:

filepath = :”F:/tutorial/sample.xlsx”

To save the workbook use

wb.save(filepath)

This will create a new empty Excel file in the path specified.

If you want to create a new Excel file in the same location as the Python scripts, you can skip filepath and directly mention the file name in wb.save command as shown below:

from openpyxl import Workbook 
wb = Workbook() 
wb.save("demo.xlsx")

Write Data To The Excel File

Now that we know how to create a new file, let’s go ahead and fill in data to the file. To achieve this, we have to load the workbook which we have created above. This can be done using the command below:

wb = load_workbook(“demo.xlsx”)

We have to specify in which Sheet we are going to enter the data. Since we have just one sheet, we can just use the “active” keyword and this will use the current active sheet to enter the data.

sheet = wb.active

In case you have multiple sheets, you have to mention the name of the worksheet, as given below. Where SheetName is the name of the sheet in the workbook.

sheet = wb["SheetName"]

There are 2 ways to enter the data in the Excel file. These are as follows:

• Directly use columnrow combination. Example [A1], where A is the column and 1 is the row.
• Use the row and column numbers. Example row=4, column=2

sheet['A1'] = 'Software Testing Help' 
sheet.cell(row=4, column=2).value = 'Openpyxl Tutorial'

Make sure to save the file after entering the values.

wb.save("demo.xlsx")

Complete code:

from openpyxl import load_workbook
wb = load_workbook("demo.xlsx")	
# Sheet is the SheetName where the data has to be entered
sheet = wb["Sheet"]
# Enter into 1st row and Ath column
sheet['A1'] = 'Software Testing Help'
# Similarly you can enter in the below shown fashion
sheet.cell(row=2, column=1).value = 'OpenPyxl Tutorial'
sheet['B1'] = 10	
sheet.cell(row=2, column=2).value =13.4
wb.save("demo.xlsx")

The above code will write “Software Testing Help” , 10 and “Openpyxl Tutorial”, 13.4 in 1^st and 2^nd row respectively as shown below.

Rename The Sheet In Excel File

In case you are tracking the report date wise and you want to change the sheet name to the current date. It can be done through the “title” command.

For this scenario, we need to get the current date. So first, we need to import datetime package.

from datetime import datetime

now() will give us the current date and time .strftime() method returns a string representing date and time.

now = datetime.now() 
date_time = now.strftime("%d.%m.%Y")

You can change the name of the sheet using sheet.title method. Don’t forget to save the file.

sheet.title = date_time

wb.save("demo.xlsx")

Complete code:

from datetime import datetime
from openpyxl import load_workbook
#Load the workbook demo.xlsx
wb = load_workbook("demo.xlsx")
#Select the current active sheet
sheet = wb.active
now = datetime.now()
date_time = now.strftime("%d.%m.%Y")
sheet.title = date_time	
wb.save("demo.xlsx")

Since we have used active sheet, the current active sheet’s name has been changed.

Add Sheets To The Excel File

Considering the above example to track the report of every day, we need to create a new sheet for each day. To add new sheets to the Excel, use the create_sheet command.

Syntax:

create_sheet(title=None, index=None)

Create a worksheet (at an optional index).

Parameters:

• title (str): optional title of the sheet.
• index (int): optional position at which the sheet will be inserted.

Note: If Parameters are not entered, then it creates the sheet after the last sheet and names it as “sheet”.

sheetname ="Day2 Result "+now.strftime("%d.%m.%Y") 
#Add new sheet using the index and title fields 
wb.create_sheet(index = 1 , title = sheetname)

Complete Code:

from datetime import datetime
from openpyxl import load_workbook
wb = load_workbook("demo.xlsx")
sheet = wb.active
now = datetime.now()
date_time = now.strftime("%d.%m.%Y")
sheet.title = date_time
sheetname ="Day2 Result "+now.strftime("%d.%m.%Y")
wb.create_sheet(index = 1 , title = sheetname)
wb.save("demo.xlsx")

Result:

Appending Multiple Values Into Sheet

We can append a group of values at the bottom of the current sheet. This can be achieved by “append” command.

Syntax:

append(iterable)

Where iterable is a list of values or single value.

• If it’s a list, all the values are added in order, starting from the first column.
• If it’s one value, then the values are assigned to the columns indicated by the keys (numbers or letters).

Refer to the code below:

from openpyxl import load_workbook
wb = load_workbook("demo.xlsx")
#Mention the sheet where the data can be entered,
sheet = wb["Day2 Result 27.07.2020"]
# Assign multiple values to data
data =[('Emp Id', 'Emp Name', 'Designation'),
       (1, 'XYZ', 'Manager'),
       (2, 'ABC', 'Consultant')]
#Append all rows
for i in data:
    sheet.append(i)
wb.save("demo.xlsx")

Result:

Note: You can Print the sheet names using print(“active sheet title: ” + sheet.title)

Delete A Sheet From Excel Workbook

In Openpyxl, if you delete a sheet, be a little careful as it is not recoverable. Let’s create an Excel sheet with 3 Sheets and call it as “Sheet1” “Sheet2” “Sheet3” and save it as DeleteSheet.xlsx.

It is always better to know the sheets, which are already present before deleting any. You can get it through sheetnames command.

Then you can use wb.remove(sheetname) or del wb[sheetname]

Where, wb is the workbook you have loaded.

Refer to the code below:

import openpyxl
wb = openpyxl.load_workbook("DeleteSheet.xlsx")
print("The names of work sheets before deleting")
print(wb.sheetnames)
sheetDelete = wb["Sheet2"]
wb.remove(sheetDelete)  #Sheet2 will be deleted
#del wb[sheetDelete]    #Alternatively you can use del cmd
print("The names of work sheets after deleting")
print(wb.sheetnames)
wb.save("DeleteSheet.xlsx")

Result:

Reading Data From The Excel File

Similar to writing data to the file, we can read the data from an Excel file using 2 ways.

Either, directly use columnrow combination. Example [A1] or, use the row and column numbers. Example row=4, column=2.

sheet.cell().value command will help us retrieve the value from the specified cell.

Find the complete code below:

from openpyxl import load_workbook
wb = load_workbook("demo.xlsx")
sheet = wb.active
dataAtA1 = sheet['A1']
dataAtA2 = sheet.cell(row=2, column=1)
print("Data at A1:", dataAtA1.value)
print("Data at A2:", dataAtA2.value)

Result:

Reading All Values In The File

Usually, in software testing, we need to verify the functionality using various sets of data. Assume that all the data are placed in a workbook and we need to test functionality using all the data. We need to thus traverse through each row and column to retrieve the values. For a large set of data, the above method is not feasible. In order to achieve this, we have to iterate through the entire sheet to fetch the values.

We need to first fetch the number of rows and number of columns in the sheet.

• max_row-This gives the maximum row index containing data (1-based)
• max_column – The maximum column index containing data (1-based)

row_count = sheet.max_row 
column_count = sheet.max_column

Note: max_row and max_column are 1- based indexing. To go to the last row, we need to add one to the count value.

Below is the Complete code to read the values:

from openpyxl import load_workbook
wb = load_workbook("demo.xlsx")
sheet = wb["Day2 Result 27.07.2020"]
row_count = sheet.max_row
column_count = sheet.max_column
for i in range(1, row_count + 1):
    for j in range(1, column_count + 1):
        data = sheet.cell(row=i, column=j).value
        print(data, end='   ')
    print('\n')

Note: We have used end=’ ‘ to have a space between each column and new line (\n) to print the values in a new row.

Result:

Getting Column Letter

Suppose you have a large set of data stored in a workbook and you want to know in which row index and column a particular value is available. You can find that using get_column_letter. This converts a column index into a column letter. Example 3 to C.

Refer to the below code:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
wb = load_workbook("demo.xlsx")
sheet = wb["Day2 Result 27.07.2020"]
row_count = sheet.max_row
column_count = sheet.max_column
for i in range(1, row_count + 1):
    for j in range(1, column_count + 1):
        data = sheet.cell(row=i, column=j).value
        if data=="ABC":
          column_letter = get_column_letter(j)
          print("ABC employee name is present in column ", column_letter, "and in row index ", i )

Result:

Data-Driven Testing

Now that we know Openpyxl helps us in reading and writing data from Excel, we will use these concepts to build a test case to test the application with multiple different values retrieved from the spreadsheet. Hence achieving data-driven testing.

>>Recommended Reading: How Data-Driven Testing Works

Consider a scenario where we will log in to Facebook.com, read login credentials from the workbook, verify the title of the page after login, and write the result back to the same workbook.

Repeat the above scenario for various login credentials

Creating Excel File With Input data

Let’s create a file with login credentials, expected title, actual title, and the result field and save it as Inputdata.xlsx.

Note: This is a dummy file used to show an example. Fill in the correct email and password while practicing the script.

Writing Test Script

Looking at the above input file, we can say that we have to repeat the script 4 times for 4 different credentials. So in order to avoid redundancy, let’s create a separate class that will contain functions for reading and writing the Excel file. We have named it as ExcelUtils.

Below is the ExcelUtils code:

import openpyxl

def readData(file,sheetname,rownum,colnum):
    workbook = openpyxl.load_workbook(file)
    sheet =  workbook[sheetname]
    inputvalue =sheet.cell(row=rownum, column=colnum).value
    return inputvalue

def writeData(file,sheetname,rownum, colnum,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook[sheetname]
    sheet.cell(row=rownum, column=colnum).value= data
    workbook.save(file)

Now import ExcelUtils.py class into your main class and we will be able to reuse the read and write functions

We will read the email and password from the Excel file and login to Facebook. If the login is successful then the script will update the result in the file as Passed. If the login is unsuccessful then the script will update the actual title of the page and mark the result as Failed.

Note: Install Selenium libraries in your IDE, You cannot execute the code without that. pip install Selenium command is used to install Selenium libraries.

Below is the Complete Code:

from openpyxl import load_workbook
from selenium import webdriver
from ExcelTest import ExcelUtils
driver=webdriver.Chrome\
    (r"C:\Users\Admin\PycharmProjects\SeleniumTest\Drivers\chromedriver.exe")
driver.get("https://www.facebook.com/")
driver.maximize_window()
file = "InputData.xlsx"
wb = load_workbook(file)
sheet=wb.active
row_count = sheet.max_row
col_count = sheet.max_column
for r in range(2,row_count+1):
    email= ExcelUtils.readData(file, "Sheet1", r, 1)
    password = ExcelUtils.readData(file, "Sheet1", r, 2)
    driver.find_element_by_id("email").send_keys(email)
    driver.find_element_by_id("pass").send_keys(password)
    driver.find_element_by_id("u_0_b").click()
    time.sleep(6)
    pagetitle=driver.title
    print(pagetitle)
    if pagetitle=="Facebook":
        ExcelUtils.writeData(file,"Sheet1",r,5,"Passed")
    else:
        ExcelUtils.writeData(file,"Sheet1",r,4,pagetitle)
        ExcelUtils.writeData(file,"Sheet1",r,5,"Failed")
driver.find_element_by_xpath\
    ("//*[@id='blueBarDOMInspector']/div/div[1]/div/div/h1/a/i").click()

Result:

Upload File

Once you start automating the real-time projects, sooner or later you are going to perform file uploads. For example, uploading the result file into the shared drive or the application testing by uploading a file, etc. The problem with file upload is, once you click the upload button, then the select file dialog box which opens up is not owned by the browser, so you cannot control it using Selenium.

To achieve this, we need to directly pass the file-path to the input element.

elementinput = driver.find_element_by_id("content") 
elementinput.send_keys(r"C:\Users\Admin\PycharmProjects\open1\Test\InputData.xlsx")

We should be selecting the input element and then passing the path of the file to it using the send_keys method.

[image source] 

In this example, we are taking the input field webelement of id(“content”) and passing the filepath directly using send keys.

Below is the code for uploading an Excel file.

from selenium import webdriver
driver=webdriver.Chrome(r"C:\Users\Admin\PycharmProjects\SeleniumTest\Drivers\chromedriver.exe")
driver.get("https://sheet.zoho.com/sheet/Excelviewer")
driver.maximize_window()
elementinput = driver.find_element_by_id("content")
elementinput.send_keys(r"C:\Users\Admin\PycharmProjects\open1\Test\InputData.xlsx")

Result:

Download File

Downloading a file is fairly simple in Selenium. Find the web element of the file and the download button. But usually, the files are downloaded under the download folder.

If you want to download the files in a specific folder, then you will have to use chrome options and specifying the path of the folder where the file has to be downloaded.

Refer to the code below:

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
chromeOptions = Options()
chromeOptions.add_experimental_option("prefs",{"download.default_directory":r
"C:\Users\Admin\PycharmProjects\open1\ExcelTest"})
driver = 
webdriver.Chrome(executable_path=r"C:\Users\Admin\PycharmProjects\Selenium
Test\Drivers\chromedriver.exe",chrome_options=chromeOptions)
driver.get("https://file-examples.com/index.php/sample-documents-
download/sample-xls-download/")
driver.find_element_by_xpath("//*[@download='file_example_XLSX_10.xlsx']").click()

This code will download the file in the path specified by you.

Result:

Frequently Asked Questions

Q #1) How do I install Openpyxl?

Answer: We have to execute a command to get the libraries into our Python. Open the command prompt and change the directory to the Python working directory and execute the below command.

pip install openpyxl

Q #2) Can I use Python in Excel?

Answer: Openpyxl module allows Python to read data from an Excel file and also allows us to write/modify/append data to the same.

Q #3) How do I load a workbook in Openpyxl?

Answer:

#Import load_workbook
from openpyxl import load_workbook

#Load the workbook
wb = load_workbook(path of the workbook)

Example- wb =load_workbook(“C:\\Users\\demo.xlsx”)

Q #4) Does Openpyxl work with XLS?

Answer: No, it does not support XLS. If you try to use .XLS file format, Python will throw an exception.

raise InvalidFileException(msg)

openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

Further learning =>> Exception Handling in Python

Q #5) How do I find what version of Openpyxl I have?

Answer: Open command prompt. Change the directory to the folder where your Python is installed, then execute the command pip list. This will give all the packages installed along with their version.

Q #6) Does Openpyxl work with Excel 2016?

Answer: Yes, it supports xlsx, xlsm, xltx, and xltm file formats.

Q #7) How do I create a new sheet in Openpyxl?

Answer: To add new sheets to the Excel use

create_sheet(title=None, index=None)

Create a worksheet (at an optional index).

Parameters:

• title (str)–optional title of the sheet.
• index (int)–optional position at which the sheet will be inserted.

Note: it does not enter If Parameters then it creates sheet after the last sheet and names it as “sheet”

Example – wb.create_sheet(index = 3 , title = “Day4”)

Q #8) How do I close a workbook in Openpyxl?

Answer: Workbook_Object.close()

This will Close the workbook file if open. It only affects read-only and write-only modes. Example – Wb.close()

Once you close the workbook. You won’t be allowed to use it again until you load the workbook once again.

Q #9) How to solve – no module named ‘openpyxl’ error?

Answer: ModuleNotFoundError: No module named openpyxl error occurs when that module is not installed.

If you have both Python2.x and Python3.x versions installed in the same machine, then use pip to install in Python2.x and pip3 to install in Python3.x.

Example –

pip install openpyxl – Install Openpyxl module in Python2.x

pip3 install openpyxl – Install Openpyxl module in Python3.x

Q #10) How to open an Excel file that already exists using Openpyxl?

Answer: If you already have an Excel file then use the load_workbook function to open.

If the file is located in your python current working directory, just write the filename as an argument to this function.

Example – from openpyxl import load_workbook
wb= load_workbook(“myexcelfile.xlsx”)

If your file is not in your python working directory, then mention the path of the file as a parameter to load the workbook.

Example – from openpyxl import load_workbook
wb =load_workbook(“C:\\Users\\myexcelfile.xlsx”)

Conclusion

In this tutorial, we have learned about the Openpyxl library which gives us the flexibility to interact with Excel and hence makes data-driven testing easy.

Below mentioned are the essence of this tutorial:

1. It is a Python library that allows us to use Excel to perform actions like reading data and writing data.
2. This Python library can be installed by just a single command pip install openpyxl.
3. This module can be easily installed in PyCharm IDE using the Available Packages window.
4. We have seen how to create a workbook and write data into the same.
5. It also allows us to rename the sheet names, create new sheets, and also removing the sheets.
6. Reading an individual cell value or reading the entire workbook can be done using this library.
7. We have also seen a sample test script that shows how a data-driven test can be performed.
8. Uploading and downloading the file using selenium is also been described in this tutorial.