vue使用xlsx.js导出excel。处理合并单元格，字体颜色，边框等 - 剑轩的专栏

导出excel的效果：

有合并单元格，字体颜色，边框等。

引入需要使用的组件

import { utils } from 'xlsx'
import XLSX from 'xlsx-style'
import FileSaver from 'file-saver'

xlsx-style用来处理样式，可能这个库里边有包含xlsx的库，不是很确定，没有去尝试

引入了后运行可能会报错

解决方法：
在\node_modules\xlsx-style\dist\cpexcel.js 807行的

var cpt = require('./cpt' + 'able');

改为：

var cpt = cptable;

核心代码

核心代码如下：

// 导出实验课程excel
    exportLabroomCourse(cid) {
      const levelArray = ['对口', '高质量', '明星']
      const typeArray = ['必做', '拓展']
      this.$http.get('/education/api/course/GetLabRoomByCourseID', { CourseID: cid }).then(res => {
        const excelData = [{ no: '课程实验清单' }]
        const header = {
          no: '序号',
          courseName: '教材名称',
          chapterName: '教材章节',
          level: '等级',
          type: '类型',
          labRoomTitle: '实验名称'
        }
        excelData.push(header)
        let index = 1
        const chapterMerges = []
        console.log(res.data)
        res.data.chapterList.forEach((cp) => {
          // debugger
          chapterMerges.push({
            e: { c: 2, r: index + 2 },
            s: { c: 2, r: index + 1 + cp.labRoomVMList.length }
          })
          cp.labRoomVMList.forEach((kp) => {
            excelData.push({
              no: index,
              courseName: res.data.courseName,
              chapterName: `${cp.chapterTitle} ${cp.titleContent}`,
              level: levelArray[kp.level-1],
              type: typeArray[kp.type-1],
              labRoomTitle: kp.labRoomTitle
            })
            index++
          })
        })
        // 合并结束位置 c:列位置 r:表示行位置
        const merges = [
          { // 标题合并
            e: { c: 0, r: 1 },
            s: { c: 5, r: 1 }
          },
          { // 课程名称纵向合并
            e: { c: 1, r: 3 },
            s: { c: 1, r: index + 1 }
          }]
        // 合并章节
        merges.push(...chapterMerges)
        console.log(excelData)
        this.exportExcelFile(excelData, merges, res.data.courseName, `${res.data.courseName}.xlsx`, index + 2)
      })
    },
    /**
     * 导出 excel 文件
     * @param array JSON 数组
     * @param sheetName 第一张表名
     * @param fileName 文件名
     * @param merges 合并单元格json
     * @param rowCount 行数
     */
    exportExcelFile(array, merges, sheetName = 'sheet1', fileName = 'example.xlsx', rowCount) {
      console.log(array)
      const jsonWorkSheet = utils.json_to_sheet(array)
      // 合并单元格
      if (merges && merges.length > 0) {
        jsonWorkSheet['!merges'] = merges
      }
      // 不要第一行
      jsonWorkSheet['!ref'] = `A2:F${rowCount + 2}`
      // 设置列宽
      jsonWorkSheet['!cols'] = [
        { wpx: 65 },
        { wpx: 165 },
        { wpx: 280 },
        { wpx: 80 },
        { wpx: 80 },
        { wpx: 280 }]
      jsonWorkSheet['!rows'] = [{ hpt: 20 }]// 行高
      for (var prop in jsonWorkSheet) {
        if (prop.indexOf('!') === -1) {
          jsonWorkSheet[prop].s = {
            font: {
              name: '微软雅黑',
              sz: 10,
              color: 'FF000000'
            },
            alignment: { horizontal: 'center', vertical: 'center', wrap_text: true },
            border: {
              top: { style: 'thin', color: 'FFFFAA00' },
              bottom: { style: 'thin', color: 'FFFFAA00' },
              left: { style: 'thin', color: 'FFFFAA00' },
              right: { style: 'thin', color: 'FFFFAA00' }
            }
          }
          const rc = prop.substring(1, prop.length)
          // 从第4行(内容居左)
          if (rc > 3 && (prop.indexOf('C') !== -1 || prop.indexOf('F') !== -1)) {
            jsonWorkSheet[prop].s.alignment.horizontal = 'left'
          }
        }
      }
      console.log(jsonWorkSheet)
      const workBook = {
        SheetNames: [sheetName],
        Sheets: {
          [sheetName]: jsonWorkSheet
        }
      }
      var wbout = XLSX.write(workBook, {
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary'
      })
      FileSaver.saveAs(
        new Blob([this.s2ab(wbout)], { type: 'application/octet-stream' }),
        fileName
      )
    },
    s2ab(s) {
      var buf = new ArrayBuffer(s.length)
      var view = new Uint8Array(buf)
      for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
      return buf
    }

使用一个按钮执行这个方法即可,传递了一个课程id

<el-button size="mini" type="text" @click="exportLabroomCourse(scope.row.id)">导出</el-button>

贴一下接口返回的测试数据

测试数据如下：

{
    "code": 0,
    "data": {
        "courseID": "1a9b5c35f6ad47ddab639d39f4c02664",
        "courseName": "C语言程序设计(V12.0.0)",
        "chapterList": [
            {
                "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                "chapterTitle": "第1章",
                "titleContent": "编程的基本概念",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "79cea8e744ac4225820fa81d21da3420",
                        "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                        "labRoomTitle": "完成安装Visual Studio 2010",
                        "level": 1,
                        "type": 2
                    },
                    {
                        "labRoomID": "82da1c41cb7542cba2ec6b47f54ebd9e",
                        "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                        "labRoomTitle": "完成在控制台输出“Hello World”",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "59cb300285404556a6a6208ad936244c",
                        "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                        "labRoomTitle": "完成在控制台打印“我爱熊猫”图案",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "f6f88d40e6254b189c441a7b3e8d766d",
                        "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                        "labRoomTitle": "完成打印一个菱形图案",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "0337c524550445bd9a3c9ae668b51c00",
                        "chapterID": "14e204abb35a4dc29bcb79be9a0a4768",
                        "labRoomTitle": "啊啊啊啊啊啊",
                        "level": 2,
                        "type": 1
                    }
                ]
            },
            {
                "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                "chapterTitle": "第2章",
                "titleContent": "变量、常量和数据类型",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "edcea5c1d08449f4b6f451c1a1898f51",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "完成交换两个变量的值",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "5763dee5c407425485ff2fd15899a335",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "完成计算五门课程的总成绩和平均成绩",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "10b3bd176e9549138f13ea0547d03d31",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "完成求圆的面积和周长",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "f3a19f52795644cbb0d4d45dc18389d2",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "完成进行算术运算输出运算图形",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "d931b2e157984f1fa463f0a28d7c5c03",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "AJ上传的测试实验",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "2d04eba77d0341b9b96f61f2a97ee398",
                        "chapterID": "7d93c5eb862b49078aa451453d65ef63",
                        "labRoomTitle": "完成进行算术运算输出运算图形",
                        "level": 1,
                        "type": 1
                    }
                ]
            },
            {
                "chapterID": "62c7deff7b3b4520a7429b566750b314",
                "chapterTitle": "第3章",
                "titleContent": "运算符与表达式",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "8463c8e35a4a4455bbc2d0668d8a122b",
                        "chapterID": "62c7deff7b3b4520a7429b566750b314",
                        "labRoomTitle": "完成在三个不相同的数中求最大值",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "57ba743fdfeb4e7e9e3fe3dc90ad84ff",
                        "chapterID": "62c7deff7b3b4520a7429b566750b314",
                        "labRoomTitle": "完成大小写互换",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "6d66d991520b4d92bddd7626e11da2ff",
                        "chapterID": "62c7deff7b3b4520a7429b566750b314",
                        "labRoomTitle": "完成求一个五位数各个位上数字和",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "f84ae8e39f4840239d92ff0a3ecf1606",
                        "chapterID": "62c7deff7b3b4520a7429b566750b314",
                        "labRoomTitle": "完成在四个不同的数中求最小值",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "daff898455584845b36d17b4a6147534",
                        "chapterID": "62c7deff7b3b4520a7429b566750b314",
                        "labRoomTitle": "增加第三章实验",
                        "level": 3,
                        "type": 1
                    }
                ]
            },
            {
                "chapterID": "27a7ec69305747d3a8d0ad4f2db57b21",
                "chapterTitle": "第4章",
                "titleContent": "输入输出语句",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "fd7fa47b8d5542afb76537998c94e5a4",
                        "chapterID": "27a7ec69305747d3a8d0ad4f2db57b21",
                        "labRoomTitle": "完成计算圆相关的数据",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "83acde49d5694befb8e8611f98b24cea",
                        "chapterID": "27a7ec69305747d3a8d0ad4f2db57b21",
                        "labRoomTitle": "完成实现单个汉字的输入输出",
                        "level": 1,
                        "type": 1
                    }
                ]
            },
            {
                "chapterID": "3f408833fca74a80b8211ef43c9b2c43",
                "chapterTitle": "第5章",
                "titleContent": "条件语句1",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "33d6e120db084d35bc912d176bdc09b2",
                        "chapterID": "3f408833fca74a80b8211ef43c9b2c43",
                        "labRoomTitle": "完成通过条件语句实现数学计算操作",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "9916059f248b4e3b88fdb97f80cf3e42",
                        "chapterID": "3f408833fca74a80b8211ef43c9b2c43",
                        "labRoomTitle": "完成判断成绩等级",
                        "level": 1,
                        "type": 2
                    },
                    {
                        "labRoomID": "9709c2c526194eb6bed9ea55e60ab82a",
                        "chapterID": "3f408833fca74a80b8211ef43c9b2c43",
                        "labRoomTitle": "完成判断三角形类型",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "a503294f126e4390854f13ae418d80f5",
                        "chapterID": "3f408833fca74a80b8211ef43c9b2c43",
                        "labRoomTitle": "完成计算个人所得税",
                        "level": 1,
                        "type": 1
                    }
                ]
            },
            {
                "chapterID": "d83d73af5d544586bff8fc9aeccfe2ba",
                "chapterTitle": "第6章",
                "titleContent": "条件语句2",
                "kpList": null,
                "labRoomVMList": [
                    {
                        "labRoomID": "233b282eae17459ea1e7932f46f1557f",
                        "chapterID": "d83d73af5d544586bff8fc9aeccfe2ba",
                        "labRoomTitle": "完成使用switch-case实现菜单选择",
                        "level": 1,
                        "type": 1
                    },
                    {
                        "labRoomID": "737649e346d4471fa18e10b7f3e5aba6",
                        "chapterID": "d83d73af5d544586bff8fc9aeccfe2ba",
                        "labRoomTitle": "完成计算某天是某年里的第几天",
                        "level": 1,
                        "type": 2
                    },
                    {
                        "labRoomID": "c1d86ec5756c43b5bff714fc0904a581",
                        "chapterID": "d83d73af5d544586bff8fc9aeccfe2ba",
                        "labRoomTitle": "完成计算自2005年以来的总天数",
                        "level": 1,
                        "type": 1
                    }
                ]
            },
                ]
            }
        ]
    },
    "success": true,
}