How to read an excel file contents on client side?

user_481479 0
Viewed 63208

From the JSP page, I need to browse excel file and after selecting file on system, I need to read that excel file contents and fill my form.

Currently I have tried with below code but its only working in IE with some changes in IE internet options for ActiveXObject. Its not working in rest of the browsers.

<script>
    function mytest2() {
        var Excel;
        Excel = new ActiveXObject("Excel.Application"); 
        Excel.Visible = false;
        form1.my_textarea2.value = Excel.Workbooks.Open("C:/Documents and Settings/isadmin/Desktop/test.xlsx").ActiveSheet.Cells(1,1).Value;
        Excel.Quit();
    }
</script>

Please suggest some solution so that it works in all browsers.

excel jsp javascript
6 Answers

Using SheetJS and ECMAScript you can do the following:

add this in your file :

import { read, writeFileXLSX, utils } from "https://cdn.sheetjs.com/xlsx-0.18.7/package/xlsx.mjs";
        
// returns the content of the File passed as argument as a binary string
async function readFileAsBinaryString(file) { /* use in an async function to await on the result */
            
      const binaryString = await new Promise((resolve, reject) => {
                const reader = new FileReader();
                reader.onload = () => resolve(reader.result);
                reader.onerror = () => reject('error : cannot read the file');
                reader.readAsBinaryString(file);
      });
        
      //console.log(binaryString);
      return binaryString;
}
        
// returns the content of the Excel File passed as argument as a CSV formatted text
async function ExcelFileToCSV(file) {
      try {
              const data = await readFileAsBinaryString(file);
                
              let workbook = read(data, {
                  type:'binary',
              });
        
              let sheets = workbook.Sheets; // the sheets presents in the file
              let length = Object.keys(sheets).length; // number of sheets in the file
        
              if (length == 1) { // if there's only one sheet in the file
                  return utils.sheet_to_csv(sheets.sheet1); // returns the sheet converted to csv !! NOT AN ARRAY !!
                
              } else if (length > 1) { // else if there are more than one sheet in the file
                  let returnVal = [];
        
                  for (let key in sheets) { // loop over the sheets
                      returnVal.push(utils.sheet_to_csv(sheets[key])); // adds the current sheet converted to csv in the returnVal array 
                  }
                  //console.log(returnVal);
                  return returnVal; // returns an array of csv converted sheets !! ARRAY !!
              }
        
          } catch (err) {
                console.log(err);
          }
}

Or you can save it in an another file.
If you do so you will need to export the functions that you will be using and import it in the file where you are going to use it.

How to use :

async function readFile(file) { // single file
         let fileContent = await ExcelFileToCSV(file);
         console.log(fileContent); // log the file content
         // do stuff with fileContent here
}

fileContent will contain a string if there is only one sheet in the file otherwise it will be an array of string each one matching one sheet of the file used for the call.

async function readFile(files) { // multiple files
             let length = files.length;
             let filesContents = [];

             for (var i=0; i<length; i++) {
                     let fc = await ExcelFileToCSV(files[i]);
                     filesContents.push(fc);
             }
                   
             console.log(filesContents); // log  filesContents
             // do stuff with filesContents here
    }

The pro of going that way is that you can deal with async code the "synchronous way".

If you're interested in retrieving the file content in another format you can check the utils.sheet_to_XXX informations here and replace the utils.sheet_to_csv instructions in ExcelFileToCSV() or better do it in another function named accordingly.

If you want to use the functions inside an html page you will need to do it inside those scripts tags: <script type="module" defer></script>

user_181436770
Related