前端Excel導出實用方案（完整源碼，可直接應用）

前言：

技術選型：

主要功能點：

核心代碼：

完整代碼：

開發文檔

前言：

在前后端分離開發為主流的時代，很多時候，excel導出已不再由后端主導，而是把導出的操作移交到了前端。本文在全局導出組件封裝上，保持了高度的擴展性，無論大家用的是element組件庫還是antd vue的組件庫或者其他的組件庫，都容易進行更換。

技術選型：

vue + antd vue + sheetjs

前端導出excel導出，需借助第三方插件，目前兩款導出最為主流。

一款是sheetjs，優點支持多種excel格式，但是官方文檔全是英文
SheetJS Community Edition | SheetJS Community Edition

一款是exceljs，優點是中文文檔很全，缺點是導出格式受限，僅支持部分格式

https://github.com/exceljs/exceljs/blob/master/README_zh.md

因公司業務需要，用戶需支持多種excel的格式，所以本文筆者主要針對sheetjs進行封裝調用。

主要功能點：

自定義dom
拆分成多張表導出（默認超過1萬條數據自動拆分）
自定義過濾函數
各種標題自定義
數據排序
支持大數據量導出

核心代碼：

// 文件名稱
const filename = fileName;
//Excel第一個sheet的名稱
const ws_name = sheetName;
// 創建sheet
const ws = XLSX.utils.aoa_to_sheet([this.tableTitle]);
//添加數據
XLSX.utils.sheet_add_json(ws, apiData, {skipHeader: true,origin:origin
});
// 創建wokbook
const wb = XLSX.utils.book_new();
// 將數據添加到工作薄
XLSX.utils.book_append_sheet(wb, ws, ws_name);
// 導出文件
XLSX.writeFile(wb, filename);

完整代碼：

安裝sheetjs

npm i xlsx

全局導出組件代碼：

ExportExcelComponent.vue

<template><div id="excel-export"><slot name="custom" v-if="isCustom"></slot><a-button ghost type="primary" @click="startExport" v-else>導出excel</a-button><a-modalv-if="visible"v-model="visible":title="modelTitle":maskClosable="false":closable="false"><template #footer><a-buttontype="primary"ghostv-if="isAbnormal":loading="btnLoading"@click="startExport">重新導出</a-button><a-buttontype="primary"ghostv-if="isAbnormal":loading="btnLoading"@click="getTableData">繼續導出</a-button><a-button :loading="btnLoading" @click="handleClose"> 關閉 </a-button></template><a-progress:percent="percent":status="progressStatus"class="progress"/></a-modal></div>
</template>
<script>
import * as XLSX from "xlsx";export default {props: {//自定義過濾函數filterFunction: {type: Function,default: null,},//sheet名ws_name: {type: String,default: "Sheet",},//導出的excel的表名filename: {type: String,default: "Excel" + new Date().getTime(),},//拆分成每個表多少條數據，需要搭配isSplit屬性一起使用multiFileSize: {type: Number,default: 10e3,},//模態框標題modelTitle: {type: String,default: "導出excel",},//是否自定義dom,如果采用插槽，需要開啟該屬性，否則dom為默認buttonisCustom: {type: Boolean,default: false,},// 導出的數據表的表頭tableTitleData: {type: Array,required: true,default: () => [],},//請求數據的api函數asyncDataApi: {type: Function,default: () => {},},//請求參數listQuery: {type: Object,default: () => ({}),},},data() {return {ws: null,isAbnormal: false,btnLoading: false,progressStatus: "active",visible: false,percent: 0,tableData: [],currentPage: 1,multiFileNum: 0,};},computed: {// 導出的數據表的表頭tableTitle() {return this.tableTitleData.map((item) => {return item.title;});},//導出數據表的表頭的codetableCode() {return this.tableTitleData.map((item) => {return item.code;});},},watch: {percent: {handler(newVal) {if (newVal > 100) {this.progressStatus = "success";setTimeout(() => {this.handleClose();}, 500);}},},},methods: {//按照指定的title順序映射排序數組對象sortData(data, tit_code) {const newData = [];data.forEach((item) => {const newObj = {};tit_code.forEach((v) => {newObj[v] = item[v] || "";});newData.push(newObj);});return newData;},handleClose() {console.log("close");this.resetExport();this.visible = false;},resetExport() {this.percent = 0;this.progressStatus = "active";this.isAbnormal = false;this.tableData = [];this.currentPage = 1;this.multiFileNum = 0;this.ws = XLSX.utils.aoa_to_sheet([this.tableTitle]);},//獲取進度條百分比getPersent(res) {const persent_num =((res.paginator.currentPage * res.paginator.size) /res.paginator.total) *100;this.percent = parseInt(persent_num) - 1;},//異常處理handleAbnormal() {this.btnLoading = false;this.progressStatus = "exception";this.isAbnormal = true;},async startExport() {if (!this.asyncDataApi) {return new Promise(new Error("asyncDataApi is required"));}this.resetExport();await this.getTableData();},//請求導出的數據和標題async getTableData() {this.visible = true;this.btnLoading = true;this.isAbnormal = false;try {const res = await this.asyncDataApi({...this.listQuery,page: this.currentPage,});if (res.code !== 200) {this.handleAbnormal();this.$message.error(res.message || this.t("requestException"));return;}let apiData = res.data;apiData = this.sortData(apiData, this.tableCode);if (this.filterFunction) {apiData = this.filterFunction(apiData);}apiData = apiData.map((item) => Object.values(item));this.addSheetData(apiData, res);this.currentPage = res.paginator.currentPage + 1;console.log("res", res);this.getPersent(res);const isSplit =res.paginator.currentPage * res.paginator.size >=this.multiFileSize * (this.multiFileNum + 1);if (isSplit) {this.splitExport();}if (res.paginator.currentPage < res.paginator.page) {this.getTableData();return;}//當數據不滿足拆分數量時觸發this.hadnleOneExport(res);this.percent += 2;this.btnLoading = false;this.$message.success("導出成功");} catch (error) {console.log(error);this.$message.error("網絡錯誤，請稍后再試");this.handleAbnormal();}},//當數據不滿足拆分數量時觸發hadnleOneExport(res) {if (this.multiFileNum &&res.paginator.total > this.multiFileNum * this.multiFileSize) {this.multiFileNum += 1;this.exportExcel(this.filename + this.multiFileNum + ".xlsx",this.ws_name + this.multiFileNum);} else if (!this.multiFileNum) {this.exportExcel(this.filename + ".xlsx", this.ws_name);}},//拆分成多個excel導出splitExport() {this.multiFileNum += 1;this.exportExcel(this.filename + this.multiFileNum + ".xlsx",this.ws_name + this.multiFileNum);//重置表格this.ws = XLSX.utils.aoa_to_sheet([this.tableTitle]);},addSheetData(apiData, res) {//添加數據到表格 origin為每次添加數據從第幾行開始XLSX.utils.sheet_add_json(this.ws, apiData, {skipHeader: true,origin:(this.currentPage - 1) * res.paginator.size -this.multiFileSize * this.multiFileNum +1,});},//導出所有數據到一個excelexportExcel(fileName, sheetName) {// 文件名稱const filename = fileName;//Excel第一個sheet的名稱const ws_name = sheetName;// 創建wokbookconst wb = XLSX.utils.book_new();// 將數據添加到工作薄XLSX.utils.book_append_sheet(wb, this.ws, ws_name);// 導出文件XLSX.writeFile(wb, filename);},},
};
</script>

調用示例：

App.vue

<template><div><h1>測試表格導出</h1><div><ExportExcelComponent:tableTitleData="title":asyncDataApi="asyncDataApi":isCustom="isCustom":listQuery="listQuery"ref="export":filterFunction="handleDateFilter"><template #custom><!-- <a-button type="primary" @click="handleClick">導出excel</a-button> --><a-dropdown-button>Dropdown<a-menu slot="overlay" @click="handleMenuClick"><a-menu-item key="1"><a-icon type="user" />1st menu item</a-menu-item><a-menu-item key="2"><a-icon type="user" />2nd menu item</a-menu-item><a-menu-item key="3"><a-icon type="user" />3rd item</a-menu-item></a-menu></a-dropdown-button></template></ExportExcelComponent></div></div>
</template>
<script>
import ExportExcelComponent from "./ExportExcelComponent/ExportExcelComponent.vue";
import { asyncDataApi } from "./request";
import dayjs from "dayjs";
export default {data() {return {listQuery: {name: "yyy",age: 18,},isCustom: true,asyncDataApi: null,title: [{ code: "id", title: "序號" },{ code: "hobby", title: "愛好" },{ code: "name", title: "姓名" },{ code: "age", title: "年齡" },// { code: "hobby", title: "愛好" },{ code: "sex", title: "性別" },{ code: "address", title: "地址" },{ code: "birthday", title: "生日" },{ code: "createTime", title: "創建時間" },{ code: "updateTime", title: "更新時間" },{ code: "remark", title: "備注" },{ code: "status", title: "狀態" },],};},methods: {handleDateFilter(data) {const res = data.reduce((pre, cur) => {for (let i in cur) {if (i === "createTime") {cur[i] = dayjs(cur[i] * 1000).format("YYYY-MM-DD HH:mm:ss");}}pre.push(cur);return pre;}, []);return res;},async handleMenuClick(val) {// const titleNewData = [];// for (let i = 1; i < 500; i++) {//   this.title.forEach((item) => {//     titleNewData.push({ code: item.code + i, title: item.title + i });//   });// }// this.title = titleNewData;console.log("點擊了導出excel", val);await (this.asyncDataApi = asyncDataApi);this.$refs.export.startExport();},// async handleClick() {//   console.log("點擊了導出excel");//   await (this.asyncDataApi = asyncDataApi);//   this.$refs.export.startExport();// },},components: {ExportExcelComponent,},
};
</script>

mock數據:

request.js

const asyncDataApi = (listquery) => {console.log("params", listquery);// 模擬異步請求接口return new Promise((resolve, reject) => {setTimeout(() => {const data = [];for (let i = listquery.page * 100; i < (listquery.page + 1) * 100; i++) {const obj = {id: i - 99,name: "姓名" + i,age: 20 + i,hobby:"趙客縵胡纓，吳鉤霜雪明。銀鞍照白馬，颯沓如流星。十步殺一人，千里不留行。事了拂衣去，深藏身與名。閑過信陵飲，脫劍膝前橫。將炙啖朱亥，持觴勸侯嬴。" +i,sex: "男" + i,birthday: "2020-01-01",createTime: "1701155392",updateTime: "2020-01-01",remark: "備注" + i,status: "1" + i,};// let newObj = {};// for (var a = 1; a < 500; a++) {//   for (let k in obj) {//     newObj[k + a] = obj[k];//   }// }// data.push(newObj);data.push(obj);}resolve({data,code: 200,msg: "請求成功",paginator: {page: 1000,size: 100,total: 100000,currentPage: listquery.page,},});}, 100);});
};
export { asyncDataApi };

開發文檔

調用方式：

如果不采用自定義dom的話，直接點擊默認的按鈕可直接導出表格數據；如果采用自定義dom的話，通過ref實例調用子組件內的startExport方法，執行導出操作

<template><ExportExcelComponent...:isCustom = "true":asyncDataApi="asyncDataApi":tableTitleData="titles"ref="export"><a-button type="primary" @click="handleClick">導出excel</a-button></ExportExcelComponent>
</template>
?
<script>import { asyncDataApi } from '@/api/member'export default{data(){return:{titles:[]asyncDataApi,}}methods:{handleClick(){this.$refs.export.startExport();}}}
</script>

API

屬性如下

參數	說明	類型	默認值
listQuery	請求參數	Object	{}
asyncDataApi	請求數據的api函數	Function	必傳
tableTitleData	導出的數據表的表頭	Array	必傳
isCustom	是否自定義dom,如果采用插槽，需要開啟該屬性，否則dom為默認button；可以傳遞 `v-slot:custom` 來自定義 dom。	Boolean	false
modelTitle	模態框標題	String	"導出excel"
multiFileSize	拆分成每個表多少條數據，需要搭配isSplit屬性一起使用	Number	10e3
filename	導出的excel的表名	String	"Excel" + new Date().getTime()
ws_name	sheet名	String	"Sheet"
filterFunction	自定義過濾函數；可在業務層處理數據格式，如時間格式化等	Function(data)	null

FAQ

filterFunction怎么使用

<template><ExportExcelComponent...:isCustom = "true":asyncDataApi="asyncDataApi":tableTitleData="titles"ref="export":filterFunction="handleDateFilter"><a-button type="primary" @click="handleClick">導出excel</a-button></ExportExcelComponent>
</template>
?
<script>import { asyncDataApi } from '@/api/member'export default{data(){return:{titles:[]asyncDataApi,}}methods:{handleDateFilter(data) {const res = data.reduce((pre, cur) => {for (let i in cur) {if (i === "createTime") {cur[i] = dayjs(cur[i] * 1000).format("YYYY-MM-DD HH:mm:ss");}}pre.push(cur);return pre;}, []);return res;},handleClick(){this.$refs.export.startExport();}}}
</script>

導出表格數據為空是什么情況？

因為導出的表格數據的順序和標題的順序并不一定是一致的，所以在組件內部做了映射排序，一定要確保傳入的標題數據在調用導出接口之前執行。如果傳遞的標題有誤，在進行映射的時候，這時標題和表格數據并不匹配，那么就會出現數據映射為空的情況

Promise Error："asyncDataApi is required"

當傳遞給組件的后端api需要在點擊dom后賦值再傳遞的時候，一定要確保在導入后端api之后再調用組件內的導出方法，否則因為后端api還沒傳遞過去就調用，然后拋錯或者導出異常

正確示例：

async handleClick() {await (this.asyncDataApi = asyncDataApi);this.$refs.export.getTableData();
},

后端導出表格api數據返回格式

因該組件為全局組件，方便以后復用，需與后端協商規定好數據導出的格式。以下為筆者的公司，與后端同事協商的數據格式。大家可根據自己公司需要，更改以上源碼中后端返回值字段。

//后端返回數據結構
{"status": true,"data": [{...},{...},],"paginator": {"currentPage": 1,"total": 200,"size": 20,"page": 10}
}