excel文件处理库
调用demo:
import { XLSX } from '../lib/excel-conduct-library.esm'
import path from 'path'
const Excel = new XLSX()
const filePath = path.join(__dirname, `files/文件名称_${+new Date()}.xlsx`)
const workbook = Excel.getWorkbook(filePath)
let _currentHeight = 0
// 添加一个简单表示例：
const worksheet = Excel.getSheet(workbook, '简单表示例')
Excel.addSimpleTable(worksheet, {
columns: [
{
title: '主键',
name: 'id',
width: 8
},
{
title: '信息备注',
name: 'remark',
width: 16
}
]
},
[
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' }
])
// 提交工作薄
worksheet.commit()
// 自由绘制excel内容：
const worksheet1 = Excel.getSheet(workbook, '自由绘制excel内容')
// 绘制表格标题
Excel.addSystemInfo(worksheet1, {
content: [
{ font: { bold: true, size: 18 }, text: '我是标题XXXXXXXX ' },
{ font: { bold: true, size: 10 }, text: '导出时间：#now 导出人: #userName' }
]
}, 'A1')
// 绘制表格头部（简单单表）
Excel.addTableHeader(worksheet1, {
columns: [
{
title: '主键',
name: 'id',
width: 10
},
{
title: '信息备注',
name: 'remark',
width: 100
}
]
}, 2)
// 填充数据
Excel.addTableData(worksheet1, [
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' },
{ id: 1, remark: 'nihao' }
])
// 添加多文本信息
Excel.addAttention(worksheet1, {
content: [
{ font: { bold: true, size: 12 }, text: '注意：1.xxxxxxx 2.xxxxxxx 3.xxxxxxx' }
],
rows: 3
})
Excel.commitContent(worksheet1)
_currentHeight = Excel.getCurrentHeight(worksheet1)
// 绘制表格头部（支持多层级表格）
Excel.addTableHeader(worksheet1, {
columns: [
{
title: '主键',
name: 'id',
width: 10
},
{
title: '信息备注',
name: 'remark',
width: 100
},
{
title: '一级列',
width: 100,
item: [
{
title: '姓名',
name: 'name',
width: 70
},
{
title: '年龄',
name: 'age',
width: 20
},
{
title: '二级列',
width: 80,
item: [
{
title: '薪资',
name: 'salery',
width: 30
},
{
title: '工作年限',
name: 'days',
width: 90
},
{
title: '工作年限',
name: 'shouru',
render: function (row, data) { row.shouru = row.salery * row.days },
width: 90
}
]
}
]
}
]
}, _currentHeight)
// 填充数据
Excel.addTableData(worksheet1, [
{ id: 1, remark: 'nihao', name: 'nihao', age: 12, salery: 2300, days: 12, shouru: 0 },
{ id: 1, remark: 'nihanihaonihaoo', name: 'nihaoaaa', age: 14, salery: 2300, days: 12, shouru: 0 },
{ id: 1, remark: 'nihao', name: 'nihaonihaonihaonihaonihaonihaonihaonihaonihaonihaonihaonihaonihao', age: 34, salery: 2300, days: 12, shouru: 0 },
{ id: 1, remark: 'nihao', name: 'nihaoa', age: 34, salery: 2300, days: 12, shouru: 0 },
{ id: 1, remark: 'nihao', name: 'nihaoaaa', age: 23, salery: 2300, days: 12, shouru: 0 }
])
// 其它的api
console.log('表格高度: ', Excel.getCurrentHeight(worksheet1))
console.log('表格宽度：', Excel.getCurrentWidth(worksheet1))
worksheet1.commit()
// 添加树形表示例：
const worksheet2 = Excel.getSheet(workbook, '树形表示例')
// 绘制表格头部
Excel.addTableHeader(worksheet2, {
columns: [
{
title: '一级',
name: 'level1',
width: 10
},
{
title: '二级',
name: 'level2',
width: 10
},
{
title: '三级',
name: 'level3',
width: 10
},
{
title: '四级',
name: 'level4',
width: 10
},
{
title: '数量',
name: 'amount',
width: 20
},
{
title: '金额',
name: 'jeSum',
width: 50
}
]
})
// 填充数据
Excel.addTableData(worksheet2, [
{ _outLine: 0, level1: '钱塘江大桥', level2: '', level3: '', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 1, level1: '钱塘江大桥', level2: '桥面工程', level3: '', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 2, level1: '钱塘江大桥', level2: '桥面工程', level3: '路面', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥面工程', level3: '路面', level4: '1-200路基', amount: 2, jeSum: 23 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥面工程', level3: '路面', level4: '200-400路基', amount: 2, jeSum: 23 },
{ _outLine: 2, level1: '钱塘江大桥', level2: '桥面工程', level3: '围栏', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥面工程', level3: '围栏', level4: '400-600围栏', amount: 2, jeSum: 23 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥面工程', level3: '围栏', level4: '600-800围栏', amount: 2, jeSum: 23 },
{ _outLine: 1, level1: '钱塘江大桥', level2: '桥体工程', level3: '', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 2, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '1#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '2#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '3#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '4#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '5#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '6#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '7#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '8#', amount: 10, jeSum: 220 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥墩', level4: '9#', amount: 10, jeSum: 220 },
{ _outLine: 2, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '', amount: 0, jeSum: 0 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '1#', amount: 10, jeSum: 10 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '2#', amount: 20, jeSum: 20 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '3#', amount: 450, jeSum: 40 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '4#', amount: 60, jeSum: 20 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '5#', amount: 40, jeSum: 40 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '6#', amount: 30, jeSum: 60 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '7#', amount: 20, jeSum: 30 },
{ _outLine: 3, level1: '钱塘江大桥', level2: '桥体工程', level3: '桥柱', level4: '8#', amount: 50, jeSum: 20 }
])
console.log('表格高度: ', Excel.getCurrentHeight(worksheet2))
console.log('表格宽度：', Excel.getCurrentWidth(worksheet2))
worksheet2.commit()
// 提交文件
workbook.commit()