vue导入导出excel、设置单元格文字颜色、背景色、合并单元格（使用xlsx-js-style库）

 

npm i xlsx-js-style

<template><button @click="download">下载 Excel 表格</button><el-table :data="tableData" style="width: 100%"><el-table-column prop="date" label="日期" width="180" /><el-table-column prop="name" label="Name" width="180" /><el-table-column prop="维生素A" label="维生素A" width="180" /><el-table-column prop="维生素D" label="维生素D" /><el-table-column prop="维生素E" label="维生素E" width="180" /><el-table-column prop="维生素B1" label="维生素B1" width="180" /><el-table-column prop="维生素B2" label="维生素B2" width="180" /><el-table-column prop="维生素B6" label="维生素B6" width="180" /></el-table>
</template>
<script setup>
import { reactive } from "vue";
import XLSX from "xlsx-js-style";const tableData = reactive([{name: "下限",维生素A: 500,维生素D: 6,维生素E: 10,维生素B1: 500,维生素B2: 800,维生素B6: 300,date: "/",},{name: "上限",维生素A: 1200,维生素D: 10,维生素E: 20,维生素B1: 1000,维生素B2: 1600,维生素B6: 600,date: "/",},{name: "Charlie",维生素A: 674,维生素D: 7.2,维生素E: 21,维生素B1: 798,维生素B2: 1200,维生素B6: 485,date: "2024.12.29",},{name: "Alice",维生素A: 690,维生素D: 8.1,维生素E: 18,维生素B1: 698,维生素B2: 1112,维生素B6: 674,date: "2024.12.29",},{name: "Bob",维生素A: 719,维生素D: 9,维生素E: 9.8,维生素B1: 498,维生素B2: 1342,维生素B6: 241,date: "2024.12.29",},{name: "Charlie",维生素A: 674,维生素D: 7.2,维生素E: 21,维生素B1: 798,维生素B2: 1200,维生素B6: 485,date: "2024.12.30",},{name: "Alice",维生素A: 690,维生素D: 8.1,维生素E: 18,维生素B1: 698,维生素B2: 1112,维生素B6: 674,date: "2024.12.30",},{name: "Bob",维生素A: 719,维生素D: 9,维生素E: 9.8,维生素B1: 498,维生素B2: 1342,维生素B6: 241,date: "2024.12.30",},{name: "Charlie",维生素A: 684,维生素D: 5.9,维生素E: 9,维生素B1: 478,维生素B2: 1462,维生素B6: 123,date: "2024.12.30",},
]);// 获取上下限
const lowerLimit = tableData[0];
const upperLimit = tableData[1];// 判断每列的值，超出范围时给相应单元格添加红色样式
const getFormattedData = () => {const formattedData = [];const merges = []; // 用于存储需要合并的单元格// 插入下限行并设置背景色为灰色const lowerLimitRow = { name: "下限" };for (const [key, value] of Object.entries(lowerLimit)) {if (key !== "name") {lowerLimitRow[key] = {v: value,s: {font: { color: { rgb: "000000" } },fill: { fgColor: { rgb: "D3D3D3" } }, // 设置背景色为灰色},};} else {lowerLimitRow[key] = {v: value,s: {font: { color: { rgb: "000000" } },fill: { fgColor: { rgb: "D3D3D3" } }, // 设置背景色为灰色},};}}formattedData.push(lowerLimitRow);// 插入上限行并设置背景色为灰色const upperLimitRow = { name: "上限" };for (const [key, value] of Object.entries(upperLimit)) {if (key !== "name") {upperLimitRow[key] = {v: value,s: {font: { color: { rgb: "000000" } },fill: { fgColor: { rgb: "D3D3D3" } }, // 设置背景色为灰色},};} else {upperLimitRow[key] = {v: value,s: {font: { color: { rgb: "000000" } },fill: { fgColor: { rgb: "D3D3D3" } }, // 设置背景色为灰色},};}}formattedData.push(upperLimitRow);let currentDate = "";let startRow = 2; // 从第三行开始（下限和上限已经插入）// 遍历表格数据for (let i = 2; i < tableData.length; i++) {const row = tableData[i];const formattedRow = { name: row.name };// 遍历每一列for (const [key, value] of Object.entries(row)) {if (key === "name" || key === "date") {formattedRow[key] = value;// 检查日期是否与上一行相同，若相同则合并单元格if (row.date === currentDate) {// 继续处理} else {if (currentDate !== "") {// 合并日期单元格merges.push({ s: { r: startRow, c: 0 }, e: { r: i, c: 0 } });}currentDate = row.date;startRow = i + 1;}continue;}// 获取下限和上限const lower = lowerLimit[key];const upper = upperLimit[key];// 判断是否超出范围let cellStyle = {};if (value < lower) {cellStyle = { font: { color: { rgb: "FF0000" } } }; // 红色字体} else if (value > upper) {cellStyle = { font: { color: { rgb: "FF0000" } } }; // 红色字体}// 将样式和数据一起保存formattedRow[key] = { v: value, s: cellStyle };}formattedData.push(formattedRow);}// 合并最后一个日期的单元格merges.push({s: { r: startRow, c: 0 },e: { r: tableData.length, c: 0 },});console.log("merges", merges);return { formattedData, merges };
};// 下载 Excel 文件
const download = () => {const { formattedData, merges } = getFormattedData();const reformattedData = formattedData.map((item) => {// 创建一个新的对象，确保 `date` 始终在最前面const { date, ...rest } = item;return { date, ...rest };});// 转换格式化后的表格数据为工作表const ws = XLSX.utils.json_to_sheet(reformattedData);// 添加合并单元格的逻辑ws["!merges"] = merges;// 创建一个新的工作簿并添加工作表const wb = XLSX.utils.book_new();XLSX.utils.book_append_sheet(wb, ws, "Sheet1");// 导出 Excel 文件XLSX.writeFile(wb, "example.xlsx");
};
</script>