vue合并表格excel導出_vue element 導出excel表格(前端實作excel合并單元格)

npm install --save xlsx file-saver

建立@/vendor/Export2Excel.js：

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

require('[email protected]/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];

var data =oo[0];

var ws_name ="SheetJS";

var wb =new Workbook(),ws =sheet_from_array_of_arrays(data);

// ws['!cols'] = ['apple', 'banan'];

ws['!merges'] =ranges;

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) {

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));