Using XlsxWriter in Django 5 to output Excel sheets

In this tutorial we’ll use XlsxWriter Python module in order to generate Excel sheets from within a Django 5 application.

Prerequisites: A Unix-like system (Linux, macOS, or WSL) and Python 3 installed.

Let’s open the terminal and execute following command to create a new folder:

mkdir myapp

Go inside this newly created folder:

cd myapp

Then create virtual environment by executing following command:

python3 -m venv .env

Activate virtual environment:

source .env/bin/activate

(Once the virtual environment is activated, your terminal defaults to the local sandbox, meaning you can simply use python and pip instead of python3 or pip3.)

Now, install Django 5 along with XlsxWriter library by executing following command:

pip install django==5 xlsxwriter

Then create the project:

django-admin startproject project .

Next, create an app named reports:

python manage.py startapp reports

In order to define routes for our reports app, create urls.py inside reports folder by executing:

touch reports/urls.py

Then, open reports/urls.py and put following code inside it:

from django.urls import path
from . import views

app_name = "reports"
urlpatterns = [
    path('', views.reports, name='reports'),
    path('ledger', views.ledger, name='ledger'),
]

Now, let’s propagate our app’s routes by amending project/urls.py as follows:

from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path("reports/", include("reports.urls")),
    path("admin/", admin.site.urls),
]

Next, amend project/settings.py as follows, in order to add reports app into project:

INSTALLED_APPS = [
    'reports.apps.ReportsConfig',
    # ... default django apps
]

Now, let’s have our views’ logic by amending reports/views.py as follows:

from django.shortcuts import render
from . import utils

def reports(request):
	return render(request, 'reports/index.html', context={})

def ledger(request):
	response = utils.ledger()
	return response

Create another file named utils.py inside reports folder, by entering following command:

touch reports/utils.py

We’ve created the utils.py file just to keep our views.py clean. Now, insert Excel generating logic inside utils.py file:

import xlsxwriter
import io
from django.http import FileResponse

ledger_data = [
    {"date": "2026-01-01", "ref": "RV003", "description": "Sales", "debit": 1000, "credit": 0},
    {"date": "2026-01-02", "ref": "PV124", "description": "Rent", "debit": 0, "credit": 500},
    {"date": "2026-01-03", "ref": "PV128", "description": "Salary", "debit": 1000, "credit": 0},
    {"date": "2026-01-04", "ref": "PV131", "description": "Utilities", "debit": 0, "credit": 200},
]

def ledger():

    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('ledger')
    header1 = "&CThe Ledger"
    footer1 = "&LERP Report"

    worksheet.set_header(header1)
    worksheet.set_footer(footer1)

    headers = ['Date', 'Ref', 'Description', 'Debit', 'Credit', 'Running Balance']

    for col, header in enumerate(headers):
        worksheet.write(0, col, header)

    balance = 0
    ob = 0
    row_count = 3

    format1 = workbook.add_format({'num_format': 'd mmmm yyyy'})
    format2 = workbook.add_format({'num_format': '#,##0.00'})
    worksheet.set_column(0, 0, 18)

    worksheet.write(1,2, 'Opening Balance')
    worksheet.write(1,5, ob, format2)
    balance = float(ob)
    for row, data in enumerate(ledger_data, start=2):
        balance += float(data['debit']) - float(data['credit'])

        worksheet.write(row, 0, data['date'], format1)
        worksheet.write(row, 1, data['ref'])
        worksheet.write(row, 2, data['description'])
        worksheet.write(row, 3, data['debit'], format2)
        worksheet.write(row, 4, data['credit'], format2)
        worksheet.write(row, 5, balance, format2)

        row_count = row_count + 1

    total_debit = sum(float(data['debit']) for data in ledger_data)
    total_credit = sum(float(data['credit']) for data in ledger_data)

    worksheet.write(row_count, 1, 'Totals')
    worksheet.write(row_count, 3, total_debit, format2)
    worksheet.write(row_count, 4, total_credit, format2)

    worksheet.autofit()
    workbook.close()
    output.seek(0)
    return FileResponse(output, as_attachment=True, filename="ledger.xlsx")

Next, run the migrations:

python manage.py migrate

Now, let’s move on to client-side. So, create template folder reports/templates/reports by executing following command:

mkdir -p reports/templates/reports

Create base.html and index.html inside the above folder. You may execute following command to create these files in one go:

touch reports/templates/reports/{base,index}.html

Put following code inside reports/templates/reports/base.html:

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="">
    <title>{% block title %}My App{% endblock %}</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
</head>
  <body>

    <div class="container-fluid">
      <div class="row">

        <main role="main" class="col-md-9 ml-sm-auto col-lg-10 pt-3 px-4">

          {% block content %}
          {% endblock %}

        </main>
      </div>
    </div>
  
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-kenU1KFdBIe4zVF0s0G1M5b4hcpxyD9F7jL+jjXkk+Q2h455rYXK/7HAuoJl+0I4" crossorigin="anonymous"></script>
  </body>
</html>

Then, put following code inside reports/templates/reports/index.html:

{% extends "reports/base.html" %}
{% block content %}

<div class="m-4">
  <a href="{% url 'reports:ledger' %}" class="btn btn-primary btn-sm">Generate Ledger</a>
</div>

{% endblock %}

Follow the prompts to configure credentials, then boot the local engine:

python manage.py runserver

Next, go to localhost:8000/reports. You can generate Excel sheet by pressing the button.