Export2Excel.js导出多维数组的复杂表格并设置表格样式Export2Excel.js excel導出插件
導出多維數組的數據
需要先把原數組進⾏拆分,重新組成⼀個⼀維數組(插件所需的是⼀維數組)
多維數組中的數據項中的鍵名存在相同,故不能直接把他们都放在⼀个json数据中,
浏览器识别到相同的键名会进⾏去重,导致数据只有⼀条的问题,所以不只是要把多维数组
改为⼀维数组,也需要把数组中的对象的键值进⾏改变,以达到唯⼀性。
json 数据处理层
formatJson(filterVal, tableJsonList){
let temp1 =[];
let temp2 =[];
let temp3 ={};
/
/ console.sult.data);
// var result = sult.data;
tableJsonList.map(v =>{
//console.log('v',v);
for(let key in v){
if(key =='workdetailslist')continue;
//console.log('key',key);
//console.log('value',v[key]);
temp3[key]= v[key];//獲取原數組的鍵值
for(let i =0; i < v.workdetailslist.length; i++){
/********
作為⼩⽩的我,在新組對象中,怎麼也是想不到下⾯的這步,
請教同事才弄出來,技術還得多練才⾏
**********/
for(let key1 in v.workdetailslist[i]){
//console.log(v.workdetailslist[i]);
temp3[key1 + i]= v.workdetailslist[i][key1];//重新定義鍵名和給鍵名賦值
}
}
}
temp1.push(temp3);//把已重新賦值的鍵值對放進新數組,組成新數組
temp3 ={};//清空臨時存儲對象,否则数据⼀直都是同⼀条
/
/console.log('temp3',temp3);
});
//console.log('temp1', temp1);
return temp1.map(t =>
filterVal.map(j =>{
//console.log('t[j]', t[j]);
return t[j];
})
);
}
Export2Excel.js — 导出带样式的excel 表格
import{ saveAs }from"file-saver";
// import XLSX from "xlsx";
import XLSX from"xlsx-style";
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 = Attribute("colspan");
var rowspan = Attribute("rowspan");
var cellValue = cell.innerText;
if(cellValue !==""&& cellValue ==+cellValue) cellValue =+cellValue;
//Skip ranges
ranges.forEach(function(range){
if(
R>= range.s.r &&
R<= &&
outRow.length >= range.s.c &&
outRow.length <=
){
for(var i =0; i <= - 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;
<R) =R;
<C) =C;
var cell ={
v: data[R][C]
};
// 如果单元格所在的值为空,让其值为“——”,否则下⾯设置的边框对其不⽣效if(cell.v ==null){
cell.v ="——";
}
var cell_ref =de_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){
typeof arraycell.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"]=de_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 = ElementById(id);
var oo =generateArray(theTable);
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"
}),
"test.xlsx"
);
}
// 主要修改内容在这⾥
export function export_json_to_excel({
title,// 新增的可设置标题的参数,表格标题
multiHeader =[],//复杂表格时需增加的⾃定义表头
header,
data,
filename,
merges =[],
autoWidth =true,
bookType ="xlsx"
}={}){
/* original data */
filename = filename ||"excel-list";
data =[...data];
// console.log('data',data);
// debugger
/** 当表头是为空数组时,则⽆需unshift 进去 **/
if(header.length !=0){
data.unshift(header);
}
for(let i = multiHeader.length -1; i >-1; i--){
data.unshift(multiHeader[i]);
}
data.unshift(title);// 表格标题
var ws_name ="SheetJS";
var wb =new Workbook(),
ws =sheet_from_array_of_arrays(data);
if(merges.length >0){
if(!ws["!merges"]) ws["!merges"]=[];
merges.forEach(item =>{
ws["!merges"].push(XLSX.utils.decode_range(item)); });
}
// 设置单元格宽度
if(autoWidth){
/*设置worksheet每列的最⼤宽度*/
const colWidth = data.map(row =>
row.map(val =>{
/*先判断是否为null/undefined*/
if(val ==null|| val == undefined){
return{
wch:10
};
}else String().charCodeAt(0)>255){
/*再判断是否为中⽂*/
return{
wch: String().length *2
};
}else{
return{
wch: String().length *1.5
};
}
})
);
/*以主表第⼆⾏为初始值,因为第⼀⾏是表格标题,,所以以主表第⼆⾏为初始值*/ let result = colWidth[1];
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;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name]= ws;
var dataInfo = wb.Sheets[wb.SheetNames[0]];
// 设置单元格框线
const borderAll ={
top:{
style:"thin"
},
bottom:{
style:"thin"
},
left:{
style:"thin"
},
right:{
style:"thin"
}
};
// 给所有单元格加上边框,内容居中,字体,字号,标题表头特殊格式部分后⾯替换for(var i in dataInfo){
if(
i =="!ref"||
i =="!merges"||
i =="!cols"||
i =="!rows"||
i =="A1"
){}else{
dataInfo[i +""].s ={
border: borderAll,
alignment:{
horizontal:"center",
vertical:"center"
},
font:{
name:"微软雅⿊",
sz:10
}
};
}
}

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。