sheetjs tutorial
Excel HTML Javascript

SheetJS Tutorial – Convert Excel to HTML Table

In previous tutorial, we’ve talked about SheetJS and how to convert html table to excel file. For this tutorial, we’re going to do it backward. I’m going to show you how to convert excel file that user uploaded back to html table and show it on the webpage using SheetJS. As always – No server side script or AJAX involved. Let’s go check it out!

Here is our sample page, with input tag for user to upload the excel file.

sheet js tutorial excel to html 1

First, let’s read the file using FileReader in change event handler. We’ll read it as ArrayBuffer and pass the file object using event.target.files[0].

var reader = new FileReader();
reader.readAsArrayBuffer(e.target.files[0]);

However, since the content type of the excel is in octet stream so we need to convert what we’ve got from FileReader to UInt8Array. We can do that by passing FileReader result into UInt8Array constructor.

var data = new Uint8Array(reader.result);

Then pass the array into SheetJS read() function and let it know we’re sending in array which we’ll get the workbook object in return.

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

Now we have imported the excel file as SheetJS workbook object. Next we will use write() function to convert the it into html string. you’ll need to pass the sheet name and set the output type as binary and bookType as html.

var htmlstr = XLSX.write(wb,{sheet:"sheet no1", type:'binary',bookType:'html'});

and finally we append that html string to wherever you want to show the table!

$('#wrapper')[0].innerHTML += htmlstr;

Now, we’re ready. Here is our test excel filesheet js tutorial excel to html 2

Once I pick the excel file with browse button, the excel was immediately converted into HTML table and displayed to the webpage

sheet js tutorial excel to html 3

So that’s it! Hope you guys love the tutorial. You can find full source code below. Don’t forget to subscribe our Youtube Channel for more interesting javascript library and other tutorials!

Full Source Code

<html>
<head>
<script src="jquery-2.1.4.js"></script>
<script lang="javascript" src="xlsx.full.min.js"></script>
<link rel="stylesheet" type="text/css" href="styles.css">

</head>
<body>
<div id="navbar"><span>Red Stapler - SheetJS </span></div>
<div id="wrapper">
        
        <input type="file" id="input-excel" />

</div>
<script>
        $('#input-excel').change(function(e){
                var reader = new FileReader();
                reader.readAsArrayBuffer(e.target.files[0]);

                reader.onload = function(e) {
                        var data = new Uint8Array(reader.result);
                        var wb = XLSX.read(data,{type:'array'});

                        var htmlstr = XLSX.write(wb,{sheet:"sheet no1", type:'binary',bookType:'html'});
                        $('#wrapper')[0].innerHTML += htmlstr;
                }
        });

</script>
</body>
</html>

 

14 comments

  1. how do I load a file specified in advance? For example, I want to specify the file e.g. myFile.xls in the HTML or JavaScript instead of having the file specified by the user through “Browse” button interface.

      1. Yes,
        /* start code */
        var url = “11in02ar.xls”;

        var request = new XMLHttpRequest();

        // set request to get the specified file
        request.open(“GET”, url, true);
        request.responseType = “arraybuffer”;
        var data;
        var wb;

        request.onreadystatechange = function () {
        “use strict”;

        if (request.readyState === 4) {//request successful, file read.

        // create Table HTML code from data read from excel file
        data = new Uint8Array(request.response);
        wb = XLSX.read(data, {type: ‘array’});
        var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

        $(‘#wrapper’)[0].innerHTML += htmlstr;
        };
        /* end code */

        1. Can I use external excel files via URL?
          Like,
          var url = “https://github.com/callicoder/java-read-write-excel-file-using-apache-poi/blob/master/poi-generated-file.xlsx”;

      2. yes,

        /* start code */
        var url = “11in02ar.xls”;// replace “11in02ar.xls” with the URL to the file you want.

        var request = new XMLHttpRequest();

        // set request to get the specified file
        request.open(“GET”, url, true);
        request.responseType = “arraybuffer”;
        var data;
        var wb;

        request.onreadystatechange = function () {
        “use strict”;

        if (request.readyState === 4) {//request successful, file read.

        // create Table HTML code from data read from excel file
        data = new Uint8Array(request.response);
        wb = XLSX.read(data, {type: ‘array’});
        var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

        $(‘#wrapper’)[0].innerHTML += htmlstr;
        };

      3. var url = “11in02ar.xls”; // replace “11in02ar.xls” with your own URL.

        var request = new XMLHttpRequest();

        // set request to get the specified file
        request.open(“GET”, url, true);
        request.responseType = “arraybuffer”;
        var data;
        var wb;

        request.onreadystatechange = function () {
        “use strict”;

        if (request.readyState === 4) {//request successful, file read.

        // create Table HTML code from data read from excel file
        data = new Uint8Array(request.response);
        wb = XLSX.read(data, {type: ‘array’});
        var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

        // create table by inserting html into an element
        $(‘#wrapper’)[0].innerHTML += htmlstr;
        };

      4. var url = “11in02ar.xls”; // replace “11in02ar.xls” with your own URL.

        var request = new XMLHttpRequest();

        // set request to get the specified file
        request.open(“GET”, url, true);
        request.responseType = “arraybuffer”;
        var data;
        var wb;

        request.onreadystatechange = function () {
        “use strict”;

        if (request.readyState === 4) {//request successful, file read.

        // create Table HTML code from data read from excel file
        data = new Uint8Array(request.response);
        wb = XLSX.read(data, {type: ‘array’});
        var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

        // create table by inserting html into an element
        $(‘#wrapper’)[0].innerHTML += htmlstr;
        };

  2. On line 24,

    var htmlstr = XLSX.write(wb,{sheet:”sheet no1″, type:’binary’,bookType:’html’});

    seems to work better if the type is ‘string’, not ‘binary’. When it’s set to binary, an em dash “—” results in strange characters while

    var htmlstr = XLSX.write(wb,{sheet:”sheet no1″, type:’string’,bookType:’html’});

    renders the em dash “—” in the file correctly.

  3. var url = “11in02ar.xls”; // replace “11in02ar.xls” with your own URL.

    var request = new XMLHttpRequest();

    // set request to get the specified file
    request.open(“GET”, url, true);
    request.responseType = “arraybuffer”;
    var data;
    var wb;

    request.onreadystatechange = function () {
    “use strict”;

    if (request.readyState === 4) {//request successful, file read.

    // create Table HTML code from data read from excel file
    data = new Uint8Array(request.response);
    wb = XLSX.read(data, {type: ‘array’});
    var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

    // create table by inserting html into an element
    $(‘#wrapper’)[0].innerHTML += htmlstr;
    };

  4. The solution to my problem was

    var url = “11in02ar.xls”; // replace “11in02ar.xls” with your own URL.

    var request = new XMLHttpRequest();

    // set request to get the specified file
    request.open(“GET”, url, true);
    request.responseType = “arraybuffer”;
    var data;
    var wb;

    request.onreadystatechange = function () {
    “use strict”;

    if (request.readyState === 4) {//request successful, file read.

    // create Table HTML code from data read from excel file
    data = new Uint8Array(request.response);
    wb = XLSX.read(data, {type: ‘array’});
    var htmlstr = XLSX.write(wb, {sheet : “”, type: ‘string’, bookType: ‘html’});

    // create table by inserting html into an element
    $(‘#wrapper’)[0].innerHTML += htmlstr;
    };

  5. The following error comes on line “XLSX.read(data,{type:’array’});” :
    Uncaught ReferenceError: XLSX is not defined on drop file

Leave a Reply

Your email address will not be published. Required fields are marked *