JavaScript/jQuery read system excel file without uploading

Today, I will tell you how to read an excel file data without uploading a file. I had searched a lot to find the solution that how can I read a server file through JS/jQuery because I don’t want to use any server-side language and neither I want to select and upload the file.

I wanted to use only JS/jQuery to make this work and decided not use a server-side language. JavsScript is most widely used as a scripting language which has a large number of the library. Then I came to know that there are some  libraries  xlsx.js,  iemagic.js,  shim.js,  jszip.js  which makes possible to read excel file. You can download Sheet.js library where you can get these files.

Here is the following code which you can test and use.

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>jQuery/Ajax Read</title>

<style>
    td,th {padding: 5px; border: 1px solid #ccc;}
</style>
</head>
<body>

    <input type="text" name="pan"> <button name="button">Submit</button>
    <br><br>

    <div class="table"></div>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="iemagic.js"></script>
    <script src="shim.js"></script>
    <script type="text/javascript" src="jszip.js"></script>
    <script type="text/javascript" src="xlsx.js"></script>

    <script>
    $(document).ready( function() {
       var data = SheetName = '';
       var fileUrl = "data.xlsx";
       var oReq;

       $('button').on('click', function(e){
         e.preventDefault();
         $('.table').text('');
         var result = false;
         var num = $('input').val();

         /* Check if data available or not */
         if( data.length < 1 ) {
           alert('Content not found!');
           return false;
         }

         /* iterating data to match id */
         for( i = 0; i < data.length; i++ ) {
           if( data[i].ID === num ) {
             toOutput( data[i] );
             result = true;
             break;
           }
         }

         /* If not informatin found */
         if( !result ) {
           html = '<p>No information found!</p>';
           $(html).appendTo( '.table' );
         }
       });

       function toOutput( data ) {
         html = '<p>We found this information: Sheet name: <b>'+SheetName+'</b></p>';
         html += '<table><tr>';
         html += '<th>ID</th>' + '<th>Name</th>' + '<th>Email</th>';
         html += '</tr>';
         html += '<tr>';
         html += '<td>' + data.ID + '</td>' + '<td>' + data.Name + '</td>' + '<td>' + data.Email + '</td>';
         html += '</tr></table>';

         $(html).appendTo( '.table' );
       }
       if(window.XMLHttpRequest) oReq = new XMLHttpRequest();
       else if(window.ActiveXObject) oReq = new ActiveXObject('MSXML2.XMLHTTP.3.0');
       else throw "XHR unavailable for your browser";

       oReq.open("GET", fileUrl, true);

       if(typeof Uint8Array !== 'undefined') {
         oReq.responseType = "arraybuffer"; // Set response type
         oReq.onload = function(e) {
           if(typeof console !== 'undefined')
           var arraybuffer = oReq.response;
           var data = new Uint8Array(arraybuffer);
           var wb = XLSX.read(data, {type:"array"}); // Read file data
           toJson(wb);
        };
      }
      else {
        oReq.setRequestHeader("Accept-Charset", "x-user-defined"); 
        oReq.onreadystatechange = function() {
        if(oReq.readyState == 4 && oReq.status == 200) {
          var ff = convertResponseBodyToText(oReq.responseBody);
          if(typeof console !== 'undefined')
            var wb = XLSX.read(ff, {type:"binary"});
            toJson(wb);
          }
        };
     }
     oReq.send(); // sends request

     function toJson(excel) {
       SheetName = excel.SheetNames[0];
       excel.SheetNames.forEach(function(sheetName) {
        var json = XLSX.utils.sheet_to_json(excel.Sheets[sheetName]); // convert sheet into json format
        if( !json.length ) return false;
        data = json;
       });
    }
});
</script>

</body>
</html>

 

If you having trouble, you can download this file jQuery-read-excel. If you have any question let me know in a comment.

 

Comments

Be the first to comment