Generating Styled Excel & PowerPoint Reports Automatically with Python

# pip install openpyxl python-pptx

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

from pptx import Presentation
from pptx.util import Inches, Pt
from pptx.dml.color import RGBColor


# ── shared theme ─────────────────────────────────────────────────────────
DARK   = "1A233E"
ACCENT = "E67E22"
WHITE  = "FFFFFF"
ALT_BG = "F5F0EB"

HEADER_FONT = Font(name="Calibri", bold=True, size=11, color=WHITE)
HEADER_FILL = PatternFill(start_color=DARK,   end_color=DARK,   fill_type="solid")
ALT_FILL    = PatternFill(start_color=ALT_BG, end_color=ALT_BG, fill_type="solid")
THIN_BORDER = Border(**{side: Side(style="thin", color="D0D0D0")
                        for side in ("left", "right", "top", "bottom")})


# ── Excel helpers ─────────────────────────────────────────────────────────
def style_header(ws, row: int, ncols: int) -> None:
    for col in range(1, ncols + 1):
        c = ws.cell(row=row, column=col)
        c.font, c.fill, c.border = HEADER_FONT, HEADER_FILL, THIN_BORDER
        c.alignment = Alignment(horizontal="center", wrap_text=True)


def style_data(ws, row: int, ncols: int, alt: bool = False) -> None:
    for col in range(1, ncols + 1):
        c = ws.cell(row=row, column=col)
        c.border    = THIN_BORDER
        c.alignment = Alignment(wrap_text=True, vertical="top")
        if alt:
            c.fill = ALT_FILL


def auto_width(ws, ncols: int, max_w: int = 50) -> None:
    for col in range(1, ncols + 1):
        best = max(
            (len(str(cell.value)) for row in ws.iter_rows(min_col=col, max_col=col)
             for cell in row if cell.value),
            default=10,
        )
        ws.column_dimensions[get_column_letter(col)].width = min(best, max_w) + 2


# ── write Excel workbook ──────────────────────────────────────────────────
def build_excel(rows: list[dict], output: str = "report.xlsx") -> None:
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Engagement"

    headers = ["Date", "Platform", "Likes", "Comments", "Engagement"]
    ws.append(headers)
    style_header(ws, 1, len(headers))

    for i, row in enumerate(rows, start=2):
        ws.append([row["date"], row["platform"],
                   row["likes"], row["comments"], row["engagement"]])
        style_data(ws, i, len(headers), alt=(i % 2 == 0))

    auto_width(ws, len(headers))
    wb.save(output)
    print(f"Excel saved → {output}")


# ── PowerPoint KPI card helper ────────────────────────────────────────────
def add_kpi_slide(prs: Presentation, title: str, value: str, subtitle: str) -> None:
    layout = prs.slide_layouts[6]          # blank layout
    slide  = prs.slides.add_slide(layout)
    slide.background.fill.solid()
    slide.background.fill.fore_color.rgb = RGBColor(0xF8, 0xF9, 0xFA)

    # Card shape
    card = slide.shapes.add_shape(
        1,                                  # MSO_SHAPE_TYPE.RECTANGLE
        Inches(1), Inches(1.5), Inches(8), Inches(4),
    )
    card.fill.solid()
    card.fill.fore_color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    card.line.color.rgb = RGBColor(0xE6, 0x7E, 0x22)

    tf = card.text_frame
    tf.word_wrap = True
    p1 = tf.paragraphs[0]
    r1 = p1.add_run()
    r1.text = title
    r1.font.size, r1.font.bold = Pt(18), True
    r1.font.color.rgb = RGBColor(0x1A, 0x23, 0x3E)

    p2 = tf.add_paragraph()
    r2 = p2.add_run()
    r2.text = value
    r2.font.size, r2.font.bold = Pt(48), True
    r2.font.color.rgb = RGBColor(0xE6, 0x7E, 0x22)

    p3 = tf.add_paragraph()
    r3 = p3.add_run()
    r3.text = subtitle
    r3.font.size = Pt(12)
    r3.font.color.rgb = RGBColor(0x75, 0x75, 0x75)


# ── example usage ─────────────────────────────────────────────────────────
# sample_rows = [
#     {"date": "2024-07-01", "platform": "Instagram", "likes": 212, "comments": 18, "engagement": 230},
#     {"date": "2024-07-02", "platform": "Facebook",  "likes":  88, "comments":  5, "engagement":  93},
# ]
# build_excel(sample_rows)
#
# prs = Presentation()
# add_kpi_slide(prs, "Total Engagement", "1,420", "Last 30 days across all platforms")
# prs.save("dashboard.pptx")