devbox
小惠珠哦
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：小惠珠哦 | 2024-08-04 14:25:06

赞

踩

前端导出excel

1.先安装相应的插件

  1. npm install xlsx@0.16.8
  2. npm install file-saver
  3. npm install xlsx-style-medalsoft

2.封装好公共导出功能。我这里在utils文件夹下的tools.js

  1. import FileSaver from "file-saver";
  2. import XLSX from "xlsx";
  3. import XLSXStyle from "xlsx-style-medalsoft";
  4.  
  5. const OMS = {};
  6.  
  7. // 导出Excel-单表格Excel  带样式
  8. OMS.downLoadXlsx = ({
  9.   dom = "el-table",
  10.   name = "文件",
  11.   ColumnWdth = [],
  12.   rowName = null,
  13. }) => {
  14.   const table = document.getElementById(dom);
  15. //   这里是如果前面设置了导出合并行列用display的话，这里要删除掉，不然会导出多空格
  16.    const hiddenCells = table.querySelectorAll('td[style*="display: none"]');
  17.    hiddenCells.forEach((cell) => cell.parentNode.removeChild(cell));
  18.   //   因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉
  19.   if (table.querySelector(".el-table__fixed")) {
  20.     table.removeChild(table.querySelector(".el-table__fixed"));
  21.   }
  22.   if (table.querySelector(".el-table__fixed-right")) {
  23.     table.removeChild(table.querySelector(".el-table__fixed-right"));
  24.   }
  25.   const et = XLSX.utils.table_to_book(table, { raw: true }); // 此处传入table的DOM节点,raw为true表示单元格为文本格式(未加工)
  26.   const wbs = et.Sheets.Sheet1;
  27.  
  28.   // 删掉末尾空行
  29.   Object.keys(wbs).forEach((item, index) => {
  30.     if (!item.startsWith("!") && wbs[item].v === "") {
  31.       delete wbs[item];
  32.     }
  33.     if (rowName!='') {
  34.       if (item.includes(rowName)) {
  35.         delete wbs[item];
  36.       }
  37.     }
  38.   });
  39.   console.log(wbs, "wbs");
  40.  // debugger;
  41.   //  设置表格列行高
  42.   // 设置表格列宽度
  43.   if (ColumnWdth.length === 0) {
  44.     for (let i = 0; i < 30; i++) {
  45.       wbs["!cols"][i] = { wch: 12.5 };
  46.     //   wbs["!rows"][i] = { hpt: 60 };
  47.     }
  48.   } else {
  49.     ColumnWdth.forEach((item, i) => {
  50.         // 这里设置全部行高
  51.       wbs["!cols"][i] = { wch: item };
  52.       wbs["!rows"][i] = { hpt: item };
  53.     });
  54.   }
  55.  
  56.   // 循环遍历每一个表格，设置样式
  57.   for (const key in wbs) {
  58.     if (!key.startsWith("!")) {
  59.       wbs[key].s = {
  60.         font: {
  61.           sz: 11, // 字体大小
  62.           bold: false, // 加粗
  63.           name: "宋体", // 字体
  64.           color: {
  65.             rgb: "000000", // 十六进制，不带#
  66.           },
  67.         },
  68.         alignment: {
  69.           // 文字居中
  70.           horizontal: "center",
  71.           vertical: "center",
  72.           wrapText: false, // 文本自动换行
  73.           textIndent: 1, // 设置文本溢出时的自动缩进，单位为字符数
  74.         },
  75.         border: {
  76.           // 设置边框
  77.           //   top: { style: "thin" },
  78.           //   bottom: { style: "thin" },
  79.           //   left: { style: "thin" },
  80.           //   right: { style: "thin" },
  81.         },
  82.       };
  83.     }
  84.   }
  85.  
  86.   const arr = [
  87.     "A",
  88.     "B",
  89.     "C",
  90.     "D",
  91.     "E",
  92.     "F",
  93.     "G",
  94.     "H",
  95.     "I",
  96.     "J",
  97.     "K",
  98.     "L",
  99.     "M",
  100.     "N",
  101.     "O",
  102.     "P",
  103.     "Q",
  104.     "R",
  105.     "S",
  106.     "T",
  107.     "U",
  108.     "V",
  109.     "W",
  110.     "X",
  111.     "Y",
  112.     "Z",
  113.   ];
  114.  
  115.   // 行列合并
  116.   const range = wbs["!merges"];
  117.   if (range) {
  118.     range.forEach((item) => {
  119.       const startColNumber = Number(item.s.r);
  120.       const endColNumber = Number(item.e.r);
  121.       const startRowNumber = Number(item.s.c);
  122.       const endRowNumber = Number(item.e.c);
  123.       const test = wbs[arr[startRowNumber] + (startColNumber + 1)];
  124.       for (let col = startColNumber; col <= endColNumber; col++) {
  125.         for (let row = startRowNumber; row <= endRowNumber; row++) {
  126.           wbs[arr[row] + (col + 1)] = test;
  127.         }
  128.       }
  129.     });
  130.   }
  131.  
  132.   const etout = XLSXStyle.write(et, {
  133.     bookType: "xlsx",
  134.     type: "buffer",
  135.   });
  136.   // eslint-disable-next-line no-useless-catch
  137.   try {
  138.     FileSaver.saveAs(
  139.       new Blob([etout], { type: "application/octet-stream" }),
  140.       `${name}.xlsx`
  141.     ); // 导出的文件名
  142.   } catch (e) {
  143.     throw e;
  144.   }
  145. };
  146.  
  147. export default OMS;

3.页面中使用

给表格绑定一个id，通过获取dom来导出

  1. import OMS from "@/utils/tools";
  2.  
  3.  
  4.      <el-table
  5.           v-if="showTable"
  6.           id="kuTable"
  7.           max-height="525"
  8.           v-loading="loading"
  9.           :data="wmsStockList"
  10.           @selection-change="handleSelectionChange"
  11.         >
  12.                </el-table>
  13.  
  14. watch: {
  15.        
  16.         tableData: {
  17.             immediate: true,
  18.             async handler () {
  19.                 console.log("tableData", this.tableData);
  20.                 await this.$nextTick(); //根据实际选择延迟调用
  21.                 // const tds = document.querySelectorAll('#table .el-table__fixed-footer-wrapper tr>td');//表格有固定列时 写法
  22.                 const tds = document.querySelectorAll(
  23.                     "#kuTable .el-table__footer-wrapper tr>td"
  24.                 )
  25.                 tds[0].colSpan = 3;
  26.                 tds[1].colSpan = 0;
  27.                 tds[2].colSpan = 0;
  28.                 tds[1].rowSpan = 0;
  29.                 tds[2].rowSpan = 0;
  30.                 tds[0].style.textAlign = "center";
  31.                 tds[1].style.display = "none";
  32.                 tds[2].style.display = "none";
  33.             },
  34.         },
  35.     },
  36.  
  37.  
  38. // 导出
  39.     handleExport() {
  40.       // const that = this
  41.       this.$confirm("是否确认导出入库列表?", "警告", {
  42.         confirmButtonText: "确定",
  43.         cancelButtonText: "取消",
  44.         type: "warning",
  45.       })
  46.         .then(() => {
  47.           //   console.log(this.pmsSpuInfoList, "this.pmsSpuInfoList");
  48.           this.exportLoading = true;
  49.           OMS.downLoadXlsx({
  50.             dom: "kuTable",
  51.             name: "入库列表",
  52.             ColumnWdth: [
  53.               12, 12, 20, 30, 20, 20, 30, 20, 40, 12, 20, 12, 14, 15, 15,
  54.             ], // 每一列的宽度，需要直接指定，接受数字
  55.             rowName: "P", //可以设置哪行不要的
  56.           });
  57.           this.exportLoading = false;
  58.           this.showTable = false;
  59.           this.$nextTick(() => {
  60.             // this.getList();
  61.             this.showTable = true;
  62.           });
  63.         })
  64.         .catch(function () {});
  65.     },
  1. <template>
  2.     <BasicLayout>
  3.         <template #wrapper>
  4.             <el-card class="box-card">
  5.                 <el-form ref="queryForm" :model="queryParams" label-width="68px">
  6.                     <el-row :gutter="20">
  7.                         <el-col :span="4">
  8.                             <el-form-item label="供应商ID" prop="supplierId">
  9.                                 <el-input clearable v-model="queryParams.supplierId" placeholder="请填入供应商ID" />
  10.                             </el-form-item>
  11.                         </el-col>
  12.                         <el-col :span="4">
  13.                             <el-form-item label="支付:" label-width="50px">
  14.                                 <selector clearable :options="payOptions" :select-value.sync="queryParams.paymentMethod"
  15.                                     :placeholders="''" :widths="widths"></selector>
  16.                             </el-form-item>
  17.                         </el-col>
  18.                         <el-col :span="4">
  19.                             <el-form-item label="统计时间">
  20.                                 <el-date-picker style="width: 100%; padding-top: 2px" v-model="queryParams.at" type="month"
  21.                                     placeholder="选择月" :picker-options="pickerOptions" format="yyyy-MM">
  22.                                 </el-date-picker>
  23.                             </el-form-item>
  24.                         </el-col>
  25.                         <el-col :span="3">
  26.                             <el-form-item class="comClass" style="margin-left: 12px; margin-bottom: 0px">
  27.                                 <el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery"
  28.                                     v-permisaction="['srm-supplier:report']">查询</el-button>
  29.                                 <el-button style="margin-left: 12px" type="warning" icon="el-icon-download" size="mini"
  30.                                     @click="handleExport">导出</el-button>
  31.                             </el-form-item>
  32.                         </el-col>
  33.                     </el-row>
  34.                     <el-row> </el-row>
  35.                 </el-form>
  36.                 <el-table id="kuTable" :data="tableData" border style="width: 100%" v-loading="loading" :max-height="500"
  37.                     show-summary :summary-method="getSummaries" ref="table">
  38.                     <!-- :summary-method="getSummaries" -->
  39.  
  40.                     <el-table-column header-align="center" :label="headerItem">
  41.                         <el-table-column label="供应商ID" align="center" width="120">
  42.                             <template slot-scope="{ row }">
  43.                                 {{ row.supplierId }}
  44.                             </template>
  45.                         </el-table-column>
  46.                         <el-table-column label="供应商名称(全称)" align="center" prop="catId" width="150"
  47.                             :show-overflow-tooltip="true">
  48.                             <template slot-scope="{ row }">
  49.                                 {{ row.supplierName }}
  50.                             </template>
  51.                         </el-table-column>
  52.                         <el-table-column label="结款方式" align="center" :show-overflow-tooltip="true">
  53.                             <template slot-scope="{ row }">
  54.                                 {{ row.settlementMethod }}
  55.                             </template>
  56.                         </el-table-column>
  57.                         <el-table-column label="进货数量" align="center" prop="stockQuantity" :show-overflow-tooltip="true">
  58.                             <template slot-scope="{ row }">
  59.                                 {{ row.stockQuantity }}
  60.                             </template>
  61.                         </el-table-column>
  62.                         <el-table-column label="进货金额" align="center" prop="stockPrice" :show-overflow-tooltip="true">
  63.                             <template slot-scope="{ row }">
  64.                                 {{ row.stockPrice }}
  65.                             </template>
  66.                         </el-table-column>
  67.                         <el-table-column label="库存量" align="center" prop="remainingQuantity" :show-overflow-tooltip="true">
  68.                             <template slot-scope="{ row }">
  69.                                 {{ row.remainingQuantity }}
  70.                             </template>
  71.                         </el-table-column>
  72.                         <el-table-column label="库存金额" align="center" prop="remainingPrice" :show-overflow-tooltip="true">
  73.                             <template slot-scope="{ row }">
  74.                                 {{ row.remainingPrice }}
  75.                             </template>
  76.                         </el-table-column>
  77.                         <el-table-column label="发货量" align="center" prop="quantity" width="180">
  78.                             <template slot-scope="scope">
  79.                                 <div>{{ scope.row.quantity }}</div>
  80.                             </template>
  81.                         </el-table-column>
  82.                         <el-table-column label="发货金额" align="center" prop="amount" width="180">
  83.                             <template slot-scope="scope">
  84.                                 <div>{{ scope.row.amount }}</div>
  85.                             </template>
  86.                         </el-table-column>
  87.                         <el-table-column label="收货量" align="center" prop="real_quantity" width="180">
  88.                             <template slot-scope="scope">
  89.                                 <div>{{ scope.row.real_quantity }}</div>
  90.                             </template>
  91.                         </el-table-column>
  92.                         <el-table-column label="收货金额" align="center" prop="realAmount" width="180">
  93.                             <template slot-scope="scope">
  94.                                 <div>{{ scope.row.realAmount }}</div>
  95.                             </template>
  96.                         </el-table-column>
  97.                     </el-table-column>
  98.                 </el-table>
  99.                 <pagination v-show="total > 0" :total="total" :page.sync="queryParams.pageIndex"
  100.                     :limit.sync="queryParams.pageSize" @pagination="getList" />
  101.             </el-card>
  102.         </template>
  103.     </BasicLayout>
  104. </template>
  105.  
  106. <script>
  107. import OMS from "@/utils/tools";
  108. import moment from "moment";
  109. const selector = () => import("@/views/compoments/selector.vue");
  110. import { getSupplierSalesReport } from "@/api/purchase/supplier";
  111. import { listSrmSupplier } from "@/api/purchase/supplier";
  112.  
  113. export default {
  114.     name: "supplierSales",
  115.     components: {
  116.         selector,
  117.     },
  118.     data () {
  119.         return {
  120.             // 查询参数
  121.             queryParams: {
  122.                 pageIndex: 1,
  123.                 pageSize: 10,
  124.                 at: "",
  125.             },
  126.             form: {},
  127.             title: "",
  128.             supplierSelect: "",
  129.             total: 0,
  130.             tableData: [],
  131.             loading: false,
  132.             widths: "195px",
  133.             pickerOptions: {
  134.                 disabledDate (time) {
  135.                     const currentYear = new Date().getFullYear();
  136.                     const currentMonth = new Date().getMonth() + 1;
  137.                     const selectedYear = time.getFullYear();
  138.                     const selectedMonth = time.getMonth() + 1;
  139.                     return (
  140.                         selectedYear > currentYear ||
  141.                         (selectedYear === currentYear && selectedMonth > currentMonth)
  142.                     );
  143.                 },
  144.             },
  145.             headerItem: "",
  146.             selectList: [],
  147.             payOptions: [],
  148.         };
  149.     },
  150.     created () {
  151.         this.queryParams.at = moment().format("YYYY-MM");
  152.         this.headerItem =
  153.             moment(this.queryParams.at).format("YYYY年MM月") + "供应商进销报表";
  154.         this.getList();
  155.         this.getDict();
  156.     },
  157.     watch: {
  158.         "queryParams.at": {
  159.             handler (v) {
  160.                 if (v == "" || v == undefined) {
  161.                     this.queryParams.at = moment().format("YYYY-MM");
  162.                 }
  163.             },
  164.         },
  165.         tableData: {
  166.             immediate: true,
  167.             async handler () {
  168.                 console.log("tableData", this.tableData);
  169.                 await this.$nextTick(); //根据实际选择延迟调用
  170.                 // const tds = document.querySelectorAll('#table .el-table__fixed-footer-wrapper tr>td');//表格有固定列时 写法
  171.                 const tds = document.querySelectorAll(
  172.                     "#kuTable .el-table__footer-wrapper tr>td"
  173.                 )
  174.                 tds[0].colSpan = 3;
  175.                 tds[1].colSpan = 0;
  176.                 tds[2].colSpan = 0;
  177.                 tds[1].rowSpan = 0;
  178.                 tds[2].rowSpan = 0;
  179.                 tds[0].style.textAlign = "center";
  180.                 tds[1].style.display = "none";
  181.                 tds[2].style.display = "none";
  182.             },
  183.         },
  184.     },
  185.     updated () {
  186.         this.$nextTick(() => {
  187.             console.log("表格重新渲染了");
  188.             this.$refs["table"].doLayout();
  189.         });
  190.     },
  191.  
  192.     methods: {
  193.         /** 查询参数列表 */
  194.         getList () {
  195.             this.loading = true;
  196.             getSupplierSalesReport(this.queryParams).then((response) => {
  197.                 this.tableData = response.data.list;
  198.                 this.total = response.data.count;
  199.                 this.loading = false;
  200.             });
  201.         },
  202.         // 供应商列表
  203.         getSupplier () {
  204.             listSrmSupplier(this.addDateRange(this.queryParams, this.dateRange)).then(
  205.                 (response) => {
  206.                     this.selectList = response.data.list;
  207.                 }
  208.             );
  209.         },
  210.         // getSummaries (param) {
  211.         //     const { columns, data } = param;
  212.         //     const sums = [];
  213.         //     columns.forEach((column, index) => {
  214.         //         if (index === 0) {
  215.         //             sums[index] = "总价";
  216.         //             return;
  217.         //         }
  218.         //         const values = data.map((item) => Number(item[column.property]));
  219.         //         if (!values.every((value) => isNaN(value))) {
  220.         //             sums[index] = values.reduce((prev, curr) => {
  221.         //                 const value = Number(curr);
  222.         //                 if (!isNaN(value)) {
  223.         //                     return prev + curr;
  224.         //                 } else {
  225.         //                     return prev;
  226.         //                 } index.vue
  227.         //             }, 0);
  228.         //             //   sums[index] += sums[index];
  229.         //         } else {
  230.         //             sums[index] = "N/A";
  231.         //         }
  232.         //     });
  233.  
  234.         //     return sums;
  235.         // },
  236.         // 获取字段值
  237.         async getDict () {
  238.             const queryArr = [
  239.                 { query: { type: "sys_payment_method" }, key: "payOptions" },
  240.             ];
  241.             const promises = queryArr.map(async (item) => {
  242.                 const res = await this.getDicts(item.query.type);
  243.                 this[item.key] = res.data;
  244.             });
  245.             await Promise.all(promises);
  246.         },
  247.         getSummaries (param) {
  248.             const { columns, data } = param;
  249.             const sums = [];
  250.             columns.forEach((column, index) => {
  251.                 if (index === 0) { // 需要显示'总价'的列 坐标 :0 
  252.                     sums[index] = '总价';
  253.                     return;
  254.                 }
  255.                 const indexes = [2, 3, 4, 5, 6, 7, 8, 9, 10]; // 需要显示总和的列的索引
  256.                 if (indexes.includes(index)) {
  257.                     const values = data.map(item => Number(item[column.property]));
  258.                     if (!values.some(isNaN)) {
  259.                         const sum = values.reduce((prev, curr) => prev + curr, 0);
  260.                         sums[index] = `${sum}元`;
  261.                     } else {
  262.                         sums[index] = 'N/A';
  263.                     }
  264.                 }
  265.             });
  266.  
  267.             return sums;
  268.         },
  269.  
  270.         // 表单重置
  271.         reset () {
  272.             this.form = {
  273.                 supplier: undefined,
  274.                 groupDetails: undefined,
  275.             };
  276.             this.resetForm("form");
  277.         },
  278.         /** 搜索按钮操作 */
  279.         handleQuery () {
  280.             this.queryParams.pageIndex = 1;
  281.             this.$nextTick(() => {
  282.                 this.headerItem =
  283.                     moment(this.queryParams.at).format("YYYY年MM月") + "供应商进销报表";
  284.             });
  285.             this.getList();
  286.         },
  287.         handleExport () {
  288.             // const that = this
  289.             this.$confirm("是否确认导出供应商进销报表?", "警告", {
  290.                 confirmButtonText: "确定",
  291.                 cancelButtonText: "取消",
  292.                 type: "warning",
  293.             })
  294.                 .then(() => {
  295.                     this.exportLoading = true;
  296.                     const tds = document.querySelectorAll('.el-table__footer td')
  297.                     tds[0].setAttribute('rowspan', '1')
  298.                     tds[1].setAttribute('rowspan', '1')
  299.                     OMS.downLoadXlsx({
  300.                         dom: "kuTable",
  301.                         name: `${this.headerItem}`,
  302.                         ColumnWdth: [
  303.                             12, 20, 20, 30, 20, 20, 30, 20, 20, 12, 20,
  304.                         ], // 每一列的宽度，需要直接指定，接受数字
  305.                         rowName: "L,M", // 哪行不要的
  306.                     });
  307.                     this.exportLoading = false;
  308.                     this.showTable = false;
  309.                     this.$nextTick(() => {
  310.                         // this.getList();
  311.                         this.showTable = true;
  312.                     });
  313.                 })
  314.                 .catch(function () { });
  315.         },
  316.     },
  317. };
  318. </script>
  319.  
  320. <style lang="scss" scoped>
  321. ::v-deep .comClass .el-form-item__content {
  322.     margin-left: 0px !important;
  323. }
  324.  
  325. ::v-deep .el-input__inner {
  326.     height: 32px !important;
  327. }
  328. </style>

4.效果图

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小惠珠哦/article/detail/928226
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号