Excel to JSON javascript code

Question

I want to convert excel sheet data to json  It has to be dynamic  so there is an upload button where user uploads the excel sheet and the data is then converted into json  Could you please provide me the javascript code  I tried SheetJS but couldn t figure out  I would prefer something straight forward     I really appreciate your help

User · Answer

js-xlsx library makes it easy to convert Excel/CSV files into JSON objects.

Download the xlsx.full.min.js file from here. Write below code on your HTML page Edit the referenced js file link (xlsx.full.min.js) and link of Excel file

<!doctype html>
<html>

<head>
    <title>Excel to JSON Demo</title>
    <script src="xlsx.full.min.js"></script>
</head>

<body>

    <script>
        /* set up XMLHttpRequest */
        var url = "http://myclassbook.org/wp-content/uploads/2017/12/Test.xlsx";
        var oReq = new XMLHttpRequest();
        oReq.open("GET", url, true);
        oReq.responseType = "arraybuffer";

        oReq.onload = function(e) {
            var arraybuffer = oReq.response;

            /* convert data to binary string */
            var data = new Uint8Array(arraybuffer);
            var arr = new Array();
            for (var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
            var bstr = arr.join("");

            /* Call XLSX */
            var workbook = XLSX.read(bstr, {
                type: "binary"
            });

            /* DO SOMETHING WITH workbook HERE */
            var first_sheet_name = workbook.SheetNames[0];
            /* Get worksheet */
            var worksheet = workbook.Sheets[first_sheet_name];
            console.log(XLSX.utils.sheet_to_json(worksheet, {
                raw: true
            }));
        }

        oReq.send();
    </script>
</body>
</html>

Input:
Click here to see the input Excel file

Output:
Click here to see the output of above code

User · Answer

The answers are working fine with xls format but  in my case  it didn t work for xlsx format  Thus I added some code here  it works both xls and xlsx format   I took the sample from the official sample link   Hope it may help    function fileReader oEvent            var oFile   oEvent target files 0           var sFilename   oFile name           var reader   new FileReader            var result                reader onload   function  e                var data   e target result              data   new Uint8Array data               var workbook   XLSX read data   type   array                 console log workbook               var result                   workbook SheetNames forEach function  sheetName                    var roa   XLSX utils sheet to json workbook Sheets sheetName    header  1                    if  roa length  result sheetName    roa                                 see the result  caution  it works after reader event is done              console log result                      reader readAsArrayBuffer oFile         Add your id of  File Input       fileUpload   change function ev               Do something          fileReader ev

User · Answer

NOTE  Not 100  Cross Browser  Check browser compatibility   http   caniuse com  search FileReader  as you will see people have had issues with the not so common browsers  But this could come down to the version of the browser   I always recommend using something like caniuse to see what generation of browser is supported    This is only a working answer for the user  not a final copy and paste code for people to just use    The Fiddle  http   jsfiddle net d2atnbrt 3   THE HTML CODE     lt input type  file  id  my file input    gt   lt div id  my file output  gt  lt  div gt    THE JS CODE    var oFileIn     function         oFileIn   document getElementById  my file input        if oFileIn addEventListener            oFileIn addEventListener  change   filePicked  false               function filePicked oEvent           Get The File From The Input     var oFile   oEvent target files 0       var sFilename   oFile name         Create A File Reader HTML5     var reader   new FileReader            Ready The Event For When A File Gets Selected     reader onload   function e            var data   e target result          var cfb   XLS CFB read data   type   binary             var wb   XLS parse xlscfb cfb              Loop Over Each Sheet         wb SheetNames forEach function sheetName                   Obtain The Current Row As CSV             var sCSV   XLS utils make csv wb Sheets sheetName                   var oJS   XLS utils sheet to row object array wb Sheets sheetName                        my file output   html sCSV               console log oJS                             Tell JS To Start Reading The File   You could delay this if desired     reader readAsBinaryString oFile       This also requires https   cdnjs cloudflare com ajax libs xls 0 7 4-a xls js to convert to a readable format  i ve also used jquery only for changing the div contents and for the dom ready event   so jquery is not needed  This is as basic as i could get it    EDIT - Generating A Table  The Fiddle  http   jsfiddle net d2atnbrt 5   This second fiddle shows an example of generating your own table   the key here is using sheet to json to get the data in the correct format for JS use     One or two comments in the second fiddle might be incorrect as modified version of the first fiddle   the CSV comment is at least  Test XLS File  http   www whitehouse gov sites default files omb budget fy2014 assets receipts xls  This does not cover XLSX files thought  it should be fairly easy to adjust for them using their examples

User · Answer

Kwang-Chun Kang Thanks Kang a lot  I found the solution is working and very helpful  it really save my day  For me I am trying to create a React js component that convert   xlsx to json object when user upload the excel file to a html input tag  First I need to install XLSX package with   npm install xlsx --save   Then in my component code  import with   import XLSX from  xlsx    The component UI should look like this    lt input   accept   xlsx    type  file    onChange  this fileReader    gt    It calls a function fileReader    which is exactly same as the solution provided  To learn more about fileReader API  I found this blog to be helpful      https   blog teamtreehouse com reading-files-using-the-html5-filereader-api

[javascript] Excel to JSON javascript code?

Examples related to javascript

Examples related to html