nodejs excel to json code example
Example 1: react convert excel to json
import React, { useState } from "react";
import "./App.css";
import * as XLSX from "xlsx";
class ExcelToJson extends React.Component {
constructor(props) {
super(props);
this.handleClick = this.handleClick.bind(this);
this.state = {
file: "",
};
}
handleClick(e) {
this.refs.fileUploader.click();
}
filePathset(e) {
e.stopPropagation();
e.preventDefault();
var file = e.target.files[0];
console.log(file);
this.setState({ file });
console.log(this.state.file);
}
readFile() {
var f = this.state.file;
var name = f.name;
const reader = new FileReader();
reader.onload = (evt) => {
const bstr = evt.target.result;
const wb = XLSX.read(bstr, { type: "binary" });
const wsname = wb.SheetNames[0];
const ws = wb.Sheets[wsname];
const data = XLSX.utils.sheet_to_csv(ws, { header: 1 });
console.log("Data>>>" + data);
console.log(this.convertToJson(data));
};
reader.readAsBinaryString(f);
}
convertToJson(csv) {
var lines = csv.split("\n");
var result = [];
var headers = lines[0].split(",");
for (var i = 1; i < lines.length; i++) {
var obj = {};
var currentline = lines[i].split(",");
for (var j = 0; j < headers.length; j++) {
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
return JSON.stringify(result);
}
render() {
return (
<div>
<input
type="file"
id="file"
ref="fileUploader"
onChange={this.filePathset.bind(this)}
/>
<button
onClick={() => {
this.readFile();
}}
>
Read File
</button>
</div>
);
}
}
export default ExcelToJson;
Example 2: convert excel file to json using node js
let express = require('express'),
app = express(),
bodyParser = require('body-parser'),
multer = require('multer'),
crypto = require('crypto'),
xlsxtojson = require('xlsx-to-json'),
xlstojson = require("xls-to-json");
let fileExtension = require('file-extension');
app.use(bodyParser.json());
let storage = multer.diskStorage({
destination: function (req, file, cb) {
cb(null, './input/')
},
filename: function (req, file, cb) {
crypto.pseudoRandomBytes(16, function (err, raw) {
cb(null, raw.toString('hex') + Date.now() + '.' + fileExtension(file.mimetype));
});
}
});
let upload = multer({storage: storage}).single('file');
app.post('/sendFile', function(req, res) {
let excel2json;
upload(req,res,function(err){
if(err){
res.json({error_code:401,err_desc:err});
return;
}
if(!req.file){
res.json({error_code:404,err_desc:"File not found!"});
return;
}
if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
excel2json = xlsxtojson;
} else {
excel2json = xlstojson;
}
excel2json({
input: req.file.path,
output: "output/"+Date.now()+".json",
lowerCaseHeaders:true
}, function(err, result) {
if(err) {
res.json(err);
} else {
res.json(result);
}
});
})
});
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('Server running on port 3000');
});