建议把代码封装成一个函数,这样就不用每个页面都写了,直接调用就好了。
<template><div><h1>导出数据为 Excel</h1><button @click="exportToExcel(dynamicData, ['name', 'age', 'city'], ['姓名', '年龄', '城市'],'某某文件')">导出 Excel</button></div>
</template><script>
import XLSX from 'xlsx';
import { saveAs } from 'file-saver';export default {data() {return {// 动态数据源dynamicData: [{ name: 'Alice', age: 30, city: 'New York', email: 'alice@example.com',like:'吃饭' },{ name: 'Alice', age: 40, city: '啊啊啊', email: '666@example.com',like:'吃饭' },{ name: 'Bob', age: 25, city: 'Los Angeles', email: 'bob@example.com',like:'睡觉' },{ name: 'Charlie', age: 35, city: 'Chicago', email: 'charlie@example.com',like:'打豆豆' },],};},methods: {exportToExcel(jsonData, keys, headers,fileName) {// 动态生成数据const filteredData = jsonData.map(item => {const row = {};keys.forEach((key, index) => {row[headers[index]] = item[key]; // 将自定义表头作为键,原数据中的值作为值});return row;});// 创建工作表,传入数据const worksheet = XLSX.utils.json_to_sheet(filteredData);// 调用合并相同列代码mergeCells(worksheet, jsonData, keys.length);const workbook = XLSX.utils.book_new();XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');const excelBuffer = XLSX.write(workbook, {bookType: 'xlsx',type: 'array',});const data = new Blob([excelBuffer], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',});saveAs(data, `${fileName}.xlsx`);},},
};// 和平相同列
function mergeCells(worksheet, jsonData, numKeys) {const mergedCells = [];// 对每一列进行处理for (let col = 0; col < numKeys; col++) {let startRow = 1; // 从第二行开始(索引为1)let lastValue = jsonData[0][Object.keys(jsonData[0])[col]]; // 获取列的第一个值let count = 1;for (let row = 1; row <= jsonData.length; row++) {const currentValue =row < jsonData.length? jsonData[row][Object.keys(jsonData[0])[col]]: null;if (currentValue === lastValue) {count++;} else {if (count > 1) {mergedCells.push({s: { r: startRow, c: col }, // 起始单元格e: { r: startRow + count - 1, c: col }, // 结束单元格});}// 更新起始行和状态startRow = row;lastValue = currentValue;count = 1; // 重置计数器}}// 检查最后一组相同的单元格if (count > 1) {mergedCells.push({s: { r: startRow, c: col },e: { r: startRow + count - 1, c: col },});}}// 合并单元格worksheet["!merges"] = mergedCells;
}
</script>
准备基础的插件代码
1、正常编写页面文件 checkboxDiaglg.vue
<template><el-dialogdestroy-on-closev-model="drawer.show"title="请选择需要导出的列"width="500"><div style="width: 100%; height: 200px; overflow: auto"><template v-for="(column, index) in drawer.titleList" :key="index"><el-checkboxv-model="column.show":label="column.label":style="{width: '45%',margin: '0 10px 10px 0',overflow: 'hidden',}":data-index="index"border></el-checkbox></template></div><template #footer><div class="dialog-footer"><el-button @click="close">取消</el-button><el-button type="primary" @click="changeExcel_OK"> 确认导出 </el-button></div></template></el-dialog>
</template>
<script setup>
import {ref
} from "vue";
import { exportToExcel333 } from "../tool"; const changeExcel_OK = () => {console.log("确认导出数据");const filteredArray = drawer.value.titleList.filter((item) => item.show);drawer.value.show = false;// 提取 name 和 id 的值const label = filteredArray.map((item) => item.label);const field = filteredArray.map((item) => item.field);let data = drawer.value.data_List; //表格数据let keys = field; //需要展示的列let headers = label; //每一列对应的中文名称let fileName = drawer.value.fileName; //文件导出的名称exportToExcel333(data, keys, headers, fileName);
};const drawer = ref({show: false,titleList: [],data_List: [],fileName: "",
});
const open = (titleList, dataList, fileName) => {titleList.forEach((item) => {item.show = true;});drawer.value.titleList = titleList;drawer.value.fileName = fileName;drawer.value.data_List = dataList;drawer.value.show = true;
};
const close = () => {drawer.value.show = false;
};
defineExpose({open,close,
});
</script>
2、封装 导出方法 tool.js文件
/*** 无需页面直接导出相对应的表格数据* jsonData 表格数据源* keys 需要导出的列* headers 每一列对应的中文* fileName 导出的文件名称* show 是否需要合并列 (这个合并有些问题,合并需要自己写一个把)* ** */
import * as XLSX from "xlsx";
import { saveAs } from "file-saver";
export function exportToExcel333(jsonData, keys, headers,fileName,show=false){// 动态生成数据const filteredData = jsonData.map((item) => {const row = {};keys.forEach((key, index) => {row[headers[index]] = item[key]; // 将自定义表头作为键,原数据中的值作为值});return row;});// 创建工作表,传入数据const worksheet = XLSX.utils.json_to_sheet(filteredData);// 是否需要合并if(show){mergeCells(worksheet, jsonData, keys.length)}const workbook = XLSX.utils.book_new();XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");const excelBuffer = XLSX.write(workbook, {bookType: "xlsx",type: "array",});const data = new Blob([excelBuffer], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8",});saveAs(data, `${fileName}.xlsx`);
}
function mergeCells(worksheet, jsonData, numKeys) {const mergedCells = [];// 对每一列进行处理for (let col = 0; col < numKeys; col++) {let startRow = 1; // 从第二行开始(索引为1)let lastValue = jsonData[0][Object.keys(jsonData[0])[col]]; // 获取列的第一个值let count = 1;for (let row = 1; row <= jsonData.length; row++) {const currentValue =row < jsonData.length? jsonData[row][Object.keys(jsonData[0])[col]]: null;if (currentValue === lastValue) {count++;} else {if (count > 1) {mergedCells.push({s: { r: startRow, c: col }, // 起始单元格e: { r: startRow + count - 1, c: col }, // 结束单元格});}// 更新起始行和状态startRow = row;lastValue = currentValue;count = 1; // 重置计数器}}// 检查最后一组相同的单元格if (count > 1) {mergedCells.push({s: { r: startRow, c: col },e: { r: startRow + count - 1, c: col },});}}// 合并单元格worksheet["!merges"] = mergedCells;
}
把导出写成 全局插件 的方式
不理解的可以看这篇文章:https://blog.csdn.net/weixin_39593730/article/details/139335445?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_utm_term~default-1-139335445-blog-126727544.235v43pc_blog_bottom_relevance_base8&spm=1001.2101.3001.4242.2&utm_relevant_index=4
3、编写 插件文件 modalPlugin.js
/**** * 这个是全局插件,* * * */ import { createApp,createVNode,render,provide } from 'vue';
import checkboxDiaglg from './checkboxDiaglg.vue'; // 插件页面代码export default {install(app,options){//确保每个插件都使用独立的容器进行渲染。即使它们在同一个页面中,也要为每个插件创建不同的 div 容器。这样可以避免相互之间的插入冲突。const container = document.createElement('div'); // 创建一个新的容器document.body.appendChild(container); // 将容器添加到bodyconst vnode = createVNode(checkboxDiaglg);render(vnode, container); // 将虚拟节点渲染到容器const $myChecj={open:(titleList,dataList,fileName)=>{// vnode?.component?.exposed?. 固定写法// open 插件暴露出去的方法vnode?.component?.exposed?.open(titleList,dataList,fileName)}}// 由于vue3中不建议这种直接挂在全局的写法,所以建议用 provide 这种//app.config.globalProperties.$myChecj=$myChecj;// 建议使用这种app.provide('$myChecj',$myChecj)}
};
4、在main.js
import { createApp } from 'vue'
import ModalPlugin from './plugIn/modalPluginThree.js' // 导入插件js文件
const app = createApp(App)
app.use(ModalPlugin)
app.mount("#app")
5、页面上引入局部插件使用 index.vue
<template><div><h1>欢迎使用 全局导出excel插件</h1><button @click="showModal">打开弹出窗口</button></div>
</template><script setup>
import {ref,inject,getCurrentInstance} from 'vue'
import LoadingService from "../../plugIn/modalPlugin"; //全局插件
const data_title=ref([
{field: "badge",//是否需要导出的列的字段的值show: true, //是否需要导出的列名称label: "工号",},{field: "name",//是否需要导出的列的字段的值show: true,//是否需要导出的列名称label: "员工姓名",},
]);
const data_list=ref([]);// const conext = getCurrentInstance().appContext.config.globalProperties; //不建议使用这种
let $myChecj=inject('$myChecj'); //建议使用这种
const showModal = () => {
let list = JSON.parse(JSON.stringify(data_title.value));let dataList = data_list.value;let fileName = '文件名称;// conext.$myChecj.open(list, dataList, fileName); //不建议使用这种$myChecj.open(list, dataList, fileName); //建议使用这种
};
</script>
把导出写成 局部插件 的方式
3、编写 插件文件 modalPlugin.js
/**** * 这个是局部插件,* 需要在使用的页面里引入* * */ import { createApp } from 'vue';
import checkboxDiaglg from './checkboxDiaglg.vue';let loadingInstance;const LoadingService = {open(titleList,dataList,fileName) {if (!loadingInstance) {const loadingApp = createApp(checkboxDiaglg);const mountNode = document.createElement('div');document.body.appendChild(mountNode);loadingInstance = loadingApp.mount(mountNode);}loadingInstance.open(titleList,dataList,fileName);},close() {if (loadingInstance) {loadingInstance.close();}},
};export default LoadingService;
4、页面上引入局部插件使用 index.vue
<template><div><h1>欢迎使用 局部导出excel插件</h1><button @click="showModal">打开弹出窗口</button></div>
</template><script setup>
import {ref} from 'vue'
import LoadingService from "../../plugIn/modalPlugin"; //局部插件
const data_title=ref([
{field: "badge",//是否需要导出的列的字段的值show: true, //是否需要导出的列名称label: "工号",},{field: "name",//是否需要导出的列的字段的值show: true,//是否需要导出的列名称label: "员工姓名",},
]);
const data_list=ref([]);
const showModal = () => {
let list = JSON.parse(JSON.stringify(data_title.value));let dataList = data_list.value;let fileName = '文件名称;LoadingService.open(list, dataList, fileName);
};
</script>