Generate an HTML Report from Excel Data with Jinja2

A pandas table converted with to_html() is a table, not a report. A report has a title and date, a row of headline figures, a section per region with its own table and chart, notes, and a consistent look from month to month. Building that HTML with string concatenation in Python gets unreadable within a page. Jinja2 separates the two jobs: Python reads the workbook and computes everything, and a template file — HTML with a few loops and placeholders — decides how it looks. The result is a single .html file that opens in any browser, can be attached to an email or published to an intranet, and needs no server. This page builds such a report, covering escaping, number formatting, CSS bar charts, shared layouts and tests. It belongs to Generating Excel Reports from Templates.

Prerequisites

pip install pandas openpyxl jinja2 lxml

sales.xlsx has one row per region and product with units and revenue. One product name, Gloves <bulk>, contains angle brackets — the kind of cell text that breaks naive HTML generation. lxml is used only in the test at the end.

Step 1 — Shape the data in Python

The template should not do arithmetic. Compute every number it will show — totals, shares, the largest value for scaling bars — and pass plain Python structures:

import pandas as pd
from datetime import date

sales = pd.read_excel("sales.xlsx", sheet_name="Sales")
total = sales["Revenue"].sum()

regions = []
for name, grp in sales.groupby("Region", sort=False):
    rows = grp.sort_values("Revenue", ascending=False).to_dict("records")
    regions.append({
        "name": name,
        "revenue": grp["Revenue"].sum(),
        "share": grp["Revenue"].sum() / total,
        "rows": rows,
        "max": grp["Revenue"].max(),
    })

context = {
    "title": "Monthly Sales Report",
    "period": "September 2026",
    "generated": date(2026, 10, 2),
    "kpis": [
        {"label": "Revenue", "value": total, "fmt": "money"},
        {"label": "Units", "value": int(sales["Units"].sum()), "fmt": "int"},
        {"label": "Regions", "value": sales["Region"].nunique(), "fmt": "int"},
    ],
    "regions": regions,
}
print([(r["name"], f"{r['share']:.1%}") for r in regions])

to_dict("records") turns each group into a list of dictionaries, which Jinja2 loops over naturally. Keeping the calculations here means they can be unit-tested without rendering anything, and the template stays readable by someone who knows HTML but not pandas.

Step 2 — Set up the environment with escaping and filters

A Jinja2 Environment loads templates from a folder. Three settings matter for reports: autoescaping, strict handling of missing values, and custom filters for number formats:

from pathlib import Path
from jinja2 import Environment, FileSystemLoader, StrictUndefined, select_autoescape

Path("templates").mkdir(exist_ok=True)

env = Environment(
    loader=FileSystemLoader("templates"),
    autoescape=select_autoescape(["html"]),
    undefined=StrictUndefined,
    trim_blocks=True, lstrip_blocks=True,
)
env.filters["money"] = lambda v: f"€{v:,.0f}"
env.filters["int"] = lambda v: f"{v:,}"
env.filters["pct"] = lambda v: f"{v:.0%}"
print(env.filters["money"](23072.0), env.filters["pct"](0.4069))

select_autoescape(["html"]) escapes every value inserted into a .html template, so Gloves <bulk> becomes Gloves <bulk> and displays as written instead of being parsed as a tag. Without it, any cell containing <, & or a stray <script> would corrupt or compromise the page. StrictUndefined raises an error when the template uses a name the context does not contain, instead of silently rendering an empty string — a renamed column then fails the build rather than producing a report with blank figures. The filters keep formatting decisions in one place: {{ value | money }} everywhere money appears.

Step 3 — Write a base layout and a report template

Template inheritance lets several reports share one layout. The base defines the page skeleton and styles, with blocks that child templates fill:

Path("templates/base.html").write_text("""<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>{{ title }} · {{ period }}</title>
<style>
  body { font-family: system-ui, sans-serif; margin: 2rem auto; max-width: 860px; color: #172033; padding: 0 16px; }
  .kpis { display: flex; gap: 1rem; flex-wrap: wrap; }
  .kpi { background: #EEF2FF; border-radius: 10px; padding: .8rem 1.2rem; }
  .kpi b { display: block; font-size: 1.5rem; color: #4338CA; }
  table { border-collapse: collapse; width: 100%; margin: .5rem 0 1.5rem; }
  th, td { padding: .35rem .6rem; border-bottom: 1px solid #E2E8F0; text-align: left; }
  td.num, th.num { text-align: right; font-variant-numeric: tabular-nums; }
  footer { color: #5B6780; font-size: .85rem; }
  .bar-row { display: flex; align-items: center; gap: .5rem; margin: .2rem 0; max-width: 420px; }
  .bar-row span { flex: 0 0 110px; font-size: .8rem; }
  .bar { display: block; height: 14px; background: #4338CA; border-radius: 3px;
         -webkit-print-color-adjust: exact; print-color-adjust: exact; }
</style>
</head>
<body>
<header><h1>{{ title }}</h1><p>{{ period }}</p></header>
{% block content %}{% endblock %}
<footer>Generated {{ generated.strftime("%d %B %Y") }} from sales.xlsx</footer>
</body>
</html>
""", encoding="utf-8")
print("base.html written")

All styles are inline in a <style> element and there are no external scripts, fonts or images, so the single file renders the same when opened from an email attachment or a file share, offline. The viewport meta tag and the flexible KPI row keep it readable on a phone.

Step 4 — Loops, sections and CSS bar charts

The report template extends the base and fills the content block. Each region gets a heading, a table and a small horizontal bar chart drawn with plain HTML and CSS — a div per product whose width is a percentage — so there is no charting library and no JavaScript, and it prints cleanly:

Path("templates/report.html").write_text("""{% extends "base.html" %}
{% block content %}
<section class="kpis">
{% for k in kpis %}
  <div class="kpi">{{ k.label }}<b>{{ k.value | money if k.fmt == "money" else k.value | int }}</b></div>
{% endfor %}
</section>
{% for r in regions %}
<section>
  <h2>{{ r.name }} — {{ r.revenue | money }} ({{ r.share | pct }} of total)</h2>
  <div class="bars" role="img" aria-label="Revenue by product in {{ r.name }}">
  {% for row in r.rows %}
    <div class="bar-row"><span>{{ row.Product }}</span>
      <i class="bar" style="width: {{ (row.Revenue / r.max * 100) | round(1) }}%"></i></div>
  {% endfor %}
  </div>
  <table>
    <thead><tr><th>Product</th><th class="num">Units</th><th class="num">Revenue</th></tr></thead>
    <tbody>
    {% for row in r.rows %}
      <tr><td>{{ row.Product }}</td><td class="num">{{ row.Units | int }}</td><td class="num">{{ row.Revenue | money }}</td></tr>
    {% endfor %}
    </tbody>
  </table>
</section>
{% endfor %}
{% endblock %}
""", encoding="utf-8")

html = env.get_template("report.html").render(**context)
Path("report.html").write_text(html, encoding="utf-8")
print(len(html), "characters;", html.count("<section"), "sections (KPI row + 3 regions)")

{% extends %} pulls in the base layout, and {% block content %} replaces its empty block. Each bar's width is the product's revenue as a percentage of the region's largest, computed in the template with round — simple presentation arithmetic is fine there; business logic is not. The bar block carries role="img" and an aria-label so screen readers announce what it shows, and print-color-adjust: exact stops browsers dropping the bar colour when printing. A second report — say, a stock report — can extend the same base.html and get the same header, footer and styles for free.

Step 5 — Test the rendered report

Because the output is HTML, it can be parsed and checked like data. A test confirms the escaping, the figures and the structure:

from lxml import html as lh

doc = lh.fromstring(Path("report.html").read_text(encoding="utf-8"))
print("title:", doc.findtext(".//title"))
print("KPIs:", [" ".join(k.itertext()).strip() for k in doc.xpath('//div[@class="kpi"]')])
print("sections:", [h.text_content() for h in doc.xpath("//h2")])
print("escaped product kept as text:", "Gloves <bulk>" in doc.text_content(), "| raw tag in page:", "<bulk>" in Path("report.html").read_text())
print("bars per region:", [len(s.xpath('.//i[@class="bar"]')) for s in doc.xpath("//section[h2]")])

The KPI cards read Revenue €38,072, Units 934 and Regions 3, each region heading shows its revenue and share, and every region has one bar per product. The product Gloves <bulk> appears as text, while the raw string <bulk> does not occur in the HTML source — it was escaped to <bulk>. A test like this, run against a small fixture workbook, catches a template change that drops a section or a filter that formats money wrongly.

Delivering the report

The single file can go wherever a document goes. Attach it to an email, or put the key figures in the email body and attach the full report — see Email an Excel Report with an HTML Summary Body, which covers the inline-style restrictions of email clients. Upload it to a bucket or intranet share for a link everyone can open. To produce a PDF from the same HTML, print it from a headless browser; the CSS bars print as crisply as the text around them. And if readers also need the data itself, render the HTML and write the workbook in the same run, so the two never disagree.

For simple tables without sections, a pandas Styler may be enough — see Convert an Excel Sheet to an HTML Table. Reach for Jinja2 templates when the page has structure beyond one table.

Common pitfalls

• Autoescape off. Cell text with < or & breaks the page or injects markup; use select_autoescape.
• Silent blanks. The default undefined renders missing names as empty strings; use StrictUndefined.
• Business logic in the template. Compute totals and shares in Python where they can be tested.
• External assets. Linked CSS, fonts or scripts break when the file is opened offline; inline them.
• Formatting numbers in many places. Register filters once and use them everywhere.

Conclusion

To generate an HTML report from Excel, read and summarise the workbook with pandas into a context of plain dictionaries, and render it with a Jinja2 template file. Turn on autoescaping so cell text cannot break the page, use StrictUndefined so missing values fail loudly, register filters for money and percentages, share a base layout through template inheritance, and draw small charts with HTML and CSS so the report stays a single self-contained file. Parse the output in a test to confirm the figures and structure.

Frequently asked questions

How do I create an HTML report from an Excel file in Python? Read the workbook with pandas, compute the figures into a dictionary, and render a Jinja2 template with Environment.get_template and render. Write the resulting string to an .html file.

Why should I turn on autoescaping in Jinja2? It converts characters such as less-than and ampersand in cell values into HTML entities, so text displays as typed and cannot inject tags or scripts into the report.

How do I add charts to an HTML report without JavaScript? Draw them with HTML and CSS in the template, for example a div per value whose width is a percentage of the largest value. They render in any browser and print cleanly.

How do I make Jinja2 fail when a value is missing? Create the Environment with undefined set to StrictUndefined. Using a name that is not in the context then raises an error instead of rendering an empty string.

Related

• Up one level: Generating Excel Reports from Templates — documents built from spreadsheet data.
• Generate Word Documents from Excel Rows — the same idea for Word files.
• Convert an Excel Sheet to a LaTeX Table — another template-driven output format.
• Print Mailing Labels from an Excel Address List — a PDF output from the same kind of data.