How to Read and Write Excel Files with the openpyxl Package

Excel File Formats Supported by the openpyxl package

Regardless of which function or class in the package is used, the openpyxl package can only read and write the following Microsoft Excel file formats, .xlsx, .xlsm (Excel files with macros enabled), .xltx (Excel template files), .xltm (Excel template files with macros enabled).

For the old Excel file format .xls, you can try to manipulate it using the packages xlrd and xlwt, or convert .xls to an Excel file in .xlsx format.

Read Excel Files with the openpyxl Package

You can read data from an Excel file using the load_workbook function in the openpyxl package or its alias open (not Python’s built-in function open), which returns a workbook object Workbook that manipulates Excel data.

load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True, rich_text=False)

filename parameter

The filename parameter is the path (absolute or relative) to the Excel file to be read, or a file-like object that is associated with the Excel file and is in binary mode. For example, a file-like object created by calling the Python built-in function open (not the alias of load_workbook) and using rb mode.

read_only parameter

The read_only parameter defaults to False and is used to indicate whether the workbook object Workbook returned by the load_workbook function is read-only. If it is set to True, then you will not be able to save changes to Excel data through the Workbook object, which may improve performance a bit, as logic associated with writing and some complex content (e.g., hyperlinks in Excel cells) will be ignored.

keep_vba parameter

In a non-test environment, the default value of the keep_vba parameter is False, which means that the contents of the Excel file relating to VBA (Visual Basic for Applications) will not be read. If it is set to True, then the contents of the Excel file relating to VBA will be read into the Workbook object, which, of course, does not mean that you can execute the code in VBA or use the functionality implemented by VBA.

data_only parameter

The data_only parameter (default value False) is used to indicate whether to read the results of formulas in cells, or to read formulas in cells when the Excel file contains cells with formulas. If set to True, then the results of formulas will be read, which means that the related cells in the Workbook object no longer have a formula.

keep_links parameter

The keep_links parameter has a default value of True and is used to indicate whether or not a cache of externally linked data, for example, the value of a cell in a worksheet of another Excel file, is to be read when the Excel file is read. If set to False, then the cache of externally linked data will not be read into the Workbook object, which will be effective when the cache is too large.

rich_text parameter

The rich_text parameter is used to indicate whether rich text formatting (RFT) in cells is preserved when reading an Excel file, and its default value is False (rich text formatting is not preserved), which means that the rich text formatting of the related cells in the Workbook object will be lost. When the rich_text parameter is True, saving the Workbook object may fail.

# Please jump the command line to the directory where Hello.xlsx is located and then run this script file
# Import function load_workbook
from openpyxl import load_workbook


# Read the Excel file
workbook = load_workbook('Hello.xlsx')


# Get cells A1, B1, C1, B4, C4 in Worksheet 1.1 and display them
worksheet = workbook['Class 1.1']
name = worksheet['A1'].value
age = worksheet['B1'].value
score = worksheet['C1'].value
print(f'The first student {name} {age} {score}')
avg_age = worksheet['B4'].value
avg_score = worksheet['C4'].value
print(f'Average formula {avg_age} {avg_score}')

The first student Jack 13 100
Average formula =AVERAGE(B1:B3) =AVERAGE(C1:C3)

# Please jump the command line to the directory where Hello.xlsx is located and then run this script file
# Import function load_workbook
from openpyxl import load_workbook


# Use the open function to open the Excel file
xlsx = open('Hello.xlsx', 'rb')
workbook = load_workbook(xlsx, data_only=True)


# Read and display the results of the formulas in cells B4 and C4
worksheet = workbook['Class 1.1']
avg_age = worksheet['B4'].value
avg_score = worksheet['C4'].value
print(f'Average {avg_age} {avg_score}')

Average 13 96.3333333333333

# Please jump the command line to the directory where Hello.xlsx is located and then run this script file
# Import package openpyxl
import openpyxl


# Open an Excel file in read-only mode and modify the worksheet title
workbook = openpyxl.open('Hello.xlsx', True)
worksheet = workbook['Class 1.1']
worksheet.title = 'Class 1.2'


# ERROR Read-only Workbook objects can't modify cell values
worksheet['A1'].value = 'A good man'

AttributeError: Cell is read only

Write (Save) Excel Files with the openpyxl Package

You can save a Workbook object as a Microsoft Excel file using the save_workbook function of the module openpyxl.writer.excel, or the save method of the Workbook object.

save_workbook(workbook, filename)
save(filename)

workbook parameter

The workbook parameter is the Workbook object to be saved.

filename parameter

The filename parameter is the absolute or relative path to the Excel file, if the path points to a file that already exists, then it will be overwritten.

# Overwrite.xlsx will be saved to the current working directory of the command line
# Import package openpyxl
import openpyxl


# Create a workbook and save it twice
workbook = openpyxl.Workbook()
workbook.save('Overwrite.xlsx')
workbook['Sheet']['A1'].value = 'Hello!'
workbook.save('Overwrite.xlsx')

# Import function save_workbook and class Workbook
from openpyxl.writer.excel import save_workbook
from openpyxl import Workbook


# Create the Workbook object and save it
workbook = Workbook(True)
workbook.save('New.xlsx')
# ERROR Can only be saved once
save_workbook(workbook, 'New.xlsx')

# Import Workbook class
from openpyxl import Workbook


# Create a Workbook object and set the worksheets in it to be hidden
workbook = Workbook()
workbook['Sheet'].sheet_state = 'hidden'
workbook.create_sheet().sheet_state = 'hidden'
# ERROR Unable to save Workbook objects without visible worksheets
workbook.save('nosheet.xlsx')

IndexError: At least one sheet must be visible

Close an Excel File Opened by the openpyxl Package

If you use the load_workbook function with the read_only parameter set to True to get a read-only Workbook object, then calling the close method of the Workbook object can close the ZipFile object related to the workbook, after that, reading the cells in the worksheet will not be feasible, but some information related to the worksheet itself can still be accessed, such as the name (title) of the worksheet.

# Please jump the command line to the directory where Hello.xlsx is located and then run this script file
from openpyxl import Workbook, load_workbook


# Create a write-only workbook
w_workbook = Workbook(True)
w_workbook.create_sheet()
w_workbook['Sheet'].append(['Hello', 'World'])
# After calling the close method, write another row of data and save it
w_workbook.close()
w_workbook['Sheet'].append(['Hello', 'World'])
w_workbook.save('Close.xlsx')


# Create a read-only workbook
r_workbook = load_workbook('Hello.xlsx', True)
print(r_workbook['Class 1.1']['A1'].value)
# After the close method is called, cell A1 is read
r_workbook.close()
# ERROR Unable to read cell A1
print(r_workbook['Class 1.1']['A1'])

Jack
…
ValueError: Attempt to use ZIP archive that was already closed

Source Code

src/en/openpyxl/excel_files·codebeatme/office-programming·GitHub