vue+iveiw表格选中数据导出excel表格

vue+iveiw表格选中项导出excel表格

​ 因为业务的需求，表格类的数据需要我们前端方面进行导出数据转化成excel,前期Baidu碰壁,后来询问公司的大佬，结合他以前做过的东西，终于皇天不负有心人，导出excel Over！！！

一 、纯前端导出excel

1. 表格导出需要的主要是两个依赖:(xlsx 和 file-saver)

   npm install --save xlsx file-saver
   

2. 新建一个文件夹，或者在自己项目的工具文件夹里创建ExportExcel.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}`);
   }
   
   

3. 第三步，直接在需要进行数据导出excel表格的页面进行操作

   // 导出
   exportForm() {
       import('@/exportExcel/Export2Excel').then(excel => {
           // 获取当前导出时间戳
           const NowTimestamp = new Date().getTime()
           // excel导出文件名   xxxx-xx-xx - 表格名字.xlsx
           const filename = this.$moment(NowTimestamp).format('yyyy-MM-DD') + '-' + 'xxxx'
           // this.$moment是处理时间的插件, 没有的话， npm install moment
           // 在main.js 里面复制粘贴下面两句话
           // import moment from 'moment'//导入文件
           // Vue.prototype.$moment = moment;//赋值使用
   
           const tHeader = ['商户名称', '商户ID', '活动名称', '...']             // 表头,是指你的表格表头
           const filterVal = ['storeName', 'storeId', 'freeCouponName', '...']// key,是指你渲染表格数据的字段
   
           const list = this.tableCheckedList  // this.tableCheckedList 选中行的数据列表
           const data = this.formatJson(filterVal, list)
           excel.export_json_to_excel({
               header: tHeader,
               data,
               filename,
               autoWidth: true,
               bookType: 'xlsx'
           })
       })
   },
   formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j] ))
   }
   

二、请求后台接口通过文件流下载导出excel

1. 新建一个文件夹，或者在自己项目的工具文件夹里创建ExportExcel.js

   import axios from 'axios';
   import moment from 'moment'//导入文件
   
   // 导出Excel公用方法
   export function exportMethod(data,obj) {
       axios({
           method:data.method,
           url: data.url,
           data: obj,
           withCredentials:true,
           responseType:'blob'
       }).then((res) => {
           const link = document.createElement('a')
           let blob = new Blob([res], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"})
           link.style.display = 'none'
           link.href = URL.createObjectURL(blob)
           link.target = '_blank'
           console.log(res)
           link.download = moment().format('yyyy-MM-DD') +  data.fileName           // 下载的文件名
           document.body.appendChild(link)
           link.click()
           document.body.removeChild(link)
           
       })
   }
   

   2. 然后, 直接在需要进行数据导出excel表格的页面进行操作

   import { exportMethod } from '@/xxxx/exportExcel';
   
   exportBtn() {
       let obj = {}
       this.transactionListData.forEach(element => {
           obj = element
       });
       
       let data = {
           method: 'post',
           url: this.$api.xxxxx,    // 请求接口地址
           fileName: '交易记录汇总',  // 导出表格文件名
       }
       exportMethod(data,obj)
   }
   

每天记录一点，成长一点！！！

wuli giao giao giao！！！