npm的xlsx前端导出excel怎么合并单元格呢？
['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
['姓名', '性别', '年龄', '注册时间'],
['张三', '男', 18, new Date()],
['李四', '女', 22, new Date()]
];
var sheet = XLSX.utils.aoa_to_sheet(aoa);
sheet['!merges'] = [
// 设置A1-C1的单元格合并
{s: {r: 0, c: 0}, e: {r: 0, c: 2}}
];
openDownloadDialog(sheet2blob(sheet), '单元格合并示例.xlsx');
最新回答
琉璃水色
2020-05-30 09:07:45
创建@/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
作者：paperss
链接：https://www.jianshu.com/p/b440106eb8a2
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
热门标签
