Bind (Populate) Excel file Sheet (WorkSheet) Names in DropDownList using JavaScript

Last Reply 4 months ago By dharmendr

Posted 4 months ago

Hello Sir,

i am facing problem when i browse excel file in excel there is multiple sheet is there like sheet1, sheet2, sheet3 etc. i want to bind this sheet in dropdown with id and sheetname so that page will not refresh.

<input type="file" name="file" class="form-control" id="Masterid" required="required" accept=".xls,.xlsx" />

 

    <script type="text/javascript">
        $('#Masterid').on('change', function (e) {
            debugger;
            var files = e.target.files;
            //var myID = 3; //uncomment this to make sure the ajax URL works
            if (files.length > 0) {
                if (window.FormData !== undefined) {
                    var data = new FormData();
                    for (var x = 0; x < files.length; x++) {
                        data.append("file" + x, files[x]);
                    }

                    $.ajax({
                        type: "POST",
                        url: 'https://localhost:44389/api/BindExcel/save',
                        contentType: false,
                        processData: false,
                        data: data,
                        success: function (result) {
                            console.log(result);
                        },
                        error: function (xhr, status, p3, p4) {
                            var err = "Error " + " " + status + " " + p3 + " " + p4;
                            if (xhr.responseText && xhr.responseText[0] == "{")
                                err = JSON.parse(xhr.responseText).Message;
                            console.log(err);
                        }
                    });
                } else {
                    alert("This browser doesn't support HTML5 file uploads!");
                }
            }
        });
    </script>

this script not send the data in web api giving error

Posted 4 months ago

Hi telldurges,

For reading Excel Sheet name i have used below article.

Convert Excel to JSON using JavaScript

Check the below sample. Theer is no need to request to api for binding DropDownList.

Use below code to bind the DropDownList with sheet names.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Read Excel Sheets Name</title>
</head>
<body>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
    <script type="text/javascript">
        function Upload() {
            //Reference the FileUpload element.
            var fileUpload = document.getElementById("fileUpload");
            //Validate whether File is valid Excel file.
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
            if (regex.test(fileUpload.value.toLowerCase())) {
                if (typeof (FileReader) != "undefined") {
                    var reader = new FileReader();

                    //For Browsers other than IE.
                    if (reader.readAsBinaryString) {
                        reader.onload = function (e) {
                            ProcessExcel(e.target.result);
                        };
                        reader.readAsBinaryString(fileUpload.files[0]);
                    } else {
                        //For IE Browser.
                        reader.onload = function (e) {
                            var data = "";
                            var bytes = new Uint8Array(e.target.result);
                            for (var i = 0; i < bytes.byteLength; i++) {
                                data += String.fromCharCode(bytes[i]);
                            }
                            ProcessExcel(data);
                        };
                        reader.readAsArrayBuffer(fileUpload.files[0]);
                    }
                } else {
                    alert("This browser does not support HTML5.");
                }
            } else {
                alert("Please upload a valid Excel file.");
            }
        };
        function ProcessExcel(data) {
            //Read the Excel File data.
            var workbook = XLSX.read(data, { type: 'binary' });
            //Fetch the name of Sheets.
            var sheetNames = workbook.SheetNames;
            var ddl = document.getElementById("ddlSheetNames");
            ddl.innerHTML = "";
            // Add default item.
            var optionDefault = document.createElement('OPTION');
            optionDefault.innerHTML = "Select";
            optionDefault.value = "0";
            ddl.add(optionDefault, 0);

            for (var i = 0; i < sheetNames.length; i++) {
                var option = document.createElement("OPTION");
                option.innerHTML = sheetNames[i];
                option.value = parseInt(i) + 1;
                // Add Sheet Names to DropDownList.
                ddl.options.add(option);
            }
        };
    </script>
    <input type="file" id="fileUpload" />
    <input type="button" id="upload" value="Upload" onclick="Upload()" />
    <hr />
    <select id="ddlSheetNames">
    </select>
</body>
</html>

Demo