提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：

主要使用ExcelJs，封装一个可以根据数据导出一个附带添加序列的Excel文档的方法
文档链接
https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md

一、ExcelJS是什么？

读取，操作并写入电子表格数据和样式到 XLSX 和 JSON 文件

二、使用步骤

1.安装

代码如下（示例）：

npm install exceljs

2.封装

代码如下（示例）：

const ExcelJS = require("exceljs");/*** 生成excel,可设置下拉框选择* @param {Array} key 列的属性名* @param {Array} data 数据* @param {Array} title 列名* @param {string} filename 文件名称* @param {object} selectList 下拉数据* */
const export_to_excel = ({ key, data, title, filename, selectList }) => {// 创建excelconst workbook = new ExcelJS.Workbook();// 设置信息workbook.creator = "qinsi";workbook.title = filename;workbook.created = new Date();workbook.modified = new Date();// 创建工作表const worksheet = workbook.addWorksheet(filename);// 设置列名let columns = [];let width = 10;title.forEach((x, index) => {/*if null/undefined*/if (x == null) {width = 10;} else if (x.toString().charCodeAt(0) > 255) {/*if chinese*/width = x.toString().length * 2;} else {width = x.toString().length;}// 设置列名、键和宽度columns.push({ header: x, key: key[index], width: width });});worksheet.columns = columns;// 设置表数据data.forEach((x) => {worksheet.addRow(x);});// 设置可编辑最大行数，用来显示下拉单元格if (data.length < 100) {worksheet.getRow(100);}const worksheet2 = workbook.addWorksheet("sheet2");Object.keys(selectList).forEach((key1) => {console.log(key1, selectList[key1]);let keyList = selectList[key1];getSource(keyList, workbook, worksheet2);const col = worksheet.getColumn(key1);// 遍历此列中的所有当前单元格，包括空单元格col.eachCell({ includeEmpty: true }, function (cell, rowNumber) {console.log(rowNumber);// 设置下拉列表cell.dataValidation = {type: "list",allowBlank: false,formulae: [`=sheet2!$A$1:$Z$1`],};});// 写入文件workbook.xlsx.writeBuffer().then((data) => {const blob = new Blob([data, { type: "application/vnd.ms-excel" }]);if (window.navigator.msSaveOrOpenBlob) {// msSaveOrOpenBlob方法返回boolean值navigator.msSaveBlob(blob, filename + ".xlsx");// 本地保存} else {const link = document.createElement("a"); // a标签下载link.href = window.URL.createObjectURL(blob); // href属性指定下载链接link.download = filename + ".xlsx"; // dowload属性指定文件名link.click(); // click()事件触发下载window.URL.revokeObjectURL(link.href); // 释放内存}});
};
/*** 递归下拉数据* @param {Array} array 下拉数组* @param {object} workbook 工作簿* @param {object} worksheet 工作表* */
const getSource = (array, workbook, worksheet) => {// 递归数据let worksheetY = null;// 循环创建联动下拉array.forEach((item, index) => {console.log(index);let column = [];let nameList = new Set();let firstSheet = workbook.getWorksheet(1);const topCol = firstSheet.getColumn(item.name);let columnName = getColumnName(topCol._number);column.push(item.value);if (item.dataSource) {item.dataSource.forEach((x) => {column.push(x.value);nameList.add(x.name);// 创建映射表，如果已创建就不创建if (!workbook.getWorksheet(x.label)) {worksheetY = workbook.addWorksheet(x.label);} else {worksheetY = workbook.getWorksheet(x.label);}});nameList.forEach((m) => {const col = firstSheet.getColumn(m);// console.log(columnName + ":" + m);col.eachCell({ includeEmpty: true }, function (cell, rowNumber) {// 设置下拉列表,根据列号获取列字母cell.dataValidation = {type: "list",allowBlank: true,formulae: [`=INDIRECT(${columnName}${rowNumber})`],};});});getSource(item.dataSource, workbook, worksheetY);}let isColumn = worksheet.getRow(1)._cells.some((n, idnexN) => {return worksheet.getRow(1).getCell(idnexN + 1).value === column[0];});if (!isColumn) {worksheet.getColumn((worksheet._columns || []).length + 1).values =column;worksheet.eachRow(function (row, rowNumber) {// 设置名row.eachCell(function (cell, colNumber) {if (colNumber === (worksheet._columns || []).length) {rowNumber > 1 ? cell.addName(column[0]) : "";}});});}});
};
// 根据列号获取字母号
function getColumnName(columnNum) {if (columnNum > 26) {return (String.fromCharCode(Math.floor(columnNum / 26) + 64) +String.fromCharCode((columnNum % 26) + 64));} else {return String.fromCharCode(columnNum + 64);}
}
export default {ExcelJS,export_to_excel
};

3.使用

<template><div class="about"><button @click="testExcel">导出</button></div>
</template><script>
// 根据excelJs所在路径自己
import excelJs from "@/utils/excelJs";
export default {methods: {testExcel() {let data = {data: {name: ["mtType","chicun","dept","haveOutStation",],title: ["资料1","资料2","资料3","资料4",],},code: 200,message: "ok",requestStampe: "2022-03-15 10:12:46",resopnseStampe: "2022-03-15 10:12:46",};let selectList = {dept: [{label: "资料3",name: "dept",value: "测试1",dataSource: [{label: "资料4",name: "haveOutStation",value: "产品组1",dataSource: [{label: "类别",value: "夹克",name: "mtType",dataSource: [{label: "尺寸",value: "XS",name: "chicun",},{label: "尺寸",value: "S",name: "chicun",},],},{value: "衬衫",label: "类别",name: "mtType",dataSource: [{label: "尺寸",value: "XXS",name: "chicun",},{label: "尺寸",value: "S",name: "chicun",},],},],},{label: "资料4",name: "haveOutStation",value: "产品组2",dataSource: [{label: "类别",value: "夹克",name: "mtType",dataSource: [{label: "尺寸",value: "XS2",name: "chicun",},{label: "尺寸",value: "S3",name: "chicun",},],},],},],},{label: "资料4",name: "dept",value: "测试2",dataSource: [{label: "资料4",name: "haveOutStation",value: "产品组3",dataSource: [{label: "类别",value: "夹克",name: "mtType",dataSource: [{label: "尺寸",value: "XS6",name: "chicun",},{label: "尺寸",value: "S7",name: "chicun",},],},],},],},],};const paramss = {title: data.data.title,key: data.data.name,data: [{mtType: 1,chicun: 1,dept: 1,haveOutStation: 1,},],selectList: selectList,autoWidth: true,filename: "test",};excelJs.export_to_excel(paramss);},
};
</script>

---