Post
Ask Question
Yokeswaran G

Yokeswaran G

  • NA
  • 5
  • 925

Open and authenticate password protected Excel File

May 17 2021 11:40 AM
HI All,
 
I just want to open excel file before reading the uploaded excel file, i am using xlsx.read javascript API to read uploaded excel file, before that i need to open the file because the file is being password protected, i have shared the both concepts read excel file and open excel file can anyone please let me know how to open the using xlsx.read
 
Excel Reader using Javascript:
  1. <html>  
  2. <input type="file" id="fileUpload" />  
  3. <input type="button" id="upload" value="Upload" onclick="UploadProcess()" />  
  4. <br/>  
  5. <div id="ExcelTable"></div>  
  6. <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>  
  7. <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>  
  8. <script type="text/javascript">  
  9.     function UploadProcess() {  
  10.         //Reference the FileUpload element.  
  11.         var fileUpload = document.getElementById("fileUpload");  
  12.         //Validate whether File is valid Excel file.  
  13.         var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;  
  14.         if (regex.test(fileUpload.value.toLowerCase())) {  
  15.             if (typeof (FileReader) != "undefined") {  
  16.                 var reader = new FileReader();  
  17.                 //For Browsers other than IE.  
  18.                 if (reader.readAsBinaryString) {  
  19.                     reader.onload = function (e) {  
  20.                         GetTableFromExcel(e.target.result);  
  21.                     };  
  22.                     reader.readAsBinaryString(fileUpload.files[0]);  
  23.                 } else {  
  24.                     //For IE Browser.  
  25.                     reader.onload = function (e) {  
  26.                         var data = "";  
  27.                         var bytes = new Uint8Array(e.target.result);  
  28.                         for (var i = 0; i < bytes.byteLength; i++) {  
  29.                             data += String.fromCharCode(bytes[i]);  
  30.                             alert("Data:"+data)  
  31.                         }  
  32.                         GetTableFromExcel(data);  
  33.                     };  
  34.                     reader.readAsArrayBuffer(fileUpload.files[0]);  
  35.                 }  
  36.             } else {  
  37.                 alert("This browser does not support HTML5.");  
  38.             }  
  39.         } else {  
  40.             alert("Please upload a valid Excel file.");  
  41.         }  
  42.     };  
  43.     function GetTableFromExcel(data) {  
  44.         //Read the Excel File data in binary  
  45.         var workbook = XLSX.open(data, {  
  46.             type: 'binary'  
  47.         });  
  48.         //get the name of First Sheet.  
  49.         var Sheet = workbook.SheetNames[0];  
  50.         //Read all rows from First Sheet into an JSON array.  
  51.         var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);  
  52.         //Create a HTML Table element.  
  53.         var myTable  = document.createElement("table");  
  54.         myTable.border = "1";  
  55.         //Add the header row.  
  56.         var row = myTable.insertRow(-1);  
  57.         //Add the header cells.  
  58.         var headerCell = document.createElement("TH");  
  59.         headerCell.innerHTML = "Id";  
  60.         row.appendChild(headerCell);  
  61.         headerCell = document.createElement("TH");  
  62.         headerCell.innerHTML = "Name";  
  63.         row.appendChild(headerCell);  
  64.         headerCell = document.createElement("TH");  
  65.         headerCell.innerHTML = "Country";  
  66.         row.appendChild(headerCell);  
  67.         headerCell = document.createElement("TH");  
  68.         headerCell.innerHTML = "Age";  
  69.         row.appendChild(headerCell);  
  70.         headerCell = document.createElement("TH");  
  71.         headerCell.innerHTML = "Date";  
  72.         row.appendChild(headerCell);  
  73.          headerCell = document.createElement("TH");  
  74.         headerCell.innerHTML = "Gender";  
  75.         row.appendChild(headerCell);  
  76.         //Add the data rows from Excel file.  
  77.         for (var i = 0; i < excelRows.length; i++) {  
  78.             //Add the data row.  
  79.             var row = myTable.insertRow(-1);  
  80.             //Add the data cells.  
  81.             var cell = row.insertCell(-1);  
  82.             cell.innerHTML = excelRows[i].Id;  
  83.             cell = row.insertCell(-1);  
  84.             cell.innerHTML = excelRows[i].Name;  
  85.             cell = row.insertCell(-1);  
  86.             cell.innerHTML = excelRows[i].Country;      
  87.             cell = row.insertCell(-1);  
  88.             cell.innerHTML = excelRows[i].Age;      
  89.             cell = row.insertCell(-1);  
  90.             cell.innerHTML = excelRows[i].Date;      
  91.             cell = row.insertCell(-1);  
  92.             cell.innerHTML = excelRows[i].Gender;     
  93.             alert(row);  
  94.         }  
  95.         var ExcelTable = document.getElementById("ExcelTable");  
  96.         ExcelTable.innerHTML = "";  
  97.         ExcelTable.appendChild(myTable);  
  98.     };  
  99. </script>  
  100. </html>  
Open or Download Excel File using Javascript:
  1. <html>  
  2.   <body>  
  3.   <input type="button" onclick="Upload()" value="Open File">  
  4.   <!--<input type="button" onclick='window.open("test.xlsx")' value="Open File">-->  
  5.     <script type="text/javascript">  
  6.     function Upload() {   
  7.       var request = new XMLHttpRequest();  
  8.         request.open("GET", "C:\Users\user\Downloads\test.xlsx");  
  9.         request.responseType = "blob";  
  10.         request.onload = function() {  
  11.           // set `blob` `type` to `"text/html"`  
  12.           var blob = new Blob([this.response], {type:"text/html"});  
  13.           var url = URL.createObjectURL(blob);  
  14.           window.open(url);   
  15.         }  
  16.         request.send();  
  17.     }     
  18.     </script>  
  19.   </body>  
  20. </html>
I just want to open or authenticate before read an excel file, kindly let me know if any solution for this, thanks in advance.

Answers (1)