How to read an excel file contents on client side?

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 wo开发者_如何学Pythonrks in all browsers.

An xlsx spreadsheet is a zip file with a bunch of xml files in it. Using something like zip.js, you can extract the xml files and parse them in the browser. xlsx.js does this. Here's my simple example. Copied here for convenience:

/*
    Relies on jQuery, underscore.js, Async.js (https://github.com/caolan/async), and zip.js (http://gildas-lormeau.github.com/zip.js).
    Tested only in Chrome on OS X.

    Call xlsxParser.parse(file) where file is an instance of File. For example (untested):

    document.ondrop = function(e) {
        var file = e.dataTransfer.files[0];
        excelParser.parse(file).then(function(data) {
            console.log(data);
        }, function(err) {
            console.log('error', err);
        });
    }
*/

xlsxParser = (function() {
    function extractFiles(file) {
        var deferred = $.Deferred();

        zip.createReader(new zip.BlobReader(file), function(reader) {
            reader.getEntries(function(entries) {
                async.reduce(entries, {}, function(memo, entry, done) {
                    var files = ['xl/worksheets/sheet1.xml', 'xl/sharedStrings.xml'];
                    if (files.indexOf(entry.filename) == -1) return done(null, memo);

                    entry.getData(new zip.TextWriter(), function(data) {
                        memo[entry.filename.split('/').pop()] = data;
                        done(null, memo);
                    });
                }, function(err, files) {
                    if (err) deferred.reject(err);
                    else deferred.resolve(files);
                });
            });
        }, function(error) { deferred.reject(error); });

        return deferred.promise();
    }

    function extractData(files) {
        var sheet = $(files['sheet1.xml']),
            strings = $(files['sharedStrings.xml']),
            data = [];

        var colToInt = function(col) {
            var letters = ["", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
            var col = $.trim(col).split('');

            var n = 0;

            for (var i = 0; i < col.length; i++) {
                n *= 26;
                n += letters.indexOf(col[i]);
            }

            return n;
        };

        var Cell = function(cell) {
            cell = cell.split(/([0-9]+)/);
            this.row = parseInt(cell[1]);
            this.column = colToInt(cell[0]);
        };

        var d = sheet.find('dimension').attr('ref').split(':');
        d = _.map(d, function(v) { return new Cell(v); });

        var cols = d[1].column - d[0].column + 1,
            rows = d[1].row - d[0].row + 1;

        _(rows).times(function() {
            var _row = [];
            _(cols).times(function() { _row.push(''); });
            data.push(_row);
        });

        sheet.find('sheetData row c').each(function(i, c) {
            var $cell = $(c),
                cell = new Cell($cell.attr('r')),
                type = $cell.attr('t'),
                value = $cell.find('v').text();

            if (type == 's') value = strings.find('si t').eq(parseInt(value)).text();

            data[cell.row - d[0].row][cell.column - d[0].column] = value;
        });

        return data;
    }

    return {
        parse: function(file) {
            return extractFiles(file).pipe(function(files) {
                return extractData(files);
            });
        }
    }
})();

You can load and open the file client side in most modern browsers using the HTML5 File API

Once you have loaded the file you can parse the contents with a library that supports certain excel output formats (such as csv / xlsx).

Here are a couple of options...

• CSV
• XLSX / CSV / XML

It is generally not possible to read/write any file via JavaScript in a browser. So without any additional plug-ins you will not be able to read/write Excel files from the browser. The ActiveX objects of Excel let you do this, but only IE supports ActiveX objects.

There may be other plugins for other browsers, but i am aware of none.

In the first place, why do you want to do that? Can you give a use case? Perhaps there are better options available than what you are trying.

UPDATE

You will have to pass the excel file to the server and do the reading of the excel in the server side (in a servlet for instance).

You will have to use a <input type='file'> in the JSP within a multipart form
<form name="myForm" action="myServlet" enctype="multipart/form-data" method="post">

On the server side, you may want to use Apache Commons File Upload.

Once you have the file (or a stream on it) you can parse the file using, say, Apache POI HSSF/XSSF and then update the data to a database or pass it back to a JSP

I do this all the time - my prototypes are designed to let stakeholders modify an excel and have it populate the html prototype, often without a server.

1. Save the excel as XML
2. Load the XML via AJAX and parse out the structure

(1) here's a handy button macro I put in my excel files, allowing painless saving as XML (and re-saving as xls):

    Sub SaveAndExportXML()
      Dim fname As String
      Dim fnamexml As String
      fname = ThisWorkbook.FullName
      fnamexml = Replace(fname, ".xls", ".xml", 1, 1, vbTextCompare)

      If MsgBox("Will save the following (runtime XML and source XLS) files: " & vbNewLine & "XML: " & fnamexml & vbNewLine & "Src: " & fname & vbNewLine & vbNewLine & "Ok?  If so, please click Yes here and on the next 3 prompts...", vbYesNo) = vbNo Then Exit Sub
      ActiveWorkbook.SaveAs Filename:=fnamexml, FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False
      're-save as xls:
      ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    End Sub

(2) The parsing JS is more complicated but the premise is simple. There will be XML objects like Worksheet, Table, Row, Cell, and Data. There's also an XML attribute called ss:Index I find handy sometimes.

This one works in all major browsers.

https://catamphetamine.github.io/read-excel-file/

<input type="file" id="input" />

import readXlsxFile from 'read-excel-file'

const input = document.getElementById('input')

input.addEventListener('change', () => {
  readXlsxFile(input.files[0]).then((data) => {
    // `data` is an array of rows
    // each row being an array of cells.
  })
})

In the example above data is raw string data. It can be parsed to JSON with a strict schema by passing schema argument. See API docs for an example of that.

API docs: http://npmjs.com/package/read-excel-file

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>