Excel Report Generator using Python and Streamlit

Introduction

Every office runs on Excel reports, and every office person hates building them by hand. This app takes raw data and produces a formatted, multi-sheet Excel workbook — styled headers, conditional color coding, column widths that fit, and a summary sheet — downloadable in one click. It is the reporting counterpart to the CSV explorer: that app reads data, this one ships it.

The engine is openpyxl, the library Pandas uses under the hood for .to_excel — but we drop down one level to control styling, which is where the professional look comes from.

Features

• Multi-sheet workbooks — raw data + summary + chart data.
• Styled headers — bold white text on a dark fill, frozen panes.
• Conditional formatting — red/green cells based on thresholds.
• Auto column widths — no more squinting at truncated text.
• Instant download — workbook served via st.download_button.

Prerequisites

• Python 3.8+ — from python.org.
• Dependencies:

pip install streamlit pandas openpyxl

Step 1: Create the Script

Save as excel_report.py:

import streamlit as st
import pandas as pd
import numpy as np
from io import BytesIO
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

st.set_page_config(page_title="Excel Report Generator", page_icon="📊")
st.title("📊 Excel Report Generator")

uploaded = st.file_uploader("Upload a CSV to report on", type=["csv"])

if uploaded:
    df = pd.read_csv(uploaded)
    numeric_cols = df.select_dtypes("number").columns.tolist()
    st.dataframe(df.head(), use_container_width=True)

    threshold = None
    if numeric_cols:
        watch_col = st.selectbox("Highlight column", numeric_cols)
        threshold = st.number_input("Highlight cells above", value=float(df[watch_col].mean()))

    if st.button("📈 Generate Excel report", type="primary"):
        buffer = BytesIO()
        with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
            df.to_excel(writer, sheet_name="Data", index=False)
            df.describe().to_excel(writer, sheet_name="Summary")

            sheet = writer.sheets["Data"]
            header_fill = PatternFill("solid", fgColor="0F766E")
            for cell in sheet[1]:
                cell.font = Font(bold=True, color="FFFFFF")
                cell.fill = header_fill
                cell.alignment = Alignment(horizontal="center")

            for i, col in enumerate(df.columns, 1):
                width = max(df[col].astype(str).str.len().max(), len(col)) + 3
                sheet.column_dimensions[get_column_letter(i)].width = min(width, 45)
            sheet.freeze_panes = "A2"

            if threshold is not None:
                col_idx = df.columns.get_loc(watch_col) + 1
                fill_hi = PatternFill("solid", fgColor="FEE2E2")
                for row in range(2, len(df) + 2):
                    cell = sheet.cell(row=row, column=col_idx)
                    if isinstance(cell.value, (int, float)) and cell.value > threshold:
                        cell.fill = fill_hi

        st.download_button(
            "⬇️ Download report.xlsx",
            buffer.getvalue(),
            "report.xlsx",
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )
        st.success("Report ready!")

Step 2: Run the App

streamlit run excel_report.py

Upload a CSV, pick a column to highlight, generate — open the workbook and check the styling, the Summary sheet, and the frozen header row.

How It Works

pd.ExcelWriter is a context manager wrapping a workbook: everything written inside the with block lands in the buffer, and the file finalizes on exit — that finalization is why the download button must read the buffer after the block. Two sheets come from plain to_excel calls; everything else is openpyxl styling applied to the sheets Pandas created.

The styling loop shows the two idioms you need for 90% of Excel polish. Cell-level formatting: iterate sheet[1] (row 1) applying font and fill to each header cell. Sheet-level formatting: column_dimensions[letter].width computed from actual content length — max(len(column values), len(header)) clamped to 45 — which makes reports readable without manual fiddling. freeze_panes = "A2" pins the header row.

Conditional highlighting walks the chosen column’s cells by row and fills the ones above threshold — the same threshold logic as the BMI calculator’s category ladder, pointed at spreadsheet cells.

Common Errors & Fixes

• KeyError: 'Worksheet Data does not exist' — you styled before writing; all to_excel calls must come before accessing writer.sheets.
• Downloaded file is corrupt — the buffer was read inside the with block before finalization; move buffer.getvalue() after the block.
• openpyxl value errors on mixed types — a column with strings and numbers confuses cell writing; clean dtypes first (pd.to_numeric(..., errors="coerce")).
• Column widths ignore your numbers — widths are in character units, not pixels; the +3 padding and cap of 45 are tuned for default fonts.

Key Concepts

• ExcelWriter as a transaction — write sheets inside, style inside, finalize after.
• sheet[1] row access — openpyxl cells are addressable like 2D arrays.
• Content-based widths — measure data, set width once.
• In-memory xlsx — BytesIO makes workbooks downloadable without touching disk.

What to Try Next

• Add a chart sheet — openpyxl’s LineChart anchored below the data.
• Add cell borders and zebra striping with PatternFill on alternate rows.
• Email the report automatically — the email automation tutorial covers SMTP.
• Schedule nightly report generation on your home server with cron.

FAQ

Can it write .xls (old format)?

No — openpyxl writes .xlsx only. The old format is obsolete; if a system demands it, write .xlsx and let the user save-as.

Why openpyxl instead of pandas alone?

to_excel writes data but no styling. The moment a report needs headers, colors, or widths — i.e., to look professional — you want openpyxl’s object model.

How do I add a title row above the headers?

Insert a row (sheet.insert_rows(1)), merge cells across the width, set the value and a larger font — then shift your freeze pane to A3.