Import Excel data into HTML table using JQuery
To import Excel data into an HTML table using jQuery, we can use the “SheetJS” library, which allows us to read Excel files and extract the data. We’ll also use jQuery to manipulate the DOM and populate the HTML table with the data.
Here’s a step-by-step guide to import Excel data into an HTML table using jQuery and SheetJS:
- Download the “SheetJS” library from the GitHub repository: https://github.com/SheetJS/js-xlsx
- Create the HTML structure with an empty table:
<!DOCTYPE html>
<html>
<head>
<title>Import Excel Data into HTML Table</title>
<link rel="stylesheet" href="styles.css">
</head>
<body>
<input type="file" id="fileInput">
<table id="dataTable">
<thead>
<tr>
<th>Name</th>
<th>Age</th>
<th>Department</th>
<th>Salary</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="xlsx.full.min.js"></script>
<script src="script.js"></script>
</body>
</html>- Implement the JavaScript (script.js):
$(document).ready(function() {
$('#fileInput').on('change', function(e) {
const file = e.target.files[0];
const reader = new FileReader();
reader.onload = function(event) {
const data = event.target.result;
const workbook = XLSX.read(data, { type: 'binary' });
const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];
const jsonData = XLSX.utils.sheet_to_json(sheet);
populateTable(jsonData);
};
reader.readAsBinaryString(file);
});
function populateTable(data) {
const tableBody = $('#dataTable tbody');
tableBody.empty();
data.forEach(function(row) {
const newRow = $('<tr>');
newRow.append(`<td>${row.Name}</td>`);
newRow.append(`<td>${row.Age}</td>`);
newRow.append(`<td>${row.Department}</td>`);
newRow.append(`<td>${row.Salary}</td>`);
tableBody.append(newRow);
});
}
});- Create the CSS (styles.css) – Optional:
You can customize the CSS to style the table and other elements on the page as desired.
In this example, we use jQuery to detect changes in the file input (#fileInput) and read the uploaded Excel file using the “SheetJS” library. The FileReader is used to read the file as a binary string, and then the “SheetJS” library converts the binary data into a JSON object representing the Excel data.
The populateTable() function takes the JSON data and populates the HTML table with it. Each object in the JSON array corresponds to a row in the table.
With this code, you can upload an Excel file using the file input, and the data from the Excel file will be imported into the HTML table dynamically.
Remember to place the “xlsx.full.min.js” and “SheetJS” files in the same directory as your HTML file for the code to work correctly.
Please note that this implementation only works for Excel files with a single sheet. If you need to handle multiple sheets or more complex Excel files, you may need to adjust the code accordingly.