环境：

node:v16.3.0

npm:7.15.1

"xlsx": "^0.18.5",

"xlsx-style": "^0.8.13"

"file-saver": "^2.0.5"

npm相关包下载

//地址：https://www.npmjs.com/package/xlsx
npm i xlsx
//地址：https://www.npmjs.com/package/xlsx-style
npm i xlsx-style
//地址：https://www.npmjs.com/package/file-saver
npm i file-saver

引入

import * as XLSX from 'xlsx/xlsx.mjs'
import XLSXS from 'xlsx-style'
import FileSaver from 'file-saver'

导出

<template>
 <el-button
    class="el-blue-button"
    size="mini"
    @click="exportExcel"
    style="float: right"
  >
    导出
</el-button>
<cc-table
    id="outTable"  //必须
    :table-column="tableColumn"
    :table-data="tableData"
    @refresh="getTableData"
  />
 </template>
  
<script>
import * as XLSX from 'xlsx/xlsx.mjs'
import XLSXS from 'xlsx-style'
import FileSaver from 'file-saver'
export default {
     methods: {
         exportExcel () {
          var wb = XLSX.utils.table_to_book(document.querySelector('#outTable'), { raw: false })
          const sheet = wb
          sheet.SheetNames.forEach(sheetName => {
            const worksheetData = sheet.Sheets[sheetName]

            // 设置单元格高度 无效
            // worksheetData['!rows'] = [{
            //   hpx: 80
            // }, {
            //   hpx: 40
            // }, {
            //   hpx: 30
            // }, {
            //   hpx: 20
            // }]
            // 设置单元格宽度 有效
            // worksheetData['!cols'] = [{
            //   wpx: 140
            // }, {
            //   wpx: 100
            // }, {
            //   wpx: 100
            // }, {
            //   wpx: 100
            // }]
           
            // 添加样式
            const range = XLSX.utils.decode_range(worksheetData['!ref'])
            for (let row = range.s.r; row <= range.e.r; row++) {
              for (let col = range.s.c; col < range.e.c; col++) {
                // console.log({ r: row, c: col })
                const cellAddress = XLSX.utils.encode_cell({ r: row, c: col })
                // console.log('cellAddress', cellAddress)
                // 设置单元格宽度
                worksheetData['!cols'][col] = {
                  wpx: 140
                }
                const cellStyle = {
                  font: {
                    bold: false,
                    color: {
                      rgb: '000000'
                    },
                    name: '微软雅黑',
                    sz: 12
                  },
                  alignment: {
                    // 居中
                    horizontal: 'center',
                    vertical: 'center'
                  }
                }
                if (row === 0) {
                  //如果为第一行，单独指定样式
                  worksheetData[cellAddress].s = {
                    fill: {
                      fgColor: {
                        rgb: 'EBF0FE'
                      }
                    },
                    font: {
                      bold: true,
                      name: '微软雅黑',
                      sz: 12
                    },
                    border: {
                      top: {
                        style: 'thin',
                        color: {
                          auto: 1
                        }
                      },
                      left: {
                        style: 'thin',
                        color: {
                          auto: 1
                        }
                      },
                      right: {
                        style: 'thin',
                        color: {
                          auto: 1
                        }
                      },
                      bottom: {
                        style: 'thin',
                        color: {
                          auto: 1
                        }
                      }
                    },
                    alignment: {
                      // 居中
                      horizontal: 'center',
                      vertical: 'center'
                    }
                  }
                } else {
                  if (col === 0) {
                    // 如果为第一列：时间格式指定
                    worksheetData[cellAddress].z = 'yyyy-mm-dd hh:mm:ss'
                  }
                  worksheetData[cellAddress].s = cellStyle
                }
              }
            }
          })
          /* get binary string as output */
          var wbout = XLSXS.write(sheet, { bookType: 'xlsx', bookSST: false, type: 'binary' })
          try {
            FileSaver.saveAs(
              new Blob([this.s2ab(wbout)], {
                type: 'application/octet-stream'
              }),
              this.$route.meta.label + '.xlsx'
            )
          } catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
          return wbout
        },
        s2ab (s) {
          var buf = new ArrayBuffer(s.length)
          var view = new Uint8Array(buf)
          for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
          return buf
        }
     }
}
</script>

说明

raw如果为真，每个单元格将保存原始字符串，这会导致excel表格右下角无法为数值时显示：平均值、求和，仅显示计数

var wb = XLSX.utils.table_to_book(document.querySelector('#outTable'), { raw: false })

raw如果为false，这会导致时间格式导出值为数值格式，所以需要手动格式化

worksheetData[cellAddress].z = 'yyyy-mm-dd hh:mm:ss'

报错

• ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js

  解决：

  在vue.config.js 添加

   module.exports = {
     ...
     configureWebpack: {
       ...
       externals: [{
         './cptable': 'var cptable'
       }],
     },
     ...
   }
  

其他

添加两个Sheet页

 const wsData = [
    ['风速', '功率'],
    ...data[0].data
  ]
  const wsData2 = [
    ['风速', '功率'],
    ...data[1].data
  ]
  const ws = XLSX.utils.aoa_to_sheet(wsData)
  const ws2 = XLSX.utils.aoa_to_sheet(wsData2)
  const wb = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(wb, ws, data[0].name, true)
  XLSX.utils.book_append_sheet(wb, ws2, data[1].name, true)
  XLSX.writeFile(wb, `${dayjs().format('YYYY-MM-DD HH:mm:ss')} 散点图导出.xlsx`)
  

将sheet转换为json

const originalData = XLSX.utils.sheet_to_json(worksheetData, { header: 1 })
originalData.unshift([['标题']]);

tableToSheet时指定sheet名称

var wb = XLSX.utils.table_to_book(document.querySelector('#outTable'), { sheet: '功率预测列表' }, { raw: false })