前端表格导出

  1. npm install -S file-saver(生产依赖,则为-s)
     
    npm install -S xlsx
     
    npm install -D script-loader (开发依赖,则为-d)
  2. 安装完成后新建文件夹(static下)exportExcel在文件下载写入两个文件Blob.js和Export2Excel.js
  3. 以下是Blob.js,可以直接复制走
  4. /* Blob.js
     * A Blob, File, FileReader & URL implementation.
     * 2018-08-09
     *
     * By Eli Grey, http://eligrey.com
     * By Jimmy Wärting, https://github.com/jimmywarting
     * License: MIT
     *   See https://github.com/eligrey/Blob.js/blob/master/LICENSE.md
     */
     
    ;(function(){
     
        var global = typeof window === 'object'
          ? window : typeof self === 'object'
            ? self : this
       
        var BlobBuilder = global.BlobBuilder
          || global.WebKitBlobBuilder
          || global.MSBlobBuilder
          || global.MozBlobBuilder;
       
        global.URL = global.URL || global.webkitURL || function(href, a) {
          a = document.createElement('a')
          a.href = href
          return a
        }
       
        var origBlob = global.Blob
        var createObjectURL = URL.createObjectURL
        var revokeObjectURL = URL.revokeObjectURL
        var strTag = global.Symbol && global.Symbol.toStringTag
        var blobSupported = false
        var blobSupportsArrayBufferView = false
        var arrayBufferSupported = !!global.ArrayBuffer
        var blobBuilderSupported = BlobBuilder
          && BlobBuilder.prototype.append
          && BlobBuilder.prototype.getBlob;
       
        try {
          // Check if Blob constructor is supported
          blobSupported = new Blob(['ä']).size === 2
       
          // Check if Blob constructor supports ArrayBufferViews
          // Fails in Safari 6, so we need to map to ArrayBuffers there.
          blobSupportsArrayBufferView = new Blob([new Uint8Array([1,2])]).size === 2
        } catch(e) {}
       
        /**
         * Helper function that maps ArrayBufferViews to ArrayBuffers
         * Used by BlobBuilder constructor and old browsers that didn't
         * support it in the Blob constructor.
         */
        function mapArrayBufferViews(ary) {
          return ary.map(function(chunk) {
            if (chunk.buffer instanceof ArrayBuffer) {
              var buf = chunk.buffer;
       
              // if this is a subarray, make a copy so we only
              // include the subarray region from the underlying buffer
              if (chunk.byteLength !== buf.byteLength) {
                var copy = new Uint8Array(chunk.byteLength);
                copy.set(new Uint8Array(buf, chunk.byteOffset, chunk.byteLength));
                buf = copy.buffer;
              }
       
              return buf;
            }
       
            return chunk;
          });
        }
       
        function BlobBuilderConstructor(ary, options) {
          options = options || {};
       
          var bb = new BlobBuilder();
          mapArrayBufferViews(ary).forEach(function(part) {
            bb.append(part);
          });
       
          return options.type ? bb.getBlob(options.type) : bb.getBlob();
        };
       
        function BlobConstructor(ary, options) {
          return new origBlob(mapArrayBufferViews(ary), options || {});
        };
       
        if (global.Blob) {
          BlobBuilderConstructor.prototype = Blob.prototype;
          BlobConstructor.prototype = Blob.prototype;
        }
       
        function FakeBlobBuilder() {
          function toUTF8Array(str) {
            var utf8 = [];
            for (var i=0; i < str.length; i++) {
              var charcode = str.charCodeAt(i);
              if (charcode < 0x80) utf8.push(charcode);
              else if (charcode < 0x800) {
                utf8.push(0xc0 | (charcode >> 6),
                  0x80 | (charcode & 0x3f));
              }
              else if (charcode < 0xd800 || charcode >= 0xe000) {
                utf8.push(0xe0 | (charcode >> 12),
                  0x80 | ((charcode>>6) & 0x3f),
                  0x80 | (charcode & 0x3f));
              }
              // surrogate pair
              else {
                i++;
                // UTF-16 encodes 0x10000-0x10FFFF by
                // subtracting 0x10000 and splitting the
                // 20 bits of 0x0-0xFFFFF into two halves
                charcode = 0x10000 + (((charcode & 0x3ff)<<10)
                  | (str.charCodeAt(i) & 0x3ff));
                utf8.push(0xf0 | (charcode >>18),
                  0x80 | ((charcode>>12) & 0x3f),
                  0x80 | ((charcode>>6) & 0x3f),
                  0x80 | (charcode & 0x3f));
              }
            }
            return utf8;
          }
          function fromUtf8Array(array) {
            var out, i, len, c;
            var char2, char3;
       
            out = "";
            len = array.length;
            i = 0;
            while (i < len) {
              c = array[i++];
              switch (c >> 4)
              {
                case 0: case 1: case 2: case 3: case 4: case 5: case 6: case 7:
                // 0xxxxxxx
                out += String.fromCharCode(c);
                break;
                case 12: case 13:
                // 110x xxxx   10xx xxxx
                char2 = array[i++];
                out += String.fromCharCode(((c & 0x1F) << 6) | (char2 & 0x3F));
                break;
                case 14:
                  // 1110 xxxx  10xx xxxx  10xx xxxx
                  char2 = array[i++];
                  char3 = array[i++];
                  out += String.fromCharCode(((c & 0x0F) << 12) |
                    ((char2 & 0x3F) << 6) |
                    ((char3 & 0x3F) << 0));
                  break;
              }
            }
            return out;
          }
          function isDataView(obj) {
            return obj && DataView.prototype.isPrototypeOf(obj)
          }
          function bufferClone(buf) {
            var view = new Array(buf.byteLength)
            var array = new Uint8Array(buf)
            var i = view.length
            while(i--) {
              view[i] = array[i]
            }
            return view
          }
          function encodeByteArray(input) {
            var byteToCharMap = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
       
            var output = [];
       
            for (var i = 0; i < input.length; i += 3) {
              var byte1 = input[i];
              var haveByte2 = i + 1 < input.length;
              var byte2 = haveByte2 ? input[i + 1] : 0;
              var haveByte3 = i + 2 < input.length;
              var byte3 = haveByte3 ? input[i + 2] : 0;
       
              var outByte1 = byte1 >> 2;
              var outByte2 = ((byte1 & 0x03) << 4) | (byte2 >> 4);
              var outByte3 = ((byte2 & 0x0F) << 2) | (byte3 >> 6);
              var outByte4 = byte3 & 0x3F;
       
              if (!haveByte3) {
                outByte4 = 64;
       
                if (!haveByte2) {
                  outByte3 = 64;
                }
              }
       
              output.push(
                byteToCharMap[outByte1], byteToCharMap[outByte2],
                byteToCharMap[outByte3], byteToCharMap[outByte4])
            }
       
            return output.join('')
          }
       
          var create = Object.create || function (a) {
            function c() {}
            c.prototype = a;
            return new c
          }
       
          if (arrayBufferSupported) {
            var viewClasses = [
              '[object Int8Array]',
              '[object Uint8Array]',
              '[object Uint8ClampedArray]',
              '[object Int16Array]',
              '[object Uint16Array]',
              '[object Int32Array]',
              '[object Uint32Array]',
              '[object Float32Array]',
              '[object Float64Array]'
            ]
       
            var isArrayBufferView = ArrayBuffer.isView || function(obj) {
              return obj && viewClasses.indexOf(Object.prototype.toString.call(obj)) > -1
            }
          }
       
       
       
          /********************************************************/
          /*                   Blob constructor                   */
          /********************************************************/
          function Blob(chunks, opts) {
            chunks = chunks || []
            for (var i = 0, len = chunks.length; i < len; i++) {
              var chunk = chunks[i]
              if (chunk instanceof Blob) {
                chunks[i] = chunk._buffer
              } else if (typeof chunk === 'string') {
                chunks[i] = toUTF8Array(chunk)
              } else if (arrayBufferSupported && (ArrayBuffer.prototype.isPrototypeOf(chunk) || isArrayBufferView(chunk))) {
                chunks[i] = bufferClone(chunk)
              } else if (arrayBufferSupported && isDataView(chunk)) {
                chunks[i] = bufferClone(chunk.buffer)
              } else {
                chunks[i] = toUTF8Array(String(chunk))
              }
            }
       
            this._buffer = [].concat.apply([], chunks)
            this.size = this._buffer.length
            this.type = opts ? opts.type || '' : ''
          }
       
          Blob.prototype.slice = function(start, end, type) {
            var slice = this._buffer.slice(start || 0, end || this._buffer.length)
            return new Blob([slice], {type: type})
          }
       
          Blob.prototype.toString = function() {
            return '[object Blob]'
          }
       
       
       
          /********************************************************/
          /*                   File constructor                   */
          /********************************************************/
          function File(chunks, name, opts) {
            opts = opts || {}
            var a = Blob.call(this, chunks, opts) || this
            a.name = name
            a.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date
            a.lastModified = +a.lastModifiedDate
       
            return a
          }
       
          File.prototype = create(Blob.prototype);
          File.prototype.constructor = File;
       
          if (Object.setPrototypeOf)
            Object.setPrototypeOf(File, Blob);
          else {
            try {File.__proto__ = Blob} catch (e) {}
          }
       
          File.prototype.toString = function() {
            return '[object File]'
          }
       
       
          /********************************************************/
          /*                FileReader constructor                */
          /********************************************************/
          function FileReader() {
            if (!(this instanceof FileReader))
              throw new TypeError("Failed to construct 'FileReader': Please use the 'new' operator, this DOM object constructor cannot be called as a function.")
       
            var delegate = document.createDocumentFragment()
            this.addEventListener = delegate.addEventListener
            this.dispatchEvent = function(evt) {
              var local = this['on' + evt.type]
              if (typeof local === 'function') local(evt)
              delegate.dispatchEvent(evt)
            }
            this.removeEventListener = delegate.removeEventListener
          }
       
          function _read(fr, blob, kind) {
            if (!(blob instanceof Blob))
              throw new TypeError("Failed to execute '" + kind + "' on 'FileReader': parameter 1 is not of type 'Blob'.")
       
            fr.result = ''
       
            setTimeout(function(){
              this.readyState = FileReader.LOADING
              fr.dispatchEvent(new Event('load'))
              fr.dispatchEvent(new Event('loadend'))
            })
          }
       
          FileReader.EMPTY = 0
          FileReader.LOADING = 1
          FileReader.DONE = 2
          FileReader.prototype.error = null
          FileReader.prototype.onabort = null
          FileReader.prototype.onerror = null
          FileReader.prototype.onload = null
          FileReader.prototype.onloadend = null
          FileReader.prototype.onloadstart = null
          FileReader.prototype.onprogress = null
       
          FileReader.prototype.readAsDataURL = function(blob) {
            _read(this, blob, 'readAsDataURL')
            this.result = 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
          }
       
          FileReader.prototype.readAsText = function(blob) {
            _read(this, blob, 'readAsText')
            this.result = fromUtf8Array(blob._buffer)
          }
       
          FileReader.prototype.readAsArrayBuffer = function(blob) {
            _read(this, blob, 'readAsText')
            this.result = blob._buffer.slice()
          }
       
          FileReader.prototype.abort = function() {}
       
       
          /********************************************************/
          /*                         URL                          */
          /********************************************************/
          URL.createObjectURL = function(blob) {
            return blob instanceof Blob
              ? 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
              : createObjectURL.call(URL, blob)
          }
       
          URL.revokeObjectURL = function(url) {
            revokeObjectURL && revokeObjectURL.call(URL, url)
          }
       
          /********************************************************/
          /*                         XHR                          */
          /********************************************************/
          var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
          if (_send) {
            XMLHttpRequest.prototype.send = function(data) {
              if (data instanceof Blob) {
                this.setRequestHeader('Content-Type', data.type)
                _send.call(this, fromUtf8Array(data._buffer))
              } else {
                _send.call(this, data)
              }
            }
          }
       
          global.FileReader = FileReader
          global.File = File
          global.Blob = Blob
        }
       
        if (strTag) {
          File.prototype[strTag] = 'File'
          Blob.prototype[strTag] = 'Blob'
          FileReader.prototype[strTag] = 'FileReader'
        }
       
        function fixFileAndXHR() {
          var isIE = !!global.ActiveXObject || (
            '-ms-scroll-limit' in document.documentElement.style &&
            '-ms-ime-align' in document.documentElement.style
          )
       
          // Monkey patched
          // IE don't set Content-Type header on XHR whose body is a typed Blob
          // https://developer.microsoft.com/en-us/microsoft-edge/platform/issues/6047383
          var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
          if (isIE && _send) {
            XMLHttpRequest.prototype.send = function(data) {
              if (data instanceof Blob) {
                this.setRequestHeader('Content-Type', data.type)
                _send.call(this, data)
              } else {
                _send.call(this, data)
              }
            }
          }
       
          try {
            new File([], '')
          } catch(e) {
            try {
              var klass = new Function('class File extends Blob {' +
                'constructor(chunks, name, opts) {' +
                'opts = opts || {};' +
                'super(chunks, opts || {});' +
                'this.name = name;' +
                'this.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date;' +
                'this.lastModified = +this.lastModifiedDate;' +
                '}};' +
                'return new File([], ""), File'
              )()
              global.File = klass
            } catch(e) {
              var klass = function(b, d, c) {
                var blob = new Blob(b, c)
                var t = c && void 0 !== c.lastModified ? new Date(c.lastModified) : new Date
       
                blob.name = d
                blob.lastModifiedDate = t
                blob.lastModified = +t
                blob.toString = function() {
                  return '[object File]'
                }
       
                if (strTag)
                  blob[strTag] = 'File'
       
                return blob
              }
              global.File = klass
            }
          }
        }
       
        if (blobSupported) {
          fixFileAndXHR()
          global.Blob = blobSupportsArrayBufferView ? global.Blob : BlobConstructor
        } else if (blobBuilderSupported) {
          fixFileAndXHR()
          global.Blob = BlobBuilderConstructor;
        } else {
          FakeBlobBuilder()
        }
       
      })();
       
       

    这下面是Export2Excel.js,加了代码,让导出后表格根据内容自适应宽度

  5. /* eslint-disable */
    require('./Blob');
    require('script-loader!file-saver');
    
    require('script-loader!xlsx/dist/xlsx.core.min');
    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);
        console.log('a')
        var oo = generateArray(theTable);
        var ranges = oo[1];
     
        /* original data */
        var data = oo[0];
        var ws_name = "SheetJS";
        console.log(data);
     
        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")
    }
     
    function formatJson(jsonData) {
        console.log(jsonData)
    }
    export function export_json_to_excel(th, jsonData, defaultTitle) {
     
        /* original data */
     
        var data = jsonData;
        data.unshift(th);
        var ws_name = "SheetJS";
     
        var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
     
     
        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
    
        /*设置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 + 5 };
          } else {
            return { wch: val.toString().length + 5 };
          }
        })
      );
      /*以第一行为初始值*/ 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;
     
        var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
        var title = defaultTitle || '列表'
        saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
    }
     
    
     
    <template>
      <div class="hello">
        <div>
          <el-button
            type="primary"
            size="mini"
            style="margin: 30px auto"
            @click="handleDownloadData"
            >导出</el-button
          >
          <el-table
            :data="tableData"
            stripe
            style="width: 600px; margin: 30px auto"
          >
            <el-table-column prop="date" label="日期" width="180">
            </el-table-column>
            <el-table-column prop="name" label="姓名" width="180">
            </el-table-column>
            <el-table-column prop="address" label="地址"> </el-table-column>
          </el-table>
        </div>
      </div>
    </template>
    
    methods: {
        // 导出的方法
        handleDownloadData() {
          // 导出表格的表头设置
          let allColumns = this.tableTitleData;
          var columnNames = [];
          var columnValues = [];
          for (var i = 0; i < allColumns.length; i++) {
            columnNames[i] = allColumns[i].label;
            columnValues[i] = allColumns[i].prop;
          }
          require.ensure([], () => {
            // const { export_json_to_excel } = require("../vendor/Export2Excel");
             const { export_json_to_excel } = require("../../static/exportExcel/Export2Excel");
            const tHeader = columnNames;
            const filterVal = columnValues;
    
            const list = this.tableData;
            const data = this.formatJson(filterVal, list);
            export_json_to_excel(tHeader, data, "导出excel列表demo");
          });
        },
        formatJson(filterVal, jsonData) {
          return jsonData.map((v) => filterVal.map((j) => v[j]));
        },
      },

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值