通过xlsx库实现将数据生成excel

1. 数据生成excel
2. 自定义表头
3. 合并单元格
4. 最后一行求和
5. 

import * as XLSX from 'xlsx'
/**
 * 下载excel
 * @param {Array} data 下载的数据
 * @param {String} filename 文件名
 * @param {Object} headerMapping 表头 例如：{name:'姓名'}
 * @param {Array} mergeCell 合并单元格 例如:[ { s: { r: 1, c: 7 }, e: { r: 3, c: 7 }}]
 *  s 代表开始 e代表结束  r代表行 c代表列
 * @param {Boolean} isSum 是否求和
 */
export function exportToExcel(data, filename, headerMapping = null, mergeCell = null, isSum = false) {
  const worksheet = XLSX.utils.json_to_sheet(data)
  // 修改表头
  if (headerMapping) {
    const headerRange = XLSX.utils.decode_range(worksheet['!ref'])
    for (let col = headerRange.s.c; col <= headerRange.e.c; col++) {
      const headerCell = XLSX.utils.encode_cell({ r: 0, c: col })
      const headerCellValue = worksheet[headerCell].v
      if (headerMapping.hasOwnProperty(headerCellValue)) {
        worksheet[headerCell].v = headerMapping[headerCellValue]
      }
    }
  }
  // 合并单元格
  if (mergeCell) {
    worksheet['!merges'] = mergeCell
  }
  if (isSum) {
    const range = XLSX.utils.decode_range(worksheet['!ref'])
    // 插入总和行
    const totalRow = range.e.r + 1 // 最后一行的下一行
    const totalCell = XLSX.utils.encode_cell({ r: totalRow, c: 0 }) // 第一列的单元格
    worksheet[totalCell] = { t: 's', v: '总和' }
    worksheet['!ref'] = `A1:${String.fromCharCode(range.e.c + 65)}${totalRow + 1}`
    // 求和
    for (let i = 1; i <= range.e.c; i++) {
      let sum = 0
      for (let j = 1; j <= range.e.r; j++) {
        const cel = XLSX.utils.encode_cell({ r: j, c: i })// 每一列的的每一个单元格
        if (worksheet[cel].t !== 'n') { // 如果不是数值类型不累加
          break
        }
        sum += worksheet[cel].v
      }
      const c = XLSX.utils.encode_cell({ r: totalRow, c: i })
      worksheet[c] = { t: 'n', v: sum || '' }
    }
  }
  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
  XLSX.writeFile(workbook, `${filename}.xlsx`)
}

例子：

export default {
  data() {
    return {
      tableData: [
        {
          project: '过磅费',
          openingBalance: 10,
          shouldMoney: 10000,
          realityMoney: 8000,
          closeBalance: 9,
          remark: '无',
          order: 1,
          createBy: '小李',
          financeLeader: '财务主管',
          checkPerson: '财务小张',
          manager: '张经理',
          editable: false
        },
        {
          project: '临时摊位费',
          openingBalance: 10,
          shouldMoney: 10000,
          realityMoney: 8000,
          closeBalance: 9,
          remark: '无',
          order: 1,
          createBy: '小李',
          financeLeader: '财务主管',
          checkPerson: '财务小张',
          manager: '张经理',
          editable: false
        },
        {
          project: '保证金',
          openingBalance: 10,
          shouldMoney: 10000,
          realityMoney: 8000,
          closeBalance: 9,
          remark: '无',
          order: 1,
          createBy: '小李',
          financeLeader: '财务主管',
          checkPerson: '财务小张',
          manager: '张经理',
          editable: false
        }
      ]
    }
  },
  methods: {
    downLoad() {
      const fields = {
        project: '项目',
        openingBalance: '期初数',
        shouldMoney: ' 本月应收',
        realityMoney: ' 本月实收',
        closeBalance: '期末数',
        remark: '备注',
        order: '明细表序列:',
        createBy: '创建人',
        financeLeader: '财务部负责人',
        checkPerson: '核对人',
        manager: '部门经理'
      }
      const mergeCells = [
        { s: { r: 1, c: 7 }, e: { r: 3, c: 7 }},
        { s: { r: 1, c: 8 }, e: { r: 3, c: 8 }},
        { s: { r: 1, c: 9 }, e: { r: 3, c: 9 }},
        { s: { r: 1, c: 10 }, e: { r: 3, c: 10 }}
      ]
      exportToExcel(this.tableData, 'user', fields, mergeCells, true)
    }
  }
}