前端导出 Excel

需求背景

在项目开发中遇到需要前端将后端返回的数据导出的任务,由于后端已经全部返回了相关数据,为避免重复请求接口、减少后端IO提升系统性能,因此需要在前端将后端返回的任务数据导出成 Excel。之前没有做过前端根据数据直接导出excel的案例,特此做记录。

使用的插件

  1. xlsx 用于解析和编写各种电子表格的前端 JavaScript 库。xlsx文档
  2. xlsx-style 用于为 xlsx 库添加样式,比如字体颜色,大小,行宽等。但是只支持xlsx、xlsm、xlsb格式。
  3. FileSaver.js 负责下载保存文件。对各种兼容性比较好,对跨域文件也有处理。
  4. lodash js 常用工具库。

遇到的问题

一、引入 xlsx-style 报错

This relative module was not found: 
./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js

解决方案:在 vue.config.js中添加下面配置:

chainWebpack: config => {
  config.externals({ './cptable': 'var cptable' })
}

二、样式设置问题

  1. xlsx-style 只支持 16 进制的 rgb 颜色,比如 { rgb: 'ff0000' }。注意这里是没有 # 号的。
  2. 添加背景色是使用 fill 的 fgColor,而不是 bgColor。这里需要驼峰编写。

三、使用 xlsx 方法时报错
使用 XLSX.utils.json_to_sheet方法将 json 转成 workSheet 对象时报错

"TypeError: Cannot read properties of undefined (reading 'utils')"

xlsx.js 库不是 默认 导出,而是将所有方法属性单个导出,因此导入时应该是:

import * as XLSX from 'xlsx';
// 或者
import { utils } from 'xlsx';

四、引入 path 和 fs 模块报错

/// 首先安装 npm install node-polyfill-webpack-plugin -S

const NodePolyfillPlugin = require('node-polyfill-webpack-plugin')

transpileDependencies: true,
configureWebpack: {
    // 引入 NodePolyfillPlugin 使 es6 模块可以引入 common.js 模块,webpack5以上需要手动安装
    plugins: [new NodePolyfillPlugin()],
    // 禁用 fs 模块
    resolve: { fallback: { fs: false } }
  },

实现步骤

说明:xlsx 可以使用获取表格 DOM 的方式将 DOM 转换为 xlsx workbook,但由于当前表格实现多是虚拟滚动或者分页实现的,所以这种方式虽然简单但并不能很好实现预期目标。因此本文略过这种实现方案。

基础版本

直接使用 xlsx 一个库实现导出excel,没有样式。

/**
* @description 基础版本
*/
downloadExcel (array, sheetName , fileName) {
  const header = ['date', 'name', 'address'];
  const header_zh = { date: '日期', name: '姓名', address: '地址' };

  const jsonWorkSheet = XLSX.utils.json_to_sheet(
    [header_zh, ...array],
    {
      header: header,
      skipHeader: true
    }
  );
  const workBook = {
    SheetNames: [sheetName],
    Sheets: {
      [sheetName]: jsonWorkSheet
    }
  }
  return XLSX.writeFile(workBook, fileName)
},

导出效果如下:

带样式版本

使用 xlsx 的 json_to_sheet 工具方法,把数组的数据格式转换成 xlsx 需要 worksheet。然后再添加 xlsx-style 支持的样式代码,使用 xlsx-style 编写成数据流,最后再使用 FileSaver.js 把数据流保存成文件。

  1. 创建工作簿和表头
// 创建工作簿
const workBook = XLSX.utils.book_new();
// 显示表头, 只包含表头的表格
const jsonWorkSheet = XLSX.utils.json_to_sheet(
  // [colNames, ...data],
  [colNames],
  {
    header: keys,
    skipHeader: true
  }
)
  1. 追加表格实际要导出的数据
// 追加数据到excel中,从第二行开始
XLSX.utils.sheet_add_json(jsonWorkSheet, data, { header: keys, skipHeader: true, origin: 'A2' })
  1. 设置表格样式
let workBookOut;
for (const key in jsonWorkSheet) {
  // 获取第一行数据,设置对应的样式
  if (key.replace(/[^0-9]/ig, '') === '1') {
    jsonWorkSheet[key].s = style.hs;
  } else {
    // 非A-Za-z结尾的字符串将会被替换为空,最后结果结尾的一定是A-Za-z
    let keyStr = key.replace(/[^A-Za-z]+$/ig, '')
    // 全是 A-Za-z,避免!ref !cols !rows属性
    const reg = /^[A-Za-z]+$/
    if (reg.test(keyStr)) {
      jsonWorkSheet[key].s = style.bs;
    }
  }
}
// 表格列样式
jsonWorkSheet['!cols'] = colsParams;
  1. 生成导出流,下载文件
// 二进制流
workBookOut = XLSXStyle.write(workBook, { bookType: bookType, bookSST: false, type: 'binary' });
saveAs(new Blob([workBook2ArrayBuffer(workBookOut)], { type: '' }), filename);
  1. 完整代码
import * as XLSX from 'xlsx';
import XLSXStyle from 'xlsx-style';
import { saveAs } from 'file-saver'
import path from 'path';
import _ from 'lodash';

// 默认文件名
const FILE_NAME = '数据导出表.xlsx';
// 列属性支持的配置字段
const COL_PARAMS = ['hidden', 'wpx', 'width', 'wch', 'MDW'];
// 表格样式
const TABLE_STYlE = {
  // 表头样式
  hs: {
    font: { sz: 10, color: { rgb: "000000" }, bold: true },
    alignment: { horizontal: "center", vertical: "center", wrapText: true },
    fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } },
    border: { bottom: { style: 'thin', color: '000000' }, right: { style: 'thin', color: '000000' } }
  },
  // 内容样式
  bs: {
    font: { sz: 11 },
    alignment: { horizontal: "center", vertical: "center", wrapText: true },
    border: { bottom: { style: 'thin', color: '000000' }, right: { style: 'thin', color: '000000' } }
  },
};

/**
 * worksheet转成ArrayBuffer
 * @param { jsonWorkSheet } jsonWorkSheet xlsx库中的worksheet
 */
function workBook2ArrayBuffer(jsonWorkSheet) {
  const buffer = new ArrayBuffer(jsonWorkSheet.length);
  const view = new Uint8Array(buffer);
  for (let i = 0; i !== jsonWorkSheet.length; ++i) {
    view[i] = jsonWorkSheet.charCodeAt(i) & 0xFF;
  }
  return buffer;
}

/**
 * 导出成Excel
 * @param {Array} data 数据
 * @param {Object} columns 表格columns,用于获取 keys, colNames, 列宽属性
 * @param {String} filename 文件名。支持:'.xlsx', '.xlsm', '.xlsb', '.csv'
 */
export function exportExcel(data, columns, filename = FILE_NAME, style = TABLE_STYlE) {
  if (!data?.length || !columns?.length) {
    return console.error('data或colums不存在无法导出');
  }

  // 根据不同的扩展名,导出不同格式的文件
  let bookType = null;
  let ext = path.extname(filename);
  if (!ext) {
    filename += '.xlsx';
    bookType = 'xlsx';
  } else if (!['.xlsx', '.xlsm', '.xlsb', '.csv'].includes(ext)) {
    return console.error(`不支持导出${ext}格式的文件`);
  } else {
    bookType = ext.substring(1).toLowerCase();
  }
  // data 字段key
  const keys = [];
  // 表格列字段对象
  const colNames = {};
  // 设置列宽属性
  const colsParams = [];

  columns.forEach(column => {
    if (!column?.type) {
      keys.push(column.field);
      colNames[column.field] = column.label;

      let param = {};
      COL_PARAMS.forEach(item => {
        if (column?.exportParams[item]) {
          param[item] = column.exportParams[item];
        }
      })
      colsParams.push(param);
    }
  })

  // 创建工作簿
  const workBook = XLSX.utils.book_new();

  // 显示表头, 只包含表头的表格
  const jsonWorkSheet = XLSX.utils.json_to_sheet(
    // [colNames, ...data],
    [colNames],
    {
      header: keys,
      skipHeader: true
    }
  )

  // 过滤数据,只显示表头包含的数据
  for (let i = 0; i < data.length; i++) {
    // loadsh pick方法:根据keys,返回一个新数组
    data[i] = _.pick(data[i], keys);
  }

  // 追加数据到excel中,从第二行开始
  XLSX.utils.sheet_add_json(jsonWorkSheet, data, { header: keys, skipHeader: true, origin: 'A2' })
  const sheetName = filename.split('.')[0];
  workBook.SheetNames.push(sheetName);
  workBook.Sheets[sheetName] = jsonWorkSheet;

  let workBookOut;
  for (const key in jsonWorkSheet) {
    // 获取第一行数据,设置对应的样式
    if (key.replace(/[^0-9]/ig, '') === '1') {
      jsonWorkSheet[key].s = style.hs;
    } else {
      // 非A-Za-z结尾的字符串将会被替换为空,最后结果结尾的一定是A-Za-z
      let keyStr = key.replace(/[^A-Za-z]+$/ig, '')
      // 全是 A-Za-z,避免!ref !cols !rows属性
      const reg = /^[A-Za-z]+$/
      if (reg.test(keyStr)) {
        jsonWorkSheet[key].s = style.bs;
      }
    }
  }
  // 表格列样式
  jsonWorkSheet['!cols'] = colsParams;
  // 二进制流
  workBookOut = XLSXStyle.write(workBook, { bookType: bookType, bookSST: false, type: 'binary' });

  saveAs(new Blob([workBook2ArrayBuffer(workBookOut)], { type: '' }), filename);
}

总结

本文介绍了前端使用 xlsx 插件实现下载功能的案例, 通过 xlsx 库结合 xlsx-style 和 fileSaver 可以根据后端返回的数据由前端实现导出表格并设置样式的需求。

参考文章

  1. xlsx.js 表格的导出与导入
  2. js xlsx自定义样式导出
  3. xlsx文档
posted @ 2023-05-28 14:04  公瑾当年  阅读(2406)  评论(0编辑  收藏  举报