Use openpyxl - open, save Excel files in Python

python
Use openpyxl - open, save Excel files in Python
Page content

Introduction

In this article I show how to work Excel with openpyxl.

Environment

Runtime environment is as below.

  • python 3.6
  • openpyxl 2.5.6

Install

Use openpyxl. The openpyxl official document says ..

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

However, openpyxl can also work with Mac Excel 2016 on my Macbook Pro.

Let’s install with pip command.

1pip install openpyxl

Create new Excel file

Import openpyxl

At first, import Workbook class from openpyxl

1from openpyxl import Workbook

Create Workbook

The Workbook is a Class for Excel file in openpyxl.

Creating Workbook instance works creating a new empty workbook with at least one worksheet.

1# Create a Workbook
2wb = Workbook()

Change the name of Worksheet

Now change the name of Worksheet to “Changed Sheet” .

1ws =  wb.active
2ws.title = "Changed Sheet"

The active property in Workbook instance returns the reference to the active worksheet.

Save file

Save a file as sample_book.xlsx with save function.

1wb.save(filename = 'sample_book.xlsx')

The saved xlsx file exists in the same folder as the program.

download_file

Now open the file and check that the file name has been changed correctly.

rename_sheet

Open a Excel file that already exists

It is easy to get Workbook instance using load_workbook function.

1from openpyxl import load_workbook
2wb = load_workbook('sample_book.xlsx')
3print(wb.sheetnames)

Conclusion

It is available to ..

  • Create Excel file creating Workbook instance
  • Save a Excel file with save function
  • Change name of default Worksheet with active property
  • Open Excel file with load_workbook function