Javascript Excel to JSON Project to Build Countries Select Dropdown Using AJAX & jQuery in Browser

Welcome folks today in this blog post we will be generating a countries select dropdown by exporting the data present inside the excel file to json. And then we will be displaying the data in a select field.

Get Started

In order to get started we need to create an index.html file and copy paste the below code

index.html

<div>
        <input type="button" value="Create Select">
        <div class="countries"></div>
    </div>

<div>

</div>

As you can see we have a simple button to generate the select dropdown of countries. And then we a simple div where we will be showing the select dropdown list of countries. And now we need to apply some custom css as shown below

style.css

 input {
        width: 200px;
        height: 40px;
    }

    div {
        width: 200px;
        margin: 20px auto;
    }

    select {
        width: 200px;
        height: 30px;
    }

input {

width: 200px;

height: 40px;

}

div {

width: 200px;

margin: 20px auto;

}

select {

width: 200px;

height: 30px;

}

And now we will be writing the javascript code to send an ajax request to the excel file and convert the excel data to json using jquery. The excel file is shown below

Now we will be writing the javascript code to fetch this data into a select dropdown list

const url = 'https://dl.dropbox.com/s/5bbg3dukkyveqea/Country%20List.xlsx?dl=0';
    $('input').click(() => {
        genCountriesSelect(url);
    });

const url = 'https://dl.dropbox.com/s/5bbg3dukkyveqea/Country%20List.xlsx?dl=0';

$('input').click(() => {

genCountriesSelect(url);

});

And now inside this block of code you can see we are initializing the url where the excel file is hosted on the internet. And also we have binded a onclick listener to the button when we click the create button this custom function getCountriesSelect() will execute and we are passing the url as an argument. Now we need to write this function

function genCountriesSelect(url) {
        /* set up async GET request */
        const req = new XMLHttpRequest();
        req.open("GET", url, true);
        req.responseType = "arraybuffer";

        req.onload = function (e) {
            
        }
        req.send();
    }

function genCountriesSelect(url) {

/* set up async GET request */

const req = new XMLHttpRequest();

req.open("GET", url, true);

req.responseType = "arraybuffer";

req.onload = function (e) {

}

req.send();

}

As you can see guys we have initialized a simple ajax get xmlhttprequest to the passed url that we have specified. And then we are also setting the responseType to be arrayBuffer because this is an excel file. And then we are actually sending the request using the send() method and also we have a callback function of onload. When the request is started this function will execute. Now we need to write what happens inside this request

req.onload = function (e) {
            const data = new Uint8Array(req.response);
            const workbook = XLSX.read(data, { type: "array" });

            const first_sheet_name = workbook.SheetNames[0];
            const worksheet_raw = workbook.Sheets[first_sheet_name];
            const excel_data = XLSX.utils.sheet_to_json(worksheet_raw);
            console.log(excel_data)

            const $select = $('<select id="country" name="country" class="demo-dropdown">');
            for (i = 0; i < excel_data.length; i++) {
                const country = excel_data[i]['Country'];
                $('<option value="' + country + '">' + country + '</option>').appendTo($select);
            }
            $select.appendTo('.countries');
        }

req.onload = function (e) {

const data = new Uint8Array(req.response);

const workbook = XLSX.read(data, { type: "array" });

const first_sheet_name = workbook.SheetNames[0];

const worksheet_raw = workbook.Sheets[first_sheet_name];

const excel_data = XLSX.utils.sheet_to_json(worksheet_raw);

console.log(excel_data)

const $select = $('<select id="country" name="country" class="demo-dropdown">');

for (i = 0; i < excel_data.length; i++) {

const country = excel_data[i]['Country'];

$('<option value="' + country + '">' + country + '</option>').appendTo($select);

}

$select.appendTo('.countries');

}

As you can see here we are getting the response from the ajax request. First of all we are converting the response to Uint8Array and then we are using a special .XLSX utils library to convert the actual sheet of excel data to json data. For this you need to include the cdn of libraries along side with jquery as shown below

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.full.min.js"></script>

1 2	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.full.min.js"></script>

After getting the json data of countries inside an array. We can use the for loop to loop through all the countries which are present inside the array. We are extracting the country option and then one by one appending to the select list. And lastly we are adding the select list to the div element. So if you execute the application in the browser it will look something like this

FULL SOURCE CODE

Wrapping it up this is the full source code of this application

index.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<style>
    input {
        width: 200px;
        height: 40px;
    }

    div {
        width: 200px;
        margin: 20px auto;
    }

    select {
        width: 200px;
        height: 30px;
    }
</style>

<body>
    <div>
        <input type="button" value="Create Select">
        <div class="countries"></div>
    </div>
</body>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.full.min.js"></script>
<script>

    const url = 'https://dl.dropbox.com/s/5bbg3dukkyveqea/Country%20List.xlsx?dl=0';
    $('input').click(() => {
        genCountriesSelect(url);
    });

    function genCountriesSelect(url) {
        /* set up async GET request */
        const req = new XMLHttpRequest();
        req.open("GET", url, true);
        req.responseType = "arraybuffer";

        req.onload = function (e) {
            const data = new Uint8Array(req.response);
            const workbook = XLSX.read(data, { type: "array" });

            const first_sheet_name = workbook.SheetNames[0];
            const worksheet_raw = workbook.Sheets[first_sheet_name];
            const excel_data = XLSX.utils.sheet_to_json(worksheet_raw);
            console.log(excel_data)

            const $select = $('<select id="country" name="country" class="demo-dropdown">');
            for (i = 0; i < excel_data.length; i++) {
                const country = excel_data[i]['Country'];
                $('<option value="' + country + '">' + country + '</option>').appendTo($select);
            }
            $select.appendTo('.countries');
        }
        req.send();
    }
</script>

</html>

<!DOCTYPE html>

<head>

<title>Document</title>

</head>

<style>

input {

width: 200px;

height: 40px;

}

div {

width: 200px;

margin: 20px auto;

}

select {

width: 200px;

height: 30px;

}

</style>

<body>

<div>

</div>

</body>

const url = 'https://dl.dropbox.com/s/5bbg3dukkyveqea/Country%20List.xlsx?dl=0';

$('input').click(() => {

genCountriesSelect(url);

});

function genCountriesSelect(url) {

/* set up async GET request */

const req = new XMLHttpRequest();

req.open("GET", url, true);

req.responseType = "arraybuffer";

req.onload = function (e) {

const data = new Uint8Array(req.response);

const workbook = XLSX.read(data, { type: "array" });

const first_sheet_name = workbook.SheetNames[0];

const worksheet_raw = workbook.Sheets[first_sheet_name];

const excel_data = XLSX.utils.sheet_to_json(worksheet_raw);

console.log(excel_data)

const $select = $('<select id="country" name="country" class="demo-dropdown">');

for (i = 0; i < excel_data.length; i++) {

const country = excel_data[i]['Country'];

$('<option value="' + country + '">' + country + '</option>').appendTo($select);

}

$select.appendTo('.countries');

}

req.send();

}

</script>

</html>