Fetch and Read Excel Sheets Data in HTML Table with JavaScript . In today's video I will show you how to read and display Excel Sheets data in Html table using JavaScript.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Excel Sheets to HTML Table</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js" integrity="sha512-r22gChDnGvBylk90+2e/ycr3RVrDi8DIOkIGNhJlKfuyQM4tIRAI062MaV8sfjQKYVGjOBaZBOA87z+IhZE9DA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<!-- This script tag imports the xlsx.full.min.js library which provides functions to read Excel files in JavaScript. -->
<style>
tr {
background-color: gray;
}
/* This CSS selector sets the background color of every other row to light gray. */
tr:nth-child(2n) {
background-color: lightgray;
}
</style>
</head>
<body>
<div class="table"></div>
<!-- This empty div will contain the HTML table generated from the Excel file. -->
<script>
let table = document.querySelector(".table");
// This line selects the empty div with the class "table" and stores it in the table variable.
(
async() => {
// This creates an anonymous async function that immediately executes.
let workbook = XLSX.read(await (await fetch("./excel.xlsx")).arrayBuffer());
// This line uses the fetch API to download the Excel file named "excel.xlsx" and reads it using the xlsx library.
// The result is stored in the workbook variable.
console.log(workbook);
// This logs the workbook object to the console for debugging purposes.
let worksheet = workbook.SheetNames;
// The SheetNames property of the workbook object returns an array of sheet names.
// Here, we are storing the sheet names in the worksheet variable.
worksheet.forEach(name => {
// This is a forEach loop that iterates over each sheet name.
let html = XLSX.utils.sheet_to_html(workbook.Sheets[name]);
// This line converts the sheet data to HTML using the sheet_to_html() method provided by the xlsx library.
// The result is stored in the html variable.
table.innerHTML += `
<h3>${name}</h3>${html}
`;
// This line appends the sheet name and its HTML representation to the table div using template literals.
})
}
)()
// This immediately invoked function expression (IIFE) executes the code inside it as soon as it is defined.
// Here, it contains an async function that reads the Excel file and generates an HTML table from each sheet.
</script>
</body>
</html>
The code starts with the HTML boilerplate, including the DOCTYPE declaration and the opening and closing html tags.
The head section includes the meta tags that specify the character encoding, the compatibility mode, and the viewport settings for the page. It also includes a title tag that sets the title of the page to "Excel Sheets to HTML Table".
The head section also includes a script tag that loads the xlsx.full.min.js library from the cdnjs CDN. This library provides the functionality to read Excel files and convert them to HTML tables.
The style section defines some CSS rules for the table rows. The tr rule sets the background color of every other row to gray, and the tr:nth-child(2n) rule sets the background color of the even rows to light gray.
The body section includes a div element with the class "table". This is where the HTML table will be displayed.
The JavaScript code starts with an immediately invoked async function expression that reads the Excel file and converts it to an HTML table.
Inside the function, a table variable is declared and assigned to the div element with the class "table" from the HTML.
The XLSX.read() method is used to read the Excel file with the fetch() method. The arrayBuffer() method is used to convert the response to an array buffer that can be passed to the XLSX.read() method.
The console.log() method is called to print the workbook object to the console. This object contains information about the Excel file, such as the sheet names, data, and formatting.
The worksheet variable is assigned to an array of sheet names from the workbook object.
The worksheet.forEach() method is used to loop through each sheet name in the worksheet array. Inside the loop, the XLSX.utils.sheet_to_html() method is used to convert the sheet data to an HTML table.
The innerHTML property of the table element is used to add the HTML table to the page. The sheet name is added as a heading above the table.
The script ends with the closing })(); of the immediately invoked async function expression.