记一次利用sheetjs将前台表格转换excel的经历

原创
2021/05/12 17:17
阅读数 1.6K

1 关键技术与难点

本文记录一次在前台利用sheetjs,将js数据转化为excel表格的实践经历.

其中涉及到的一些难点包含:

  1. 多行合并列的处理;(均为表头,不含表体)
  2. 表格数据的设计与处理;

表头截图


2 前置说明

注:如果不是自定义表格设计,而是固定列表格设计,则可忽略2.1的数据库设计,直接从2.2的实例数据参考即可.

2.1 数据库设计

此处主要介绍表格数据的column(列)和data(表体数据)的设计,
仅介绍与表格设计相关的字段,忽略项目逻辑相关的字段.实际开发字段需要酌情增加.

  1. column数据库表设计
字段名 说明
code 列编码
name 列名称/title
parent_code 父列编码,用以记录列的父子间关系
data_type 数据类型,如string,integer,decimal,none(仅用于无数据的父列),date,select(选择),boolean(布尔),calc(公式),也可根据自身需求增加
select_source 选择来源,仅当数据类型为selectcalc时有用
sort 排序

以上介绍的,是最基础的一些字段,实际还可以考虑增加如min_width(最小宽度),bold_font(是否粗体)这样的字段.
对于data_type字段,还有一些附加的设计内容,本文涉及不多,以后如果有需求,也许会另开一篇博客介绍.

实例数据(json格式):

[
    {
        code:'xiangmu',
        name:'项目',
        parentCode:'',
        dataType:'none',
        selectSource:''null'',
        sort:1
    },
    {
        code:'xiangmumingcheng',
        name:'项目名称',
        parentCode:'xiangmu',
        dataType:'string',
        selectSource:'',
        sort:2
    },
    {
        code:'caigoupinmu',
        name:'采购品目',
        parentCode:'xiangmu',
        dataType:'select',
        selectSource:'CGPM',
        sort:3
    },
    {
        code:'danjia',
        name:'单价',
        parentCode:'',
        dataType:'decimal',
        selectSource:'',
        sort:4
    },
    ...
]
  1. data数据库表设计
字段名 说明
column_code 列编码
row_code 行编码,我将之设计为uuid格式,这样前台可直接生成.
value 值,以字符串方式保存所有值
show_content 展示内容,适用于像selectboolean这样的列

同样为最基础字段.有些可以扩展.

实例数据(json格式):

[
    {
        columnCode:'xiangmumingcheng'
        rowCode:'9cd97861-ebab-4002-8cc3-c12ceaa92cab',
        value:'**业务经费',
        showContent:''
    },
    {
        columnCode:'caigoupinmu',
        rowCode:'9cd97861-ebab-4002-8cc3-c12ceaa92cab',
        value:'BGSB',
        showContent:'办公设备'
    },
    {
        columnCode:'danjia',
        rowCode:'9cd97861-ebab-4002-8cc3-c12ceaa92cab',
        value:'13.5',
        showContent:''
    },
]
2.2 前台数据体现

简单来说,就是将columndata转化为对象集合的格式.
对于data中的showContent,采用前缀_show_加编码作为key.
转换方法本身比较简单,且非本文重点,就不再赘述了.

实例数据(json格式):

[
    {
        xiangmumingcheng:'**业务经费',
        caigoupinmu:'BGSB',
        _show_caigoupinmu:'办公设备',
        danjia:'13.5'
    }
]
2.3 excel导出的标准方法

根据中文api,sheetjs将js数据转换为表格的方式,有:

  • aoa_to_sheet 把转换JS数据数组的数组为工作表。
  • json_to_sheet 把JS对象数组转换为工作表。
  • table_to_sheet 把DOM TABLE元素转换为工作表。

本文采用aoa_to_sheet作为核心方法.
项目内已经写好了直调方法:

/* 需要npm中导入sheetjs */
import XLSX from 'xlsx';

/**
 * 将数组转换为excel
 * @param key 列key数组,按照index确认
 * @param data 数据数组
 * @param title 表头数组,如果为合并表头模式(merge不为空)时,title为二维数组
 * @param filename 文件名&sheet名
 * @param autoWidth 自动宽度
 * @param merge 合并表头数据(实际上excel内的所有单元格都可合并)
 */
export const export_array_to_excel = ({key, data, title, filename, autoWidth,merge}) => {
    const wb = XLSX.utils.book_new();
    const arr = json_to_array(key, data);
    if(merge){
      arr.unshift(...title)
    }else{
      arr.unshift(title);
    }
    const ws = XLSX.utils.aoa_to_sheet(arr);
    if(autoWidth){
        auto_width(ws, arr);
    }
    //合并单元格,适用于有父行的内容
    //格式可参考https://zhuanlan.zhihu.com/p/141328581
    if(merge){
      ws['!merges']=merge;
    }
    XLSX.utils.book_append_sheet(wb, ws, filename);
    XLSX.writeFile(wb, filename + '.xlsx');
}
//辅助:自动宽度
function auto_width(ws, data){
    /*set worksheet max width per col*/
    const colWidth = data.map(row => row.map(val => {
        /*if null/undefined*/
        if (val == null) {
            return {'wch': 10};
        }
        /*if chinese*/
        else if (val.toString().charCodeAt(0) > 255) {
            return {'wch': val.toString().length * 2};
        } else {
            return {'wch': val.toString().length};
        }
    }))
    /*start in the first row*/
    let 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;
}

3 核心代码

参数方面,arrTableData参考自2.2的实例数据.arrColumn则参考2.1的列的实例数据.
如果不是自定义列,则arrColumn就需要手动写死了.

import { isEmpty } from '@/common/about-string'
import { getShowContentKey } from '@/common/about-table'

/**
 * 导出默认表格
 * @param arrTableData
 * @param arrColumn
 * @param filename
 */
export const exportDefaultTable = (arrTableData, arrColumn, { filename = '' } = { filename: '' }) => {
  // 1.数据预处理
  let mapColumn = new Map()

  // console.log('1');

  // 默认父类均在子列前
  let arrAncestorCode = []
  let intRowIndex = 0
  let intColIndex = -1
  let intMaxRowIndex = 0
  let intMaxColIndex = 0
  let mapColumnIndex = new Map()
  for (let c of arrColumn) {
    mapColumn.set(c.code, c)

    if (isEmpty(c.parentCode)) {
      intRowIndex = 0
      intColIndex += 1
      arrAncestorCode = [c.code]
    } else {
      let intParentCodeIndex = arrAncestorCode.findIndex(s => s === c.parentCode)
      if (intParentCodeIndex < arrAncestorCode.length - 1) {
        // 左移
        intRowIndex = intParentCodeIndex + 1
        intColIndex += 1
        arrAncestorCode.splice(intRowIndex, arrAncestorCode.length - intParentCodeIndex, c.code)
      } else {
        // 下移
        intRowIndex += 1
        arrAncestorCode.push(c.code)
      }
    }
    if (intMaxRowIndex < intRowIndex) {
      intMaxRowIndex = intRowIndex
    }
    mapColumnIndex.set(c.code, { sr: intRowIndex, sc: intColIndex })
  }
  intMaxRowIndex += 1
  intMaxColIndex = intColIndex + 1
  // console.log('2',mapColumnIndex);
  // 反填,根据行和列记录columnCode
  let mapStartIndex = new Map()
  for (let s of mapColumnIndex.keys()) {
    let { sr, sc } = mapColumnIndex.get(s)
    if (typeof mapStartIndex.get(sc) === 'undefined') {
      mapStartIndex.set(sc, new Map())
    }
    mapStartIndex.get(sc).set(sr, s)
  }
  // 遍历列和行,父子表头站位
  for (let ic = 0; ic < intMaxColIndex; ic++) {
    for (let ir = 0; ir < intMaxRowIndex; ir++) {
      if (typeof mapStartIndex.get(ic) !== 'undefined') {
        if (typeof mapStartIndex.get(ic).get(ir) !== 'undefined') {
          // do nothing
        } else {
          let strLastMark = mapStartIndex.get(ic).get(ir - 1)
          if (typeof strLastMark !== 'undefined') {
            mapStartIndex.get(ic).set(ir, strLastMark)
          } else {
            strLastMark = mapStartIndex.get(ic - 1).get(ir)
            mapStartIndex.get(ic).set(ir, strLastMark)
          }
        }
      }
    }
  }
  // console.log('3',mapStartIndex);
  // 再反填,根据名称推测站位父子(不一定是按照标准顺序的)
  let mapIndexArr = new Map()
  for (let [ic, mapIndexEach] of mapStartIndex.entries()) {
    for (let [ir, s] of mapIndexEach.entries()) {
      if (typeof mapIndexArr.get(s) === 'undefined') {
        mapIndexArr.set(s, [])
      }
      mapIndexArr.get(s).push({ c: ic, r: ir })
    }
  }
  // console.log('4',mapIndexArr);
  // 列出需要合并的列
  let arrColumnMerge = []
  for (let [s, arrPosition] of mapIndexArr.entries()) {
    if (arrPosition.length > 1) {
      let ms = arrPosition[0]
      let me = arrPosition[arrPosition.length - 1]
      arrColumnMerge.push({
        s: ms,
        e: me
      })
    }
  }
  // console.log('5',arrColumnMerge,intMaxRowIndex,intMaxColIndex);
  // 构建key和title
  let arrKey = []
  let arrTitle = []
  for (let ir = 0; ir < intMaxRowIndex; ir++) {
    arrTitle.push([])
    for (let ic = 0; ic < intMaxColIndex; ic++) {
      let strMark = mapStartIndex.get(ic).get(ir)
      arrTitle[ir].push(mapColumn.get(strMark).name)
      if (ir === intMaxRowIndex - 1) {
        arrKey.push(strMark)
      }
    }
  }
  // console.log('6',arrTitle);
  // 2.表格数据整理导出版
  let arrTableDataExport = []
  for (let [i, mRow] of arrTableData.entries()) {
    let mNew = { ...mRow }
    for (let k of arrKey) {
      let mColumn = mapColumn.get(k)
      if (typeof mColumn !== 'undefined') {
        switch (mColumn.dataType) {
          case 'integer':
          case 'decimal':
          case 'calc':
            if (isEmpty(mNew[k])) {
              mNew[k] = 0
            } else {
              mNew[k] = Number(mNew[k])
            }
            break
          case 'select':
          case 'boolean':
            mNew[k] = mNew[getShowContentKey(k)]
            break
          default:
            break
        }
      }
    }

    arrTableDataExport.push(mNew)
  }
  // console.log('7',arrTableData,arrTableDataExport);
  // 2.excel构建
  const mTable = {
    title: arrTitle,
    key: arrKey,
    data: arrTableDataExport,
    autoWidth: true,
    merge: arrColumnMerge,
    filename
  }

    //此处即是调用最核心的方法
  export_array_to_excel(mTable)
}

经过测试,该方法是生效的.
尽管在合并表头方面,代码有些过长.以后有时间的话,我再考虑如何精简吧.

以下附加两个辅助方法:

/**
 * 是否为空(主要适用于字符串)
 * @param str
 * @returns {boolean}
 */
export const isEmpty = str => {
  return typeof str === 'undefined' || str === null || str === '' || str === '--'
}

const PREFIX_SHOW = '_show_'

/**
 * 获取展示内容key
 * @param key
 * @returns {string}
 */
export const getShowContentKey = key => {
  return PREFIX_SHOW + key
}

4 参考说明


5 其他

本文中的表格的表头列,本质上还是属于'树状数据'.
按照以往的经验,树数据的设计加上level,既可避免出现互为父子的矛盾,也可使一些计算环节更加简单.
以后若是有时间,可以考虑加上这个字段,甚至直接增加一个祖先后代关联表,查看是否可以减少一些计算量.

end

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部