使用excelJs.js,导出excel，可以设置序列以及下拉框的联动
壤驷康裕
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
前言
提示：这里可以添加本文要记录的大概内容：
主要使用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 }) => {
// 创建excel
const 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>
类似资料：