纯前端导出xlsx表格
效果
模板
这种多级表头 前端写出来很崩溃 最后用的是直接模板上传 然后数据回填的思想
先安装exceljs插件
npm install exceljs引入
import ExcelJS from "exceljs";我做了两种 一种是将模板变成静态文件去访问他再导出 因为不可能每次导出都要你先上传模板
一种是要上传模板才可以导出
第一种：不需要上传模板 直接将模板设置为静态文件访问
将模板放到公共文件夹
结构是：public->xlsx->template
mounted() {// 在组件挂载时读取模板文件this.loadTemplateFile();},我还在访问静态文件时候踩雷xlsx/template.xlsx 我原本路径是这样 但是这个项目有后缀所以加上后缀名casset。 new Uint8Array(response) 里面的response一定要打印 看是否有data 没有的话直接response放进去 打印出 this.templateFile.length一定要大于0
async loadTemplateFile() {try {const response = await axios.get("/casset/xlsx/template.xlsx", {responseType: "arraybuffer",});console.log("response", response);this.templateFile = new Uint8Array(response);console.log("Template file loaded successfully, file size:",this.templateFile.length);} catch (error) {console.error("Error loading template file:", error);}},这边和第二种逻辑一样的 但是有一个差别因为这边是个模板是由两张xlsx的 导致我无法正确加载工作表 所以我改成
// 如果有多个工作表，选择第一个工作表const worksheet = workbook.worksheets[0];完整代码如下
async exportToExcel() {if (!this.templateFile || this.templateFile.length === 0) {alert("请先上传模板文件");return;}const workbook = new ExcelJS.Workbook();// await workbook.xlsx.load(this.templateFile);try {await workbook.xlsx.load(this.templateFile);console.log("Workbook loaded successfully");} catch (error) {console.error("Error loading workbook:", error);alert("未能正确加载工作表，请检查模板文件。");return;}// 检查是否有至少一个工作表if (workbook.worksheets.length < 1) {alert("未能正确加载工作表，请检查模板文件。");return;}// 如果有多个工作表，选择第一个工作表const worksheet = workbook.worksheets[0];// 检查工作表是否存在if (!worksheet) {alert("未能正确加载工作表，请检查模板文件。");return;}// 获取模板表头最后一行的索引const headerEndRow = 6; // 假设表头占用前两行，请根据实际情况调整// 分离数据和合计行const data = this.dataSource.slice(0, -1); // 除去最后一行data.forEach((item, index) => {const rowIndex = index + headerEndRow + 1; // 从表头结束的下一行开始const row = worksheet.getRow(rowIndex);row.getCell(1).value =item.companyName !== undefined ? item.companyName : 0; // 确保数据存在row.getCell(2).value =item.projectsNum !== undefined ? item.projectsNum : 0;row.getCell(3).value =item.allDealMoney !== undefined ? item.allDealMoney : 0;row.getCell(4).value =item.yearShouldRentMoney !== undefined ? item.yearShouldRentMoney : 0;row.getCell(5).value =item.yearRealRentMoney !== undefined ? item.yearRealRentMoney : 0;row.getCell(6).value =item.platFormProjectsNum !== undefined ? item.platFormProjectsNum : 0;row.getCell(7).value =item.platFormFiveYearsProjectsNum !== undefined? item.platFormFiveYearsProjectsNum: 0;row.getCell(8).value =item.platFormGroupProjectsNum !== undefined? item.platFormGroupProjectsNum: 0;row.getCell(9).value =item.platFormGovernmentProjectsNum !== undefined? item.platFormGovernmentProjectsNum: 0;row.getCell(10).value =item.platFormRentAreas !== undefined ? item.platFormRentAreas : 0;row.getCell(11).value =item.platFormEquipNum !== undefined ? item.platFormEquipNum : 0;row.getCell(12).value =item.platFormAllContractMoney !== undefined? item.platFormAllContractMoney: 0;row.getCell(13).value =item.independentProjectsNum !== undefined? item.independentProjectsNum: 0;row.getCell(14).value =item.independentRentAreas !== undefined? item.independentRentAreas: 0;row.getCell(15).value =item.independentEquipNum !== undefined ? item.independentEquipNum : 0;row.getCell(16).value =item.independentAllContractMoney !== undefined? item.independentAllContractMoney: 0;row.getCell(17).value =item.agreementProjectsNum !== undefined? item.agreementProjectsNum: 0;row.getCell(18).value =item.agreementFiveYearsProjectsNum !== undefined? item.agreementFiveYearsProjectsNum: 0;row.getCell(19).value =item.agreementGroupProjectsNum !== undefined? item.agreementGroupProjectsNum: 0;row.getCell(20).value =item.agreementGovernmentProjectsNum !== undefined? item.agreementGovernmentProjectsNum: 0;row.getCell(21).value =item.agreementRentAreas !== undefined ? item.agreementRentAreas : 0;row.getCell(22).value =item.agreementEquipNum !== undefined ? item.agreementEquipNum : 0;row.getCell(23).value =item.agreementAllContractMoney !== undefined? item.agreementAllContractMoney: 0;row.commit();// 设置单元格居中和边框row.eachCell((cell) => {cell.alignment = { horizontal: "center", vertical: "middle" };cell.border = {top: { style: "thin" },left: { style: "thin" },bottom: { style: "thin" },right: { style: "thin" },};});// 设置行高row.height = 20; // 可根据需要调整行高});// 添加合计行const summaryRowIndex = data.length + headerEndRow + 1;const summaryRow = worksheet.getRow(summaryRowIndex);const summary = this.dataSource[this.dataSource.length - 1]; // 最后一行作为合计行summaryRow.getCell(1).value = "合计"; // 设置companyName为"合计"summaryRow.getCell(2).value =summary.projectsNum !== undefined ? summary.projectsNum : 0;summaryRow.getCell(3).value =summary.allDealMoney !== undefined ? summary.allDealMoney : 0;summaryRow.getCell(4).value =summary.yearShouldRentMoney !== undefined? summary.yearShouldRentMoney: 0;summaryRow.getCell(5).value =summary.yearRealRentMoney !== undefined ? summary.yearRealRentMoney : 0;summaryRow.getCell(6).value =summary.platFormProjectsNum !== undefined? summary.platFormProjectsNum: 0;summaryRow.getCell(7).value =summary.platFormFiveYearsProjectsNum !== undefined? summary.platFormFiveYearsProjectsNum: 0;summaryRow.getCell(8).value =summary.platFormGroupProjectsNum !== undefined? summary.platFormGroupProjectsNum: 0;summaryRow.getCell(9).value =summary.platFormGovernmentProjectsNum !== undefined? summary.platFormGovernmentProjectsNum: 0;summaryRow.getCell(10).value =summary.platFormRentAreas !== undefined ? summary.platFormRentAreas : 0;summaryRow.getCell(11).value =summary.platFormEquipNum !== undefined ? summary.platFormEquipNum : 0;summaryRow.getCell(12).value =summary.platFormAllContractMoney !== undefined? summary.platFormAllContractMoney: 0;summaryRow.getCell(13).value =summary.independentProjectsNum !== undefined? summary.independentProjectsNum: 0;summaryRow.getCell(14).value =summary.independentRentAreas !== undefined? summary.independentRentAreas: 0;summaryRow.getCell(15).value =summary.independentEquipNum !== undefined? summary.independentEquipNum: 0;summaryRow.getCell(16).value =summary.independentAllContractMoney !== undefined? summary.independentAllContractMoney: 0;summaryRow.getCell(17).value =summary.agreementProjectsNum !== undefined? summary.agreementProjectsNum: 0;summaryRow.getCell(18).value =summary.agreementFiveYearsProjectsNum !== undefined? summary.agreementFiveYearsProjectsNum: 0;summaryRow.getCell(19).value =summary.agreementGroupProjectsNum !== undefined? summary.agreementGroupProjectsNum: 0;summaryRow.getCell(20).value =summary.agreementGovernmentProjectsNum !== undefined? summary.agreementGovernmentProjectsNum: 0;summaryRow.getCell(21).value =summary.agreementRentAreas !== undefined? summary.agreementRentAreas: 0;summaryRow.getCell(22).value =summary.agreementEquipNum !== undefined ? summary.agreementEquipNum : 0;summaryRow.getCell(23).value =summary.agreementAllContractMoney !== undefined? summary.agreementAllContractMoney: 0;summaryRow.commit();// 设置合计行样式summaryRow.eachCell((cell, colNumber) => {cell.alignment = { horizontal: "center" };cell.border = {top: { style: "thin" },left: { style: "thin" },bottom: { style: "thin" },right: { style: "thin" },};});// 导出文件const buffer = await workbook.xlsx.writeBuffer();const blob = new Blob([buffer], { type: "application/octet-stream" });const url = window.URL.createObjectURL(blob);const a = document.createElement("a");a.href = url;a.download = "租赁情况汇总表.xlsx";a.click();window.URL.revokeObjectURL(url); //释放内存},第二种：上传模板才可以导出
<div class="table-operator"><inputtype="file"id="template-upload"@change="handleFileUpload"style="margin-left: 8px"/><a-button type="primary" @click="exportToExcel" style="margin-left: 8px">打印</a-button></div> handleFileUpload(event) {const file = event.target.files[0];if (file) {const reader = new FileReader();reader.onload = (e) => {this.templateFile = new Uint8Array(e.target.result);};reader.readAsArrayBuffer(file);}}, async exportToExcel() {if (!this.templateFile) {alert("请先上传模板文件");return;}const workbook = new ExcelJS.Workbook();await workbook.xlsx.load(this.templateFile);const worksheet = workbook.getWorksheet(1);// // 添加表头// const headerRow = worksheet.getRow(1);// this.columns.forEach((column, index) => {// // headerRow.getCell(index + 1).value = column.title;// headerRow.getCell(index + 1).alignment = { horizontal: "center" };// headerRow.getCell(index + 1).font = { bold: true };// // 设置列宽// worksheet.getColumn(index + 1).width = 20; // 可根据需要调整宽度// });// 获取模板表头最后一行的索引const headerEndRow = 4; // 假设表头占用前两行，请根据实际情况调整// 分离数据和合计行const data = this.dataSource.filter((item) => !item.isTotalRow);data.forEach((item, index) => {const rowIndex = index + headerEndRow + 1; // 从表头结束的下一行开始const row = worksheet.getRow(rowIndex);row.getCell(1).value = index + 1; // 序号列row.getCell(2).value = item.companyName || ""; // 确保数据存在row.getCell(3).value = item.assetName || "";row.getCell(4).value = item.bookMoney || "";row.getCell(5).value = transferWay(item.transferWay) || "";row.getCell(6).value = transferFinalOrg(item.transferFinalOrg) || "";row.getCell(7).value = item.receiveName || "";row.getCell(8).value = translateReceiveType(item.receiveType) || "";row.getCell(9).value = item.evaluateMoney || "";row.getCell(10).value = item.transferMinPrice || "";row.getCell(11).value = item.dealMoney || "";row.getCell(12).value = item.moreMoney || "";row.getCell(13).value = item.signDate || "";row.getCell(14).value = item.remark || "";row.commit();// 设置单元格居中和边框row.eachCell((cell) => {cell.alignment = { horizontal: "center", vertical: "middle" };cell.border = {top: { style: "thin" },left: { style: "thin" },bottom: { style: "thin" },right: { style: "thin" },};});// 设置行高row.height = 20; // 可根据需要调整行高});// 添加合计行const summaryRowIndex = data.length + headerEndRow + 1;const summaryRow = worksheet.getRow(summaryRowIndex);const summary = this.dataSource[this.dataSource.length - 1]; // 最后一行作为合计行summaryRow.getCell(1).value = "合计";summaryRow.getCell(4).value = summary.bookMoney;summaryRow.getCell(5).value = summary.transferWay;summaryRow.getCell(6).value = summary.transferFinalOrg;summaryRow.getCell(7).value = summary.receiveName;summaryRow.getCell(8).value = summary.receiveType;summaryRow.getCell(9).value = summary.evaluateMoney;summaryRow.getCell(10).value = summary.transferMinPrice;summaryRow.getCell(11).value = summary.dealMoney;summaryRow.getCell(12).value = summary.moreMoney;summaryRow.getCell(13).value = summary.signDate;summaryRow.getCell(14).value = summary.remark;summaryRow.commit();// 合并合计行的前三列worksheet.mergeCells(summaryRowIndex, 1, summaryRowIndex, 3);worksheet.getCell(summaryRowIndex, 1).alignment = {horizontal: "center",};// 设置合计行样式summaryRow.eachCell((cell, colNumber) => {cell.alignment = { horizontal: "center" };cell.border = {top: { style: "thin" },left: { style: "thin" },bottom: { style: "thin" },right: { style: "thin" },};});// 导出文件const buffer = await workbook.xlsx.writeBuffer();const blob = new Blob([buffer], { type: "application/octet-stream" });const url = window.URL.createObjectURL(blob);const a = document.createElement("a");a.href = url;a.download = "exported_data.xlsx";a.click();window.URL.revokeObjectURL(url);},