I want to convert excel sheet data to json. It has to be dynamic, so there is an upload button where user uploads the excel sheet and the data is then converted into json. Could you please provide me the javascript code? I tried SheetJS but couldn't figure out. I would prefer something straight forward :)
I really appreciate your help!
This question is related to
javascript
html
The answer is
The answers are working fine with xls format but, in my case, it didn't work for xlsx format. Thus I added some code here. it works both xls and xlsx format.
I took the sample from the official sample link.
Hope it may help !
function fileReader(oEvent) {
var oFile = oEvent.target.files[0];
var sFilename = oFile.name;
var reader = new FileReader();
var result = {};
reader.onload = function (e) {
var data = e.target.result;
data = new Uint8Array(data);
var workbook = XLSX.read(data, {type: 'array'});
console.log(workbook);
var result = {};
workbook.SheetNames.forEach(function (sheetName) {
var roa = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {header: 1});
if (roa.length) result[sheetName] = roa;
});
// see the result, caution: it works after reader event is done.
console.log(result);
};
reader.readAsArrayBuffer(oFile);
}
// Add your id of "File Input"
$('#fileUpload').change(function(ev) {
// Do something
fileReader(ev);
}
js-xlsx library makes it easy to convert Excel/CSV files into JSON objects.
Download the xlsx.full.min.js file from here. Write below code on your HTML page Edit the referenced js file link (xlsx.full.min.js) and link of Excel file
<!doctype html>
<html>
<head>
<title>Excel to JSON Demo</title>
<script src="xlsx.full.min.js"></script>
</head>
<body>
<script>
/* set up XMLHttpRequest */
var url = "http://myclassbook.org/wp-content/uploads/2017/12/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("");
/* Call XLSX */
var workbook = XLSX.read(bstr, {
type: "binary"
});
/* DO SOMETHING WITH workbook HERE */
var first_sheet_name = workbook.SheetNames[0];
/* Get worksheet */
var worksheet = workbook.Sheets[first_sheet_name];
console.log(XLSX.utils.sheet_to_json(worksheet, {
raw: true
}));
}
oReq.send();
</script>
</body>
</html>
Input:
Output:
@Kwang-Chun Kang Thanks Kang a lot! I found the solution is working and very helpful, it really save my day. For me I am trying to create a React.js component that convert *.xlsx to json object when user upload the excel file to a html input tag. First I need to install XLSX package with:
npm install xlsx --save
Then in my component code, import with:
import XLSX from 'xlsx'
The component UI should look like this:
<input
accept=".xlsx"
type="file"
onChange={this.fileReader}
/>
It calls a function fileReader(), which is exactly same as the solution provided. To learn more about fileReader API, I found this blog to be helpful: https://blog.teamtreehouse.com/reading-files-using-the-html5-filereader-api
Source: Stackoverflow.com