Openpyxl

This blog explain how to use the openpyxl library with Python.

By default, openpyxl library is not installed with Python. To install openpyxl, open cmd and type pip install openpyxl. It will take some time to download and install openpyxl library.

In the above screenshot, I have already downloaded the openpyxl library and tried to reinstall the library. Version openpyxl-3.0.10 installed.

We can verify the version of the library that has been installed by using pip list command

We will start by creating a file directly by using the openpyxl library. There is no need that the excel file should be already present on the system. Openpyxl allows us to create a file. An Excel file in openpyxl is called a workbook. Start by importing workbook class. A workbook is always created with at least one worksheet. Below is the code to import and create the workbook

>>> from openpyxl import Workbook
>>> wb = Workbook()

By default, a workbook is created with one worksheet. However, we can add multiple worksheets to the workbook

Each worksheet has an index value that starts from 0. The first worksheet in the workbook will have an index value of 0, second worksheet will have an index value of 1, and so on. We can access the worksheet by using its index value. When a workbook is created, a name is automatically assigned to it. The name is formatted as sheet, sheet1, sheet2, and so on.

To create a worksheet, use Workbook.create_sheet() method. Let’s create two additional worksheets by using Workbook.create_sheet() method

>>> ws1 = wb.create_sheet("FirstSheet")
>>> ws2 = wb.create_sheet("SecondSheet")

Use for loop to print all the worksheets in the workbook.

>>> for sheet in wb:
...  print(sheet)
...
<Worksheet "Sheet">
<Worksheet "FirstSheet">
<Worksheet "SecondSheet">

In the above code, the for loop displays all worksheets. The workbook contains three worksheets. Now, add two more worksheets without giving a name.

>>> ws3 = wb.create_sheet()
>>> ws4 = wb.create_sheet()

Again, using the for loop to display the worksheets. Two sheets which were created without specifying a name have been named sheet1 and sheet2. Below is the output of for loop

<Worksheet "Sheet">
<Worksheet "FirstSheet">
<Worksheet "SecondSheet">
<Worksheet "Sheet1">
<Worksheet "Sheet2">

We got five worksheets in a workbook. To determine which one is active or currently used for reading or writing, is by issuing the active command. Print statement display “Sheet” is the active worksheet or used for reading or writing.

>>> print(wb.active)
<Worksheet "Sheet">

We can change the sheet on which we want to work by using an active command. To make “Sheet1” or ws4 an active worksheet, issue the below command.

>>> wb.active = wb["Sheet1"]
>>> print(wb.active)
<Worksheet "Sheet1">

The same thing can be achieved using index value associated with a worksheet. Let’s say we want to make “Sheet2” active. Use index value 4, which is associated with “Sheet2”.

>>> wb.active = 4
>>> print(wb.active)
<Worksheet "Sheet2">

As of now, we have five worksheets in a workbook. Now we are adding a worksheet that should appear between “Firstsheet” and “Secondsheet.”

Printing the names of all worksheets

>>> print(wb.sheetnames)
['Sheet', 'FirstSheet', 'SecondSheet', 'Sheet1', 'Sheet2']

>>> ws5 = wb.create_sheet("Sixth_WorkSheet",2)
>>> print(wb.sheetnames)
['Sheet', 'FirstSheet', 'Sixth_WorkSheet', 'SecondSheet', 'Sheet1', 'Sheet2']

Added “Sixth_WorkSheet” between “FirstSheet” and “SecondSheet”

Program 1 : Create a workbook cars with worksheets TATA, Toyota, Ford and Jeep

Importing workbook class

>>> from openpyxl import Workbook

Creating a workbook called cars

>>> cars = Workbook()

By default, a workbook is created with one worksheet, “Sheet”. We have to change the name “Sheet” to “TATA”.

>>> print(cars.sheetnames)
['Sheet']

Changing the name of the default worksheet

>>> cars['Sheet'].title = 'TATA'
>>> print(cars.sheetnames)
['TATA']

Now create a pending worksheet with the names “Toyota”, “Ford”, and “Jeep”.

>>> ws2 = cars.create_sheet('Toyota')
>>> ws3 = cars.create_sheet('Ford')
>>> ws4 = cars.create_sheet('Jeep')
>>> print(cars.sheetnames)
['TATA', 'Toyota', 'Ford', 'Jeep']

Finding the active worksheet and making the “Ford” worksheet active by using index value 

>>> print(cars.active)
<Worksheet "TATA">
>>> cars.active = 2
>>> print(cars.active)
<Worksheet "Ford">

Program 2 : Create a workbook cities with worksheets Beijing, Paris, London and Mumbai

>>> from openpyxl import Workbook
>>> cities = Workbook()
>>> print(cities.sheetnames)
['Sheet']
>>> cities['Sheet'].title = 'Beijing' #Change the name of the default 'Sheet'
>>> print(cities.sheetnames)
['Beijing']
>>> ws2 = cities.create_sheet('Paris')
>>> ws3 = cities.create_sheet('London')
>>> ws4 = cities.create_sheet('Mumbai')
>>> print(cities.sheetnames)
['Beijing', 'Paris', 'London', 'Mumbai']
>>> print(cities.active)
<Worksheet "Beijing">

Program 3 : Create a workbook countries with worksheets India, China, USA, Brazil and Japan

>>> from openpyxl import Workbook
>>> countries = Workbook()
>>> print(countries.sheetnames)
['Sheet']
>>> countries['Sheet'].title = 'India'
>>> print(countries.sheetnames)
['India']
>>> ws2 = countries.create_sheet('China')
>>> ws3 = countries.create_sheet('USA')
>>> ws4 = countries.create_sheet('Brazil')
>>> ws5 = countries.create_sheet('Japan')
>>> print(countries.sheetnames)
['India', 'China', 'USA', 'Brazil', 'Japan']
>>> countries.active = countries['Japan']
>>> print(countries.active)
<Worksheet "Japan">

Program 4 : Create a workbook watches with worksheets Rolex, Samsung, Apple, Omega and use for loop to print names of every worksheet

>>> from openpyxl import Workbook
>>> watches = Workbook()
>>> print(watches.sheetnames)
['Sheet']
>>> watches['Sheet'].title = 'Rolex'
>>> print(watches.sheetnames)
['Rolex']
>>> ws2 = watches.create_sheet('Samsung')
>>> ws3 = watches.create_sheet('Apple')
>>> ws4 = watches.create_sheet('Omega')
>>> for watch in watches:
...  print(watch)
...
<Worksheet "Rolex">
<Worksheet "Samsung">
<Worksheet "Apple">
<Worksheet "Omega">

The Workbook.save() method can be used to save a file. Below is an example.

>>> from openpyxl import Workbook
>>> Student = Workbook()
>>> Student.save('Student.xlsx')

We can use the load_workbook() method to open an existing file.

>>> from openpyxl import load_workbook
>>> Student = load_workbook('Student.xls')

Program 5 : Create a workbook University with worksheets Mumbai, Harvard, Oxford and Stanford. Save the workbook with universitly.xlsx. Now load the same file with a workbook object University2 and add a new worksheet Cambridge and save the file

>>> #Importing Workbook class
>>> from openpyxl import Workbook
>>> #Importing load_workbook method
>>> from openpyxl import load_workbook
>>> #Create a workbook University
>>> University = Workbook()
>>> #Changing the default worksheet name to Mumbai
>>> print(University.sheetnames)
['Sheet']
>>> University['Sheet'].title = 'Mumbai'
>>> print(University.sheetnames)
['Mumbai']
>>> #Creating pending worksheets
>>> ws2 = University.create_sheet('Harvard')
>>> ws3 = University.create_sheet('Oxford')
>>> ws4 = University.create_sheet('Stanford')
>>> #Display all worksheets in University workbook
>>> print(University.sheetnames)
['Mumbai', 'Harvard', 'Oxford', 'Stanford']
>>> #Saving the workbook
>>> University.save('University.xlsx')

>>> #Loading the file University.xlsx with a new object University2
>>> University2 = load_workbook('University.xlsx')
>>> #Printing sheetnames
>>> print(University2.sheetnames)
['Mumbai', 'Harvard', 'Oxford', 'Stanford']
>>> #Adding a new worksheet Cambridge between worksheet Mumbai and Harvard
>>> ws5 = University2.create_sheet('Cambridge',1)
>>> #Display names of all sheets
>>> print(University2.sheetnames)
['Mumbai', 'Cambridge', 'Harvard', 'Oxford', 'Stanford']
>>> #Saving the file
>>> University2.save('University.xlsx')