How to parse excel file in javascript / html5

Question

I can read the Excel file through FileReader but it displays text as well as strange characters with it. I need to read the xls file, read the data in each column and convert it to JSON.

How to read xls file line by line?

Answer 1

The function below converts Excel worksheet data (in .xlsx format) to JSON format. You can add a promise to a function.

 <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script> <script> var ExcelToJSON = function() { this.parseExcel = function(file) { var reader = new FileReader(); reader.onload = function(e) { var data = e.target.result; var workbook = XLSX.read(data, { type: 'binary' }); workbook.SheetNames.forEach(function(sheetName) { // Here is your object var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]); var json_object = JSON.stringify(XL_row_object); console.log(json_object); }) }; reader.onerror = function(ex) { console.log(ex); }; reader.readAsBinaryString(file); }; }; </script> 

Below in the post is the code for Excel XLS format in JSON JavaScript code?

Answer 2

An old question, but I have to point out that the general task of parsing XLS files from javascript is tedious and difficult, but not impossible.

I have basic parsers implemented in pure JS:

• http://oss.sheetjs.com/js-xls/ (xls files what you wanted)
• http://oss.sheetjs.com/js-xlsx/ (xlsx / xlsm / xlsb files)

Both pages are XLS / XLSX parsers based on the HTML5 file API (you can drag and drop your file and print the data in cells in a comma-separated list). You can also create JSON objects (if the first line is the title bar).

The http://oss.sheetjs.com/ test suite shows a version that uses XHR to retrieve and analyze files.

Answer 3

This code can help you. Most of the time jszip.js does not work, so include xlsx.full.min.js in your js code.

HTML code

  <input type="file" id="file" ng-model="csvFile" onchange="angular.element(this).scope().ExcelExport(event)"/> 

Javascript

 <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"> </script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"> </script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.8/xlsx.full.min.js"> </script> $scope.ExcelExport= function (event) { var input = event.target; var reader = new FileReader(); reader.onload = function(){ var fileData = reader.result; var wb = XLSX.read(fileData, {type : 'binary'}); wb.SheetNames.forEach(function(sheetName){ var rowObj =XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]); var jsonObj = JSON.stringify(rowObj); console.log(jsonObj) }) }; reader.readAsBinaryString(input.files[0]); }; 

Answer 4

Download the excel file here and you can get the JSON data in console :

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script> <script> var ExcelToJSON = function() { this.parseExcel = function(file) { var reader = new FileReader(); reader.onload = function(e) { var data = e.target.result; var workbook = XLSX.read(data, { type: 'binary' }); workbook.SheetNames.forEach(function(sheetName) { // Here is your object var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]); var json_object = JSON.stringify(XL_row_object); console.log(JSON.parse(json_object)); jQuery( '#xlx_json' ).val( json_object ); }) }; reader.onerror = function(ex) { console.log(ex); }; reader.readAsBinaryString(file); }; }; function handleFileSelect(evt) { var files = evt.target.files; // FileList object var xl2json = new ExcelToJSON(); xl2json.parseExcel(files[0]); } </script> <form enctype="multipart/form-data"> <input id="upload" type=file name="files[]"> </form> <textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea> <script> document.getElementById('upload').addEventListener('change', handleFileSelect, false); </script> 

This is a combination of the following Stackoverflow messages:

1. stack overflow
2. stack overflow

Good luck ...

Answer 5

If you want the easiest and tiniest way to read a * .xlsx file in a browser, then this library can do:

https://catamphetamine.imtqy.com/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 above example, data is raw string data. It can be parsed in strict schema JSON by passing the schema argument. See the API docs for an example of this.

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

Answer 6

If you are interested in how to read a file from the server, this code may be useful.

Limitations:

1. The file must be on the server (local / remote).
2. You will need to adjust the headers or use the Google CORS plugin.

 <Head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> <script lang="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.12.4/xlsx.core.min.js"></script> </head> <body> <script> /* set up XMLHttpRequest */ // replace it with your file path in local server var url = "http://localhost/test.xlsx"; var oReq = new XMLHttpRequest(); oReq.open("GET", url, true); oReq.responseType = "arraybuffer"; oReq.onload = function(e) { var arraybuffer = oReq.response; /* convert data to binary string */ var data = new Uint8Array(arraybuffer); var arr = new Array(); for (var i = 0; i != data.length; ++i) { arr[i] = String.fromCharCode(data[i]); } var bstr = arr.join(""); var cfb = XLSX.read(bstr, { type: 'binary' }); cfb.SheetNames.forEach(function(sheetName, index) { // Obtain The Current Row As CSV var fieldsObjs = XLS.utils.sheet_to_json(cfb.Sheets[sheetName]); fieldsObjs.map(function(field) { $("#my_file_output").append('<input type="checkbox" value="' + field.Fields + '">' + field.Fields + '<br>'); }); }); } oReq.send(); </script> </body> <div id="my_file_output"> </div> </html> 

Answer 7

include xslx.js, xlsx.full.min.js, jszip.js

add onchange event handler to input file

 function showDataExcel(event) { var file = event.target.files[0]; var reader = new FileReader(); var excelData = []; reader.onload = function (event) { var data = event.target.result; var workbook = XLSX.read(data, { type: 'binary' }); workbook.SheetNames.forEach(function (sheetName) { // Here is your object var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]); for (var i = 0; i < XL_row_object.length; i++) { excelData.push(XL_row_object[i]["your column name"]); } var json_object = JSON.stringify(XL_row_object); console.log(json_object); alert(excelData); }) }; reader.onerror = function (ex) { console.log(ex); }; reader.readAsBinaryString(file); } 

Answer 8

XLS is a proprietary binary format used by Microsoft. Parsing XLS with server-side languages ​​is very difficult without using a specific library or Office Interop. Doing this with javascript is not possible. Thanks to the HTML5 API, you can read its binary content, but to analyze and interpret it, you will need to dive into the XLS format specifications . Starting with Office 2007, Microsoft has embraced Open XML ( xslx for Excel) formats, which is the standard.

Answer 9

var excel = new ActiveXObject ("Excel.Application"); var book = excel.Workbooks.Open (your_full_file_name_here.xls); var sheet = book.Sheets.Item (1); var value = sheet.Range ("A1");

when you have a sheet. You can use VBA functions as in Excel.