Welcome to a tutorial on how to display an Excel file as an HTML table in Python Flask. So you want to read an Excel file and display it as an HTML table? Yes, it is actually not that difficult – Read on for the example!
ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
TABLE OF CONTENTS
DOWNLOAD & NOTES
Firstly, here is the download link to the example code as promised.
QUICK NOTES
- Create a project folder, e.g.
D:\xlstable
, unzip the code inside this folder. - Navigate to the project folder in the command line
cd D:\xlstable
, create a virtual environment to not mess up your other projects.virtualenv venv
- Windows –
venv\scripts\activate
- Mac/Linux –
venv/bin/activate
- Get all the required packages –
pip install openpyxl flask
- Launch
python S2_server.py
and accesshttp://localhost
.
EXAMPLE CODE DOWNLOAD
Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
EXCEL TO HTML TABLE
All right, let us now get into the example of reading an Excel file in Python and displaying it in an HTML table with Flask.
STEP 1) DUMMY EXCEL
Jo Doe | jo@doe.com | 465785 |
Joa Doe | joa@doe.com | 123456 |
Job Doe | job@doe.com | 234567 |
Joe Doe | joe@doe.com | 345678 |
Jog Doe | jog@doe.com | 578456 |
Joh Doe | joh@doe.com | 378945 |
Joi Doe | joi@doe.com | 456789 |
Jon Doe | jon@doe.com | 987654 |
Jor Doe | jor@doe.com | 754642 |
Joy Doe | joy@doe.com | 124578 |
For a start, here is the Excel file that we will be working with. Nothing special here… Just a list of dummy users.
STEP 2) FLASK SERVER
# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
from openpyxl import load_workbook
# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
app = Flask(__name__)
# app.debug = True
# (B) DEMO - READ EXCEL & GENERATE HTML TABLE
@app.route("/")
def index():
# (B1) OPEN EXCEL FILE + WORKSHEET
book = load_workbook("S1_dummy.xlsx")
sheet = book.active
# (B2) PASS INTO HTML TEMPLATE
return render_template("S3_excel_table.html", sheet=sheet)
# (C) START
if __name__ == "__main__":
app.run(HOST_NAME, HOST_PORT)
The Flask server script is super straightforward once you trace through it.
- Load the required modules, and define the Flask settings; Python cannot read Excel files natively, and we will be using
openpyxl
. Link to the full documentation is in the extras section below if you want to follow up. - Serve the “Excel to HTML” demo page – Open
S1_dummy.xlsx
, select the required workbook and pass it into the HTML template. - Captain Obvious at your service. Start running the Flask server.
STEP 3) HTML TEMPLATE
<table id="demo">
{% for row in range(1, sheet.max_row + 1) %}
<tr>
{% for col in range(1, sheet.max_column + 1): %}
<td>{{ sheet.cell(row, col).value }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
Don’t think this needs much explanation. We are just looping through the rows and columns of the Excel file to generate the HTML table.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
THE FIRST ROW IS THE HEADER
<table id="demo">
<!-- HEADER ROW -->
<thead><tr>
{% for col in range(1, sheet.max_column + 1): %}
<th>{{ sheet.cell(1, col).value }}</th>
{% endfor %}
</tr></thead>
<!-- DATA ROWS -->
<tbody>
{% for row in range(2, sheet.max_row + 1) %}
<tr>
{% for col in range(1, sheet.max_column + 1): %}
<td>{{ sheet.cell(row, col).value }}</td>
{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
Just draw the header row separately in the HTML template.
READING MULTIPLE SPREADSHEETS
@app.route("/")
def index():
# (B1) OPEN EXCEL FILES + WORKSHEETS
sheets = []
for n in range(2):
book = load_workbook("sheet"+str(n)+".xlsx")
sheets.append(book.active)
# (B2) PASS INTO HTML TEMPLATE
return render_template("S3_excel_table.html", sheets=sheets)
Instead of a single worksheet, pass an array of multiple worksheets into the HTML template.
{% for sheet in sheets %}
<table id="demo">
{% for row in range(1, sheet.max_row + 1) %}
<tr>
...
Loop through each worksheet, and draw the HTML table.
LINKS & REFERENCES
- Display CSV As Table In Javascript – Code Boxx
- Display CSV As Table In Python Flask – Code Boxx
- openpyxl – A Python library to read/write Excel
- How to Read Excel File In Python – Linuxhint
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
The code works fine but displays all the data causing the browser to crash for large data.
Is there a solution to this problem, such as replacing it, for example, with a search code?
There’s a limit called “system resources”, your best bet is called “don’t read everything at once” and “pagination”. Good luck!
It’s a really groundbreaking code.
I’d like to ask you two questions.
1. How should I proceed to display more than two Excels?
2. I tried to remove the nan value while proceeding with the above process, but I couldn’t read the data frame. Is there a solution?
Tutorial updated. See “READING MULTIPLE SPREADSHEETS” above.