精华内容
下载资源
问答
  • vue导出excel表格及合并单元格

    千次阅读 2020-04-05 21:30:16
    这里写自定义目录标题安装导入两个文件Blob.jsExport2Excel.js定义click函数合并单元格 安装 npm install file-saver -S npm install xlsx -S npm install -D script-loader 导入两个文件 在src目录下新建一个...

    安装

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

    导入两个文件

    在src目录下新建一个vendor(可以是别的名字,但是导入时需注意,没事别改,大神请忽略)目录,目录下放新建两个文件Blob.js和Export2Excel.js

    Blob.js

    /* eslint-disable */
    /* Blob.js*/
    
    /*global self, unescape */
    /*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
      plusplus: true */
    
    /*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
    
    (function (view) {
      "use strict";
    
      view.URL = view.URL || view.webkitURL;
    
      if (view.Blob && view.URL) {
        try {
          new Blob;
          return;
        } catch (e) {
        }
      }
    
      // Internally we use a BlobBuilder implementation to base Blob off of
      // in order to support older browsers that only have BlobBuilder
      var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function (view) {
        var
          get_class = function (object) {
            return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
          }
          , FakeBlobBuilder = function BlobBuilder() {
            this.data = [];
          }
          , FakeBlob = function Blob(data, type, encoding) {
            this.data = data;
            this.size = data.length;
            this.type = type;
            this.encoding = encoding;
          }
          , FBB_proto = FakeBlobBuilder.prototype
          , FB_proto = FakeBlob.prototype
          , FileReaderSync = view.FileReaderSync
          , FileException = function (type) {
            this.code = this[this.name = type];
          }
          , file_ex_codes = (
            "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
            + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
          ).split(" ")
          , file_ex_code = file_ex_codes.length
          , real_URL = view.URL || view.webkitURL || view
          , real_create_object_URL = real_URL.createObjectURL
          , real_revoke_object_URL = real_URL.revokeObjectURL
          , URL = real_URL
          , btoa = view.btoa
          , atob = view.atob
    
          , ArrayBuffer = view.ArrayBuffer
          , Uint8Array = view.Uint8Array
    
          , origin = /^[\w-]+:\/*\[?[\w\.:-]+\]?(?::[0-9]+)?/
        ;
        FakeBlob.fake = FB_proto.fake = true;
        while (file_ex_code--) {
          FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
        }
        // Polyfill URL
        if (!real_URL.createObjectURL) {
          URL = view.URL = function (uri) {
            var
              uri_info = document.createElementNS("http://www.w3.org/1999/xhtml", "a")
              , uri_origin
            ;
            uri_info.href = uri;
            if (!("origin" in uri_info)) {
              if (uri_info.protocol.toLowerCase() === "data:") {
                uri_info.origin = null;
              } else {
                uri_origin = uri.match(origin);
                uri_info.origin = uri_origin && uri_origin[1];
              }
            }
            return uri_info;
          };
        }
        URL.createObjectURL = function (blob) {
          var
            type = blob.type
            , data_URI_header
          ;
          if (type === null) {
            type = "application/octet-stream";
          }
          if (blob instanceof FakeBlob) {
            data_URI_header = "data:" + type;
            if (blob.encoding === "base64") {
              return data_URI_header + ";base64," + blob.data;
            } else if (blob.encoding === "URI") {
              return data_URI_header + "," + decodeURIComponent(blob.data);
            }
            if (btoa) {
              return data_URI_header + ";base64," + btoa(blob.data);
            } else {
              return data_URI_header + "," + encodeURIComponent(blob.data);
            }
          } else if (real_create_object_URL) {
            return real_create_object_URL.call(real_URL, blob);
          }
        };
        URL.revokeObjectURL = function (object_URL) {
          if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
            real_revoke_object_URL.call(real_URL, object_URL);
          }
        };
        FBB_proto.append = function (data/*, endings*/) {
          var bb = this.data;
          // decode data to a binary string
          if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
            var
              str = ""
              , buf = new Uint8Array(data)
              , i = 0
              , buf_len = buf.length
            ;
            for (; i < buf_len; i++) {
              str += String.fromCharCode(buf[i]);
            }
            bb.push(str);
          } else if (get_class(data) === "Blob" || get_class(data) === "File") {
            if (FileReaderSync) {
              var fr = new FileReaderSync;
              bb.push(fr.readAsBinaryString(data));
            } else {
              // async FileReader won't work as BlobBuilder is sync
              throw new FileException("NOT_READABLE_ERR");
            }
          } else if (data instanceof FakeBlob) {
            if (data.encoding === "base64" && atob) {
              bb.push(atob(data.data));
            } else if (data.encoding === "URI") {
              bb.push(decodeURIComponent(data.data));
            } else if (data.encoding === "raw") {
              bb.push(data.data);
            }
          } else {
            if (typeof data !== "string") {
              data += ""; // convert unsupported types to strings
            }
            // decode UTF-16 to binary string
            bb.push(unescape(encodeURIComponent(data)));
          }
        };
        FBB_proto.getBlob = function (type) {
          if (!arguments.length) {
            type = null;
          }
          return new FakeBlob(this.data.join(""), type, "raw");
        };
        FBB_proto.toString = function () {
          return "[object BlobBuilder]";
        };
        FB_proto.slice = function (start, end, type) {
          var args = arguments.length;
          if (args < 3) {
            type = null;
          }
          return new FakeBlob(
            this.data.slice(start, args > 1 ? end : this.data.length)
            , type
            , this.encoding
          );
        };
        FB_proto.toString = function () {
          return "[object Blob]";
        };
        FB_proto.close = function () {
          this.size = 0;
          delete this.data;
        };
        return FakeBlobBuilder;
      }(view));
    
      view.Blob = function (blobParts, options) {
        var type = options ? (options.type || "") : "";
        var builder = new BlobBuilder();
        if (blobParts) {
          for (var i = 0, len = blobParts.length; i < len; i++) {
            if (Uint8Array && blobParts[i] instanceof Uint8Array) {
              builder.append(blobParts[i].buffer);
            }
            else {
              builder.append(blobParts[i]);
            }
          }
        }
        var blob = builder.getBlob(type);
        if (!blob.slice && blob.webkitSlice) {
          blob.slice = blob.webkitSlice;
        }
        return blob;
      };
    
      var getPrototypeOf = Object.getPrototypeOf || function (object) {
        return object.__proto__;
      };
      view.Blob.prototype = getPrototypeOf(new view.Blob());
    }(
      typeof self !== "undefined" && self
      || typeof window !== "undefined" && window
      || this
    ));
    
    

    Export2Excel.js

    /* eslint-disable */
    require('script-loader!file-saver');
    // 注意,更改vendor目录此处导入需修改!!!!!!!!!!!!!!!!!!!!
    require('script-loader!src/vendor/Blob');
    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;
    
      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")
    }
    
    

    定义click函数

    当我们要导出表格执行@click事件调用handleDownload函数

    handleDownload() {
            this.downloadLoading = true
            require.ensure([], () => {
              const {export_json_to_excel} = require('src/vendor/Export2Excel')
              const tHeader = Util.cutValue(this.columns1, 'title')
              const filterVal = Util.cutValue(this.columns1, 'key')
              const list = this.tableData1
              const data = this.formatJson(filterVal, list)
              export_json_to_excel(tHeader, data, '列表excel')
              this.downloadLoading = false
            })
          },
          formatJson(filterVal, jsonData) {
            return jsonData.map(v => filterVal.map(j => v[j]))
          }
    

    Util.cutValue 是公共方法,目的是为了将,tHeader 和filterVal 的值转成数组从而生成表格

    ### Util module
    // 截取value值
    utils.cutValue = function (target, name) {
      let arr = []
      for (let i = 0; i < target.length; i++) {
        arr.push(target[i][name])
      }
      return arr
    }
    

    columns1数据结构如下,本质就是生产一个列表,也可将tHeader和filterVal 直接定义一列表,tableData格式是列表里套对象,具体可自行尝试

    columns1: [
              {
                title: '序号',
                key: 'serNum'
              },
              {
                title: '选择',
                key: 'choose',
                align: 'center',
                render: (h, params) => {
                  if (params.row.status !== '1' && params.row.status !== '2') {
                    return h('div', [
                      h('checkbox', {
                        props: {
                          type: 'selection'
                        },
                        on: {
                          'input': (val) => {
                            console.log(val)
                          }
                        }
                      })
                    ])
                  } else {
                    return h('span', {
                      style: {
                        color: '#587dde',
                        cursor: 'pointer'
                      },
                      on: {
                        click: () => {
                          // this.$router.push({name: '', query: { id: params.row.id }})
                        }
                      }
                    }, '查看订单')
                  }
                }
              },
              ...
            ],
    

    合并单元格

    一些复杂的表头并不是只有一行,还需合并单元格
    更改Export2Excel.js文件中的export_json_to_excel函数

    export function export_json_to_excel(th1, th, jsonData, defaultTitle) {
    
      /* original data */
    
      var data = jsonData;
      // 第二行表头
      data.unshift(th);
      // 第一行表头
      data.unshift(th1)
      // 这个地方定义sheet名,可更改
      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;
      
      // 设置合并单元格,注意预留空值,否则合并后值会被覆盖
      ws['!merges'] = [
        // 设置A3-A9的单元格合并
        {s: {r: 0, c: 2}, e: {r: 0, c: 8}}
      ];
    
      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")
    }
    

    click调用时候相应传参也许更改

    handleDownload() {
            this.downloadLoading = true
            require.ensure([], () => {
              const {export_json_to_excel} = require('src/vendor/Export2Excel')
              const multiHeader = ['A1', 'A2', '合并', '', '', '', '', '', '', 'A10']
              const tHeader = ['name', '用户名', '性别']
              const filterVal = ['name', 'username', 'sex']
              const list = this.tableData1
              const data = this.formatJson(filterVal, list)
              export_json_to_excel(multiHeader, tHeader, data, '列表excel')
              this.downloadLoading = false
            })
          },
          formatJson(filterVal, jsonData) {
            return jsonData.map(v => filterVal.map(j => v[j]))
          }
    

    tableDate格式

    tableDate1: [
            {'name': 'lgk', 'sex': '男', 'username': 'liqing'},
            {'name': 'll', 'sex': '女', 'username': 'yaoyao'}
          ]
    

    参考文档

    https://sheetjs.gitbooks.io/docs/#sheetjs-js-xlsx
    https://github.com/han6054/export-excel

    展开全文
  • npm install --save xlsx file-saver创建@/vendor/Export2Excel.js:require('script-loader!file-saver');require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方import XLSXfrom '...

    npm install --save xlsx file-saver

    创建@/vendor/Export2Excel.js:

    require('script-loader!file-saver');

    require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方

    import XLSXfrom 'xlsx'

    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

    if (range.e.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;

    }

    // 单列合并数据加工

    //在tableData数据中对colName列进行加工(header:['xx','yy',...],mergeRule:[2,1,1,3,2,1...])

    // 返回格式 newTableDatas = [[列头s], ["abc", "ss", "yyy", 0], [null, "xx", "y", 1], ["bca", "xxx", "yy", 1], [null, "xxx", "yy", 1]]

    // 返回格式 newMergeRules = [{"e":{"r":2,"c":0}, "s":{"r":1,"c":0}},{"e":{"r":4,"c":0}, "s":{"r":3,"c":0}}]

    function formatTableData(header,tableDatas,mergeRules,colIndex,colName){

    var newTableDatas = [header]// 第一行为列头,

    var newMergeRules= []//加工合并规则

    var currRowIndex =1 // 因为第一行为列头所以起始为1

    var mergeIndexs = [currRowIndex]// 因为第一行为列头所以起始为1,为创建newTableDatas使用

    for(var i=0;i

    var endRowIndex =currRowIndex + mergeRules[i] -1

    var merge = {"e":{"r":endRowIndex,"c": colIndex},"s":{"r":currRowIndex,"c":colIndex}}

    newMergeRules.push(merge)

    currRowIndex =currRowIndex + mergeRules[i]

    mergeIndexs.push(currRowIndex)

    }

    for(var i=0;i

    var currRow = tableDatas[i]

    if(mergeIndexs.indexOf(i+1) === -1){

    currRow[colName] =null //合并的数据 但不是第一行时,此列赋值null

    }

    var newTabRow = []

    for (var j in currRow) {

    newTabRow.push(currRow[j])

    }

    newTableDatas.push(newTabRow)

    }

    return [newTableDatas,newMergeRules]

    }

    @/vendor/Blob.js:

    export function export_data_to_excel(header,tableDatas,mergeRules,colIndex,colName,excelName) {

    var oo =formatTableData(header,tableDatas,mergeRules,colIndex,colName);

    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"}), excelName+".xlsx")

    }

    (function (view) {

    "use strict";

    view.URL = view.URL || view.webkitURL;

    if (view.Blob && view.URL) {

    try {

    new Blob;

    return;

    }catch (e) {}

    }

    // Internally we use a BlobBuilder implementation to base Blob off of

    // in order to support older browsers that only have BlobBuilder

    var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {

    var

    get_class =function(object) {

    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];

    }

    ,FakeBlobBuilder =function BlobBuilder() {

    this.data = [];

    }

    ,FakeBlob =function Blob(data, type, encoding) {

    this.data = data;

    this.size = data.length;

    this.type = type;

    this.encoding = encoding;

    }

    ,FBB_proto =FakeBlobBuilder.prototype

    ,FB_proto =FakeBlob.prototype

    ,FileReaderSync = view.FileReaderSync

    ,FileException =function(type) {

    this.code =this[this.name = type];

    }

    ,file_ex_codes = (

    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "

    +"NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"

    ).split(" ")

    ,file_ex_code =file_ex_codes.length

    ,real_URL = view.URL || view.webkitURL || view

    ,real_create_object_URL =real_URL.createObjectURL

    ,real_revoke_object_URL =real_URL.revokeObjectURL

    ,URL =real_URL

    ,btoa = view.btoa

    ,atob = view.atob

    ,ArrayBuffer = view.ArrayBuffer

    ,Uint8Array = view.Uint8Array

    ;

    FakeBlob.fake =FB_proto.fake =true;

    while (file_ex_code--) {

    FileException.prototype[file_ex_codes[file_ex_code]] =file_ex_code +1;

    }

    if (!real_URL.createObjectURL) {

    URL = view.URL = {};

    }

    URL.createObjectURL =function(blob) {

    var

    type = blob.type

    ,data_URI_header

    ;

    if (type ===null) {

    type ="application/octet-stream";

    }

    if (blobinstanceof FakeBlob) {

    data_URI_header ="data:" +type;

    if (blob.encoding ==="base64") {

    return data_URI_header +";base64," + blob.data;

    }else if (blob.encoding ==="URI") {

    return data_URI_header +"," +decodeURIComponent(blob.data);

    }if (btoa) {

    return data_URI_header +";base64," +btoa(blob.data);

    }else {

    return data_URI_header +"," +encodeURIComponent(blob.data);

    }

    }else if (real_create_object_URL) {

    return real_create_object_URL.call(real_URL, blob);

    }

    };

    URL.revokeObjectURL =function(object_URL) {

    if (object_URL.substring(0,5) !=="data:" &&real_revoke_object_URL) {

    real_revoke_object_URL.call(real_URL, object_URL);

    }

    };

    FBB_proto.append =function(data/*, endings*/) {

    var bb =this.data;

    // decode data to a binary string

    if (Uint8Array && (datainstanceof ArrayBuffer || datainstanceof Uint8Array)) {

    var

    str =""

    ,buf =new Uint8Array(data)

    ,i =0

    ,buf_len =buf.length

    ;

    for (;i

    str +=String.fromCharCode(buf[i]);

    }

    bb.push(str);

    }else if (get_class(data) ==="Blob" ||get_class(data) ==="File") {

    if (FileReaderSync) {

    var fr =new FileReaderSync;

    bb.push(fr.readAsBinaryString(data));

    }else {

    // async FileReader won't work as BlobBuilder is sync

    throw new FileException("NOT_READABLE_ERR");

    }

    }else if (datainstanceof FakeBlob) {

    if (data.encoding ==="base64" &&atob) {

    bb.push(atob(data.data));

    }else if (data.encoding ==="URI") {

    bb.push(decodeURIComponent(data.data));

    }else if (data.encoding ==="raw") {

    bb.push(data.data);

    }

    }else {

    if (typeof data !=="string") {

    data +="";// convert unsupported types to strings

    }

    // decode UTF-16 to binary string

    bb.push(unescape(encodeURIComponent(data)));

    }

    };

    FBB_proto.getBlob =function(type) {

    if (!arguments.length) {

    type =null;

    }

    return new FakeBlob(this.data.join(""), type,"raw");

    };

    FBB_proto.toString =function() {

    return "[object BlobBuilder]";

    };

    FB_proto.slice =function(start, end, type) {

    var args =arguments.length;

    if (args <3) {

    type =null;

    }

    return new FakeBlob(

    this.data.slice(start,args >1 ? end :this.data.length)

    , type

    ,this.encoding

    );

    };

    FB_proto.toString =function() {

    return "[object Blob]";

    };

    FB_proto.close =function() {

    this.size =this.data.length =0;

    };

    return FakeBlobBuilder;

    }(view));

    view.Blob =function Blob(blobParts, options) {

    var type = options ? (options.type ||"") :"";

    var builder =new BlobBuilder();

    if (blobParts) {

    for (var i =0,len = blobParts.length;i

    builder.append(blobParts[i]);

    }

    }

    return builder.getBlob(type);

    };

    }(typeof self !=="undefined" &&self ||typeof window !=="undefined" &&window ||this.content ||this));

    展开全文
  • src/libs/目录下增加excel.js excel.js /* eslint-disable */ import XLSX from 'xlsx'; function auto_width(ws, data){ /*set worksheet max width per col*/ const colWidth = data.map(row => row.m...

    src/libs/目录下增加excel.js 

    excel.js 

    /* eslint-disable */
    import XLSX from 'xlsx';
    
    function auto_width(ws, data){
        /*set worksheet max width per col*/
        const colWidth = data.map(row => row.map(val => {
            /*if null/undefined*/
            if (val == null) {
                return {'wch': 10};
            }
            /*if chinese*/
            else if (val.toString().charCodeAt(0) > 255) {
                return {'wch': val.toString().length * 2};
            } else {
                return {'wch': val.toString().length};
            }
        }))
        /*start in the first row*/
        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;
    }
    
    function json_to_array(key, jsonData){
        return jsonData.map(v => key.map(j => { return v[j] }));
    }
    
    // fix data,return string
    function fixdata(data) {
        let o = ''
        let l = 0
        const w = 10240
        for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
        o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
        return o
    }
    
    // get head from excel file,return array
    function get_header_row(sheet) {
        const headers = []
        const range = XLSX.utils.decode_range(sheet['!ref'])
        let C
        const R = range.s.r /* start in the first row */
        for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */
            var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
            var hdr = 'UNKNOWN ' + C // <-- replace with your desired default
            if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
            headers.push(hdr)
        }
        return headers
    }
    
    export const export_table_to_excel= (id, filename) => {
        const table = document.getElementById(id);
        const wb = XLSX.utils.table_to_book(table, { raw: true });
        XLSX.writeFile(wb, filename);
    
        /* the second way */
        // const table = document.getElementById(id);
        // const wb = XLSX.utils.book_new();
        // const ws = XLSX.utils.table_to_sheet(table);
        // XLSX.utils.book_append_sheet(wb, ws, filename);
        // XLSX.writeFile(wb, filename);
    }
    
    export const export_json_to_excel = ({data, key, title, filename, autoWidth}) => {
        const wb = XLSX.utils.book_new();
        data.unshift(title);
        const ws = XLSX.utils.json_to_sheet(data, {header: key, skipHeader: true});
        if(autoWidth){
            const arr = json_to_array(key, data);
            auto_width(ws, arr);
        }
        XLSX.utils.book_append_sheet(wb, ws, filename);
        XLSX.writeFile(wb, filename + '.xlsx');
    }
    
    export const export_array_to_excel = ({key, data, title, filename, autoWidth}) => {
        const wb = XLSX.utils.book_new();
        const arr = json_to_array(key, data);
        arr.unshift(title);
        const ws = XLSX.utils.aoa_to_sheet(arr);
        if(autoWidth){
            auto_width(ws, arr);
        }
        XLSX.utils.book_append_sheet(wb, ws, filename);
        XLSX.writeFile(wb, filename + '.xlsx');
    }
    
    export const read = (data, type) => {
        /* if type == 'base64' must fix data first */
        // const fixedData = fixdata(data)
        // const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
        const workbook = XLSX.read(data, { type: type });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const header = get_header_row(worksheet);
        const results = XLSX.utils.sheet_to_json(worksheet);
        return {header, results};
    }
    
    export default {
      export_table_to_excel,
      export_array_to_excel,
      export_json_to_excel,
      read
    }
    

    使用方法export_table_to_excel导出EXCEL,即可导出包含合并单元格格式的EXCEL

      handleExport(){

           excel.export_table_to_excel("maintable","TEST.xlsx");

        }

    使用以下方法导出后,%变为0,百分制格式的字符串也变成了数字, 然后通过设置raw:true,就可以导出字符串并没有做转换。

    export const export_table_to_excel= (id, filename) => {

        const table = document.getElementById(id);

        const wb = XLSX.utils.table_to_book(table);

        XLSX.writeFile(wb, filename);

    }

     

     const wb = XLSX.utils.table_to_book(table, { raw: true });

     

    展开全文
  • vue + element中 el-table 导出Excel合并单元格,解决输入框问题) 单纯记笔记,嘻嘻 合并单元格解决了,页面输入框也解决了 第一步可能需要安装相关依赖 npm install --save xlsx file-saver 2、在组件里面引入...

    vue + element中 el-table 导出Excel(合并单元格,解决输入框问题)

    单纯记笔记,嘻嘻
    合并单元格解决了,页面输入框也解决了

    第一步可能需要安装相关依赖
    npm install --save xlsx file-saver

    2、在组件里面引入依赖
    import FileSaver from ‘file-saver’
    import XLSX from ‘xlsx’

    3、在methods里写一个方法(直接复制就可以)
    exportExcel () {
    var wb = XLSX.utils.table_to_book(document.querySelector(’#out-table’))
    var wbout = XLSX.write(wb, { bookType: ‘xlsx’, bookSST: true, type: ‘array’ })
    try {
    FileSaver.saveAs(new Blob([wbout], { type: ‘application/octet-stream’ }), ‘sheetjs.xlsx’)
    } catch (e) { if (typeof console !== ‘undefined’) console.log(e, wbout) }
    return wbout
    },
    ***注意:XLSX.uitls.table_to_book(“#out-table”为el-table标签的id) ,sheetjs 就是导出表格的名字,可以更改!***

    4、点击设置好的导出按钮执行 上面的方法就行啦 。

    5.直接将页面的数据布局复制下来,单元格也会合并。

    6.页面数据如果有输入框(不支持直接生成,输入框生成后无数据),或者需要在其他页面生成时,可以写一个隐藏的el-table,进行同步。**

    7.没得图片,忘记截图了 >-<

    展开全文
  • 安装blob和export2excel两个文件放到vendor文件夹下
  • => { this.colData.push({ dataItem: res.warehouseId, dataName: res.warehouseName }) }) }, exportList() { const wscols = [ { wpx: 220 } // 第一列宽度设置单位px ] /** * 合并单元格元素(decode_range方法...
  • //给所以单元格加上边框 for (var i indataInfo) {if (i == '!ref' || i == '!merges' || i == '!cols' || i == 'A1') { }else{ dataInfo[i+ ''].s ={ border: borderAll } } }//去掉标题边框 let arr = ["A1", "B1...
  • 前段时间接到一个做报表的项目,需要将数据导出成...由SheetJS出品的js-xlsx是一款非常方便的只需要纯JS即可读取和导出excel的工具库,功能强大,支持格式众多,支持xls、xlsx、ods(一种OpenOffice专有表格文件格...
  • export_table_to_excel ( id ) { var theTable = document . getElementById ( id ) ; var oo = generateArray ( theTable ) ; var ranges = oo [ 1 ] ; /* original data */ var data...
  • 后台系统开发中,统一功能经常遇到需要将统计的数据以EXCEL的形式导出。针对这个需求,我遍在网上搜寻相应的解决方案。最终选择了大多数的建议: 使用js-xlsx插件(https://www.npmjs.com/package/xlsx)。 js-xlsx...
  • VUE导出Excel文件.rar

    2020-06-02 15:15:08
    VUE导出Excel文件,两种方法,方法二带样式:文字居中,自动换行,列宽设置,单元格合并,冻结表头等。 https://www.cnblogs.com/yinxingen/p/11052184.html
  • 本来是希望可以导出的excel可以合并单元格,查了很久,一直没头绪,能力有限,只能重复信息的 运行环境npm安装 导出excel的插件,随便一查按步骤就好 这可能是比较笨的方法,如果大佬知道怎么让它导出单元格可以合并...
  • SheetJS免费版的不支持格式,比如居中、自动换行都不行。xlsx-style是SheetJS的一个分支,且支持各种格式,可以做到文字居中,自动换行,列宽设置,单元格合并,冻结表头等。
  • prop="address"label="地址"width="300"> prop="zip"label="邮编"width="120"> 导出 name:'project', data() {return{ tableData: [{ date:'2016-05-03', name:'王小虎', province:'上海', city:'普陀区', address:'...
  • 1. 环境 vue:2.6.10 ...可以将线上的excel导出excel文件,并保留合并单元格信息 3. 缺点 不能读取本地excel文件中的样式信息 不能导出线上excel文件样式信息 3. 代码 <template> <div>
  • * 合并单元格元素(decode_range方法解析数据格式) { s: { //s start 开始 c: 1,//cols 开始列 r: 0 //rows 开始行 }, e: {//e end 结束 c: 4,//cols 结束列 r: 0 //rows 结束行 } } */ const wsMerge =...
  • 2.在所需要导出vue文件里引入: import { toExportExcel } from “…/libs/excel/Export2Excel2.js”; 3.点击导出按钮时: toExportExcel(title, tHeader, datas, filename); //标题,表头,表数据,表名字 注: ...
  • 报表导出功能在JavaWeb 开发中非常常见,网上搜索相应的代码也非常多,大多都相似:利用POI生成excel 文件到服务器,再利用InputStream和Response 返回给前端做处理。无聊中发现WorkBook的write方法API如下: 随...

空空如也

空空如也

1 2 3
收藏数 46
精华内容 18
关键字:

vue导出excel合并单元格

vue 订阅