The Problem I Faced: 500 Excel Files Every Month
Earlier this year I took on the task of processing revenue reports from 12 branches — each branch sent one Excel file per week, totaling around 500 files per month. Manual merges, manual coloring, manual borders. It was eating up nearly two full days every month.
After three weeks of writing scripts with openpyxl, that entire pipeline now runs in 4 minutes. This post covers what’s actually useful — not a Hello World tutorial, but the things I actually use in production.
Core Concepts to Know First
openpyxl works with three main layers: Workbook (the Excel file), Worksheet (a sheet), and Cell (a cell). Understand these three layers and you understand 80% of the library.
There are two important file-reading modes that many people overlook:
- Normal mode: Loads the entire file into RAM — convenient for flexible reading and writing, but noticeably memory-hungry for files 50 MB and above.
- Read-only mode (
read_only=True): Uses a generator, reading rows one at a time without loading everything into memory — handles 200 MB+ files without breaking a sweat.
The specific reason to choose openpyxl is that it handles .xlsx natively: read, write, preserve formatting, support styles and charts. Pandas is better for numerical analysis, but its Excel exports don’t preserve borders or background colors. When the output needs to go directly to a client, openpyxl is the clearer choice.
Hands-On: From Reading Data to a Finished Report
1. Reading Large Files Without Running Out of RAM
For small files under 10 MB, normal mode is fine:
from openpyxl import load_workbook
wb = load_workbook('bao_cao_thang.xlsx')
ws = wb.active
for row in ws.iter_rows(min_row=2, values_only=True):
chi_nhanh, doanh_thu, so_don = row[0], row[1], row[2]
print(f"{chi_nhanh}: {doanh_thu:,} VND ({so_don} orders)")
For files over 50 MB, switch to read-only mode immediately:
wb = load_workbook('data_lon.xlsx', read_only=True)
ws = wb.active
total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
if row[1]: # Skip empty rows
total += row[1]
wb.close() # Important: must close when using read_only
print(f"Total: {total:,}")
2. Writing Data and Creating New Report Files
Creating a consolidated report file from multiple source files — this is my most-used use case:
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
import glob
wb_out = Workbook()
ws_out = wb_out.active
ws_out.title = "Summary"
# Header
headers = ["Branch", "Revenue", "Orders", "Avg/Order"]
ws_out.append(headers)
# Style header
for col_idx, cell in enumerate(ws_out[1], 1):
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(fill_type="solid", fgColor="2E5090")
cell.alignment = Alignment(horizontal="center")
# Read each branch file
for filepath in glob.glob('chi_nhanh_*.xlsx'):
wb_src = load_workbook(filepath, read_only=True)
ws_src = wb_src.active
for row in ws_src.iter_rows(min_row=2, values_only=True):
if row[0]: # Has data
tb = row[1] / row[2] if row[2] else 0
ws_out.append([row[0], row[1], row[2], round(tb, 0)])
wb_src.close()
wb_out.save('tong_hop.xlsx')
3. Conditional Formatting
This is the part I spent the most time figuring out. openpyxl has fairly complete conditional formatting support, but the API is a bit different from what you see in the Excel UI.
Color cells based on their values — for example, highlighting low revenue:
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.styles import PatternFill
# Color scale: red → yellow → green by value
color_scale = ColorScaleRule(
start_type='min', start_color='FF0000',
mid_type='percentile', mid_value=50, mid_color='FFFF00',
end_type='max', end_color='00FF00'
)
ws_out.conditional_formatting.add('B2:B50', color_scale)
# Highlight cells with value < 50,000,000 in red
red_fill = PatternFill(start_color='FFB3B3', end_color='FFB3B3', fill_type='solid')
low_rule = CellIsRule(operator='lessThan', formula=['50000000'], fill=red_fill)
ws_out.conditional_formatting.add('B2:B50', low_rule)
I often use both together: the color scale to see the overall distribution at a glance, and CellIsRule to flag the specific cells that need immediate attention.
4. Creating Report Charts
openpyxl supports more chart types than I initially expected — bar, line, pie, scatter. For revenue reports, BarChart is more than sufficient:
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import SeriesLabel
# Assuming data is in column A (name) and B (revenue), rows 1 to 13
chart = BarChart()
chart.type = "col" # Vertical bar
chart.title = "Revenue by Branch"
chart.y_axis.title = "Revenue (VND)"
chart.x_axis.title = "Branch"
chart.style = 10 # Excel preset style
chart.width = 20
chart.height = 12
# Data series (column B)
data = Reference(ws_out, min_col=2, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
# Labels (column A)
categories = Reference(ws_out, min_col=1, min_row=2, max_row=13)
chart.set_categories(categories)
# Insert chart into sheet, starting at cell D2
ws_out.add_chart(chart, "D2")
wb_out.save('bao_cao_co_bieu_do.xlsx')
5. Auto-Fitting Column Widths
A small detail, but clients always ask why columns are cut off. This is a function I copy into every Excel script:
def auto_fit_columns(ws):
for col in ws.columns:
max_len = 0
col_letter = col[0].column_letter
for cell in col:
try:
val_len = len(str(cell.value)) if cell.value else 0
if val_len > max_len:
max_len = val_len
except:
pass
# +2 to add a bit of padding
ws.column_dimensions[col_letter].width = min(max_len + 2, 50)
auto_fit_columns(ws_out)
6. Adding Borders to the Table
Thin borders around the entire data table make it look noticeably more professional:
from openpyxl.styles import Border, Side
thin = Side(border_style='thin', color='AAAAAA')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws_out.iter_rows(min_row=1, max_row=ws_out.max_row,
min_col=1, max_col=ws_out.max_column):
for cell in row:
cell.border = border
Conclusion: What’s Worth Automating
After six months in production, openpyxl excels at three things: merging files into consolidated reports, applying consistent formatting from a template, and generating charts from data. If your task falls into one of those three categories — a 50-line script will replace two hours of manual work every time.
What I don’t use openpyxl for: complex statistical analysis (pandas is better), processing old .xls files (use xlrd), or anything requiring real pivot tables (still need to do it manually or use win32com on Windows).
If you’re spending more than an hour a week on manual Excel work — that’s your signal to write a script. I spent one afternoon writing the code the first time. That bought back two days every month ever since.
