Reading and Generating Excel Files with Python using openpyxl
openpyxl is the go-to library for working with Excel .xlsx files (OOXML format) in Python. It allows reading, writing, formatting cells, creating charts, applying formulas, working with multiple worksheets, and automating complex report generation — all without having Excel installed.
Installation
pip install openpyxl
# For extended image/compatibility support:
pip install openpyxl[optional]
Reading an Existing Excel File
from openpyxl import load_workbook
# Load workbook
wb = load_workbook("sales.xlsx")
# List sheets
print("Sheets:", wb.sheetnames)
# Access active sheet
ws = wb.active
# Dimensions
print(f"Dimensions: {ws.dimensions}")
print(f"Data rows: {ws.max_row}")
print(f"Data cols: {ws.max_column}")
# Read a single cell
cell = ws['B3']
print(f"B3: {cell.value} (type: {cell.data_type})")
# Read a full row
for cell in ws[2]: # row 2
print(f" {cell.coordinate}: {cell.value}")
# Iterate all data rows
for row in ws.iter_rows(min_row=2, values_only=True):
date, product, qty, price = row
print(f"{date} | {product} | {qty} | {price}")
Read as List of Dictionaries
from openpyxl import load_workbook
def excel_to_dicts(path, sheet=None):
wb = load_workbook(path, read_only=True, data_only=True)
ws = wb[sheet] if sheet else wb.active
headers = [cell.value for cell in next(ws.iter_rows(max_row=1))]
rows = []
for row in ws.iter_rows(min_row=2, values_only=True):
if any(v is not None for v in row): # skip blank rows
rows.append(dict(zip(headers, row)))
return rows
sales = excel_to_dicts("sales.xlsx")
for s in sales[:3]:
print(s)
# {'date': datetime(2026, 1, 15), 'product': 'Laptop', 'qty': 5, 'price': 999.99}
Creating a New Excel File
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from datetime import date
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"
# ── Headers with styling ────────────────────────────────
headers = ["Date", "Product", "Category", "Qty", "Price", "Total"]
for col, text in enumerate(headers, 1):
c = ws.cell(row=1, column=col, value=text)
c.font = Font(bold=True, color="FFFFFF", size=11)
c.fill = PatternFill(fill_type="solid", fgColor="2E5984")
c.alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[1].height = 20
# ── Data ─────────────────────────────────────────────────
data = [
(date(2026, 1, 10), "Laptop Pro", "Electronics", 3, 1299.99),
(date(2026, 1, 11), "4K Monitor", "Electronics", 5, 549.00),
(date(2026, 1, 12), "Mech Keyboard", "Peripherals", 10, 89.95),
(date(2026, 1, 13), "Logitech Mouse","Peripherals", 15, 49.99),
(date(2026, 1, 14), "1TB SSD", "Storage", 8, 119.00),
]
even_color = "EBF3FA"
odd_color = "FFFFFF"
for i, (dt, product, cat, qty, price) in enumerate(data, 2):
total = f"=D{i}*E{i}"
row = [dt, product, cat, qty, price, total]
color = even_color if i % 2 == 0 else odd_color
for j, value in enumerate(row, 1):
c = ws.cell(row=i, column=j, value=value)
c.fill = PatternFill(fill_type="solid", fgColor=color)
c.alignment = Alignment(vertical="center")
# ── Number formats ───────────────────────────────────────
for row in range(2, len(data) + 2):
ws[f"E{row}"].number_format = '"$"#,##0.00'
ws[f"F{row}"].number_format = '"$"#,##0.00'
ws[f"A{row}"].number_format = 'MM/DD/YYYY'
# ── Totals row ───────────────────────────────────────────
total_row = len(data) + 2
ws[f"D{total_row}"] = f"=SUM(D2:D{total_row-1})"
ws[f"F{total_row}"] = f"=SUM(F2:F{total_row-1})"
ws[f"E{total_row}"] = "TOTAL"
for col in [4, 5, 6]:
c = ws.cell(row=total_row, column=col)
c.font = Font(bold=True, color="FFFFFF")
c.fill = PatternFill(fill_type="solid", fgColor="2E5984")
# ── Column widths ────────────────────────────────────────
for col, width in enumerate([12, 20, 14, 8, 12, 12], 1):
ws.column_dimensions[get_column_letter(col)].width = width
# ── Freeze header row ────────────────────────────────────
ws.freeze_panes = "A2"
# ── Borders ──────────────────────────────────────────────
thin = Border(
left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin')
)
for row in ws.iter_rows(min_row=1, max_row=total_row, min_col=1, max_col=6):
for c in row:
c.border = thin
wb.save("sales_report.xlsx")
print("Excel file created: sales_report.xlsx")
Creating Charts
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference
wb = Workbook()
ws = wb.active
ws.title = "Data"
data = [
("Month", "Sales", "Target"),
("January", 15000, 12000),
("February", 18500, 14000),
("March", 22000, 16000),
("April", 19000, 17000),
("May", 25500, 18000),
("June", 28000, 20000),
]
for row in data:
ws.append(row)
# ── Bar chart ────────────────────────────────────────────
bar = BarChart()
bar.type = "col"
bar.title = "Sales vs Target 2026"
bar.y_axis.title = "USD ($)"
bar.x_axis.title = "Month"
bar.width, bar.height = 18, 12
sales_ref = Reference(ws, min_col=2, min_row=1, max_row=len(data))
target_ref = Reference(ws, min_col=3, min_row=1, max_row=len(data))
bar.add_data(sales_ref, titles_from_data=True)
bar.add_data(target_ref, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=len(data))
bar.set_categories(cats)
ws.add_chart(bar, "E2")
wb.save("sales_charts.xlsx")
print("Charts created")
Working with Multiple Sheets
from openpyxl import Workbook
wb = Workbook()
months = ["January", "February", "March", "April"]
for month in months:
ws = wb.create_sheet(title=month)
ws.append(["Date", "Sales", "Expenses", "Profit"])
# ... add data
del wb["Sheet"] # remove default empty sheet
# Summary sheet referencing other sheets
summary = wb.create_sheet("Summary", 0) # index 0 = first position
summary["A1"] = "Annual Summary"
for i, month in enumerate(months, 2):
summary[f"A{i}"] = month
summary[f"B{i}"] = f"={month}!B2" # cross-sheet reference
wb.save("annual_report.xlsx")
Data Validation
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.active
# Dropdown list
dv_list = DataValidation(
type="list",
formula1='"Electronics,Clothing,Food,Home,Sports"',
allow_blank=True,
showDropDown=False,
showErrorMessage=True,
errorTitle="Invalid value",
error="Please select a category from the list"
)
ws.add_data_validation(dv_list)
dv_list.add(ws["C2:C100"])
# Numeric validation (price > 0)
dv_price = DataValidation(
type="decimal",
operator="greaterThan",
formula1="0",
showErrorMessage=True,
errorTitle="Invalid price",
error="Price must be greater than 0"
)
ws.add_data_validation(dv_price)
dv_price.add(ws["E2:E100"])
wb.save("validated_form.xlsx")
Automated Reports from a Database
import sqlite3
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
from datetime import datetime
def generate_excel_from_db(db_path, query, output_path, title="Report"):
conn = sqlite3.connect(db_path)
cursor = conn.execute(query)
wb = Workbook()
ws = wb.active
ws.title = title[:30]
headers = [desc[0].replace('_', ' ').title() for desc in cursor.description]
for col, name in enumerate(headers, 1):
c = ws.cell(row=1, column=col, value=name)
c.font = Font(bold=True, color="FFFFFF")
c.fill = PatternFill(fill_type="solid", fgColor="1A5276")
for row_num, row in enumerate(cursor.fetchall(), 2):
for col_num, value in enumerate(row, 1):
ws.cell(row=row_num, column=col_num, value=value)
for col in ws.columns:
max_len = max(
(len(str(c.value)) if c.value is not None else 0)
for c in col
)
ws.column_dimensions[get_column_letter(col[0].column)].width = min(max_len + 3, 40)
wb.properties.creator = "Python openpyxl"
wb.properties.created = datetime.now()
conn.close()
wb.save(output_path)
print(f"Report saved: {output_path} ({ws.max_row - 1} rows)")
generate_excel_from_db(
"sales.db",
"SELECT date, product, qty, price FROM sales WHERE year = 2026",
"report_2026.xlsx",
"Sales 2026"
)
Additional Resource
For converting Excel files to PDF, CSV, ODS and other formats without any coding, use KaijuConverter — free and no registration required.
Related conversions
Frequent conversions across the catalogue: