javascript 在nodejs中将excel文件转换为json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28286319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting excel file to json in nodejs
提问by user1553174
I am trying to convert an excel file to json by using this api: convert-json node api.
我正在尝试使用以下 api 将 excel 文件转换为 json：convert-json node api。
I am able to do it on my local machine but not on my server. Only the csv-to-json works locally and on the server. Here is my code: https://gist.github.com/debasreedash/33efd4473ba8b344a5ac
我可以在本地机器上执行此操作，但不能在我的服务器上执行此操作。只有 csv-to-json 可以在本地和服务器上工作。这是我的代码：https: //gist.github.com/debasreedash/33efd4473ba8b344a5ac
The server crashes while trying to parse the excel file right after the first console.log. Here is what it looks like: http://imgur.com/lzUy8sc
服务器在第一个 console.log 之后尝试解析 excel 文件时崩溃。这是它的样子：http: //imgur.com/lzUy8sc
I thought that it was a problem with not having excel drivers on the server but after downloading and installing it did not work either. Has anyone come across this issue? If you need anymore information then let me know.
我认为这是服务器上没有excel驱动程序的问题，但下载并安装后它也不起作用。有没有人遇到过这个问题？如果您需要更多信息，请告诉我。
回答by Vishnu Shekhawat
Use this method for easy undertsanding and parsing :
使用此方法可以轻松进行undertsanding和解析：
npm install --save excel'
var xls = require('excel');
xls('Sheet.xlsx', function(err, data) {
if(err) throw err;
// data is an array of arrays
});
As it says, the data it returns is an array of arrays. We want it to be JSON, so that we can do whatever we want with it.
正如它所说，它返回的数据是一个数组数组。我们希望它是 JSON，这样我们就可以用它做任何我们想做的事情。
This is a function that converts an array of arrays to JSON:
这是一个将数组数组转换为 JSON 的函数：
function convertToJSON(array) {
var first = array[0].join()
var headers = first.split(',');
var jsonData = [];
for ( var i = 1, length = array.length; i < length; i++ )
{
var myRow = array[i].join();
var row = myRow.split(',');
var data = {};
for ( var x = 0; x < row.length; x++ )
{
data[headers[x]] = row[x];
}
jsonData.push(data);
}
return jsonData;
};
Then:
然后：
xlsx('tasks.xlsx', function(err,data) {
if(err) throw err;
//console.log(jsonDataArray(data));
console.log(JSON.stringify(convertToJSON(data)));
//console.log(data);
});
回答by Steve Tarver
Focusing on the first half of the first line of the question: how to convert Excel to json.
关注第一行的前半部分：如何将Excel转换为json。
Assume: Excel spreadsheet is a square of data, where the first row is object keys and remaining rows are object values and the desired json is a list of objects.
假设：Excel 电子表格是一个正方形的数据，其中第一行是对象键，其余行是对象值，所需的 json 是对象列表。
Improvements on previous answer: remove unnecessary splits and joins (unnecessary and could cause invalid transforms if keys or values contained commas), allow dotted strings in keys to imply nested objects, coffeescript, write the file.
对先前答案的改进：删除不必要的拆分和连接（如果键或值包含逗号，则不必要并且可能导致无效转换），允许键中的虚线字符串暗示嵌套对象、coffeescript、写入文件。
Dotted notation: Key row (0) containing firstName, lastName, address.street, address.city, address.state, address.zip would product, per row, a doc with first and last names and an embedded doc named address, with the address.
虚线表示法：包含 firstName、lastName、address.street、address.city、address.state、address.zip 的关键行 (0) 将在每一行中生成一个带有名字和姓氏的文档以及一个名为 address 的嵌入文档，其中包含地址。
assign function by VisioN from How to set object property (of object property of..) given its string name in JavaScript?
通过 VisioN 分配函数从如何在 JavaScript 中给定字符串名称的情况下设置对象属性（对象属性的..）？
First, load the excel module
一、加载excel模块
npm install excel --save-dev
Not elegant, just get'r'done code
不优雅，只是完成代码
fs = require 'fs'
excel = require 'excel'
FILES = [
{src: 'input.xlsx', dst: 'output.json'}
]
# Assign values to dotted property names - set values on sub-objects
assign = (obj, key, value) ->
# Because we recurse, a key may be a dotted string or a previously split
# dotted string.
key = key.split '.' unless typeof key is 'object'
if key.length > 1
e = key.shift()
obj[e] = if Object.prototype.toString.call(obj[e]) is "[object Object]" then obj[e] else {}
assign obj[e], key, value
else
obj[key[0]] = value
# The excel module reads sheet 0 from specified xlsx file
process = (src, dst) ->
excel src, (err, data) ->
throw err if err
keys = data[0]
rows = data[1..]
result = []
for row in rows
item = {}
assign item, keys[index], value for value, index in row
result.push item
fs.writeFile dst, JSON.stringify(result, null, 2), (err) ->
if err
console.error("Error writing file #{dst}", err)
else
console.log "Updated #{dst}"
process file.src, file.dst for file in FILES
回答by Pankaj
Find quick and exact solution which work for me:
找到对我有用的快速准确的解决方案：
server.js
服务器.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({ //multers disk storage settings
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');
/** Method to handle the form submit */
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;
}
// code to convert excel data to json format
excel2json({
input: req.file.path,
output: "output/"+Date.now()+".json", // output json
lowerCaseHeaders:true
}, function(err, result) {
if(err) {
res.json(err);
} else {
res.json(result);
}
});
})
});
// load index file to upload file on http://localhost:3000/
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('Server running on port 3000');
});
index.html
索引.html
<!DOCTYPE html>
<html lang="en">
<head>
<title>Excel to Json in nodejs | jsonworld</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="jumbotron text-center">
<h1>Excel to Json in nodejs</h1>
<p>source : <a href="https://jsonworld.com">jsonworld</a></p>
</div>
<div class="container">
<div class="row">
<div class="col-sm-4 col-md-offset-4">
<form id="form" enctype ="multipart/form-data" action="sendFile" method="post">
<div class="form-group">
<input type="file" name="file" class="form-control"/>
<input type="submit" value="Upload" name="submit" class="btn btn-primary" style="float:right; margin-top:30px;">
</form>
</div>
</div>
</div>
</body>
</html>
