将ant-design-vue的table表格导出为excel文件 2021-10-07

目的

将ant-design-vue的table表格导出为excel文件
如果有需求修改表格的导出式样时，不需要修改源码，添加新的工序即可。

说明

根据自己的想法做了一下改装，不知道改的合理不合理。
如果有大神愿意指导，希望可以指出不足以及修改方案等。

遇到的问题

xlsx、js-xlsx、node-xlsx的区别，本来以为xlsx可以直接做到和后台一样的文档编辑效果，查了一下才知道有所限制，看来是个大佬的个人作品。

效果图

要导出的表格

导出后的文件

例子

1.安装依赖

cnpm i file-saver -S
cnpm i js-xlsx -S
cnpm i -D script-loader

2.创建文件夹vendor
3.将Exoprt2Excel-Custom-Anywo.js创建到vendor文件夹
4.下载https://github.com/eligrey/Blob.js/blob/master/Blob.js到vendor文件夹

5.拷贝以下例子代码
   例子模板来自：https://www.antdv.com/components/table-cn/

<template>
  <div>
    <a-table
      ref="table"
      :columns="columns"
      :data-source="data"
      :scroll="{ x: 1500, y: 300 }"
    >
      <a slot="action" slot-scope="text">action</a>
    </a-table>
    <a-button @click="handleClick">导出</a-button>
  </div>
</template>
<script>
const data = [];
for (let i = 0; i < 100; i++) {
  data.push({
    key: i,
    name: `Edrward ${i}`,
    age: 32,
    address: `London Park no. ${i}`,
  });
}
import { export_table_to_excel } from "./vendor/Exoprt2Excel-Custom-Anywo";
export default {
  data() {
    return {
      data,
      isDownload: false,
    };
  },
  methods: {
    handleClick() {
      this.isDownload = true;
      this.$nextTick(() => {
        export_table_to_excel({ ref: this.$refs.table });
        this.isDownload = false;
      });
    },
  },
  computed: {
    columns() {
      return [
        {
          title: "Full Name",
          width: 100,
          dataIndex: "name",
          key: "name",
          fixed: this.isDownload ? false : "left",
        },
        {
          title: "Age",
          width: 100,
          dataIndex: "age",
          key: "age",
          fixed: this.isDownload ? false : "left",
        },
        { title: "Column 1", dataIndex: "address", key: "1", width: 150 },
        { title: "Column 2", dataIndex: "address", key: "2", width: 150 },
        { title: "Column 3", dataIndex: "address", key: "3", width: 150 },
        { title: "Column 4", dataIndex: "address", key: "4", width: 150 },
        { title: "Column 5", dataIndex: "address", key: "5", width: 150 },
        { title: "Column 6", dataIndex: "address", key: "6", width: 150 },
        { title: "Column 7", dataIndex: "address", key: "7", width: 150 },
        { title: "Column 8", dataIndex: "address", key: "8" },
        {
          title: "Action",
          key: "operation",
          fixed: this.isDownload ? false : "right",
          width: 100,
          scopedSlots: { customRender: "action" },
        },
      ];
    },
  },
};
</script>

Exoprt2Excel-Custom-Anywo.js源码

/**!
 * Exoprt2Excel-Custom-Anywo.js
 * https://blog.csdn.net/y1377593732 2021-10-07
 * xlsx API可以参考：https://www.npmjs.com/package/js-xlsx
 * 引用组件的获取方法：
 *  cnpm i file-saver -S
 *  https://github.com/eligrey/Blob.js/blob/master/Blob.js
 *  cnpm i js-xlsx -S
 *  cnpm i -D script-loader
 * 
 * 参照Export2Excel.js修改的，不知道作者，谁知道给贴一个
 */

require('script-loader!file-saver');
require('script-loader!./Blob');
require('script-loader!js-xlsx/dist/xlsx.core.min');

/**
 * @description 工作簿类
 * @returns 
 */
function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  let buf = new ArrayBuffer(s.length);
  let view = new Uint8Array(buf);
  for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

/**
 * @description 表格分析方法
 * @param {*} table 
 * @returns 
 */
function generateArray(table, { cellHandles }) {
  let out = []; // 数据集合
  let ranges = []; // 合并单元格规则集合

  // 遍历所有行
  let rows = table.querySelectorAll('tr');
  rows.forEach((row, rI) => {
    // 获取当前行的初始状态，有前面合并单元格的列时，可能会已经存在
    out[rI] = out[rI] || [];
    // 遍历当前行所有列
    let columns = row.querySelectorAll('td,th');
    let cI = 0;
    columns.forEach(cell => {
      // 判断列是否已经被合并单元格占用，如果被占用跳过
      if (out[rI].length > cI && out[rI][cI] != null) {
        // 取得之后第一个等于null的元素的下标，避免稀疏数组带来的问题
        for (let i = cI; i < out[rI].length; i++) {
          if (out[rI][i] == null) {
            cI == i; // 如果更新当前列下标
            break;
          }
          cI = i + 1;
        }
      }
      let colspan = cell.getAttribute('colspan'); // 获取列合并属性
      let rowspan = cell.getAttribute('rowspan'); // 获取行合并属性
      let cellValue = cell.innerText; // 获取单元格的值
      // 纯数字的列转换为数字类型
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
      rowspan = parseInt(rowspan || 1);
      colspan = parseInt(colspan || 1);
      // 添加合并单元格规则
      if (rowspan > 1 || colspan > 1) {
        ranges.push({ s: { r: rI, c: cI }, e: { r: rI + rowspan - 1, c: cI + colspan - 1 } });
      }
      // 有合并规则时，所有单元格设置相同值到单元格
      for (let i = rI; i < rI + rowspan; i++) {
        out[i] = out[i] || [];
        for (let j = cI; j < cI + colspan; j++) {
          out[i][j] = cellHandles.reduce((context, x) => {
            // 保留每个流水线步骤的处理结果
            let res = x(context);
            return res == null ? context : res;
          }, {
            rowEl: row,
            colEl: cell,
            value: cellValue,
            rowIndex: i,
            colIndex: j,
            rowspan,
            colspan
          });
        }
      }
      cI++;
    });
  });
  return { out, ranges };
};

/**
 * @description 创建单元格对象
 * @param {*} context 
 * @returns 
 */
function create_cell_object(context) {
  // 转换成A1的表示形式
  var cell_ref = XLSX.utils.encode_cell({ c: context.colIndex, r: context.rowIndex });
  let cell = { v: context.value || '' };

  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';

  return Object.assign(context, {
    ref: cell_ref,
    cell
  })
}

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);
}

/**
 * @description 导出表格到excel 基础版本
 * ant表格导入时，保证没有fixed的列就可以正确导出，滚动条没事
 * @param {ATable} ref 
 */
export function export_table_to_excel_base({ ref, fileName = "test.xlsx", sheetName = "Sheet1", cellHandles = [], sheetHandles = [] }) {
  let theTable;
  if (ref != null || ref.$el != null) {
    // theTable = ref.$el.querySelector("table");
    theTable = ref.$el;
  } else {
    return false;
  }

  // 分析table，并进行流水线处理
  let oo = generateArray(theTable, {
    cellHandles: [create_cell_object, ...cellHandles]
  });

  let wb = new Workbook(); // 创建工作表
  // 创建sheet
  let ws = {
    '!merges': oo.ranges,
    '!ref': XLSX.utils.encode_range({ s: { c: 0, r: 0 }, e: { c: oo.out[0].length - 1, r: oo.out.length - 1 } })
  };

  // 生成单元格
  oo.out.flat().forEach(y => ws[y.ref] = y.cell);

  // sheet流水线处理
  if (sheetHandles && sheetHandles.length) {
    ws = sheetHandles.reduce((context, x) => {
      // 保留每个流水线步骤的处理结果
      let res = x(context);
      return res == null ? context : res;
    }, { ws, cells: oo.out }).ws;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(sheetName);
  wb.Sheets[sheetName] = ws;
  let wbout = XLSX.write(wb, { bookType: 'xlsx', showGridLines: false, bookSST: false, type: 'binary' });

  saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), fileName)
  return true;
}

/**
 * @description 拷贝表格式样(列宽)
 * @param {*} param0 
 * @returns 
 */
function copy_table_style_cell_width({ ws, cells }) {
  // 确定每列的宽度
  let cols = [];
  for (let i = 0; i < cells[0].length; i++) {
    for (let j = 0; j < cells.length; j++) {
      let cell = cells[j][i];
      if (cell && cell.colspan == 1) {
        cols.push({ wpx: cell.colEl.offsetWidth });
        break;
      }
    }
  }
  ws["!cols"] = cols;
  return { ws, cells };
}

/**
 * @description 颜色转换 
 * TODO 目前方案凑活用一下，如果有好的插件给个推荐
 * @param {*} color 
 * @returns 
 */
function formatColor(color) {
  if (/^rgb/.test(color)) {
    let str = color.replace(/^rgba?\((.*)\)$/, "$1");
    let arr = str.split(", ");
    let $4 = parseFloat(arr[3] || 1);
    let res = arr.slice(0, 3).reduce((res, x) => {
      let v = parseInt(x);
      // v = $4 * v;
      return res += parseInt(v).toString(16).padStart(2, 0)
    }, '');

    if ($4 == 0) {
      return "ffffff";
    }
    return res;
  } else if (color.startsWith("#")) {
    return color.slice(1);
  }
}

/**
 * @description 拷贝表格式样
 * TODO 并没有达到拷贝效果
 * @param {*} param0 
 * @returns 
 */
function copy_table_style_cell(context) {
  let computedStyles = window.getComputedStyle(context.colEl);
  context.cell.s = {
    fill: {
      fgColor: {
        rgb: formatColor(computedStyles["background-color"])
      }
    },
    font: {
      sz: computedStyles["font-size"].slice(0, -2)
    },
    alignment: {
      vertical: "center",
      horizontal: "center",
      wrapText: true
    },
    border: {
      top: { style: 'thin' },
      bottom: { style: 'thin' },
      left: { style: 'thin' },
      right: { style: 'thin' },
    }
  };

  return context;
}

/**
 * @description 导出表格到excel 自定义版本
 * @param {ATable} ref 
 */
export function export_table_to_excel({ ref, fileName = "test.xlsx", sheetName = "Sheet1", cellHandles = [], sheetHandles = [] }) {
  export_table_to_excel_base({
    ref, fileName, sheetName, cellHandles: [
      copy_table_style_cell
      , ...cellHandles], sheetHandles: [
        copy_table_style_cell_width
        , ...sheetHandles]
  });
}