js xlsx自定义样式导出

墨蓝 2024-04-01 13:41 76阅读 0赞

**使用的技术：**  
xlsx 用于解析和编写各种电子表格。比如excel、csv、html文件  
xlsx-style 为xlsx库添加样式，比如字体颜色，大小，行宽等。但是只支持xlsx、xlsm、xlsb格式  
FileSaver.js 负责下载保存文件。对各种兼容性比较好，对跨域文件也有处理  
lodash js常用工具库

需要注意的地方  
**一、引入xlsx-style报错**

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

亲测两种解决方案：

在\node_modules\xlsx-style\dist\cpexcel.js 807行 的
     var cpt = require(’./cpt’ + ‘able’);把这一行改成 var cpt = cptable;

把xlsx的cpexcel.js文件复制到xlsx-style的dist文件夹，覆盖cpexcel.js。

**二、颜色添加不上**

xlsx-style 只 支持16进制的ARGB颜色，比如：\{ rgb: "FFFFAA00" \}。注意这里是没有\#号的。  
添加背景色是使用fill的fgColor，而不是bgColor。这里需要驼峰编写。  
**实现步骤**  
1、需求描述

将列表数据导出成Excel。所有的导出表格都固定表头样式，但是内容不固定，特殊列需要高亮（添加背景色），其他的数据列按需求设置宽度，可以添加换行等。（比如身份证号固定字符长度，地址列固定宽度、自动换行）

2、实现导出核心代码

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

创建工作簿

let wb = XLSX.utils.book_new()

创建显示表头

const ws = XLSX.utils.json_to_sheet([
      colNames
    ], { header: keys, skipHeader: true })

追加数据到excel中，从第二行开始

XLSX.utils.sheet_add_json(ws, data, { header: keys, skipHeader: true, 
    origin: 'A2' })

添加表头样式，设置背景色、字体大小、下边框线

for (const key in ws) {
      // 第一行
      if(key.replace(/[^0-9]/ig, '') === '1') {
        ws[key].s = {
          fill: {
            fgColor: { rgb: 'FFA3F4B1' } // 添加背景色
          },
          font: {
            name: '宋体', // 字体
            sz: 12, // 字体大小
            bold: true // 加粗
          },
          border: {
              // 下划线
            bottom: {
              style: 'thin',
              color: 'FF000000'
            }
          }
        }
      }
    }

生成xlsx导出类。这里必须使用xlsx-style才能生成指定样式

wbOut = XLSXStyle.write(wb, { bookType: bookType, bookSST: false, 
    type: 'binary' })

生成并下载文件

saveAs(new Blob([s2ab(wbOut)], { type: '' }), filename)

完整的代码

import 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 STYLE_PARAMS = ['fill', 'font', 'alignment', 'border']
    
    /**
     * 数字转换成excel表头。 （递归处理）
     * @param {Number} num 需要转换的数字
     */
    // eslint-disable-next-line no-unused-vars
    function numToString(num) {
      let strArray = []
      let numToStringAction = function(o) {
        let temp = o - 1
        let a = parseInt(temp / 26)
        let b = temp % 26
        strArray.push(String.fromCharCode(64 + parseInt(b + 1)))
        if (a > 0) {
          numToStringAction(a)
        }
      }
      numToStringAction(num)
      return strArray.reverse().join('')
    }
    
    /**
     * 表头字母转换成数字。（进制转换）
     * @param {string} str 需要装换的字母
     */
    function stringToNum(str) {
      let temp = str.toLowerCase().split('')
      let len = temp.length
      let getCharNumber = function(charx) {
        return charx.charCodeAt() - 96
      }
      let numout = 0
      let charnum = 0
      for (let i = 0; i < len; i++) {
        charnum = getCharNumber(temp[i])
        numout += charnum * Math.pow(26, len - i - 1)
      }
      return numout
    }
    
    /**
     * worksheet转成ArrayBuffer
     * @param {worksheet} s xlsx库中的worksheet
     */
    function s2ab(s) {
      if (typeof ArrayBuffer !== 'undefined') {
        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
      } else {
        const buf = new Array(s.length)
        for (let i = 0; i !== s.length; ++i) {
          buf[i] = s.charCodeAt(i) & 0xFF
        }
        return buf
      }
    }
    
    /**

\* 导出成Excel

* @param {Array} data 数据
     * @param {Object} columns 每列参数说明。可直接写对应的中文名称，也可以写这一列的样式

。比如列宽，背景色

* @param {String} filename 文件名。可根据文件名后缀动态判断文件格式。
    支持xlsx, xlsm, xlsb（这三个支持自定义样式）, html, csv等
     * @param {Object} styleConf 其他xlsx-style的参数

*
     * columns数据格式：{
     *         name: '姓名',
     *         sex: '性别',
     *         birthday: {
     *           name: '出生日期',
     *           wch: 14
     *         },
     *         address: {
     *           name: '地址',
     *           wpx: 160,
     *           alignment: { wrapText: true }
     *         }
     *       }
     *
     * styleConf数据格式: {
     *          'E4': {
     *             font: {
     *               bold: true,
     *               color: { rgb 'FFFF0000' }
     *             }
     *          },
     *          '!merges': [ // 合并第一行
     *             {
     *               s: { c: 0, r: 0 },
     *               e: { c: 7, r: 0 }
     *             }
     *          ]
     *        }
     */

export function exportExcel(data, columns, filename = FILE_NAME, styleConf) {
      let keys = _.keys(columns)
      let colNames = _.mapValues(columns, o => {
        if (_.isPlainObject(o)) {
          return o.name
        } else {
          return o
        }
      })

// 创建工作簿

let wb = XLSX.utils.book_new()

// 显示表头

const ws = XLSX.utils.json_to_sheet([
        colNames
      ], { header: keys, skipHeader: true })

// 过滤数据，只显示表头包含的数据

for (let i = 0; i < data.length; i++) {
        data[i] = _.pick(data[i], keys)
      }

// 追加数据到excel中，从第二行开始

XLSX.utils.sheet_add_json(ws, data, { header: keys, skipHeader: true, origin: 'A2' })
    
      wb.SheetNames.push('sheet1')
      wb.Sheets['sheet1'] = ws

// 根据不同的扩展名，导出不同格式的文件

let bookType = null
      let ext = path.extname(filename)
      if (ext == null) {
        filename += '.xlsx'
        bookType = 'xlsx'
      } else {
        bookType = ext.substr(1).toLowerCase()
      }
    
      let wbOut

// 如果是支持样式的格式

if (['xlsx', 'xlsm', 'xlsb'].includes(bookType)) {
        // ws['!merges'] = [{// 合并第一行数据[B1,C1,D1,E1]
        //   s: {// s为开始
        //     c: 0, // 开始列
        //     r: 0// 开始取值范围
        //   },
        //   e: {// e结束
        //     c: 7, // 结束列
        //     r: 0// 结束范围
        //   }
        // }]
    
        for (const key in ws) {
          // 第一行，表头
          if (key.replace(/[^0-9]/ig, '') === '1') {
            ws[key].s = {
              fill: {
                fgColor: { rgb: 'FFA3F4B1' }
              },
              font: {
                name: '宋体',
                sz: 12,
                bold: true
              },
              border: {
                bottom: {
                  style: 'thin',
                  color: 'FF000000'
                }
              }
            }
          } else {
            let str = key.replace(/[^A-Za-z]+$/ig, '')
            let colIndex = stringToNum(str) - 1
            if (keys[colIndex] && _.isPlainObject(columns[keys[colIndex]])) {
              const a = _.pick(columns[keys[colIndex]], STYLE_PARAMS)
              ws[key].s = _.assign(ws[key].s, a)
            }
          }
        }
        // 设置列宽
        const colsP = []
        _.mapValues(columns, o => {
          colsP.push(_.pick(o, COL_PARAMS))
        })
        ws['!cols'] = colsP
    
        // 合并其他样式参数
        if (styleConf) {
          for (const key in styleConf) {
            if (ws.hasOwnProperty(key)) {
              ws[key].s = styleConf[key]
            }
          }
        }
        wbOut = XLSXStyle.write(wb, { bookType: bookType, bookSST: false, type: 'binary' })
      } else {
        wbOut = XLSX.write(wb, { bookType: bookType, bookSST: false, type: 'binary' })
      }
    
      saveAs(new Blob([s2ab(wbOut)], { type: '' }), filename)
    }

调用实例

import { exportExcel } from '../../utils/xlsxUtils.js'
    
    async exportStudent() {
          const params = {
            pageNum: this.pageNum,
            pageSize: this.pageSize
          }
          params[this.queryForm.searchType] = this.queryForm.searchInput
          this.exportLoading = true
          try {
            const res = await this.$http.get('/v1/ExportOrImport/exportStudentInfo', params)
            res.data.forEach(item => {
              item.courseStr = item.courses.map(o => o.name).join('，')
              item.classStr = item.classes.map(o => o.name).join('，')
            })
            await exportExcel(res.data, {
              name: '姓名',
              sex: '性别',
              idCard: {
                name: '身份证号',
                wch: 18
              },
              birthday: {
                name: '出生日期',
                wch: 14
              },
              primaryContactPhone: {
                name: '联系人号码',
                wch: 11
              },
              primaryContactName: '联系人姓名',
              relation: '联系人关系',
              nickName: '昵称',
              school: '就读学校',
              grade: '年级',
              address: {
                name: '地址',
                wpx: 160,
                alignment: { wrapText: true }
              },
              remark: {
                name: '备注',
                wpx: 240,
                alignment: { wrapText: true }
              },
              courseStr: {
                name: '报读课程',
                wpx: 120,
                font: { color: { rgb: 'ffff0000' } },
                alignment: { wrapText: true }
              },
              classStr: {
                name: '班级',
                wpx: 120,
                font: { color: { rgb: 'ffff0000' } },
                alignment: { wrapText: true }
              }
            }, '学员信息表.xlsx', {
              'E4': {
                fill: { fgColor: { rgb: 'FFFF0000' } }
              }
            })
          } catch (error) {
    
          }
          this.exportLoading = false
        }

最后导出的样式：

![f9e11743c6ee4fc1aeac36da31f1235f.png][]

[f9e11743c6ee4fc1aeac36da31f1235f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4e04dc3dd1124e09b2fa80aa55ee9567.png