Read Excel data based on selected sheet from DropDownList in JavaScript

Last Reply 4 months ago By dharmendr

Posted 4 months ago

I have used the below code.

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

Now excel sheet is bind in dropdown 

how to read data on selected sheet from dropdown 

active only that sheet and read data from that sheet only which is selected in dropdown.

 

 

Posted 4 months ago Modified on 4 months ago

Hi telldurges,

Using the below article i have created the example.

Convert Excel to JSON using JavaScript

Check this example. Now please take its reference and correct your code.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Read Excel Sheet Data</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">
        var excelData;
        function Upload() {
            var fileUpload = document.getElementById("fileUpload");
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
            if (regex.test(fileUpload.value.toLowerCase())) {
                if (typeof (FileReader) != "undefined") {
                    var reader = new FileReader();
                    if (reader.readAsBinaryString) {
                        reader.onload = function (e) {
                            BindSheetNames(e.target.result);
                        };
                        reader.readAsBinaryString(fileUpload.files[0]);
                    } else {
                        var data = "";
                        reader.onload = function (e) {
                            var bytes = new Uint8Array(e.target.result);
                            for (var i = 0; i < bytes.byteLength; i++) {
                                data += String.fromCharCode(bytes[i]);
                            }
                            BindSheetNames(data);
                        };
                        reader.readAsArrayBuffer(fileUpload.files[0]);
                    }
                } else {
                    alert("This browser does not support HTML5.");
                }
            } else {
                alert("Please upload a valid Excel file.");
            }
        };
        function BindSheetNames(data) {
            var dvExcel = document.getElementById("dvExcel");
            dvExcel.innerHTML = "";
            excelData = data;
            var workbook = XLSX.read(data, { type: 'binary' });
            var sheetNames = workbook.SheetNames;
            var ddl = document.getElementById("ddlSheetNames");
            ddl.innerHTML = "";
            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;
                ddl.options.add(option);
            }
        };
        function SheetNameChanged(ele) {
            var selectedSheet = ele.options[ele.selectedIndex].text;
            var workbook = XLSX.read(excelData, { type: 'binary' });
            var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets["Customers"]);
            var table = document.createElement("table");
            table.Id = "tbl" + selectedSheet;

            var columns = Object.keys(excelRows[0]);
            var row = table.insertRow(-1);
            for (var i = 0; i < columns.length; i++) {
                var headerCell = document.createElement("TH");
                headerCell.innerHTML = columns[i];
                row.appendChild(headerCell);
            }

            for (var i = 0; i < excelRows.length; i++) {
                row = table.insertRow(-1);
                for (var j = 0; j < columns.length; j++) {
                    var cell = row.insertCell(-1);
                    cell.innerHTML = excelRows[i][columns[j]];
                }
            }

            var dvExcel = document.getElementById("dvExcel");
            dvExcel.innerHTML = "";
            dvExcel.appendChild(table);
            excelData = "";
        }
    </script>
    <input type="file" id="fileUpload" onchange="Upload()" />
    <select id="ddlSheetNames" onchange="SheetNameChanged(this)">
    </select><hr />
    <div id="dvExcel"></div>
</body>
</html>

Demo