Excel Output with openpyxl
Phase 4 goal
Write Python that reads messy program data, transforms it with pandas, and generates finished Excel and Word deliverables (the same kind of document automation your coworker builds), reliably and repeatably.
Environment note: Use your organization's approved Python distribution if one exists. Some environments require specific installers, package mirrors, or a pre-approved package list. Packages used:
pandas,openpyxl,python-docx,matplotlib(and optionallydocxtpl). Work only with the fictional dataset while learning.
🎯 Learning objectives
Tick each one when you can actually do it.
💡 Key concepts
- pandas → Excel:
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as xw: df.to_excel(xw, sheet_name="EVM", index=False) - openpyxl basics:
load_workbook(),wb["Sheet"],ws["B2"].value,ws.cell(row, col),ws.append(list),wb.save(). - Formatting:
Font,PatternFill,Alignment,Border,number_format = '#,##0'/'0.00'/'mm/dd/yyyy',ws.column_dimensions["A"].width,ws.freeze_panes = "A2". - Excel Tables:
from openpyxl.worksheet.table import Table, TableStyleInfo(makes the output Power Automate–ready). - Conditional formatting:
CellIsRule(operator="lessThan", formula=["0.9"], fill=red_fill). - Charts:
openpyxl.chart.LineChart+Referencefor a native Excel S-curve. - Important caveats:
- openpyxl doesn't calculate formulas. It writes formula strings, and Excel calculates them when the file is opened.
load_workbook(data_only=True)reads the cached values from the last time Excel saved the file. Use it to read results, but never save over the original with it (formulas would be lost).- Templates: open the template, write values into named cells/ranges, then
save()under a new filename.
🛠 Hands-on exercise: Formatted EVM Workbook
Data: the EVM snapshot and budget DataFrames from 4.3.
- Write sheets
Summary,EVM_Detail,Budget_FYTDwith ExcelWriter. - Reopen with openpyxl: bold header row with fill, number formats (currency, 0.00 for indices, dates), auto column widths (loop over cells), freeze panes.
- Convert each data block to an Excel Table.
- Add conditional formatting on CPI/SPI (< 0.90 red, 0.90–0.95 amber).
- Add a line chart S-curve for one program on a
Chartssheet.
📦 Mini-project: Template Filler
Make an Excel template Monthly_Program_Snapshot_TEMPLATE.xlsx (your design: header block with named cells ProgramName, DataAsOf, CPI, SPI, EAC, plus a milestone table area). Write a script that generates one snapshot file per program (12 files) from the template, named PRG-001_Snapshot_2026-09.xlsx, keeping all template formatting.
🧠 Self-check quiz
Answer in your own words, then check. Grade yourself honestly; your score is saved.
=SUM(B2:B10) to a cell with openpyxl and then read it back in the same script, what do you get?load_workbook(path, data_only=True) followed by wb.save(path)?