locked
Reading data from excel file and adding it to a dropdownlist using JQuery RRS feed

  • Question

  • User-1681336077 posted

    Hi Team,

    I have a requirement in which I need to browse to an excel file(select the file), read data from file and add the data to a dropdown list. This functionality I need to achieve through JQuery (from client side only), my website is made of ASP.NET MVC. 

    Is this possible to achieve this functionality using JS files of JQuery only?

    If above functionality is not possible using JQuery JS files only, can you please mention what is other ideal way of doing it from client side(using other JS file/s along with JQuery JS files). Please note other JS file/s should be open source only as I am not having the option of purchasing a software, I am having option to just include/use the JS file/s in my solution without purchasing it.

    Thanks,

    Salil

    Monday, October 14, 2019 11:27 AM

All replies

  • User475983607 posted

    Is this possible to achieve this functionality using JS files of JQuery only?

    If above functionality is not possible using JQuery JS files only, can you please mention what is other ideal way of doing it from client side(using other JS file/s along with JQuery JS files). Please note other JS file/s should be open source only as I am not having the option of purchasing a software, I am having option to just include/use the JS file/s in my solution without purchasing it.

    Web applications can NOT open a file on the client machine.  The file must be uploaded to the server first.

    Monday, October 14, 2019 11:30 AM
  • User665608656 posted

    Hi singhsalil2008,

    According to your requirement, you can refer to two jQuery plugins which can be used to convert the data from Excel to a JSON array in js.

    For this, you can refer to this link : Reading An Excel File Using HTML 5 And jQuery

    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>  
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script>  

    I have create a table in excel which looks like this :

    And i will use these plugins with jquery code to get the data in excel to bind the dropdownlist(select tag).

    Here is the full code , you could refer to it:

    @{
        Layout = null;
    }
    
    <!DOCTYPE html>
    
    <html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Index</title>
        <script src="~/Scripts/jquery-3.3.1.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script>
        <script type="text/javascript">
            function ExportToTable() {
         var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
         /*Checks whether the file is a valid excel file*/
         if (regex.test($("#excelfile").val().toLowerCase())) {
             var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/
             if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
                 xlsxflag = true;
             }
             /*Checks whether the browser supports HTML5*/
             if (typeof (FileReader) != "undefined") {
                 var reader = new FileReader();
                 reader.onload = function (e) {
                     var data = e.target.result;
                     /*Converts the excel data in to object*/
                     if (xlsxflag) {
                         var workbook = XLSX.read(data, { type: 'binary' });
                     }
                     else {
                         var workbook = XLS.read(data, { type: 'binary' });
                     }
                     /*Gets all the sheetnames of excel in to a variable*/
                     var sheet_name_list = workbook.SheetNames;
    
                     var cnt = 0; /*This is used for restricting the script to consider only first sheet of excel*/
                     sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/
                         /*Convert the cell value to Json*/
                         if (xlsxflag) {
                             var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                         }
                         else {
                             var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
                         }
                         if (exceljson.length > 0 && cnt == 0) {
                             BindJqurery(exceljson, '#selectOption');
                             cnt++;
                         }
                     });
                     $('#exceltable').show();
                 }
                 if (xlsxflag) {/*If excel file is .xlsx extension than creates a Array Buffer from excel*/
                     reader.readAsArrayBuffer($("#excelfile")[0].files[0]);
                 }
                 else {
                     reader.readAsBinaryString($("#excelfile")[0].files[0]);
                 }
             }
             else {
                 alert("Sorry! Your browser does not support HTML5!");
             }
         }
         else {
             alert("Please upload a valid Excel file!");
         }
     }
    
       function BindJqurery(jsondata, dropid) {/*Function used to convert the JSON array to select tag*/
        $.each(jsondata, function(key, value) {   
            $('#selectOption').append($("<option></option>")
                        .attr("value",value["ID"])
                        .text(value["Name"])); 
        });
    
     }
    
        </script>
    </head>
    <body>
        <div>
            <input type="file" id="excelfile" />
            <input type="button" id="viewfile" value="Export To Table" onclick="ExportToTable()" />
            <br />
            <br />
            <select id="selectOption"></select>
        </div>
    </body>
    </html>
    

    Here is the result :

    Best Regards,

    YongQing.

    Tuesday, October 15, 2019 5:41 AM
  • User-1681336077 posted

    Hi,

    Can you please mention the official way of downloading below two plugins -

    xlsx.core.min.js
    xls.core.min.js

    I need to download these two js files and use them in my project solution.

    Here, understanding is these js files are opensource(free to use) and could be added in a project solution for free.

    Thanks,

    Salil

    Wednesday, October 16, 2019 8:39 AM
  • User665608656 posted

    Hi singhsalil2008,

    You can find the corresponding file in these two links for download:

    https://github.com/SheetJS/js-xlsx

    https://github.com/SheetJS/js-xls

    In fact, what I provide are online references, which you can use directly if you allow.

    Best Regards,

    YongQing.

    Wednesday, October 16, 2019 9:37 AM
  • User-1681336077 posted

    Hi YongQing,

    Also, can you please reply to below -

    1) Can you please mention exact steps to download these files as I am unable to find these files in below mentioned URLs.

    2) Understanding is these files could be downloaded and used for free in a project/solution,  i.e. they are open source, can you please confirm.

    3) Apart from these two files, is their any other file/s that I need to include in my solution, if yes, can you please mention which file and how to download it.

    4) Currently, I am using Ver 3.1.1 of JQuery, can you please mention if I need to look for specific version of above mentioned js files.

    Thanks

    Salil

    Wednesday, October 16, 2019 10:13 AM
  • User665608656 posted

    Hi singhsalil2008,

    1) Can you please mention exact steps to download these files as I am unable to find these files in below mentioned URLs.

    2) Understanding is these files could be downloaded and used for free in a project/solution,  i.e. they are open source, can you please confirm.

    The JS files referenced in this code are all open source.

    You can refer to their official website links : http://sheetjs.com

    In GitHub, you can search the file name to get the corresponding file. In fact, I only found the xlsx.core.min.js file, here is the link :

    https://github.com/SheetJS/js-xlsx/tree/master/dist

    If you want to download these js files, there is a simpler way, because you already have a link to them. You can enter the link in the browser url bar, and you will get the detailed code of the js file, then you can create a customized js file in your local folder, and copy the js code on the web page to your own js file.

    3) Apart from these two files, is their any other file/s that I need to include in my solution, if yes, can you please mention which file and how to download it.

    4) Currently, I am using Ver 3.1.1 of JQuery, can you please mention if I need to look for specific version of above mentioned js files.

    In addition to the two js files about reading Excel files, you also need to refer to the basic file of jQuery.

    You have mentioned that you are using jQuery file version 3.1.1, which is applicable. You don't need to look for other versions of the file again.

    In fact, I recommend you can test the code you use first and then ask questions about the reference files. After you test, you will know the use of some files.

    Best Regards,

    YongQing.

    Thursday, October 17, 2019 2:08 AM