最近在使用node.js + exceljs做導出功能, 總結(jié)一下其基本使用方法：
依賴庫引入
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: '創(chuàng)建日期', 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)
})
在一些復雜的情況下接校，我們可能需要生成多級表頭狮崩，如下圖：
屏幕快照 2018-11-29 上午11.37.08.png
// 添加表頭
sheet.getRow(1).values = ['種類', '銷量',,,, '店鋪']
sheet.getRow(2).values = ['種類', '2018-05', '2018-06', '2018-07', '2018-08', '店鋪']
// 添加數(shù)據(jù)項定義，與之前不同的是睦柴，此時去除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}
})
