• vue：前端json数据 导出excel（Export2Excel）

    一、背景：

    导出的功能，通常后端处理好，前端直接通过地址进行下载即可，能不能是前端直接自己导出json文件，形成excel呢，当然可以。

    二、实现步骤：

    2.1、安装三个插件，分别是：file-saver、 xlsx、script-loader

    npm install -S file-saver xlsx

    npm install -D script-loader

    2.2、utils目录下创建文件

    2.3、Export2Excel.js

    1. /* eslint-disable */
    2. require('script-loader!file-saver');
    3. // require('script-loader!src/vendor/Blob');
    4. require('./Blob');
    5. require('script-loader!xlsx/dist/xlsx.core.min');
    6. import XLSX from 'xlsx'
    7.  
    8. function generateArray(table) {
    9.   var out = [];
    10.   var rows = table.querySelectorAll('tr');
    11.   var ranges = [];
    12.   for (var R = 0; R < rows.length; ++R) {
    13.     var outRow = [];
    14.     var row = rows[R];
    15.     var columns = row.querySelectorAll('td');
    16.     for (var C = 0; C < columns.length; ++C) {
    17.       var cell = columns[C];
    18.       var colspan = cell.getAttribute('colspan');
    19.       var rowspan = cell.getAttribute('rowspan');
    20.       var cellValue = cell.innerText;
    21.       if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
    22.  
    23.       //Skip ranges
    24.       ranges.forEach(function (range) {
    25.         if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
    26.           for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
    27.         }
    28.       });
    29.  
    30.       //Handle Row Span
    31.       if (rowspan || colspan) {
    32.         rowspan = rowspan || 1;
    33.         colspan = colspan || 1;
    34.         ranges.push({s: {r: R, c: outRow.length}, e: {r: R + rowspan - 1, c: outRow.length + colspan - 1}});
    35.       }
    36.       ;
    37.  
    38.       //Handle Value
    39.       outRow.push(cellValue !== "" ? cellValue : null);
    40.  
    41.       //Handle Colspan
    42.       if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    43.     }
    44.     out.push(outRow);
    45.   }
    46.   return [out, ranges];
    47. };
    48.  
    49. function datenum(v, date1904) {
    50.   if (date1904) v += 1462;
    51.   var epoch = Date.parse(v);
    52.   return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    53. }
    54.  
    55. function sheet_from_array_of_arrays(data, opts) {
    56.   var ws = {};
    57.   var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
    58.   for (var R = 0; R != data.length; ++R) {
    59.     for (var C = 0; C != data[R].length; ++C) {
    60.       if (range.s.r > R) range.s.r = R;
    61.       if (range.s.c > C) range.s.c = C;
    62.       if (range.e.r < R) range.e.r = R;
    63.       if (range.e.c < C) range.e.c = C;
    64.       var cell = {v: data[R][C]};
    65.       if (cell.v == null) continue;
    66.       var cell_ref = XLSX.utils.encode_cell({c: C, r: R});
    67.  
    68.       if (typeof cell.v === 'number') cell.t = 'n';
    69.       else if (typeof cell.v === 'boolean') cell.t = 'b';
    70.       else if (cell.v instanceof Date) {
    71.         cell.t = 'n';
    72.         cell.z = XLSX.SSF._table[14];
    73.         cell.v = datenum(cell.v);
    74.       }
    75.       else cell.t = 's';
    76.  
    77.       ws[cell_ref] = cell;
    78.     }
    79.   }
    80.   if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    81.   return ws;
    82. }
    83.  
    84. function Workbook() {
    85.   if (!(this instanceof Workbook)) return new Workbook();
    86.   this.SheetNames = [];
    87.   this.Sheets = {};
    88. }
    89.  
    90. function s2ab(s) {
    91.   var buf = new ArrayBuffer(s.length);
    92.   var view = new Uint8Array(buf);
    93.   for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    94.   return buf;
    95. }
    96.  
    97. export function export_table_to_excel(id) {
    98.   var theTable = document.getElementById(id);
    99.   console.log('a')
    100.   var oo = generateArray(theTable);
    101.   var ranges = oo[1];
    102.  
    103.   /* original data */
    104.   var data = oo[0];
    105.   var ws_name = "SheetJS";
    106.   console.log(data);
    107.  
    108.   var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
    109.  
    110.   /* add ranges to worksheet */
    111.   // ws['!cols'] = ['apple', 'banan'];
    112.   ws['!merges'] = ranges;
    113.  
    114.   /* add worksheet to workbook */
    115.   wb.SheetNames.push(ws_name);
    116.   wb.Sheets[ws_name] = ws;
    117.  
    118.   var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
    119.  
    120.   saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
    121. }
    122.  
    123. function formatJson(jsonData) {
    124.   console.log(jsonData)
    125. }
    126.  
    127. export function export_json_to_excel(th, jsonData, defaultTitle) {
    128.  
    129.   /* original data */
    130.  
    131.   var data = jsonData;
    132.   data.unshift(th);
    133.   var ws_name = "SheetJS";
    134.  
    135.   var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
    136.  
    137.  
    138.   /* add worksheet to workbook */
    139.   wb.SheetNames.push(ws_name);
    140.   wb.Sheets[ws_name] = ws;
    141.   /*设置worksheet每列的最大宽度*/
    142.   const colWidth = data.map(row =>
    143.     row.map(val => {
    144.       /*先判断是否为null/undefined*/
    145.       if (val == null) {
    146.         return { wch: 10 };
    147.       } else if (val.toString().charCodeAt(0) > 255) {
    148.         /*再判断是否为中文*/
    149.         return { wch: val.toString().length*2 + 2 };
    150.       } else {
    151.         return { wch: val.toString().length };
    152.       }
    153.     })
    154.   );
    155.   /*以第一行为初始值*/ let result = colWidth[0];
    156.   for (let i = 1; i < colWidth.length; i++) {
    157.     for (let j = 0; j < colWidth[i].length; j++) {
    158.       if (result[j]["wch"] < colWidth[i][j]["wch"]) {
    159.         result[j]["wch"] = colWidth[i][j]["wch"];
    160.       }
    161.     }
    162.   }
    163.   ws["!cols"] = result;
    164.   var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
    165.   var title = defaultTitle || '列表'
    166.   saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
    167. }

    2.3、Blob.js

    1. /* eslint-disable */
    2. /* Blob.js*/
    3.  
    4. /*global self, unescape */
    5. /*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
    6.   plusplus: true */
    7.  
    8. /*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
    9.  
    10. (function (view) {
    11.   "use strict";
    12.  
    13.   view.URL = view.URL || view.webkitURL;
    14.  
    15.   if (view.Blob && view.URL) {
    16.     try {
    17.       new Blob;
    18.       return;
    19.     } catch (e) {
    20.     }
    21.   }
    22.  
    23.   // Internally we use a BlobBuilder implementation to base Blob off of
    24.   // in order to support older browsers that only have BlobBuilder
    25.   var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function (view) {
    26.     var
    27.       get_class = function (object) {
    28.         return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
    29.       }
    30.       , FakeBlobBuilder = function BlobBuilder() {
    31.         this.data = [];
    32.       }
    33.       , FakeBlob = function Blob(data, type, encoding) {
    34.         this.data = data;
    35.         this.size = data.length;
    36.         this.type = type;
    37.         this.encoding = encoding;
    38.       }
    39.       , FBB_proto = FakeBlobBuilder.prototype
    40.       , FB_proto = FakeBlob.prototype
    41.       , FileReaderSync = view.FileReaderSync
    42.       , FileException = function (type) {
    43.         this.code = this[this.name = type];
    44.       }
    45.       , file_ex_codes = (
    46.         "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
    47.         + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
    48.       ).split(" ")
    49.       , file_ex_code = file_ex_codes.length
    50.       , real_URL = view.URL || view.webkitURL || view
    51.       , real_create_object_URL = real_URL.createObjectURL
    52.       , real_revoke_object_URL = real_URL.revokeObjectURL
    53.       , URL = real_URL
    54.       , btoa = view.btoa
    55.       , atob = view.atob
    56.  
    57.       , ArrayBuffer = view.ArrayBuffer
    58.       , Uint8Array = view.Uint8Array
    59.  
    60.       , origin = /^[\w-]+:\/*\[?[\w\.:-]+\]?(?::[0-9]+)?/
    61.     ;
    62.     FakeBlob.fake = FB_proto.fake = true;
    63.     while (file_ex_code--) {
    64.       FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
    65.     }
    66.     // Polyfill URL
    67.     if (!real_URL.createObjectURL) {
    68.       URL = view.URL = function (uri) {
    69.         var
    70.           uri_info = document.createElementNS("http://www.w3.org/1999/xhtml", "a")
    71.           , uri_origin
    72.         ;
    73.         uri_info.href = uri;
    74.         if (!("origin" in uri_info)) {
    75.           if (uri_info.protocol.toLowerCase() === "data:") {
    76.             uri_info.origin = null;
    77.           } else {
    78.             uri_origin = uri.match(origin);
    79.             uri_info.origin = uri_origin && uri_origin[1];
    80.           }
    81.         }
    82.         return uri_info;
    83.       };
    84.     }
    85.     URL.createObjectURL = function (blob) {
    86.       var
    87.         type = blob.type
    88.         , data_URI_header
    89.       ;
    90.       if (type === null) {
    91.         type = "application/octet-stream";
    92.       }
    93.       if (blob instanceof FakeBlob) {
    94.         data_URI_header = "data:" + type;
    95.         if (blob.encoding === "base64") {
    96.           return data_URI_header + ";base64," + blob.data;
    97.         } else if (blob.encoding === "URI") {
    98.           return data_URI_header + "," + decodeURIComponent(blob.data);
    99.         }
    100.         if (btoa) {
    101.           return data_URI_header + ";base64," + btoa(blob.data);
    102.         } else {
    103.           return data_URI_header + "," + encodeURIComponent(blob.data);
    104.         }
    105.       } else if (real_create_object_URL) {
    106.         return real_create_object_URL.call(real_URL, blob);
    107.       }
    108.     };
    109.     URL.revokeObjectURL = function (object_URL) {
    110.       if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
    111.         real_revoke_object_URL.call(real_URL, object_URL);
    112.       }
    113.     };
    114.     FBB_proto.append = function (data/*, endings*/) {
    115.       var bb = this.data;
    116.       // decode data to a binary string
    117.       if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
    118.         var
    119.           str = ""
    120.           , buf = new Uint8Array(data)
    121.           , i = 0
    122.           , buf_len = buf.length
    123.         ;
    124.         for (; i < buf_len; i++) {
    125.           str += String.fromCharCode(buf[i]);
    126.         }
    127.         bb.push(str);
    128.       } else if (get_class(data) === "Blob" || get_class(data) === "File") {
    129.         if (FileReaderSync) {
    130.           var fr = new FileReaderSync;
    131.           bb.push(fr.readAsBinaryString(data));
    132.         } else {
    133.           // async FileReader won't work as BlobBuilder is sync
    134.           throw new FileException("NOT_READABLE_ERR");
    135.         }
    136.       } else if (data instanceof FakeBlob) {
    137.         if (data.encoding === "base64" && atob) {
    138.           bb.push(atob(data.data));
    139.         } else if (data.encoding === "URI") {
    140.           bb.push(decodeURIComponent(data.data));
    141.         } else if (data.encoding === "raw") {
    142.           bb.push(data.data);
    143.         }
    144.       } else {
    145.         if (typeof data !== "string") {
    146.           data += ""; // convert unsupported types to strings
    147.         }
    148.         // decode UTF-16 to binary string
    149.         bb.push(unescape(encodeURIComponent(data)));
    150.       }
    151.     };
    152.     FBB_proto.getBlob = function (type) {
    153.       if (!arguments.length) {
    154.         type = null;
    155.       }
    156.       return new FakeBlob(this.data.join(""), type, "raw");
    157.     };
    158.     FBB_proto.toString = function () {
    159.       return "[object BlobBuilder]";
    160.     };
    161.     FB_proto.slice = function (start, end, type) {
    162.       var args = arguments.length;
    163.       if (args < 3) {
    164.         type = null;
    165.       }
    166.       return new FakeBlob(
    167.         this.data.slice(start, args > 1 ? end : this.data.length)
    168.         , type
    169.         , this.encoding
    170.       );
    171.     };
    172.     FB_proto.toString = function () {
    173.       return "[object Blob]";
    174.     };
    175.     FB_proto.close = function () {
    176.       this.size = 0;
    177.       delete this.data;
    178.     };
    179.     return FakeBlobBuilder;
    180.   }(view));
    181.  
    182.   view.Blob = function (blobParts, options) {
    183.     var type = options ? (options.type || "") : "";
    184.     var builder = new BlobBuilder();
    185.     if (blobParts) {
    186.       for (var i = 0, len = blobParts.length; i < len; i++) {
    187.         if (Uint8Array && blobParts[i] instanceof Uint8Array) {
    188.           builder.append(blobParts[i].buffer);
    189.         }
    190.         else {
    191.           builder.append(blobParts[i]);
    192.         }
    193.       }
    194.     }
    195.     var blob = builder.getBlob(type);
    196.     if (!blob.slice && blob.webkitSlice) {
    197.       blob.slice = blob.webkitSlice;
    198.     }
    199.     return blob;
    200.   };
    201.  
    202.   var getPrototypeOf = Object.getPrototypeOf || function (object) {
    203.     return object.__proto__;
    204.   };
    205.   view.Blob.prototype = getPrototypeOf(new view.Blob());
    206. }(
    207.   typeof self !== "undefined" && self
    208.   || typeof window !== "undefined" && window
    209.   || this
    210. ));

    2.4、业务代码测试：

    1.     // 引入Export2Excel
    2.     const {export_json_to_excel} = require('../../utils/Export2Excel');
    3.     // json数据-导出Excel
    4.     exportToExcel() {
    5.       require.ensure([], () => {
    6.         const tHeader = ['字段1', '字段2', '字段3', '字段4', '字段5'];
    7.         const filterVal = ['name1', 'name2', 'name3', 'name4', 'name5'];
    8.         this.listQuery.limit = 9999
    9.         // 调用接口，获取数据
    10.         getJsonList(this.listQuery).then(response => {
    11.           if(response.code == 200){
    12.             const exceldata = this.formatJson(filterVal, response.data.records);
    13.             export_json_to_excel(tHeader, exceldata, '西药列表');  //对应下载文件的名字
    14.           }
    15.         });
    16.         
    17.       })
    18.     },

    经过测试下载成功，打开后内容显示正确，可放心使用。

    三、欢迎交流指正，关注我，一起学习。

    四、参考链接：

    vue项目中最近使用的插件整理---（一）_沈尛的博客-CSDN博客

    2分钟快速解决vue表格导出问题_李夏洛的博客-CSDN博客

  • 相关阅读:
    基于SE-YOLOV4的变电站断路器分合状态识别算法
    【WebSocket&IndexedDB】node+WebSocket&IndexedDB开发简易聊天室
    c++和java中关于类的第几部分详解
    Spring源码分析 循环依赖 三级缓存
    python毕业设计项目源码选题（9）电子书阅读系统毕业设计毕设作品开题报告开题答辩PPT
    docker 网络（单机环境）
    深度学习(18):nerf、nerf-pytorch代码运行与学习
    数据结构与算法 | 数组(Array)
    1024程序员节主题征文 | 程序员节节日祝福语大全
    十二. 实战——项目部署（springboot 和 微服务）
  • 原文地址：https://blog.csdn.net/snowball_li/article/details/126045045