How to Read Data From Excel Sheet Using Html
In this tutorial you tin find how to read excel file using JavaScript and display excel sheet data on web page in HTML table format using JavaScript. In previous, one of our tutorial, in which we have already seen how to convert HTML table data into Excel file by using SheetJS library. Now in this tutorial also we will use SheetJS JavaScript library and by using JavaScript library, we will convert Excel file data to HTML table and display on spider web page. Hither under this tutorial, nosotros will not employ jQuery and Ajax server side script for fetch data from excel and display on web folio. Now lets kickoff seeing it!
First we have to include Bootstrap Stylesheet and SheetJS library link at header of our HTML folio.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous"> <script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script> Subsequently this nether this HTML page, we take to create ane file tag for select file excel from local computer.
<input type="file" id="excel_file" /> And below this file, nosotros have to create one division tag for brandish excel sheet information on web page in HTML table format.
<div id="excel_data" grade="mt-5"></div> Next we have to move on write JavaScript lawmaking, so get-go store file tag belongings under one variable.
const excel_file = document.getElementById('excel_file'); Side by side we have to write javascript code on alter upshot, so when user have select file from local estimator using file tag, so javascript lawmaking must exist execute.
excel_file.addEventListener('change', (event) => { }); Under this change event code first we want to bank check selected file format is .xls or .xlsx. If selected file is not excel file then it will display error on web page, and if select file is excel then information technology will proceed for display excel file data on web page.
if(!['application/vnd.openxmlformats-officedocument.spreadsheetml.canvas', 'application/vnd.ms-excel'].includes(event.target.files[0].type)) { certificate.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Only .xlsx or .xls file format are allowed</div>'; excel_file.value = ''; return imitation; } Afterwards cheque validation error, now read the file using FileReader object. Hither file must be read ads ArrayBuffer past pass the file object using event.target.files[0].
var reader = new FileReader(); reader.readAsArrayBuffer(effect.target.files[0]); IF the selected file is proper excel file and so we need to catechumen what we have got from FileReader object to Unit8Array object by passing Filereader outcome into Unit8Array constructor.
var data = new Uint8Array(reader.effect); Side by side nosotros have pass this Unit8Array data in SheetJS read() function, and information technology will return selected excel workbook object.
var work_book = XLSX.read(information, {blazon:'array'}); Later getting workbook object, next we accept to go sheet name of selected excel file. So here SheetNames variable will return sheet proper noun in array format.
var sheet_name = work_book.SheetNames; Once we accept get canvass name, now we desire to go first sheet data in JSON format, so this nosotros can get past SheetJS sheet_to_json() part past passing workbook get-go canvass name.
var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]], {header:1}); Once we take get first sail data in JSON format, adjacent we have to just write JavaScript code and catechumen that JSON data into HTML format and display under partition tag wih id excel_data. So information technology will brandish excel file data on web page in HTML tabular array format.
if(sheet_data.length > 0) { var table_output = '<table class="tabular array table-striped table-bordered">'; for(var row = 0; row < sheet_data.length; row++) { table_output += '<tr>'; for(var cell = 0; cell < sheet_data[row].length; cell++) { if(row == 0) { table_output += '<th>'+sheet_data[row][cell]+'</thursday>'; } else { table_output += '<td>'+sheet_data[row][cell]+'</td>'; } } table_output += '</tr>'; } table_output += '</table>'; document.getElementById('excel_data').innerHTML = table_output; } So once you have follow all above steps then you lot can bank check ouput in browser. So when we have select excel file and then it will display excel sail data on web folio in HTML table format without refresh of web page. So in this tutorial, we have seen how to convert Excel file to HTML tabular array at client-side by using SheetJS JavaScript library at client-side. Below you can observe complete source code.
Full Source Code
<!DOCTYPE HTML> <html> <head> <meta charset="utf-8" /> <title>Convert Excel to HTML Tabular array using JavaScript</championship> <meta name="viewport" content="width=device-width, initial-scale=i" /> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="bearding"> <script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script> </head> <body> <div class="container"> <h2 class="text-heart mt-iv mb-4">Convert Excel to HTML Table using JavaScript</h2> <div course="card"> <div class="menu-header"><b>Select Excel File</b></div> <div grade="bill of fare-body"> <input blazon="file" id="excel_file" /> </div> </div> <div id="excel_data" form="mt-5"></div> </div> </torso> </html> <script> const excel_file = document.getElementById('excel_file'); excel_file.addEventListener('modify', (event) => { if(!['application/vnd.openxmlformats-officedocument.spreadsheetml.sail', 'application/vnd.ms-excel'].includes(upshot.target.files[0].type)) { document.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Simply .xlsx or .xls file format are allowed</div>'; excel_file.value = ''; return imitation; } var reader = new FileReader(); reader.readAsArrayBuffer(event.target.files[0]); reader.onload = role(event){ var data = new Uint8Array(reader.result); var work_book = XLSX.read(information, {type:'array'}); var sheet_name = work_book.SheetNames; var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]], {header:1}); if(sheet_data.length > 0) { var table_output = '<tabular array class="table table-striped table-bordered">'; for(var row = 0; row < sheet_data.length; row++) { table_output += '<tr>'; for(var cell = 0; cell < sheet_data[row].length; prison cell++) { if(row == 0) { table_output += '<th>'+sheet_data[row][prison cell]+'</th>'; } else { table_output += '<td>'+sheet_data[row][cell]+'</td>'; } } table_output += '</tr>'; } table_output += '</tabular array>'; document.getElementById('excel_data').innerHTML = table_output; } excel_file.value = ''; } }); </script> Source: https://www.webslesson.info/2021/07/how-to-display-excel-data-in-html-table.html
0 Response to "How to Read Data From Excel Sheet Using Html"
Post a Comment