软件定制开发供应商vue：前端json数据 导出excel（Export2Excel）

一、背景：

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

二、实现步骤：

2.1、安装三个插件，分别是：、 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.     },

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

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

四、参考链接：

网站建设定制开发 软件系统开发定制 定制软件开发 软件开发定制 定制app开发 app开发定制 app开发定制公司 电商商城定制开发 定制小程序开发 定制开发小程序 客户管理系统开发定制 定制网站 定制开发 crm开发定制 开发公司 小程序开发定制 定制软件 收款定制开发 企业网站定制开发 定制化开发 android系统定制开发 定制小程序开发费用 定制设计 专注app软件定制开发 软件开发定制定制 知名网站建设定制 软件定制开发供应商 应用系统定制开发 软件系统定制开发 企业管理系统定制开发 系统定制开发