vue使用xlsx-style 导出excel 并带有样式

一、下载插件

npm install xlsx-style --save
npm install xlsx --save

下载好在 vue.config.js 修改一下 xlsx-style 不然会报错

// 添加这一句
config.externals({ './cptable': 'var cptable' })

二 创建此文件文件，如果想要修改样式就在 红色圈出的（export_json_to_excel）这里修改，其他不变

这些就是修改的样式，可以看一下官方文档，文章下面有链接

styleExcel文件

/* eslint-disable no-empty */
/* eslint-disable eqeqeq */
import { saveAs } from 'file-saver'
// import XLSX from "xlsx";
import XLSX from 'xlsx-style'
function generateArray(table) {
  var out = []
  var rows = table.querySelectorAll('tr')
  var ranges = []
  for (var R = 0; R < rows.length; ++R) {
    var outRow = []
    var row = rows[R]
    var columns = row.querySelectorAll('td')
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C]
      var colspan = cell.getAttribute('colspan')
      var rowspan = cell.getAttribute('rowspan')
      var cellValue = cell.innerText
      // eslint-disable-next-line eqeqeq
      if (cellValue !== '' && cellValue == +cellValue) cellValue = +cellValue
      // Skip ranges
      ranges.forEach(function(range) {
        if (
          R >= range.s.r &&
          R <= range.e.r &&
          outRow.length >= range.s.c &&
          outRow.length <= range.e.c
        ) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null)
        }
      })

      // Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1
        colspan = colspan || 1
        ranges.push({
          s: {
            r: R,
            c: outRow.length
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        })
      }

      // Handle Value
      outRow.push(cellValue !== '' ? cellValue : null)
      // Handle Colspan
      if (colspan) { for (var k = 0; k < colspan - 1; ++k) outRow.push(null) }
    }
    out.push(outRow)
  }
  return [out, ranges]
}

function datenum(v, date1904) {
  if (date1904) v += 1462
  var epoch = Date.parse(v)
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {}
  var range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  }
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R
      if (range.s.c > C) range.s.c = C
      if (range.e.r < R) range.e.r = R
      if (range.e.c < C) range.e.c = C
      var cell = {
        v: data[R][C]
      }
      // 如果单元格所在的值为空，让其值为“”
      if (cell.v == null) {
        cell.v = ''
      }
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      })
      if (typeof cell.v === 'number') {
        cell.t = 'n'
      } else if (typeof cell.v === 'boolean') cell.t = 'b'
      else if (cell.v instanceof Date) {
        cell.t = 'n'
        cell.z = XLSX.SSF._table[14]
        cell.v = datenum(cell.v)
      } else {
        cell.t = 's'
      }
      ws[cell_ref] = cell
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)
  return ws
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook()
  this.SheetNames = []
  this.Sheets = {}
}

function 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
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id)
  var oo = generateArray(theTable)
  var ranges = oo[1]

  /* original data */
  var data = oo[0]
  var ws_name = 'SheetJS'

  var wb = new Workbook()
  var ws = sheet_from_array_of_arrays(data)
  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name)
  wb.Sheets[ws_name] = ws

  var wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  })

  saveAs(
    new Blob([s2ab(wbout)], {
      type: 'application/octet-stream'
    }),
    'test.xlsx'
  )
}
// 主要修改内容在这里
export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  percent = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header)
  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }
  var ws_name = 'SheetJS'
  var wb = new Workbook()
  var ws = sheet_from_array_of_arrays(data)

  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = []
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  // 设置单元格宽度
  if (autoWidth) {
    /* 设置worksheet每列的最大宽度*/
    const colWidth = data.map(row =>
      row.map(val => {
        /* 先判断是否为null/undefined*/
        if (val == null || val == undefined) {
          return {
            wch: 20
          }
        } else if (val.toString().charCodeAt(0) > 255) {
          /* 再判断是否为中文*/
          return {
            wch: val.toString().length * 2
          }
        } else {
          return {
            'wch': val.toString().length * 2
          }
        }
      })
    )
    /* 以主表第二行为初始值，因为我的第一行是表格标题，会比较长，所以以主表第二行为初始值*/
    const result = colWidth[0]
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch']
        }
      }
    }
    ws['!cols'] = result
  }
  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name)
  wb.Sheets[ws_name] = ws

  var dataInfo = wb.Sheets[wb.SheetNames[0]]
  // 设置单元格框线
  const borderAll = {
    top: {
      style: 'thin'
    },
    bottom: {
      style: 'thin'
    },
    left: {
      style: 'thin'
    },
    right: {
      style: 'thin'
    }
  }

  // 给所有单元格加上边框，内容居中，字体，字号，标题表头特殊格式部分后面替换
  for (var i in dataInfo) {
    if (
      i == '!ref' ||
      i == '!merges' ||
      i == '!cols' ||
      i == '!rows' ||
      i == 'A1'
    ) {} else {
      dataInfo[i + ''].s = {
        border: borderAll,
        alignment: {
          horizontal: 'center',
          vertical: 'center'
        },
        font: {
          name: '微软雅黑',
          sz: 12
        }
      }
    }
  }

  const arrabc = ['A',
    'B',
    'C',
    'D',
    'E',
    'F',
    'G',
    'H',
    'I',
    'J',
    'K',
    'L',
    'M',
    'N',
    'O',
    'P',
    'Q',
    'R',
    'S',
    'T',
    'U',
    'V',
    'W',
    'X',
    'Y',
    'Z'
  ]

  // 给标题、表格描述信息、表头等部分加上特殊格式
  arrabc.some(function(v) {
    for (let j = 1; j < (data.length + 1); j++) {
      const _v = v + j
      if (dataInfo[_v]) {
        dataInfo[_v].s = {}
        // 标题部分A1-Z1
        dataInfo['A' + j].s = {
          border: borderAll,
          font: {
            name: '微软雅黑',
            sz: 12,
            color: {
              rgb: '000000'
            },
            bold: true
          },
          alignment: {
            horizontal: 'center',
            vertical: 'center'
          }
        }
        dataInfo[v + j].s = {
          border: borderAll,
          numFmt: '0.00',
          font: {
            name: '微软雅黑',
            sz: 12,
            color: {
              rgb: '000000'
            }
          },
          alignment: {
            horizontal: 'right',
            vertical: 'center'
          }
        }
        // 头部
        if (j == 1) {
          dataInfo[v + j].s = {
            border: borderAll,
            font: {
              name: '微软雅黑',
              sz: 13,
              color: {
                rgb: '000000'
              },
              bold: true
            },
            alignment: {
              horizontal: 'center',
              vertical: 'center'
            },
            fill: {
              fgColor: {
                rgb: 'f0f0f0'
              }
            }
          }
        }
        // 百分比 %
        if (percent.length != 0) {
          for (let index = 0; index < percent.length; index++) {
            if (j == percent[index]) {
              dataInfo[v + percent[index]].s = {
                numFmt: '0.00%',
                border: borderAll,
                font: {
                  name: '微软雅黑',
                  sz: 12,
                  color: {
                    rgb: '000000'
                  }
                },
                alignment: {
                  horizontal: 'right',
                  vertical: 'center'
                },
                fill: {
                  fgColor: {
                    rgb: 'FFF8DC'
                  }
                }
              }
            }
          }
        }
      }
    }
  })

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  })
  saveAs(
    new Blob([s2ab(wbout)], {
      type: 'application/octet-stream'
    }),
    `${filename}.${bookType}`
  )
}

三、在 utils 里创建 ExcelUtil2.js 文件 用于封装，（此文件可有可无）如果每个页面都要用到还是封装一下

/* eslint-disable eqeqeq */
/**
 * Excel带样式的工具类
 */
/**
//    * @param dataList Array 查询的数据列表
//    * @param tHeader Array 最后一行表头
//    * @param filterKey Array 要导出的字段
//    * @param merges Array 控制表头的跨行跨列
//    * @param filename String 导出的excel文件名称
      * @param percent Array 填写有百分号行数，处理百分号
      * @param num Number||string 根据index_type的值来判断，处理百分号
      * @param indexArr Array 获取index_type的值，进行判断
*/

export function exportStyleExcel(tHeader, filterKey, dataList, filename, multiHeader, merges, num, indexArr) {
  import('@/vendor/styleExcel').then(excel => {
    // 过滤表格输出的数据
    const filterData = formatJson(filterKey, dataList)
    const percent = searchKeys(num, indexArr)
    excel.export_json_to_excel({
      multiHeader,
      header: tHeader,
      data: filterData,
      filename,
      percent,
      merges,
      autoWidth: true,
      bookType: 'xlsx',
      myRowFont: '2'
    })
  })
}

function formatJson(filterKey, dataList) {
  return dataList.map(v => filterKey.map(j => {
    // 如果是金额相关的字段，则导出时返回数字格式，以便计算合计
    if (j.indexOf('money') > -1) {
      var num = v[j]
      if (num === null || num === '' || num === undefined) {
        num = 0
      }
      return parseFloat(num)
    } else {
      return v[j]
    }
  }))
}
function searchKeys(needle, haystack) {
  var percent = []
  for (const i in haystack) {
    if (haystack[i] == needle) {
      percent.push(+i + 2)
    }
  }
  return percent
}

四、引用刚刚封装的ExcelUtil2.js文件

import { exportStyleExcel } from '@/utils/ExcelUtil2'

给导出设置个点击事件

// 导出
    handleDownload() {
      this.$message('正在导出，请稍等')
      this.indexDataList('xlsx')
      const tHeader = []
      this.headerList.forEach((item) => {
        tHeader.push(item.name)
      })
      const filterVal = []
      this.headerList.forEach((item) => {
        filterVal.push(item.code)
      })
      const filename = '应收账款报表'
      //tHeader 指的是表头 filterVal 指的是表头字段 this.todoListXlsx 指的是内容 重要的这三个 4 是我处理百分号导出为文本型改为数字型
      exportStyleExcel(tHeader, filterVal, this.todoListXlsx, filename, [], [], 4, this.indexArr)
    }

最终效果

如果要修改样式 可以看看文档
添加链接描述