需求：导出多级表格，如下，每个人名对应的是不同的城市金钱和年龄，日期占俩行，需要根据数据进行动态展示
1.效果
2.关键代码讲解
2.1数据源
2.2所需插件
npm install xlsx2.3关键代码
创建name组和date组，金钱、城市、年龄根据name和date进行分组
javascript"> groupDataByDateAndName() {const grouped = {};this.tableData.forEach((item) => {if (!grouped[item.date]) {grouped[item.date] = {};}if (!grouped[item.date][item.name]) {grouped[item.date][item.name] = {city: item.city,money: item.money,age: item.age,};}});console.log(grouped, "分组数据");return grouped;},
分组之后创建表头，注意！如果要修改代码，主要修改createHeaders函数的数据，如果一个人名对应多个属性，那么需要在name之后空出多个列headerRow1.push(name, "", ""); // 姓名列和空列
javascript"> createHeaders() {const names = Array.from(new Set(this.tableData.map((item) => item.name)));const headerRow1 = ["日期"]; // 第一行开始是“日期”const headerRow2 = [""]; // 第二行开始是空列names.forEach((name) => {headerRow1.push(name, "", ""); // 姓名列和空列headerRow2.push("城市", "金钱", "年龄"); // 每个姓名下面是城市和金钱});console.log("创建头部表格", [headerRow1, headerRow2]);return [headerRow1, headerRow2];},
创建每一行的数据，最后再进行配置
javascript"> createRows(groupedData) {const rows = [];const dates = Object.keys(groupedData);const names = Array.from(new Set(this.tableData.map((item) => item.name)));dates.forEach((date) => {const row = [date]; // 每行第一列是日期names.forEach((name) => {const data = groupedData[date][name] || {};// 每个人的城市和金钱row.push(data.city || "", data.money || "", data.age || "");});rows.push(row);});console.log("创建行数据", rows);return rows;},
3.完整代码
注意，如果要改成自己的数据源，需要注意更改this.tableData和name，city，money，age改为自己数据源的属性，还有 horizontal: "center", //水平居中没有生效，等有解决方法了再更新
javascript"><template><div><button @click="exportToExcel">导出 Excel</button></div>
</template><script>
import * as XLSX from "xlsx";
// import XLSXStyle from "xlsx-style";
export default {data() {return {tableData: [{date: "2016-05-03",name: "张三",city: "上海",money: 1000,age: "17",},{date: "2016-05-03",name: "王小虎",city: "北京",money: 2000,age: "17",},{date: "2016-05-03",name: "李四",city: "武汉",money: 2000,age: "17",},{date: "2016-05-04",name: "张三",city: "上海",money: 23222,age: "17",},{date: "2016-05-04",name: "王小虎",city: "北京",money: 3000,age: "17",},{date: "2016-05-04",name: "李四",city: "武汉",money: 2000,age: "17",},{date: "2016-05-05",name: "张三",city: "上海",money: 9345,age: "17",},{date: "2016-05-05",name: "王小虎",city: "北京",money: 3100,age: "17",},{date: "2016-05-05",name: "李四",city: "武汉",money: 2000,age: "17",},],};},methods: {exportToExcel() {//步骤1：按日期和名称对数据进行分组const groupedData = this.groupDataByDateAndName();//步骤2：准备Excel的标题和行const headers = this.createHeaders();const rows = this.createRows(groupedData);//步骤3：创建工作表和工作簿const ws = XLSX.utils.aoa_to_sheet([headers[0], headers[1], ...rows]);//合并名称和日期列的标题单元格this.mergeHeaders(ws);// 步骤4：导出到Excel文件const wb = XLSX.utils.book_new();XLSX.utils.book_append_sheet(wb, ws, "数据");XLSX.writeFile(wb, "导出的表格.xlsx");},// 步骤1：按日期和名称对数据进行分组groupDataByDateAndName() {const grouped = {};this.tableData.forEach((item) => {if (!grouped[item.date]) {grouped[item.date] = {};}if (!grouped[item.date][item.name]) {grouped[item.date][item.name] = {city: item.city,money: item.money,age: item.age,};}});console.log(grouped, "分组数据");return grouped;},// 步骤2：为表格创建标题createHeaders() {const names = Array.from(new Set(this.tableData.map((item) => item.name)));const headerRow1 = ["日期"]; // 第一行开始是“日期”const headerRow2 = [""]; // 第二行开始是空列// 为城市和金钱年龄添加名称和列names.forEach((name) => {headerRow1.push(name, "", ""); // 姓名列和空列headerRow2.push("城市", "金钱", "年龄"); // 每个姓名下面是城市和金钱});console.log("创建头部表格", [headerRow1, headerRow2]);return [headerRow1, headerRow2];},//步骤3：基于分组数据创建行createRows(groupedData) {const rows = [];const dates = Object.keys(groupedData);const names = Array.from(new Set(this.tableData.map((item) => item.name)));dates.forEach((date) => {const row = [date]; // 每行第一列是日期names.forEach((name) => {const data = groupedData[date][name] || {};// 每个人的城市和金钱row.push(data.city || "", data.money || "", data.age || "");});rows.push(row);});console.log("创建行数据", rows);return rows;},//步骤4：根据名称列和日期列合并标题mergeHeaders(ws) {const names = Array.from(new Set(this.tableData.map((item) => item.name)));let colIndex = 1; // 从第1列（Excel工作表中的第二列）开始ws["!merges"] = ws["!merges"] || [];ws["!merges"].push({s: { r: 0, c: 0 }, //设置第一行和第二行的第一列都是日期e: { r: 1, c: 0 },});const dateCellRef = XLSX.utils.encode_cell({ r: 0, c: 0 });ws[dateCellRef] = {v: "日期",s: {alignment: {horizontal: "center", //水平居中},},};ws["!cols"] = [{ width: 21 }];// 遍历名称以合并标题单元格names.forEach((name) => {const colSpan = 3; // 每个 name 占多少列// 合并此名称的单元格ws["!merges"].push({s: { r: 0, c: colIndex }, // Start at row 0, column `colIndex`e: { r: 0, c: colIndex + colSpan - 1 }, // End at the next column});// 设置此合并单元格的对齐方式（中心对齐）const cellRef = XLSX.utils.encode_cell({ r: 0, c: colIndex });ws[cellRef] = {v: name,s: {alignment: {horizontal: "center", //水平居中},},};// 移动到下一个名称的下一组列colIndex += colSpan;});},},
};
</script>
文章到此结束，希望对你有所帮助~
