Convert Excel to JSON using JavaScript code

Hello friends, in this javascript tutorial we are going to learn about how to Convert Excel to JSON using jQuery code. We can read an excel file which is hosted on any server, SharePoint folder, etc with the help of jQuery code. Here you can get readymade jQuery code for Excel to JSON conversion.

Excel to JSON
Excel to JSON

Convert Excel to JSON using JavaScript Code

In this tutorial, we are going to use a js-xlsx library to convert Excel to JSON. It has xlsx.full.min.js file which we are going to refer in our HTML file. js-xlsx is a parser and writer for various spreadsheet formats. So without wasting time lets jump to coding part. We can also use .csv files as well.

Below is a code for reading Excel as a JSON object:

<!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 = "https://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>

In the above code, if you could observe, you will find that we have used xlsx.full.min.js file and a Test.xlsx file. We are going to read Test.xlsx Excel file to generate a JSON Object.

Note: While using above code make sure that you have uploaded your Excel file on any server and give the correct full path of that file. You can use the same code to convert CSV to JSON.

Here is our Test.xlsx file which looks like below

Output

When you host your index.html file on the server, on the page load we are running our script to show the objects in the browser console. Below is the output in JSON object

JSON Object output
JSON Object output

Download Excel to JSON javascript

You can use any one of the links to download the source code

  1. MyClassBook: Download
  2. Google Drive: Download
  3. MediaFire: Download

I hope you liked this article. Please let us know if you face any issues in the comment sections below. Like our facebook page and subscribe to our newsletter for future updates. Have a great time! 🙂

Multi Level Cascading Dropdown for Sharepoint Online using REST Api

Hello Friends, in this tutorial we are going to implement Multi Level Cascading Dropdown in SharePoint Online custom Bootstrap form using REST api.

Multi Level Cascading Dropdown in SharePoint Online using REST

We have one list for City which has only one column named Title.

List Name : City
List Name : City

We have one list for State which has only one column named Title

List Name: State
List Name: States

We have one master list for all (Country, state and City) in which we are storing country values in Title field, State is a lookup field in which data is coming from State list, City is also a lookup column in which data is coming fom City list.

List Name: CountryStateCity
List Name: CountryStateCity

We have one custom bootstrap form in which we have 3 dropdowns which are Multi Level Cascading Dropdown, Country, State and City.

Html code:

<html>

<head>
    <title>Contact Form Tutorial by Bootstrapious.com</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="../CustomCode/referenceFiles/bootstrap.min.css" rel="stylesheet">
    <link href='../CustomCode/referenceFiles/googleFonts.css' rel='stylesheet' type='text/css'>
    <link href="../CustomCode/css/CustomCSS.css" rel="stylesheet">
</head>

<body>

    <div class="container">

        <div class="row">

            <div class="col-lg-8 col-lg-offset-2" id="form-body">

                <div id="main-form" role="form">

                    <div class="messages"></div>

                    <div class="controls">

                        <div class="row">
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label for="form_name">Firstname *</label>
                                    <input id="form_name" type="text" name="name" class="form-control" placeholder="Please enter your firstname *">
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label for="form_lastname">Lastname *</label>
                                    <input id="form_lastname" type="text" name="surname" class="form-control" placeholder="Please enter your lastname *">
                                </div>
                            </div>
                        </div>
                        <div class="row">
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label for="form_email">Email *</label>
                                    <input id="form_email" type="email" name="email" class="form-control" placeholder="Please enter your email *">
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label for="form_phone">Phone</label>
                                    <input id="form_phone" type="tel" name="phone" class="form-control" placeholder="Please enter your phone">
                                </div>
                            </div>
                        </div>

                        <div class="row">
                            <div class="col-md-4">
                                <div class="form-group">
                                    <label for="form_country">Country</label>
                                    <select class="form-control" id="form_country">
                                    </select>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="form-group">
                                    <label for="form_state">State</label>
                                    <select class="form-control" id="form_state">
                                    </select>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="form-group">
                                    <label for="form_city">City</label>
                                    <select class="form-control" id="form_city">
                                    </select>
                                </div>
                            </div>
                        </div>
                        <div class="row">
                            <div class="col-md-12">
                                <div class="form-group">
                                    <label for="form_address">Address *</label>
                                    <textarea id="form_address" name="address" class="form-control" rows="4"></textarea>
                                </div>
                            </div>
                            <div class="col-md-12">
                                <input type="button" class="btn btn-success btn-send" value="Submit">
                            </div>
                        </div>

                        <div class="row">
                            <div class="col-md-12">
                                <p class="text-muted"><strong>*</strong> These fields are required.</p>
                            </div>
                        </div>
                    </div>

                </div>

            </div>
            <!-- /.8 -->

        </div>
        <!-- /.row-->

    </div>
    <!-- /.container-->

    <script src="../CustomCode/referenceFiles/jquery.min.js"></script>
    <script src="../CustomCode/referenceFiles/bootstrap.min.js"></script>
    <script src="../CustomCode/js/CustomJS.js"></script>
    <!--<script src="validator.js"></script>
 <script src="contact.js"></script>-->
</body>

</html>

This will look like

Multi Level Cascading Dropdown
SharePoint Bootstap form

When user selects Country, it will populate only those states which belongs to selected countris, when user select perticular state, it will populate only those cities which belongs to selected state.

here is the REST javascript file for Multi Level Cascading Dropdown:

$(document).ready(function() {
    appendCountries();
    $('#form_country').change(function() {
        appendStates();
    });
    $('#form_state').change(function() {
        appendCity();
    });
    $('.btn').click(function() {
        AddListItem();
    });
});

function appendCountries() {
    $.ajax({
        url: "../_vti_bin/ListData.svc/CountryStateCity()?$orderby= Title asc&$select=Title",
        type: "get",
        headers: {
            "Accept": "application/json;odata=verbose"
        },
        success: function(data) {
            var values = [];
            var uniqueValues = [];
            var option = "";
            var valuesArray = data.d.results;
            $.each(valuesArray, function(i, result) {
                values.push(result.Title);
            });
            $.each(values, function(i, el) {
                if ($.inArray(el, uniqueValues) === -1) {
                    uniqueValues.push(el);
                    option += "<option value='" + el + "'>" + el + "</option>";
                }
            });
            $("#form_country").append(option);
        },
        error: function(data) {
            alert(data.responseJSON.error);
        }
    });
}

function appendStates() {
    var country = $('#form_country').val();
    $.ajax({
        url: "../_vti_bin/ListData.svc/CountryStateCity()?$select=State&$filter=Title eq '" + country + "'&$expand=State&$orderby= State/Title asc",
        type: "get",
        headers: {
            "Accept": "application/json;odata=verbose"
        },
        success: function(data) {
            var values = [];
            var uniqueValues = [];
            var option = "";
            var valuesArray = data.d.results;
            $.each(valuesArray, function(i, result) {
                values.push(result.State.Title);
            });
            $.each(values, function(i, el) {
                if ($.inArray(el, uniqueValues) === -1) {
                    uniqueValues.push(el);
                    option += "<option value='" + el + "'>" + el + "</option>";
                }
            });
            $("#form_state").empty();
            $("#form_state").append(option);
        },
        error: function(data) {
            alert(data.responseJSON.error);
        }
    });
}

function appendCity() {
    var state = $('#form_state').val();
    $.ajax({
        url: "../_vti_bin/ListData.svc/CountryStateCity()?$select=City&$filter=State/Title eq '" + state + "'&$expand=State,City&$orderby= City/Title asc",
        type: "get",
        headers: {
            "Accept": "application/json;odata=verbose"
        },
        success: function(data) {
            var values = [];
            var uniqueValues = [];
            var option = "";
            var valuesArray = data.d.results;
            $.each(valuesArray, function(i, result) {
                values.push(result.City.Title);
            });
            $.each(values, function(i, el) {
                if ($.inArray(el, uniqueValues) === -1) {
                    uniqueValues.push(el);
                    option += "<option value='" + el + "'>" + el + "</option>";
                }
            });
            $("#form_city").empty();
            $("#form_city").append(option);
        },
        error: function(data) {
            alert(data.responseJSON.error);
        }
    });
}

function AddListItem()
{
    var firstName = $("#form_name").val();
    var lastName = $("#form_lastname").val();
    var email = $("#form_email");
    var phone = $('#form_phone');
    var country = $('#form_country');
    var state = $('#form_state');
    var city = $('#form_city');
    var address = $('#form_address');
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "../_vti_bin/ListData.svc/EmployeeDetails()",
        type: "POST",
        data: JSON.stringify({
            __metadata:
            {
                type: "SP.Data.TestListItem"
            },
            FirstName: firstName,
            Lastname: lastName,
            Email: email,
            Phone: phone,
            Country: country,
            State: state,
            City: city,
            Address: address
        }),
        headers:
        {
            "Accept": "application/json;odata=verbose",
            "Content-Type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "X-HTTP-Method": "POST"
        },
        success: function(data, status, xhr)
        {
            alert('Request has been submitted successfully');
        },
        error: function(xhr, status, error)
        {
            //console.log(data.responseJSON.error);
            alert('Error');
        }
    });
}

You can download full code here: Download or use mediafire link or user Google Drive link.

I hope you liked this article. If you have any queries, feel free to ask in the comment section below. Like our facebook page and subscribe to our newsletter for future updates. Have a nice day!

Convert SharePoint Date to Readable date format using jquery

Hi friends, today we are going to see how can we convert SharePoint date into human radable date format using jquery.

When you use REST api or JSOM to retrieve the date from sharepoint date field you will get the output like /Date(1508371200000)/

We can easily convert above date format into human readable format using below script. Here

var rawDate = "/Date(1508371200000)/"; 
var rawDate = parseInt(rawDate.substring(6, 19)); 
var d = new Date(rawDate); 
var result = d.format("dd MMM yyyy");

In the above code variable result will give you the output as 19 Oct 2017

Convert SharePoint date to Readable date
Convert SharePoint date to Readable date

I hope you liked this article. Please feel free to ask your queries in comment section below. Like our facebook page ans subscribe to our newslette for future updates. Have a nice day!

How to Display SharePoint List in jQuery Datatables

Hello friends, in this article we are going to retrieve data from SharePoint List using REST API and will display that data into jQuery Datatable. So let’s begin…

Show SharePoint List in Datatable format

Step 1:

We have a SharePoint list named EmployeeDetails as shown below with some dummy data. This list has 4 columns (Title, Name, Salary, Address).

EmployeeDetails List
EmployeeDetails List

Step 2 : Create a site page where we are going to show above data in datatable. For creating a page go to Site contents > Site pages > Click on new page icon. In this case I have created a page with name Employee Details.

Step 3 : Insert a Content Editor Webpart into a page.

Employee Details Page
Employee Details Page

Step 4 : Edit the Content Editor Webpart and paste the below code into it.

 

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="Stylesheet" type="text/css" />
<script type="text/javascript" src="../SiteAssets/EmployeeJqueryDatatable.js"></script>
<div id="DatatablePanel">
    <table style="width: 100%;">
        <tr>
            <td>
                <div id="DatatableGrid" style="width: 100%"></div>
            </td>
        </tr>
    </table>
</div>

download above html file here. (or you can use MediaFire link)

Step 5 : If you can observe the above code, we have referenced a .js file named EmployeeJqueryDatatable.js So we have to first create this js file and upload it into Site assets. Please copy the below code into this js file and named it as EmployeeJqueryDatatable.js

$(document).ready(function() {
    var RestUrl = "../_vti_bin/listdata.svc/EmployeeDetails";
    $.ajax({
        url: RestUrl,
        method: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                //construct HTML Table from the JSON Data
                $('#DatatableGrid').append(GenerateTableFromJson(data.d.results));
                //Bind the HTML data with Jquery DataTable
                var oTable = $('#EmployeeTable').dataTable({
                    "iDisplayLength": 5,
                    "aLengthMenu": [
                        [5, 10, 30, 50],
                        [5, 10, 30, 50]
                    ],
                    "sPaginationType": "full_numbers"
                });
            } else {
                $('#DatatableGrid').append("<span>No Employee Details Found.</span>");
            }
        },
        error: function(data) {
            $('#DatatableGrid').append("<span>Error Retreiving Employee Details. Error : " + JSON.stringify(data) + "</span>");
        }
    });

    function GenerateTableFromJson(objArray) {
        var tableContent = '<table id="EmployeeTable" style="width:100%"><thead><tr><td>Title</td>' + '<td>Name</td>' + '<td>Salary</td>' + '<td>Address</td>' + '</tr></thead><tbody>';
        for (var i = 0; i < objArray.length; i++) {
            tableContent += '<tr>';
            tableContent += '<td>' + objArray[i].Title + '</td>';
            tableContent += '<td>' + objArray[i].Name + '</td>';
            tableContent += '<td>' + objArray[i].Salary + '</td>';
            tableContent += '<td>' + objArray[i].Address + '</td>';
            tableContent += '</tr>';
        }
        return tableContent;
    }
});

download above .js file here (for security reasons I have used .txt file here, so after downloading rename it to .js) or you can use MediaFire link to download the direct js file here

Step 6 : Once you upload the above js file into your site assets, go to our EmployeeDetails page that we ceated in step no. 2. Here we go!!! below is the output for our EmployeeDetails list

Video tutorial:

I have also created a step by step video tutorial for SharePoint Datatables. Please watch it for clear understanding:

https://www.youtube.com/watch?v=gXoeBrXSb3A

I would like to reply to your queries. Please let me know whether you liked this simple step by step article on SharePoint Datatable integration. I am waiting for your comments! Please like our facebook page and subscibe to our newsletter for future updates. Have a nice day!