前端实现excel导出表格(纯前端实现)

1.先安装相应的插件

npm install [email protected]
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-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.效果图