Javascript 使用 node.js 读取 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28860728/
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
Reading Excel file using node.js
提问by Marc Rasmussen
Okay so i am using the FileUploadermodule to upload my file from angular to my REST API:
好的，所以我正在使用该FileUploader模块将我的文件从 angular 上传到我的REST API：
var uploader = $scope.uploader = new FileUploader({
url: api.getUrl('uploadCompetence',null)
});
This is sent to the following POSTfunction:
这被发送到以下POST函数：
router.route('/api/uploadCompetence')
.post(function (req, res) {
// This is where i want to read the file
var competence = Competence.build(req.body.location);
competence.add(function (success) {
res.json({message: 'quote created!'});
},
function (err) {
res.status(err).send(err);
});
})
Now my goal is to read the excelfile and then add each row to my database.
现在我的目标是读取excel文件，然后将每一行添加到我的数据库中。
However im not quite sure how i can read the file from Node.jsi have debugged my server and couldnt find the file anywhere but the the api is being called from my Angularapplication
但是，我不太确定如何从Node.js我调试服务器读取文件，但在任何地方都找不到该文件，但是正在从我的Angular应用程序调用 api
Can anyone push me in the right direction? :)
谁能把我推向正确的方向？:)
回答by aludvigsen
There are a few different libraries doing parsing of Excel files (.xlsx). I will list two projects I find interesting and worth looking into.
有几个不同的库可以解析 Excel 文件 (.xlsx)。我将列出两个我觉得有趣且值得研究的项目。
Node-xlsx
节点-xlsx
Excel parser and builder. It's kind of a wrapper for a popular project JS-XLSX, which is a pure javascript implementation from the Office Open XML spec.
Excel 解析器和生成器。它是一个流行项目JS-XLSX的包装器，它是来自 Office Open XML 规范的纯 javascript 实现。
Example for parsing file
解析文件的例子
var xlsx = require('node-xlsx');
var obj = xlsx.parse(__dirname + '/myFile.xlsx'); // parses a file
var obj = xlsx.parse(fs.readFileSync(__dirname + '/myFile.xlsx')); // parses a buffer
ExcelJS
ExcelJS
Read, manipulate and write spreadsheet data and styles to XLSX and JSON. It's an active project. At the time of writing the latest commit was 9 hours ago. I haven't tested this myself, but the api looks extensive with a lot of possibilites.
读取、操作电子表格数据和样式并将其写入 XLSX 和 JSON。这是一个活跃的项目。在撰写本文时，最近一次提交是在 9 小时前。我自己还没有测试过这个，但是这个 api 看起来很广泛，有很多可能性。
Code example:
代码示例：
// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
回答by Charitha Goonewardena
You can also use this node module called js-xlsx
1) Install module
npm install xlsx
2) Import module + code snippet
您也可以使用这个名为js-xlsx 的节点模块
1) 安装模块
npm install xlsx
2) 导入模块 + 代码片段
var XLSX = require('xlsx')
var workbook = XLSX.readFile('Master.xlsx');
var sheet_name_list = workbook.SheetNames;
var xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
console.log(xlData);
回答by Codemaker
install exceljs and use the following code,
安装 exceljs 并使用以下代码，
var Excel = require('exceljs');
var wb = new Excel.Workbook();
var path = require('path');
var filePath = path.resolve(__dirname,'sample.xlsx');
wb.xlsx.readFile(filePath).then(function(){
var sh = wb.getWorksheet("Sheet1");
sh.getRow(1).getCell(2).value = 32;
wb.xlsx.writeFile("sample2.xlsx");
console.log("Row-3 | Cell-2 - "+sh.getRow(3).getCell(2).value);
console.log(sh.rowCount);
//Get all the rows data [1st and 2nd column]
for (i = 1; i <= sh.rowCount; i++) {
console.log(sh.getRow(i).getCell(1).value);
console.log(sh.getRow(i).getCell(2).value);
}
});
回答by Akash Aher
You can use read-excel-filenpm.
您可以使用read-excel-filenpm。
In that, you can specify JSON Schemato convert XLSXinto JSONFormat.
在这里，您可以指定JSON Schema将XLSX转换为JSON格式。
const readXlsxFile = require('read-excel-file/node');
const schema = {
'Segment': {
prop: 'Segment',
type: String
},
'Country': {
prop: 'Country',
type: String
},
'Product': {
prop: 'Product',
type: String
}
}
readXlsxFile('sample.xlsx', { schema }).then(({ rows, errors }) => {
console.log(rows);
});
回答by Mani kiran
Useful link
有用的链接
https://ciphertrick.com/read-excel-files-convert-json-node-js/
https://ciphertrick.com/read-excel-files-convert-json-node-js/
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var multer = require('multer');
var xlstojson = require("xls-to-json-lc");
var xlsxtojson = require("xlsx-to-json-lc");
app.use(bodyParser.json());
var storage = multer.diskStorage({ //multers disk storage settings
destination: function (req, file, cb) {
cb(null, './uploads/')
},
filename: function (req, file, cb) {
var datetimestamp = Date.now();
cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
}
});
var upload = multer({ //multer settings
storage: storage,
fileFilter : function(req, file, callback) { //file filter
if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
return callback(new Error('Wrong extension type'));
}
callback(null, true);
}
}).single('file');
/** API path that will upload the files */
app.post('/upload', function(req, res) {
var exceltojson;
upload(req,res,function(err){
if(err){
res.json({error_code:1,err_desc:err});
return;
}
/** Multer gives us file info in req.file object */
if(!req.file){
res.json({error_code:1,err_desc:"No file passed"});
return;
}
/** Check the extension of the incoming file and
* use the appropriate module
*/
if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
exceltojson = xlsxtojson;
} else {
exceltojson = xlstojson;
}
try {
exceltojson({
input: req.file.path,
output: null, //since we don't need output.json
lowerCaseHeaders:true
}, function(err,result){
if(err) {
return res.json({error_code:1,err_desc:err, data: null});
}
res.json({error_code:0,err_desc:null, data: result});
});
} catch (e){
res.json({error_code:1,err_desc:"Corupted excel file"});
}
})
});
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('running on 3000...');
});
回答by Pranjal Sharma
Install 'spread_sheet' node module,it will both add and fetch row from local spreadsheet
安装“spread_sheet”节点模块，它将从本地电子表格中添加和获取行
