兩種vue導出excel表格方法

1.基于 js-export-excel 插件實現(xiàn)簡單表格導出（缺點不支持表格樣式修改）

import ExportJsonExcel from 'js-export-excel'
export function parseTime(time, cFormat) {  // 日期格式化方法
  if (arguments.length === 0) {
    return null
  }
  const format = cFormat || '{y}-{m}-u0z1t8os {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數(shù)據(jù)，注：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}-u0z1t8os')
  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插件實現(xiàn)，支持自定義修改表格樣式(直接使用會有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;
  }

  // 將內(nèi)容樣式應用到 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 對象轉(zhuǎn)換為二進制數(shù)據(jù)流并下載
  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: '規(guī)格',
        width: 100,
        align: 'center',
      },
      {
        field: 'goodsUnit',
        title: '單位',
        width: 100,
        align: 'center',
      },
      {
        field: 'residueQuantityBefore',
        title: '期初結(jié)存數(shù)量',
        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: '本期入庫數(shù)量',
        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: '本期出庫數(shù)量',
        width: 200,
        align: 'center',
      },
      {
        field: 'residueQuantity',
        title: '期末結(jié)存數(shù)量',
        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),
            }
          },
        },
      },
    ]