I am able to read Excel file via FileReader
but it outputs text as well as weird characters with it. I need to read xls
file row-wise, read data in every column and convert it to JSON.
How to read xls file row by row?
This question is related to
javascript
json
html
xls
filereader
excel file
here and you can get the data in JSON
format in console
:<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>_x000D_
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>_x000D_
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>_x000D_
<script>_x000D_
var ExcelToJSON = function() {_x000D_
_x000D_
this.parseExcel = function(file) {_x000D_
var reader = new FileReader();_x000D_
_x000D_
reader.onload = function(e) {_x000D_
var data = e.target.result;_x000D_
var workbook = XLSX.read(data, {_x000D_
type: 'binary'_x000D_
});_x000D_
workbook.SheetNames.forEach(function(sheetName) {_x000D_
// Here is your object_x000D_
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);_x000D_
var json_object = JSON.stringify(XL_row_object);_x000D_
console.log(JSON.parse(json_object));_x000D_
jQuery( '#xlx_json' ).val( json_object );_x000D_
})_x000D_
};_x000D_
_x000D_
reader.onerror = function(ex) {_x000D_
console.log(ex);_x000D_
};_x000D_
_x000D_
reader.readAsBinaryString(file);_x000D_
};_x000D_
};_x000D_
_x000D_
function handleFileSelect(evt) {_x000D_
_x000D_
var files = evt.target.files; // FileList object_x000D_
var xl2json = new ExcelToJSON();_x000D_
xl2json.parseExcel(files[0]);_x000D_
}_x000D_
_x000D_
_x000D_
_x000D_
</script>_x000D_
_x000D_
<form enctype="multipart/form-data">_x000D_
<input id="upload" type=file name="files[]">_x000D_
</form>_x000D_
_x000D_
<textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea>_x000D_
_x000D_
<script>_x000D_
document.getElementById('upload').addEventListener('change', handleFileSelect, false);_x000D_
_x000D_
</script>
_x000D_
This is a combination of the following Stackoverflow
posts:
Good Luck...
include the xslx.js , xlsx.full.min.js , jszip.js
add a onchange event handler to the file input
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);
}
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 the sheet. You could use VBA functions as you do in Excel.
This code can help you
Most of the time jszip.js is not working so include xlsx.full.min.js in your js code.
<input type="file" id="file" ng-model="csvFile"
onchange="angular.element(this).scope().ExcelExport(event)"/>
<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]);
};
Thank you for the answer above, I think the scope (of answers) is completed but I would like to add a "react way" for whoever using react.
Create a file called importData.js:
import React, {Component} from 'react';
import XLSX from 'xlsx';
export default class ImportData extends Component{
constructor(props){
super(props);
this.state={
excelData:{}
}
}
excelToJson(reader){
var fileData = reader.result;
var wb = XLSX.read(fileData, {type : 'binary'});
var data = {};
wb.SheetNames.forEach(function(sheetName){
var rowObj =XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
var rowString = JSON.stringify(rowObj);
data[sheetName] = rowString;
});
this.setState({excelData: data});
}
loadFileXLSX(event){
var input = event.target;
var reader = new FileReader();
reader.onload = this.excelToJson.bind(this,reader);
reader.readAsBinaryString(input.files[0]);
}
render(){
return (
<input type="file" onChange={this.loadFileXLSX.bind(this)}/>
);
}
}
Then you can use the component in the render method like:
import ImportData from './importData.js';
import React, {Component} from 'react';
class ParentComponent extends Component{
render(){
return (<ImportData/>);
}
}
<ImportData/>
would set the data to its own state, you can access Excel data in the "parent component" by following this:
If you want the simplest and tiniest way of reading an *.xlsx file in a browser then this library might do:
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
Old question, but I should note 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:
Both pages are HTML5 File API-driven XLS/XLSX parsers (you can drag-drop your file and it will print out the data in the cells in a comma-separated list). You can also generate JSON objects (assuming the first row is a header row).
The test suite http://oss.sheetjs.com/ shows a version that uses XHR to get and parse files.
XLS is a binary proprietary format used by Microsoft. Parsing XLS with server side languages is very difficult without using some specific library or Office Interop. Doing this with javascript is mission impossible. Thanks to the HTML5 File API you can read its binary contents but in order to parse and interpret it you will need to dive into the specifications of the XLS format. Starting from Office 2007, Microsoft embraced the Open XML file formats (xslx
for Excel) which is a standard.
If you are ever wondering how to read a file from server this code might be helpful.
Restrictions :
<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>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>_x000D_
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>_x000D_
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>_x000D_
<script>_x000D_
var ExcelToJSON = function() {_x000D_
_x000D_
this.parseExcel = function(file) {_x000D_
var reader = new FileReader();_x000D_
_x000D_
reader.onload = function(e) {_x000D_
var data = e.target.result;_x000D_
var workbook = XLSX.read(data, {_x000D_
type: 'binary'_x000D_
});_x000D_
workbook.SheetNames.forEach(function(sheetName) {_x000D_
// Here is your object_x000D_
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);_x000D_
var json_object = JSON.stringify(XL_row_object);_x000D_
console.log(JSON.parse(json_object));_x000D_
jQuery( '#xlx_json' ).val( json_object );_x000D_
})_x000D_
};_x000D_
_x000D_
reader.onerror = function(ex) {_x000D_
console.log(ex);_x000D_
};_x000D_
_x000D_
reader.readAsBinaryString(file);_x000D_
};_x000D_
};_x000D_
_x000D_
function handleFileSelect(evt) {_x000D_
_x000D_
var files = evt.target.files; // FileList object_x000D_
var xl2json = new ExcelToJSON();_x000D_
xl2json.parseExcel(files[0]);_x000D_
}_x000D_
_x000D_
_x000D_
_x000D_
</script>_x000D_
_x000D_
<form enctype="multipart/form-data">_x000D_
<input id="upload" type=file name="files[]">_x000D_
</form>_x000D_
_x000D_
<textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea>_x000D_
_x000D_
<script>_x000D_
document.getElementById('upload').addEventListener('change', handleFileSelect, false);_x000D_
_x000D_
</script>
_x000D_
Source: Stackoverflow.com