Openpyxl

Language: Python

Data

Openpyxl was created by Eric Gazoni and Charlie Clark to provide Python developers with a tool to interact with Excel files without relying on Microsoft Excel itself. It has become the standard library for working with modern Excel files in Python, widely used for data automation, reporting, and spreadsheet manipulation.

Openpyxl is a Python library to read, write, and modify Excel 2010 xlsx/xlsm/xltx/xltm files. It allows you to create spreadsheets, read data, and perform operations on Excel files programmatically.

Installation

pip: pip install openpyxl

conda: conda install -c anaconda openpyxl

Usage

Openpyxl allows creating new Excel workbooks, reading existing files, modifying cells, formatting, adding charts, and saving changes. It supports formulas, styles, merged cells, and more.

Creating a new workbook and adding data

from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet.append(['Alice', 30])
sheet.append(['Bob', 25])
wb.save('example.xlsx')

Creates a new Excel file with headers and two rows of data, then saves it.

Reading an existing workbook

from openpyxl import load_workbook
wb = load_workbook('example.xlsx')
sheet = wb.active
for row in sheet.iter_rows(values_only=True):
    print(row)

Opens an existing Excel file and prints all rows in the active sheet.

Formatting cells

from openpyxl.styles import Font
sheet['A1'].font = Font(bold=True, color='FF0000')
wb.save('example.xlsx')

Applies bold and red font to a specific cell.

Adding formulas

sheet['C2'] = '=SUM(B2:B3)'
wb.save('example.xlsx')

Adds a formula to sum values in a column.

Merging and unmerging cells

sheet.merge_cells('A4:B4')
sheet['A4'] = 'Merged Cell'
wb.save('example.xlsx')

Merges two cells and assigns a value to the merged area.

Adding charts

from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(sheet, min_col=2, min_row=1, max_row=3, max_col=2)
chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, 'E5')
wb.save('example.xlsx')

Adds a bar chart to visualize the data in the worksheet.

Error Handling

FileNotFoundError: Ensure the path to the Excel file exists when using `load_workbook()`.

InvalidFileException: Make sure the file is a valid .xlsx, .xlsm, .xltx, or .xltm file.

KeyError: Worksheet: Check that the worksheet name exists before accessing it with `wb[sheet_name]`.

Best Practices

Always close or save the workbook after modifications to persist changes.

Use `iter_rows(values_only=True)` when reading large files to save memory.

Leverage cell styles and formats for readability in reports.

Avoid modifying open files from multiple processes simultaneously.

Use formulas and charts to enhance automation and reporting capabilities.

Official Docs Github