Vue 解析 Public 下的 Excel 模板文件并修改数据

<script>
import XLSX from 'xlsx'
import axios from 'axios'
export default {
  data() {
    return {
      workbook: null,
      sheetData: null
    }
  },
  created() {
    axios.get('/public/template.xlsx', { responseType: 'arraybuffer' })
      .then(res => {
        const data = new Uint8Array(res.data)
        this.workbook = XLSX.read(data, { type: 'array' })
        this.sheetData = XLSX.utils.sheet_to_json(this.workbook.Sheets[this.workbook.SheetNames[0]])
        // 对数据进行修改
        const newTableData = [
          { col1: 'value1', col2: 'value2', col3: 'value3' },
          { col1: 'value4', col2: 'value5', col3: 'value6' },
          // ...
        ]
        this.sheetData = [...this.sheetData, ...newTableData]
        // 在模板excel文件最后一行，不是最后一列加入table的数据包括表头
        const range = XLSX.utils.decode_range(this.workbook.Sheets[this.workbook.SheetNames[0]]['!ref'])
        const headerRange = { s: { c: range.s.c, r: range.s.r }, e: { c: range.e.c, r: range.s.r } }
        const lastRowRange = { s: { c: range.s.c, r: range.e.r + 1 }, e: { c: range.e.c, r: range.e.r + 1 } }
        XLSX.utils.sheet_add_json(this.workbook.Sheets[this.workbook.SheetNames[0]], [{ col1: 'title1', col2: 'title2', col3: 'title3' }], headerRange)
        XLSX.utils.sheet_add_json(this.workbook.Sheets[this.workbook.SheetNames[0]], newTableData, lastRowRange)
        // 对整表添加样式
        const style = {
          font: { name: 'Arial', sz: 12, bold: true },
          fill: { fgColor: { rgb: 'FFFFAA00' } }
        }
        XLSX.utils.sheet_set_range_style(this.workbook.Sheets[this.workbook.SheetNames[0]], 'A1:C3', style)
        // 合并部分表格
        const mergeRange = { s: { c: 0, r: 4 }, e: { c: 2, r: 4 } }
        XLSX.utils.sheet_merge_range(this.workbook.Sheets[this.workbook.SheetNames[0]], mergeRange.s.r, mergeRange.s.c, mergeRange.e.r, mergeRange.e.c)
        // 添加边框
        const borderStyle = {
          top: { style: 'thin', color: { rgb: '000000' } },
          bottom: { style: 'thin', color: { rgb: '000000' } },
          left: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } }
        }
        const borderRange = { s: { c: 0, r: 0 }, e: { c: 2, r: this.sheetData.length } }
        XLSX.utils.sheet_set_border(this.workbook.Sheets[this.workbook.SheetNames[0]], borderRange.s.r, borderRange.s.c, borderRange.e.r, borderRange.e.c, borderStyle)
      })
  }
}
</script>