1.先安装相应的插件
npm install xlsx@0.16.8npm install file-savernpm install xlsx-style-medalsoft2.封装好公共导出功能。我这里在utils文件夹下的tools.js
import FileSaver from "file-saver";import XLSX from "xlsx";import XLSXStyle from "xlsx-style-medalsoft";const OMS = {};// 导出Excel-单表格Excel 带样式OMS.downLoadXlsx = ({ dom = "el-table", name = "文件", ColumnWdth = [], rowName = null,}) => { const table = document.getElementById(dom);// 这里是如果前面设置了导出合并行列用display的话，这里要删除掉，不然会导出多空格 const hiddenCells = table.querySelectorAll('td[style*="display: none"]'); hiddenCells.forEach((cell) => cell.parentNode.removeChild(cell)); // 因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉 if (table.querySelector(".el-table__fixed")) { table.removeChild(table.querySelector(".el-table__fixed")); } if (table.querySelector(".el-table__fixed-right")) { table.removeChild(table.querySelector(".el-table__fixed-right")); } const et = XLSX.utils.table_to_book(table, { raw: true }); // 此处传入table的DOM节点,raw为true表示单元格为文本格式(未加工) const wbs = et.Sheets.Sheet1; // 删掉末尾空行 Object.keys(wbs).forEach((item, index) => { if (!item.startsWith("!") && wbs[item].v === "") { delete wbs[item]; } if (rowName!='') { if (item.includes(rowName)) { delete wbs[item]; } } }); console.log(wbs, "wbs"); // debugger; // 设置表格列行高 // 设置表格列宽度 if (ColumnWdth.length === 0) { for (let i = 0; i < 30; i++) { wbs["!cols"][i] = { wch: 12.5 }; // wbs["!rows"][i] = { hpt: 60 }; } } else { ColumnWdth.forEach((item, i) => { // 这里设置全部行高 wbs["!cols"][i] = { wch: item }; wbs["!rows"][i] = { hpt: item }; }); } // 循环遍历每一个表格，设置样式 for (const key in wbs) { if (!key.startsWith("!")) { wbs[key].s = { font: { sz: 11, // 字体大小 bold: false, // 加粗 name: "宋体", // 字体 color: { rgb: "000000", // 十六进制，不带# }, }, alignment: { // 文字居中 horizontal: "center", vertical: "center", wrapText: false, // 文本自动换行 textIndent: 1, // 设置文本溢出时的自动缩进，单位为字符数 }, border: { // 设置边框 // top: { style: "thin" }, // bottom: { style: "thin" }, // left: { style: "thin" }, // right: { style: "thin" }, }, }; } } const arr = [ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", ]; // 行列合并 const range = wbs["!merges"]; if (range) { range.forEach((item) => { const startColNumber = Number(item.s.r); const endColNumber = Number(item.e.r); const startRowNumber = Number(item.s.c); const endRowNumber = Number(item.e.c); const test = wbs[arr[startRowNumber] + (startColNumber + 1)]; for (let col = startColNumber; col <= endColNumber; col++) { for (let row = startRowNumber; row <= endRowNumber; row++) { wbs[arr[row] + (col + 1)] = test; } } }); } const etout = XLSXStyle.write(et, { bookType: "xlsx", type: "buffer", }); // eslint-disable-next-line no-useless-catch try { FileSaver.saveAs( new Blob([etout], { type: "application/octet-stream" }), `${name}.xlsx` ); // 导出的文件名 } catch (e) { throw e; }};export default OMS;3.页面中使用
给表格绑定一个id，通过获取dom来导出
import OMS from "@/utils/tools"; <el-table v-if="showTable" id="kuTable" max-height="525" v-loading="loading" :data="wmsStockList" @selection-change="handleSelectionChange" > </el-table>watch: { tableData: { immediate: true, async handler () { console.log("tableData", this.tableData); await this.$nextTick(); //根据实际选择延迟调用 // const tds = document.querySelectorAll('#table .el-table__fixed-footer-wrapper tr>td');//表格有固定列时 写法 const tds = document.querySelectorAll( "#kuTable .el-table__footer-wrapper tr>td" ) tds[0].colSpan = 3; tds[1].colSpan = 0; tds[2].colSpan = 0; tds[1].rowSpan = 0; tds[2].rowSpan = 0; tds[0].style.textAlign = "center"; tds[1].style.display = "none"; tds[2].style.display = "none"; }, }, },// 导出 handleExport() { // const that = this this.$confirm("是否确认导出入库列表?", "警告", { confirmButtonText: "确定", cancelButtonText: "取消", type: "warning", }) .then(() => { // console.log(this.pmsSpuInfoList, "this.pmsSpuInfoList"); this.exportLoading = true; OMS.downLoadXlsx({ dom: "kuTable", name: "入库列表", ColumnWdth: [ 12, 12, 20, 30, 20, 20, 30, 20, 40, 12, 20, 12, 14, 15, 15, ], // 每一列的宽度，需要直接指定，接受数字 rowName: "P", //可以设置哪行不要的 }); this.exportLoading = false; this.showTable = false; this.$nextTick(() => { // this.getList(); this.showTable = true; }); }) .catch(function () {}); },<template> <BasicLayout> <template #wrapper> <el-card class="box-card"> <el-form ref="queryForm" :model="queryParams" label-width="68px"> <el-row :gutter="20"> <el-col :span="4"> <el-form-item label="供应商ID" prop="supplierId"> <el-input clearable v-model="queryParams.supplierId" placeholder="请填入供应商ID" /> </el-form-item> </el-col> <el-col :span="4"> <el-form-item label="支付:" label-width="50px"> <selector clearable :options="payOptions" :select-value.sync="queryParams.paymentMethod" :placeholders="''" :widths="widths"></selector> </el-form-item> </el-col> <el-col :span="4"> <el-form-item label="统计时间"> <el-date-picker style="width: 100%; padding-top: 2px" v-model="queryParams.at" type="month" placeholder="选择月" :picker-options="pickerOptions" format="yyyy-MM"> </el-date-picker> </el-form-item> </el-col> <el-col :span="3"> <el-form-item class="comClass" style="margin-left: 12px; margin-bottom: 0px"> <el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery" v-permisaction="['srm-supplier:report']">查询</el-button> <el-button style="margin-left: 12px" type="warning" icon="el-icon-download" size="mini" @click="handleExport">导出</el-button> </el-form-item> </el-col> </el-row> <el-row> </el-row> </el-form> <el-table id="kuTable" :data="tableData" border style="width: 100%" v-loading="loading" :max-height="500" show-summary :summary-method="getSummaries" ref="table"> <!-- :summary-method="getSummaries" --> <el-table-column header-align="center" :label="headerItem"> <el-table-column label="供应商ID" align="center" width="120"> <template slot-scope="{ row }"> {{ row.supplierId }} </template> </el-table-column> <el-table-column label="供应商名称(全称)" align="center" prop="catId" width="150" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.supplierName }} </template> </el-table-column> <el-table-column label="结款方式" align="center" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.settlementMethod }} </template> </el-table-column> <el-table-column label="进货数量" align="center" prop="stockQuantity" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.stockQuantity }} </template> </el-table-column> <el-table-column label="进货金额" align="center" prop="stockPrice" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.stockPrice }} </template> </el-table-column> <el-table-column label="库存量" align="center" prop="remainingQuantity" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.remainingQuantity }} </template> </el-table-column> <el-table-column label="库存金额" align="center" prop="remainingPrice" :show-overflow-tooltip="true"> <template slot-scope="{ row }"> {{ row.remainingPrice }} </template> </el-table-column> <el-table-column label="发货量" align="center" prop="quantity" width="180"> <template slot-scope="scope"> <div>{{ scope.row.quantity }}</div> </template> </el-table-column> <el-table-column label="发货金额" align="center" prop="amount" width="180"> <template slot-scope="scope"> <div>{{ scope.row.amount }}</div> </template> </el-table-column> <el-table-column label="收货量" align="center" prop="real_quantity" width="180"> <template slot-scope="scope"> <div>{{ scope.row.real_quantity }}</div> </template> </el-table-column> <el-table-column label="收货金额" align="center" prop="realAmount" width="180"> <template slot-scope="scope"> <div>{{ scope.row.realAmount }}</div> </template> </el-table-column> </el-table-column> </el-table> <pagination v-show="total > 0" :total="total" :page.sync="queryParams.pageIndex" :limit.sync="queryParams.pageSize" @pagination="getList" /> </el-card> </template> </BasicLayout></template><script>import OMS from "@/utils/tools";import moment from "moment";const selector = () => import("@/views/compoments/selector.vue");import { getSupplierSalesReport } from "@/api/purchase/supplier";import { listSrmSupplier } from "@/api/purchase/supplier";export default { name: "supplierSales", components: { selector, }, data () { return { // 查询参数 queryParams: { pageIndex: 1, pageSize: 10, at: "", }, form: {}, title: "", supplierSelect: "", total: 0, tableData: [], loading: false, widths: "195px", pickerOptions: { disabledDate (time) { const currentYear = new Date().getFullYear(); const currentMonth = new Date().getMonth() + 1; const selectedYear = time.getFullYear(); const selectedMonth = time.getMonth() + 1; return ( selectedYear > currentYear || (selectedYear === currentYear && selectedMonth > currentMonth) ); }, }, headerItem: "", selectList: [], payOptions: [], }; }, created () { this.queryParams.at = moment().format("YYYY-MM"); this.headerItem = moment(this.queryParams.at).format("YYYY年MM月") + "供应商进销报表"; this.getList(); this.getDict(); }, watch: { "queryParams.at": { handler (v) { if (v == "" || v == undefined) { this.queryParams.at = moment().format("YYYY-MM"); } }, }, tableData: { immediate: true, async handler () { console.log("tableData", this.tableData); await this.$nextTick(); //根据实际选择延迟调用 // const tds = document.querySelectorAll('#table .el-table__fixed-footer-wrapper tr>td');//表格有固定列时 写法 const tds = document.querySelectorAll( "#kuTable .el-table__footer-wrapper tr>td" ) tds[0].colSpan = 3; tds[1].colSpan = 0; tds[2].colSpan = 0; tds[1].rowSpan = 0; tds[2].rowSpan = 0; tds[0].style.textAlign = "center"; tds[1].style.display = "none"; tds[2].style.display = "none"; }, }, }, updated () { this.$nextTick(() => { console.log("表格重新渲染了"); this.$refs["table"].doLayout(); }); }, methods: { /** 查询参数列表 */ getList () { this.loading = true; getSupplierSalesReport(this.queryParams).then((response) => { this.tableData = response.data.list; this.total = response.data.count; this.loading = false; }); }, // 供应商列表 getSupplier () { listSrmSupplier(this.addDateRange(this.queryParams, this.dateRange)).then( (response) => { this.selectList = response.data.list; } ); }, // getSummaries (param) { // const { columns, data } = param; // const sums = []; // columns.forEach((column, index) => { // if (index === 0) { // sums[index] = "总价"; // return; // } // const values = data.map((item) => Number(item[column.property])); // if (!values.every((value) => isNaN(value))) { // sums[index] = values.reduce((prev, curr) => { // const value = Number(curr); // if (!isNaN(value)) { // return prev + curr; // } else { // return prev; // } index.vue // }, 0); // // sums[index] += sums[index]; // } else { // sums[index] = "N/A"; // } // }); // return sums; // }, // 获取字段值 async getDict () { const queryArr = [ { query: { type: "sys_payment_method" }, key: "payOptions" }, ]; const promises = queryArr.map(async (item) => { const res = await this.getDicts(item.query.type); this[item.key] = res.data; }); await Promise.all(promises); }, getSummaries (param) { const { columns, data } = param; const sums = []; columns.forEach((column, index) => { if (index === 0) { // 需要显示'总价'的列 坐标 :0 sums[index] = '总价'; return; } const indexes = [2, 3, 4, 5, 6, 7, 8, 9, 10]; // 需要显示总和的列的索引 if (indexes.includes(index)) { const values = data.map(item => Number(item[column.property])); if (!values.some(isNaN)) { const sum = values.reduce((prev, curr) => prev + curr, 0); sums[index] = `${sum}元`; } else { sums[index] = 'N/A'; } } }); return sums; }, // 表单重置 reset () { this.form = { supplier: undefined, groupDetails: undefined, }; this.resetForm("form"); }, /** 搜索按钮操作 */ handleQuery () { this.queryParams.pageIndex = 1; this.$nextTick(() => { this.headerItem = moment(this.queryParams.at).format("YYYY年MM月") + "供应商进销报表"; }); this.getList(); }, handleExport () { // const that = this this.$confirm("是否确认导出供应商进销报表?", "警告", { confirmButtonText: "确定", cancelButtonText: "取消", type: "warning", }) .then(() => { this.exportLoading = true; const tds = document.querySelectorAll('.el-table__footer td') tds[0].setAttribute('rowspan', '1') tds[1].setAttribute('rowspan', '1') OMS.downLoadXlsx({ dom: "kuTable", name: `${this.headerItem}`, ColumnWdth: [ 12, 20, 20, 30, 20, 20, 30, 20, 20, 12, 20, ], // 每一列的宽度，需要直接指定，接受数字 rowName: "L,M", // 哪行不要的 }); this.exportLoading = false; this.showTable = false; this.$nextTick(() => { // this.getList(); this.showTable = true; }); }) .catch(function () { }); }, },};</script><style lang="scss" scoped>::v-deep .comClass .el-form-item__content { margin-left: 0px !important;}::v-deep .el-input__inner { height: 32px !important;}</style>4.效果图
