OpenPyXL: Automate Excel

python -m pip install openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter

rows = [
    ("Product", "Units", "Unit Price"),
    ("Keyboard", 12, 79.99),
    ("Mouse", 25, 29.50),
    ("Monitor", 7, 249.00),
]

workbook = Workbook()
sheet = workbook.active
sheet.title = "Sales"

for row in rows:
    sheet.append(row)

sheet["D1"] = "Revenue"
for row_number in range(2, sheet.max_row + 1):
    sheet.cell(row=row_number, column=4, value=f"=B{row_number}*C{row_number}")
    sheet.cell(row=row_number, column=3).number_format = '$#,##0.00'
    sheet.cell(row=row_number, column=4).number_format = '$#,##0.00'

for cell in sheet[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="2563EB")

sheet.freeze_panes = "A2"
sheet.auto_filter.ref = sheet.dimensions
for column, width in enumerate((22, 12, 14, 14), start=1):
    sheet.column_dimensions[get_column_letter(column)].width = width

workbook.save("sales-report.xlsx")

from openpyxl import load_workbook

# data_only=True reads cached formula results, not formula text.
workbook = load_workbook("sales-report.xlsx", data_only=False)
sheet = workbook["Sales"]

for product, units, price, formula in sheet.iter_rows(min_row=2, values_only=True):
    print(product, units, price, formula)

# For very large workbooks:
# load_workbook("huge.xlsx", read_only=True)
# Workbook(write_only=True)