# Lesson 11: writing formatted Excel files with openpyxl
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference
df = pd.read_csv("sales.csv")
df["amount"] = df["qty"] * df["rate"]
summary = df.groupby("region", as_index=False)["amount"].sum()
with pd.ExcelWriter("region_report.xlsx") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False)
    df.to_excel(xw, sheet_name="Data", index=False)
wb = load_workbook("region_report.xlsx"); ws = wb["Summary"]
for c in ws[1]:
    c.font = Font(bold=True, color="FFFFFF"); c.fill = PatternFill("solid", fgColor="1F6F43")
for c in ws["B"][1:]:
    c.number_format = "#,##0"
ws.column_dimensions["A"].width = 12; ws.column_dimensions["B"].width = 14
n = ws.max_row
ws.append(["Total", f"=SUM(B2:B{n})"])
ch = BarChart(); ch.title = "Sales by region"
ch.add_data(Reference(ws, min_col=2, min_row=1, max_row=n), titles_from_data=True)
ch.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n)); ws.add_chart(ch, "D2")
wb.save("region_report.xlsx")
print("Saved region_report.xlsx with", n - 1, "regions, a total formula and a chart")