vue导出多页表格

1. 安装依赖

npm install file-saver --save
npm install xlsx --save
npm install script-loader --save-dev

2. vue调用导出方法

  <div class="app-container">
    <el-button
      style="margin-bottom:20px"
      type="primary"
      icon="el-icon-document"
      @click="handleDownload"
    >
      export
    </el-button>
  </div>
</template>

<script lang="ts">
import { defineComponent } from 'vue'
import { exportJsonToExcelMult } from '@/utils/excel'
export default defineComponent({
  setup() {
    const handleDownload = () => {
      // 注意：组装的导出excel所需要的数据结构
      const multiHeader = [
        [
          ['comm序号', 'null', 'null', 'null', 'Date'],
          ['comm', 'null', 'null', 'Readings', 'Date']
        ],
        [
          ['序号', '标题', '作者', '服务'],
          ['序号', '标题', '作者', '服务']
        ]
      ]
      const header = [
        ['Id', 'Title', 'Author', 'Readings', 'Date'],
        ['序号', '标题', '作者', '服务']
      ]
      const data = [
        [
          [
            1,
            'Gxjxpr Jdfjwf Lxssr Deyonm Moxy Ryxhzp Gxtukv Bvnmvmlo',
            'Melissa',
            1670,
            '1998-10-24 01:53:18'
          ],
          [
            2,
            'Moxy Ryxhzp Gxtukv Bvnmvmlo',
            'Melissa',
            1670,
            '1998-10-24 01:53:18'
          ],
          [3, 'Bvnmvmlo', 'Melissa', 1670, '1998-10-24 01:53:18']
        ],
        [
          [
            16666,
            'Gxjxpr Jdfjwf Lxssr Deyonm Moxy Ryxhzp Gxtukv Bvnmvmlo',
            'Melissa',
            1670
          ],
          [266666, 'Moxy Ryxhzp Gxtukv Bvnmvmlo', 'Melissa', 1670],
          [366666, 'Bvnmvmlo', 'Melissa', 1670]
        ]
      ]
      const sheetname = ['sheet1', 'sheet2']
      const filename = 'exportMultExcel'
      const merges = [['A1:D1', 'A2:C2'], []]
      exportJsonToExcelMult(
        multiHeader,
        header,
        data,
        sheetname,
        filename,
        merges,
        true,
        'xlsx'
      )
    }
    return { handleDownload }
  }
})
</script>

3. utils文件夹下建 excel.ts，用来封装方法

不会TS,暂时如此

import { saveAs } from 'file-saver'
import XLSX from 'xlsx'

export const exportJsonToExcelMult = (
  multiHeader: any[] = [],
  header: any[] = [],
  data: any,
  sheetname: string[],
  filename = 'excel-list',
  merges: any[] = [],
  autoWidth = true,
  bookType = 'xlsx'
) => {
  data = [...data]
  for (let i = 0; i < header.length; i++) {
    data[i].unshift(header[i])
  }
  if (multiHeader.length) {
    multiHeader.forEach((mh, index) => {
      for (let i = mh.length - 1; i > -1; i--) {
        data[index].unshift(mh[i])
      }
    })
  }

  const wsName = sheetname
  const wb = new Workbook()
  const ws: any[] = []
  for (let i = 0; i < header.length; i++) {
    ws.push(sheetFromDataArray(data[i]))
  }

  if (merges.length > 0) {
    merges.forEach((items, index) => {
      if (!items) return
      items.forEach((item: any) => {
        if (!ws[index]['!merges']) {
          ws[index]['!merges'] = []
        }
        ws[index]['!merges'].push(XLSX.utils.decode_range(item))
      })
    })
  }
  if (autoWidth) {
    // 设置worksheet每列的最大宽度
    const colWidth: any[] = []
    for (let i = 0; i < header.length; i++) {
      colWidth.push(
        data[i].map((row: any[]) =>
          row.map(val => {
            // 先判断是否为 null/undefined
            if (val == null) {
              return {
                wch: 10
              }
              // 再判断是否为中文
            } else if (val.toString().charCodeAt(0) > 255) {
              return {
                wch: val.toString().length * 2
              }
            } else {
              return {
                wch: val.toString().length
              }
            }
          })
        )
      )
    }
    // 以第一行为初始值
    const result = []
    for (let k = 0; k < colWidth.length; k++) {
      result[k] = colWidth[k][0]
      for (let i = 1; i < colWidth[k].length; i++) {
        for (let j = 0; j < colWidth[k][i].length; j++) {
          if (result[k][j].wch < colWidth[k][i][j].wch) {
            result[k][j].wch = colWidth[k][i][j].wch
          }
        }
      }
    }
    // 分别给sheet表设置宽度
    for (let i = 0; i < result.length; i++) {
      ws[i]['!cols'] = result[i]
    }
  }

  // Add worksheet to workbook
  for (let i = 0; i < header.length; i++) {
    wb.SheetNames.push(wsName[i])
    wb.Sheets[wsName[i]] = ws[i]
  }

  const wbout = XLSX.write(wb, {
    bookType: bookType as any,
    bookSST: false,
    type: 'binary'
  })

  saveAs(
    new Blob([s2ab(wbout)], {
      type: 'application/octet-stream'
    }),
    `${filename}.${bookType}`
  )
}