最近在使用node.js + exceljs做导出功能, 总结一下其基本使用方法:
依赖库引入
const fs = require('fs')
const Excel = require('exceljs')
初始化Excel
const workbook = new Excel.Workbook()
workbook.creator = 'test'
workbook.lastModifiedBy = 'test'
workbook.created = new Date()
workbook.modified = new Date()
生成一个工作表
let sheet = workbook.addWorksheet('2018-10报表')
# Add column headers and define column keys and widths
sheet.columns = [
{header: '创建日期', key: 'create_time', width: 15},
{header: '单号', key: 'id', width: 15},
{header: '电话号码', key: 'phone', width: 15},
{header: '地址', key: 'address', width: 15}
]
const data = [{
create_time: '2018-10-01',
id: '787818992109210',
phone: '11111111111',
address: '深圳市'
}]
# Add an array of rows
sheet.addRows(data)
生成excel文件
const filePath = `server/attachement/用户报表.xlsx`
return await workbook.xlsx.writeFile(filePath).then( async () => {
this.ctx.attachment(`用户报表.xlsx`)
this.ctx.type = '.xlsx'
this.ctx.body = fs.readFileSync(filePath)
}, function (err: any) {
console.log(err)
})
在一些复杂的情况下,我们可能需要生成多级表头,如下图:
// 添加表头
sheet.getRow(1).values = ['种类', '销量',,,, '店铺']
sheet.getRow(2).values = ['种类', '2018-05', '2018-06', '2018-07', '2018-08', '店铺']
// 添加数据项定义,与之前不同的是,此时去除header字段
sheet.columns = [
{key: 'category', width: 30},
{key: '2018-05', width: 30},
{key: '2018-06', width: 30},
{key: '2018-07', width: 30},
{key: '2018-08', width: 30},
{key: 'store', width: 30},
]
const data = [{
category: '衣服',
'2018-05': 300,
'2018-06': 230,
'2018-07': 730,
'2018-08': 630,
'store': '王小二旗舰店'
}, {
category: '零食',
'2018-05': 672,
'2018-06': 826,
'2018-07': 302,
'2018-08': 389,
'store': '吃吃货'
}]
sheet.addRows(data)
// 合并单元格
sheet.mergeCells(`B1:E1`)
sheet.mergeCells('A1:A2')
sheet.mergeCells('F1:F2')
// 设置每一列样式
const row = sheet.getRow(1)
row.eachCell((cell, rowNumber) => {
sheet.getColumn(rowNumber).alignment = {vertical: 'middle', horizontal: 'center'}
sheet.getColumn(rowNumber).font = {size: 14, family: 2}
})