Using Python openpyxl to Create, Access, and Save Excel Workbooks

Python has become a standard tool for data handling in finance, commerce, and internet companies, often replacing manual Excel workflows with automated scripts.

The article introduces the openpyxl library, which allows Python code to read and write Excel files (xlsx, xlsm, xltx, xltm) without needing to open the file in Excel.

Creating/Opening a Workbook

First download a sample shiyanlou.xlsx file and install a specific version of openpyxl:

!wget -nc "https://labfile.oss.aliyuncs.com/courses/1585/shiyanlou.xlsx"
!pip install openpyxl==3.0.3

To create a new workbook:

from openpyxl import Workbook
wb = Workbook()  # instantiate a workbook object
print(wb)

To open an existing workbook:

from openpyxl import load_workbook
wb = load_workbook(filename='shiyanlou.xlsx')
print(wb)

When loading a workbook you can use parameters such as data_only (to read stored values instead of formulas) and keep_vba (to preserve VBA macros).

The default active sheet can be accessed via wb.active. New sheets can be added with wb.create_sheet(), optionally naming them and specifying insertion position.

Accessing Worksheet Cells and Their Values

Cells can be accessed directly using the sheet as a dictionary, e.g., c = ws['A4'], and values can be read or written with the value attribute:

ws['A4'] = 4
c.value  # returns 4

Alternatively, the cell() method can be used:

d = ws.cell(row=4, column=2, value=10)  # writes to B4
ws['B4'].value

Accessing Multiple Cells

Ranges can be sliced, for example cell_range = ws['A1':'C2']. Whole columns or rows can be accessed with ws['C'], ws['C:D'], ws[10], or ws[5:10]. Iterators such as ws.iter_rows() and ws.iter_cols() return cell objects, and with values_only=True they return just the values.

Reading Only Values

The ws.values property yields rows of plain values, useful for quick data extraction:

for row in ws.values:
    for value in row:
        print(value)

Saving the Workbook

After modifications, the workbook is saved to an .xlsx file (overwrites if it exists): wb.save("shiyanlou.xlsx") This end‑to‑end example demonstrates how Python and openpyxl can replace repetitive manual Excel tasks with concise, reproducible scripts.