VUE导出excel表格

╰半橙微兮° 2022-10-08 02:30 280阅读 0赞

本来想直接白嫖文档demo和大佬博客的demo，发现白嫖失败，于是写下自己踩坑过程

### 目录 ###

*   *  安装命令
     *  在src下新建立一个verdor文件夹
     *  使用
     *  (进阶)导出已选择项

## 安装命令 ##

npm install xlsx file-saver -S
    npm install script-loader -S -D

## 在src下新建立一个verdor文件夹 ##

在verdor下新建Export2Excel.js文件  
Export2Excel.js内容如下

/* eslint-disable */
    import {  saveAs } from 'file-saver'
    import XLSX from 'xlsx'
    
    function generateArray(table) { 
      var out = [];
      var rows = table.querySelectorAll('tr');
      var ranges = [];
      for (var R = 0; R < rows.length; ++R) { 
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 0; C < columns.length; ++C) { 
          var cell = columns[C];
          var colspan = cell.getAttribute('colspan');
          var rowspan = cell.getAttribute('rowspan');
          var cellValue = cell.innerText;
          if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
    
          //Skip ranges
          ranges.forEach(function (range) { 
            if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) { 
              for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
            }
          });
    
          //Handle Row Span
          if (rowspan || colspan) { 
            rowspan = rowspan || 1;
            colspan = colspan || 1;
            ranges.push({ 
              s: { 
                r: R,
                c: outRow.length
              },
              e: { 
                r: R + rowspan - 1,
                c: outRow.length + colspan - 1
              }
            });
          };
    
          //Handle Value
          outRow.push(cellValue !== "" ? cellValue : null);
    
          //Handle Colspan
          if (colspan)
            for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
      }
      return [out, ranges];
    };
    
    function datenum(v, date1904) { 
      if (date1904) v += 1462;
      var epoch = Date.parse(v);
      return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    }
    
    function sheet_from_array_of_arrays(data, opts) { 
      var ws = { };
      var range = { 
        s: { 
          c: 10000000,
          r: 10000000
        },
        e: { 
          c: 0,
          r: 0
        }
      };
      for (var R = 0; R != data.length; ++R) { 
        for (var C = 0; C != data[R].length; ++C) { 
          if (range.s.r > R) range.s.r = R;
          if (range.s.c > C) range.s.c = C;
          if (range.e.r < R) range.e.r = R;
          if (range.e.c < C) range.e.c = C;
          var cell = { 
            v: data[R][C]
          };
          if (cell.v == null) continue;
          var cell_ref = XLSX.utils.encode_cell({ 
            c: C,
            r: R
          });
    
          if (typeof cell.v === 'number') cell.t = 'n';
          else if (typeof cell.v === 'boolean') cell.t = 'b';
          else if (cell.v instanceof Date) { 
            cell.t = 'n';
            cell.z = XLSX.SSF._table[14];
            cell.v = datenum(cell.v);
          } else cell.t = 's';
    
          ws[cell_ref] = cell;
        }
      }
      if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
      return ws;
    }
    
    function Workbook() { 
      if (!(this instanceof Workbook)) return new Workbook();
      this.SheetNames = [];
      this.Sheets = { };
    }
    
    function s2ab(s) { 
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    }
    
    export function export_table_to_excel(id) { 
      var theTable = document.getElementById(id);
      var oo = generateArray(theTable);
      var ranges = oo[1];
    
      /* original data */
      var data = oo[0];
      var ws_name = "SheetJS";
    
      var wb = new Workbook(),
          ws = sheet_from_array_of_arrays(data);
    
      /* add ranges to worksheet */
      // ws['!cols'] = ['apple', 'banan'];
      ws['!merges'] = ranges;
    
      /* add worksheet to workbook */
      wb.SheetNames.push(ws_name);
      wb.Sheets[ws_name] = ws;
    
      var wbout = XLSX.write(wb, { 
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary'
      });
    
      saveAs(new Blob([s2ab(wbout)], { 
        type: "application/octet-stream"
      }), "test.xlsx")
    }
    
    export function export_json_to_excel({ 
                                           multiHeader = [],
                                           header,
                                           data,
                                           filename,
                                           merges = [],
                                           autoWidth = true,
                                           bookType = 'xlsx'
                                         } = { }) { 
      /* original data */
      filename = filename || 'excel-list'
      data = [...data]
      data.unshift(header);
    
      for (let i = multiHeader.length - 1; i > -1; i--) { 
        data.unshift(multiHeader[i])
      }
    
      var ws_name = "SheetJS";
      var wb = new Workbook(),
          ws = sheet_from_array_of_arrays(data);
    
      if (merges.length > 0) { 
        if (!ws['!merges']) ws['!merges'] = [];
        merges.forEach(item => { 
          ws['!merges'].push(XLSX.utils.decode_range(item))
        })
      }
    
      if (autoWidth) { 
        /*设置worksheet每列的最大宽度*/
        const colWidth = data.map(row => row.map(val => { 
          /*先判断是否为null/undefined*/
          if (val == null) { 
            return { 
              'wch': 10
            };
          }
          /*再判断是否为中文*/
          else if (val.toString().charCodeAt(0) > 255) { 
            return { 
              'wch': val.toString().length * 2
            };
          } else { 
            return { 
              'wch': val.toString().length
            };
          }
        }))
        /*以第一行为初始值*/
        let result = colWidth[0];
        for (let i = 1; i < colWidth.length; i++) { 
          for (let j = 0; j < colWidth[i].length; j++) { 
            if (result[j]['wch'] < colWidth[i][j]['wch']) { 
              result[j]['wch'] = colWidth[i][j]['wch'];
            }
          }
        }
        ws['!cols'] = result;
      }
    
      /* add worksheet to workbook */
      wb.SheetNames.push(ws_name);
      wb.Sheets[ws_name] = ws;
    
      var wbout = XLSX.write(wb, { 
        bookType: bookType,
        bookSST: false,
        type: 'binary'
      });
      saveAs(new Blob([s2ab(wbout)], { 
        type: "application/octet-stream"
      }), `${ filename}.${ bookType}`);
    }

## 使用 ##

直接在你的methods下面粘贴这两个方法handleDownload 、 formatJson。

handleDownload() { 
        //downloadLoading是elementui自带的效果，在data里面初始化为downloadLoading: false
        //如果想用可以看下面的button代码介绍
          this.downloadLoading = true
          import('@/vendor/Export2Excel').then(excel => { 
            const tHeader = ['序号', '姓名', '学号', '性别', '年级','班级','职务']
            const filterVal = ['id','name', 'studentid', 'gender', 'grade','classname','post']
            const list = this.list
            //这个list是我的data里面的数据，我后端返回的是一个ArrayList对象
            const data = this.formatJson(filterVal, list)
            excel.export_json_to_excel({ 
              header: tHeader,
              data,
              filename: 'student_table',
              autoWidth: this.autoWidth,
              bookType: this.bookType
            })
            this.downloadLoading = false
          })
        },
        formatJson(filterVal, jsonData) { 
          return jsonData.map(v => filterVal.map(j => { 
            if (j === 'timestamp') { 
              return parseTime(v[j])
            } else { 
              return v[j]
            }
          }))
        }

介绍一下handleDownload 里面几个参数（重点）：  
**tHeader** 就是你表格的表头  
比如我的是

const tHeader = ['序号', '姓名', '学号', '性别', '年级','班级','职务']

效果就是如下图所示

![在这里插入图片描述][20210619225259359.png]

**filterVal** 对应的是你的list字面的key  
这个list是我的data里面的数据，我后端返回的是一个ArrayList对象，list

其他的就直接看官方的表格介绍吧  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70]  
最后偷懒可以直接用现成的button，button代码都不用写了（downloadLoading是elementui自带的效果，在data里面初始化为downloadLoading: false）

<el-button :loading="downloadLoading" style="margin:0 0 20px 20px;" type="primary" icon="el-icon-document" @click="handleDownload">
                导出表格
              </el-button>

到这里就结束啦，下面还有一种导出已选择项的补充

## (进阶)导出已选择项 ##

效果图，在最左侧多了一列选择框，只导出已经勾选的内容  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70 1]

**新增代码**

在el-table的定义下面新增 ref=“multipleTable” 与 @selection-change=“handleSelectionChange”

<el-table
       ref="multipleTable"
       v-loading="listLoading"
       :data="list"
       fit
       border
       @selection-change="handleSelectionChange"
            >
    //新增加一列勾选框
    <el-table-column type="selection" align="center" />

**新增data**

data() { 
        return { 
          multipleSelection: [],
          downloadLoading: false,
          list: [],
        }
      },

![在这里插入图片描述][20210620104940342.png]

**新增方法**

handleSelectionChange(val) { 
          this.multipleSelection = val
        },

**handleDownload改动**

改动的地方如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70 2]  
代码：

handleDownload() { 
          if (this.multipleSelection.length) { 
            this.downloadLoading = true
            import('@/vendor/Export2Excel').then(excel => { 
              const tHeader = ['序号', '姓名', '学号', '性别', '年级', '班级', '职务']
              const filterVal = ['id', 'name', 'studentid', 'gender', 'grade', 'classname', 'post']
              const list = this.multipleSelection
              const data = this.formatJson(filterVal, list)
              excel.export_json_to_excel({ 
                header: tHeader,
                data,
                filename: 'student_table',
                autoWidth: this.autoWidth,
                bookType: this.bookType
              })
              this.downloadLoading = false
            })
          }else { 
            this.$message({ 
              message: '请先选择要导出的内容',
              type: 'warning'
            })
          }
        }

**over**

[20210619225259359.png]: /images/20221005/1e6d112c39d14650a7a346157b916532.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70]: /images/20221005/137d40498c624e3db158582e1b369416.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70 1]: /images/20221005/6f56a2425dc34b92b812f36e44958121.png
[20210620104940342.png]: /images/20221005/1fd79ec229c94a7cb97b759d9fb48d2d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTkzOTgy_size_16_color_FFFFFF_t_70 2]: /images/20221005/19e3962dc8f44947b2bba7550e8d51fa.png