Monday 26 July 2021

How to Display Excel Data in HTML Table using JavaScript


In this tutorial you can 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 web page. Here under this tutorial, we will not use jQuery and Ajax server side script for fetch data from excel and display on web page. Now lets start seeing it!


How to Display Excel Data in HTML Table using JavaScript





First we have to include Bootstrap Stylesheet and SheetJS library link at header of our HTML page.


<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>


After this under this HTML page, we have to create one file tag for select file excel from local computer.


<input type="file" id="excel_file" />


And below this file, we have to create one division tag for display excel sheet data on web page in HTML table format.


<div id="excel_data" class="mt-5"></div>


Next we have to move on write JavaScript code, so first store file tag property under one variable.


const excel_file = document.getElementById('excel_file');


Next we have to write javascript code on change event, so when user have select file from local computer using file tag, then javascript code must be execute.


excel_file.addEventListener('change', (event) => {

});


Under this change event code first we want to 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 it will proceed for display excel file data on web page.


if(!['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'].includes(event.target.files[0].type))
    {
        document.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Only .xlsx or .xls file format are allowed</div>';

        excel_file.value = '';

        return false;
    }


After check validation error, now read the file using FileReader object. Here file must be read ads ArrayBuffer by pass the file object using event.target.files[0].


var reader = new FileReader();

reader.readAsArrayBuffer(event.target.files[0]);


IF the selected file is proper excel file then we need to convert what we have got from FileReader object to Unit8Array object by passing Filereader result into Unit8Array constructor.


var data = new Uint8Array(reader.result);


Next we have pass this Unit8Array data in SheetJS read() function, and it will return selected excel workbook object.


var work_book = XLSX.read(data, {type:'array'});


After getting workbook object, next we have to get sheet name of selected excel file. So here SheetNames variable will return sheet name in array format.


var sheet_name = work_book.SheetNames;


Once we have get sheet name, now we want to get first sheet data in JSON format, so this we can get by SheetJS sheet_to_json() function by passing workbook first sheet name.


var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]], {header:1});


Once we have get first sheet data in JSON format, next we have to simply write JavaScript code and convert that JSON data into HTML format and display under division tag wih id excel_data. So it will display excel file data on web page in HTML table format.


        if(sheet_data.length > 0)
        {
            var table_output = '<table 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; cell++)
                {

                    if(row == 0)
                    {

                        table_output += '<th>'+sheet_data[row][cell]+'</th>';

                    }
                    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 can check ouput in browser. So when we have select excel file then it will display excel sheet data on web page in HTML table format without refresh of web page. So in this tutorial, we have seen how to convert Excel file to HTML table at client-side by using SheetJS JavaScript library at client-side. Below you can find complete source code.

Full Source Code



<!DOCTYPE HTML>
<html>
<head>
	<meta charset="utf-8" />
	<title>Convert Excel to HTML Table using JavaScript</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
    <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>
</head>
<body>
    <div class="container">
    	<h2 class="text-center mt-4 mb-4">Convert Excel to HTML Table using JavaScript</h2>
    	<div class="card">
    		<div class="card-header"><b>Select Excel File</b></div>
    		<div class="card-body">
    			
                <input type="file" id="excel_file" />

    		</div>
    	</div>
        <div id="excel_data" class="mt-5"></div>
    </div>
</body>
</html>

<script>

const excel_file = document.getElementById('excel_file');

excel_file.addEventListener('change', (event) => {

    if(!['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'].includes(event.target.files[0].type))
    {
        document.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Only .xlsx or .xls file format are allowed</div>';

        excel_file.value = '';

        return false;
    }

    var reader = new FileReader();

    reader.readAsArrayBuffer(event.target.files[0]);

    reader.onload = function(event){

        var data = new Uint8Array(reader.result);

        var work_book = XLSX.read(data, {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 = '<table 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; cell++)
                {

                    if(row == 0)
                    {

                        table_output += '<th>'+sheet_data[row][cell]+'</th>';

                    }
                    else
                    {

                        table_output += '<td>'+sheet_data[row][cell]+'</td>';

                    }

                }

                table_output += '</tr>';

            }

            table_output += '</table>';

            document.getElementById('excel_data').innerHTML = table_output;
        }

        excel_file.value = '';

    }

});

</script>




14 comments:

  1. how to convert csv file into html using javascript

    ReplyDelete
  2. Thank you for sharing sir!

    ReplyDelete
  3. Hi, I've found this code very helpful. Is there a way the generated HTML table data can be imported to a mysql database table?

    ReplyDelete
    Replies
    1. Please if you found the way, can you tell me?

      Delete
  4. Can you tell me how this would work if you wanted to use a predefined xcel sheet as opposed to using the input to upload a file?

    ReplyDelete
    Replies
    1. I also have the same query. Would be grateful for any help with this.

      Delete
    2. How to predefine an excel sheet and executing the function on load rather than uploading the file?

      Delete
    3. Anch'io ho lo stesso problema. Se lo hai risolto, puoi dirmi come? Grazie.

      Delete
  5. How can I use a spesific excel link to convert? Not uploading file.

    ReplyDelete
  6. How do i display the excel sheet without having to input a file on the client end... from backend

    ReplyDelete