excel-conduct-library

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()