CODEWITHSUNDEEP
javascripthtml
Programmer
Programmer

Reputation: 437

Excel to JSON javascript code?

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!

Upvotes: 34

Views: 217050

Answers (5)

user7456320
user7456320

Reputation: 181

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 the Excel file

<!doctype html>
<html>

<head>
    <title>Excel to JSON Demo</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</head>

<body>
    <input type="file" id="data" accept=".xlsx" />
    <script>
        document.querySelector('input').addEventListener('change', function() {
            var reader = new FileReader();
            reader.onload = function() {
                var arrayBuffer = this.result,
                    array = new Uint8Array(arrayBuffer),
                    binaryString = String.fromCharCode.apply(null, array);
                /* 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(binaryString, {
                    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();
            }
            reader.readAsArrayBuffer(this.files[0]);
        });
    </script>
</body>

</html>

Input:
Click here to see the input Excel file

Output:
Click here to see the output of above code

Upvotes: 18

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2791

This is my expansion on https://stackoverflow.com/a/52237535/5079799

While it was working/great example, I'm not using an input form, but fetching from a URL and I have other things to do after fething workbook so I needed to wrap the onload into a promise.

See --> adjust onload function to be used with async/await

Here is what I ended up w/

async function Outside_Test(){
    var reso = await Get_JSON()
    console.log('reso_out')
    console.log(reso)
}

async function Get_JSON() {
    var url = "http://MyworkbookURL"
    var workbook = await Get_XLSX_As_Workbook_From_URL(url)

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

async function Get_XLSX_As_Workbook_From_URL(url) {
    const arrayBuffer = await new Promise((resolve, reject) => {
        var oReq = new XMLHttpRequest();
        oReq.open("GET", url, true);
        oReq.responseType = "arraybuffer";
        oReq.onload = () => resolve(oReq.response);
        oReq.onerror = reject;
        oReq.send();
    });
    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("");
    var workbook = XLSX.read(bstr, {
        type: "binary"
    });
    return workbook
}

Upvotes: 0

Angry 84
Angry 84

Reputation: 2995

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: 

<input type="file" id="my_file_input" />
<div id='my_file_output'></div>

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.

Upvotes: 39

Yuhang Zhou
Yuhang Zhou

Reputation: 41

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

<input
  accept=".xlsx"
  type="file"
  onChange={this.fileReader}
/>

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

Upvotes: 4

Kwang-Chun Kang
Kwang-Chun Kang

Reputation: 351

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);
}

Upvotes: 8

Related Questions