两种vue导出excel表格方法

1.基于 js-export-excel 插件实现简单表格导出（缺点不支持表格样式修改）

import ExportJsonExcel from 'js-export-excel'
export function parseTime(time, cFormat) {  // 日期格式化方法
  if (arguments.length === 0) {
    return null
  }
  const format = cFormat || '{y}-{m}-{d} {h}:{i}:{s}'
  let date
  if (typeof time === 'object') {
    date = time
  } else {
    if ((typeof time === 'string') && (/^[0-9]+$/.test(time))) {
      time = parseInt(time)
    }
    if ((typeof time === 'number') && (time.toString().length === 10)) {
      time = time * 1000
    }
    date = new Date(time)
  }
  const formatObj = {
    y: date.getFullYear(),
    m: date.getMonth() + 1,
    d: date.getDate(),
    h: date.getHours(),
    i: date.getMinutes(),
    s: date.getSeconds(),
    a: date.getDay(),
  }
  // eslint-disable-next-line camelcase
  const time_str = format.replace(/{(y|m|d|h|i|s|a)+}/g, (result, key) => {
    let value = formatObj[key]
    // Note: getDay() returns 0 on Sunday
    // eslint-disable-next-line standard/computed-property-even-spacing
    if (key === 'a') { return ['日', '一', '二', '三', '四', '五', '六'][value] }
    if (result.length > 0 && value < 10) {
      value = '0' + value
    }
    return value || 0
  })
  // eslint-disable-next-line camelcase
  return time_str
}


export function downloadData(data, columns, filename) {
  var option = {}
  let dataTable = []
  let header = []
  for (let j in columns) {
    if (columns[j].ignoreExport) {
      continue
    }
    // header.push(columns[j].title)
    header.push(columns[j].title || columns[j].titles || '-')
  }
  if (data) {  // 格式化data数据，注：data格式为[{'1列名': 值,'2列名': 值},'3列名': 值}]
    for (let i in data) {
      console.log('i', i)
      let obj = {}
      for (let j in columns) {
        if (columns[j].ignoreExport || columns[j].field === 'operation') {
          continue
        }
        if (columns[j].formatter) {
          obj[columns[j].title || columns[j].titles || '-'] = columns[j].formatter.format(data[i]).value

        } else {
          obj[columns[j].title || columns[j].titles || '-'] = data[i][columns[j].field]
        }
      }
      dataTable.push(obj)
    }
  }
  option.fileName = filename + " " + parseTime(new Date(), '{y}-{m}-{d}')
  option.datas = [
    {
      sheetData: dataTable,
      sheetName: filename,
      sheetFilter: header,
      sheetHeader: header,
      columnWidths: [8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8], // 列宽
    }
  ]
  option.sheetHeaderStyle = {
    alignment: {
      horizontal: 'center',
    },
  };

  option.cellStyle = {
    alignment: {
      horizontal: 'center',
    },
  };
  option.autoWidth = true;
  var toExcel = new ExportJsonExcel(option)
  toExcel.saveExcel()
}

2.基于XLSX，xlsx-style，file-saver插件实现，支持自定义修改表格样式(直接使用会有bug，处理方式见下方备注)

import XLSX from 'xlsx';
import XLSX_STYLE from 'xlsx-style';
import { saveAs } from 'file-saver';

export function downloadData3(data, columns, filename) {
  var option = {}
  let dataTable = []
  let header = []
  if (data) {
    for (let i in data) {
      console.log('i', i)
      let obj = {}
      for (let j in columns) {
        if (columns[j].ignoreExport || columns[j].field === 'operation') {
          continue
        }
        if (columns[j].formatter) {
          obj[columns[j].title || columns[j].titles || '-'] = columns[j].formatter.format(data[i]).value

        } else {
          obj[columns[j].title || columns[j].titles || '-'] = data[i][columns[j].field] || ''
        }
      }
      dataTable.push(obj)
    }
  }
  // 设置列宽度
  const columnWidths = [
    { wpx: 200 },
    { wpx: 200 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
    { wpx: 150 },
  ];

  // 将列宽度应用到工作表
  // 定义表头样式
  const headerStyle = {
    fill: {
      fgColor: { rgb: 'd9d4d4' },
    },
    font: {
      color: { rgb: '000000' },
      name: 'Calibri',
      sz: 11,
      bold: true
    },
    alignment: { horizontal: 'center' },
    border: {
      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 contentStyle = {
    font: {
      name: 'Calibri',
      sz: 11,
    },
    alignment: { horizontal: 'center' },
    border: {
      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 worksheet = XLSX.utils.json_to_sheet(dataTable);
  worksheet['!cols'] = columnWidths;
  // 将表头样式应用到 worksheet 对象中的表头单元格
  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: headerRange.s.r, c: col });
    worksheet[headerCell].s = headerStyle;
  }

  // 将内容样式应用到 worksheet 对象中的所有单元格
  const contentRange = XLSX.utils.decode_range(worksheet['!ref']);
  for (let row = contentRange.s.r + 1; row <= contentRange.e.r; row++) {
    for (let col = contentRange.s.c; col <= contentRange.e.c; col++) {
      const contentCell = XLSX.utils.encode_cell({ r: row, c: col });
      worksheet[contentCell].s = contentStyle;
    }
  }
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
  // 将 workbook 对象转换为二进制数据流并下载
  const wbout = XLSX_STYLE.write(workbook, { bookType: 'xlsx', type: 'binary' });
  const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' });
  saveAs(blob, filename + '.xlsx');
};
export function s2ab(s) {
  const buf = new ArrayBuffer(s.length);
  const view = new Uint8Array(buf);
  for (let i = 0; i < s.length; i++) {
    view[i] = s.charCodeAt(i) & 0xFF;
  }
  return buf;
}

备注

• 方式二问题处理
  问题一：

import xlsx之后一直在报错utils未定义，下载时直接下载如下版本
npm install --save xlsx@0.17.0
npm install --save file-saver@2.0.5

问题二：
Excel文件导出时所用的xlsx-style库错误所导致的代码运行报错

image.png

在vue.config.js添加externals配置

const vueConfig = {
  configureWebpack: {
    // webpack plugins
    externals: {
      './cptable': 'var cptable', // 添加它
    }
  },
}

• columns参考

this.columns = [
      {
        field: 'barCode',
        title: '商品条码',
        width: 140,
        align: 'center',
      },
      {
        field: 'goodsTitle',
        title: '商品名称',
        width: 180,
        showTip: true,
        align: 'center',
      },
      {
        field: 'goodsSpec',
        title: '规格',
        width: 100,
        align: 'center',
      },
      {
        field: 'goodsUnit',
        title: '单位',
        width: 100,
        align: 'center',
      },
      {
        field: 'residueQuantityBefore',
        title: '期初结存数量',
        width: 200,
        align: 'center',
      },
      {
        field: '',
        title: '期初单价',
        width: 100,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            let price = 0
            if (row.residueQuantityBefore != 0 && row.residueCostBefore != 0) {
              price = row.residueQuantityBefore / row.residueCostBefore
            }
            return { value: (price / 100).toFixed(2) }
          },
        },
      },
      {
        field: 'residueCostBefore',
        title: '期初金额',
        width: 100,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            return { value: (row.residueCostBefore / 100).toFixed(2) }
          },
        },
      },
      {
        field: 'storageQuantity',
        title: '本期入库数量',
        width: 100,
        align: 'center',
      },
      {
        field: '',
        title: '本期入库单价',
        width: 150,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            let price = 0
            if (row.storageQuantity != 0 && row.storageCost != 0) {
              price = row.storageQuantity / row.storageCost
            }
            return { value: (price / 100).toFixed(2) }
          },
        },
      },
      {
        field: 'storageCost',
        title: '本期入库金额',
        width: 150,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            return { value: (row.storageCost / 100).toFixed(2) }
          },
        },
      },
      {
        field: 'stockOutQuantity',
        title: '本期出库数量',
        width: 200,
        align: 'center',
      },
      {
        field: 'residueQuantity',
        title: '期末结存数量',
        width: 150,
        align: 'center',
      },
      {
        field: '',
        title: '期末单价',
        width: 100,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            let price = 0
            if (row.residueQuantity != 0 && row.residueCost != 0) {
              price = row.residueQuantity / row.residueCost
            }
            return {
              value: (price / 100).toFixed(2),
            }
          },
        },
      },
      {
        field: 'residueCost',
        title: '期末金额',
        width: 100,
        align: 'center',
        formatter: {
          type: 'text',
          format: (row) => {
            return {
              value: (row.residueCost / 100).toFixed(2),
            }
          },
        },
      },
    ]