1.先安装相应的插件
npm install xlsx@0.16.8
npm install file-saver
npm install xlsx-style-medalsoft
2.封装好公共导出功能。我这里在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-catchtry {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-tablev-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 = thisthis.$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 = thisthis.$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.效果图
