Openpyxl >> How to Work With Excel Files in Python

Table of Contents

A Python library to read/write Excel xlsx/xlsm/xltx/xltm files.

In this article, we will talk about how to read/write Excel files in openpyxl library including how to load an Excel file to workbook, get a worksheet from workbook, change worksheet name, insert a new worksheet, copy a worksheet, and save workbook into an Excel file.

How to load workbook from Excel file.

We can use load_workbook method of openpyxl module to load a Excel file into a workbook. To read a Excel file we need to specify the path of Excel file.

# import load_workbook from openpyxl
from openpyxl import load_workbook

# load a excel file
wb = load_workbook('path/to/sample.xlsx')

# print all sheet names
print(wb.sheetnames)

Get active worksheet

We can use active property of workbook to get active sheet. This is set to 0 by default. Unless you modify its value, you will always get the first worksheet by using this method.

# get active sheet. wb is a workbook and ws is a worksheet.
ws = wb.active

Get worksheet by sheet name

We can use sheet name as key to get a sheet from workbook.

# wb is a workbook, ws is a worksheet
ws = wb["sheet1"]

Change sheet name

We can change sheet name by changing title property of a worksheet.

# ws is a worksheet
ws.title = 'new sheet name'

Loop worksheet of workbook

We can use for in to loop worksheet of workbook.

# ws is a worksheet, wb is a workbook
for ws in wb:
    print(ws.title)

Insert worksheet

We can use create_sheet method to insert new worksheet into workbook.

# insert at the end (default)
ws1 = wb.create_sheet("newSheet")

# insert at first position
ws2 = wb.create_sheet("newSheet", 0)

# insert at left side of last worksheet
ws3 = wb.create_sheet("newSheet", -1)

Make a copy of worksheet

We can use copy_worksheet method of workbook to copy a worksheet of workbook.

# source worksheet
ws_source = wb['mySheet']
# target worksheet
ws_target = wb.copy_worksheet(ws_source)

Save workbook to Excel file

We can use save method of workbook to output a Excel file.

# To save workbook to a excel file, we need to specify target file path.
# wb is a workbook
wb.save("path/to/output.xlsx")

Save a workbook as Excel Template

We can set template=True before saving excel file to output a file as Excel Template. Or use as_template option in save method.

wb.template = True
# We need to use .xltx extension when saving to file.
wb.save('my_template.xltx')

# OR
wb.save('my_template.xltx', as_template=True)