Welcome to a tutorial on how to read an Excel file and display it in an HTML table. So you have just started with NodeJS and need to display an Excel file in the format of an HTML table? Well, let us walk through an example – Read on!
ⓘ 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
- Download and unzip into your project folder.
- Install the required modules –
npm install express ejs xlsx
- Run
node 2-server.js
and accesshttp://localhost
in your web browser.
SCREENSHOT
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.
NODEJS EXCEL TO HTML TABLE
All right, let us now get into the example of reading an Excel file and outputting it in an HTML table.
PART 1) DUMMY EXCEL FILE
Name | |
Joa Doe | joa@doe.com |
Job Doe | job@doe.com |
Joe Doe | joe@doe.com |
Jon Doe | jon@doe.com |
Joy Doe | joy@doe.com |
For this example, we will be working with this dummy list of users.
PART 2) NODEJS EXPRESS SERVER
// (A) LOAD MODULES
const xlsx = require("xlsx"),
express = require("express");
// (B) EXPRESS SERVER & EJS
const app = express();
app.set("view engine", "ejs");
// (C) SERVE DEMO PAGE
app.get("/", (req, res) => {
// (C1) OPEN EXCEL FILE - USE FIRST WORKSHEET
var workbook = xlsx.readFile("1-dummy.xlsx"),
worksheet = workbook.Sheets[workbook.SheetNames[0]],
range = xlsx.utils.decode_range(worksheet["!ref"]);
// (C2) READ EXCEL INTO OBJECT
var data = [];
for (let row=range.s.r; row<=range.e.r; row++) {
data[row] = [];
for (let col=range.s.c; col<=range.e.c; col++) {
let cell = worksheet[xlsx.utils.encode_cell({r:row, c:col})];
data[row].push(cell.v);
}
}
// (C3) RENDER HTML TEMPLATE
res.render("3-demo", { data : data });
});
// (D) START!
app.listen(80, () => console.log(`Server running at port 80`));
- Load the required modules.
- Create the Express web server. There are many HTML template engines, but we will be using EJS here.
- Take note of how this works – We will read
1-dunmy.xlsx
into an array, then pass it into the HTML template3-demo.ejs
. - Start the Express web server. Captain Obvious at your service.
PART 3) HTML TEMPLATE
<table>
<% data.forEach(row => { %>
<tr>
<% row.forEach(cell => { %>
<td><%= cell %></td>
<% }); %>
</tr>
<% }); %>
</table>
Lastly, the HTML template should be pretty self-explanatory. We are just looping through the (Excel) array, and generating the 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.
LINKS & REFERENCES
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!
Excellent post!