NodeJS excel 导出(exceljs)
与Java中POI报表比较类似,希望可以帮到你。
1.导入
excel
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/489bd6de2af15152c09b4d5ec4f27db7.png)
解析方法
var Excel = require('exceljs');
var workbook = new Excel.Workbook();
var data = [];
workbook.xlsx.readFile('import.xlsx')
.then(function(){
var worksheet = workbook.getWorksheet(1);
var row = worksheet.getRow(2);
row.eachCell(function(cell, colNumber){
var value = cell.value;
if(typeof value == "object") value = value.text;
data.push(value);
});
console.log(JSON.stringify(data));
});
测试结果
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b6059a7d1447519cf0a52d435c18451c.png#pic_center)
2.导出
导出效果
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/152f4f9e8226053e2ecda67b28996549.png)
导出方法
var Excel = require('exceljs');
var fills = {
solid: {type: "pattern", pattern:"solid", fgColor:{argb:"FFFFAAAA"}}
};
var workbook = new Excel.Workbook();
var ws1 = workbook.addWorksheet("测试一");
ws1.addRow(["地址","地面"]);
ws1.addRow(["总人口", "不可计数"]);
ws1.addRow(["类型", "动物", "非动物"]);
ws1.addRow(["统计日期", "1111-11-11 11:11:11"]);
ws1.addRow();
ws1.addRow(["1", "2", "3", "4", "5"]);
ws1.getCell("A6").fill = fills.solid;
ws1.getCell("B6").fill = fills.solid;
ws1.getCell("C6").fill = fills.solid;
ws1.getCell("D6").fill = fills.solid;
ws1.getCell("E6").fill = fills.solid;
ws1.addRow(["什么跟神马", 10, 1, "凡人修仙传", 7]);
ws1.addRow(["","","","一号遗迹", 2]);
ws1.addRow(["","","","六号遗迹", 0]);
ws1.addRow(["","","","古国一号", 0]);
ws1.addRow(["","","","锻体期", 0]);
ws1.addRow(["","","","合体期", 0]);
ws1.addRow(["","","","没资质", 1]);
ws1.mergeCells("A7:A13")
ws1.mergeCells("B7:B13")
ws1.mergeCells("C7:C13")
rowCenter(ws1, 6, 13);
colWidth(ws1, [1,2,3,4,5], 20);
var ws2 = workbook.addWorksheet("测试二");
var ws3 = workbook.addWorksheet("测试三");
function rowCenter(arg_ws, arg_start, arg_end) {
for(i = arg_start; i <= arg_end; i++) {
arg_ws.findRow(i).alignment = { vertical: 'middle', horizontal: 'center' };
arg_ws.findRow(i).eachCell(function (cell, index) {
cell.border = {
top: {style:'thin'},
left: {style:'thin'},
bottom: {style:'thin'},
right: {style:'thin'}
};
})
}
}
function colWidth(arg_ws, arg_cols, arg_width) {
for(i in arg_cols) {
arg_ws.getColumn(arg_cols[i]).width = arg_width;
}
}
workbook.xlsx.writeFile('test2.xlsx')
.then(function(){
console.log('生成 xlsx');
});