excel 前端下载插件https://github.com/wangerzi/layui-excel
插件 github 下载地址
https://github.com/wangerzi/layui-excel
<script src="js/jquery-1.11.1.min.js"></script>
<script src="js/excel.js"></script>
jqgrid excel 下载方法封装，支持复杂表头
（设置jqgrid行合并,下面方法用的到）
setRowMerger:function(opts){ var def = { gridId:'jqGrid', //grid 的id isMerge:true, // 是否做表格合并，true时即做技术又做合并，false时只做计算 data:[ // {name: 'k',gist:['k','k'],num:1}, // name: 列名称 // gist: 行合并依据列名称集合 // num: 行合并数 ] } $.extend(def,opts); var $grid = $('#'+ def.gridId); var mya = $grid.getDataIDs(); var length = mya.length; function flagFn(start,end,arr){ var flag = true $.each(arr,function(k,v){ if(start[v] != end[v]){ flag = false } }) return flag } var cellObj = {} var deleteObj = {} $.each(def.data, function(k,v){ var num = v.num; var CellName = v.name; cellObj[CellName] = {}; deleteObj[CellName] = {}; if(!num){ for (var i = 0; i < length; i++) { var before = $grid.jqGrid('getRowData', mya[i]);//从上到下获取一条信息 var rowSpanTaxCount = 1;//定义合并行数 for (var j = i + 1; j <= length; j++) { //和上边的信息对比 如果值一样就合并行数+1 然后设置rowspan 让当前单元格隐藏 var end = $grid.jqGrid('getRowData', mya[j]); if (flagFn(before,end,v.gist)) { rowSpanTaxCount++; if(def.isMerge){$grid.setCell(mya[j], CellName, '', { display: 'none' });} } else { rowSpanTaxCount = 1; break; } if(def.isMerge){ $("#" + v.name + "" + mya[i] + "").attr("rowspan", rowSpanTaxCount); } cellObj[CellName][v.name + "" + mya[i]] = [i+1, rowSpanTaxCount]; deleteObj[CellName][v.name + "" + mya[j]] = [j+1, rowSpanTaxCount]; } } }else{ for (var i = 0; i < length; i++) { for (j = i + 1; j <= length; j++) { if(j%num != 0){ if(def.isMerge){ $grid.setCell(mya[j], CellName, '', { display: 'none' });} } if(def.isMerge){ $("#" + v.name + "" + mya[i] + "").attr("rowspan", num);} cellObj[CellName][v.name + "" + mya[i]] = [i+1, num]; deleteObj[CellName][v.name + "" + mya[j]] = [j+1, num]; } } } }) $.each(cellObj,function(k,v){ $.each(deleteObj[k],function(dk,dv){ if(v[dk]){ delete v[dk] } }) }) return cellObj },
jqgrid excel 导出方法
exportExcel: function(opts){ var defauls = { fileType:'xlsx',//文件类型 支持 xlsx、csv、ods、xlsb、fods、biff2 fileName:'导出excel数据', //文件名称 gridId:'', //需要下载的jgrid表id exclude:[], //过滤非下载列name groupHeaders:null, // 表头合并参数(freeGrid版本下使用) rowMerger:{}, //行合并数据集合 showHead:true, // 显示表头 showData:true, // 显示表身数据 total:{ show:false, //是否开启合计行 position:'',//合计名称所在列位置 label:'合计',//名称 data:[] //需要合计的列 name }, download:{ data:[], //json数据 不通过jqgrid 直接下载 同时支持合计和过滤 head:[] //表头 直接下载表头 } } $.extend(true,defauls,opts); var config ={ KEY:{}, mergeConf:[], data:[], head:[], _head:[], _groupHeader:[], _colConf:{}, headLength:0, // 行合并 setRowMerger:function(){ var _heardNum = config._groupHeader && config._groupHeader.length ? config._groupHeader.length : 1 var cellMergeArr = [] var _rowMerger = defauls.rowMerger; if(_rowMerger){ var _rowMergerData = setRowMerger(_rowMerger); $.each(config._head,function(k,v){ $.each(_rowMergerData,function(rk,rv){ if(v.name == rk ){ $.each(rv,function(jk,jv){ cellMergeArr.push([ LAY_EXCEL.numToTitle(k + 1) + (jv[0] + _heardNum), LAY_EXCEL.numToTitle(k + 1) + (jv[0] + _heardNum + jv[1] - 1) ]) }) } }) }) config.mergeConf= config.mergeConf.concat(cellMergeArr) } }, gridDown:function(){ var GridParam = $("#"+defauls.gridId).jqGrid('getGridParam') var data = GridParam.data; var head = GridParam.colModel; var groupHeader = defauls.groupHeaders ? defauls.groupHeaders : GridParam.groupHeader; config._groupHeader = groupHeader || []; var _head = {} var _data = []; var exclude = defauls.exclude.concat(['rn','cb']); // 动态过滤显示的列 exclude 里的 var head1 = [] $.each(head,function(k,v){ if(!exclude.includes(v.name) && !v.hidden ){ head1.push(v) } }) head = head1 config._head = head1; // 过滤隐藏列，生产_head 头对象 $.each(head,function(k,v){ var e = 'e' + k config.KEY[e] = v.name _head[ e ] = v.label config._colConf[LAY_EXCEL.numToTitle(k + 1)] = v.width }) // 这里很重要 var _headIndex = []; var _headObjArr = {} $.each(_head,function(k,v){ _headIndex.push(k); _headObjArr[k] = []; config.headLength ++ ; }) /*复杂表头合并数据生成*/ var _groupHeaderData = []; if(groupHeader && defauls.showHead){ var _mergeConf = config.mergeConf; $.isArray(groupHeader) ? groupHeader.push({}) : [groupHeader,{}] $.each(groupHeader,function(k,v){ var _rowText = {}; var _k = k+1; var _i = 0; var _group = v['groupHeaders']; var _j = 0; var _exclude = []; for(var j = 0; j < _headIndex.length; j++){ var k2=j ,v2=_headIndex[j]; //a // 通过groupHeader 进行横向（列）合并 生成复合数组_mergeConf if(_group && _group[k2]){ var groupItem = _group[k2]; _i = groupItem.numberOfColumns _j = k2; var _name = '' $.each(config.KEY,function(n,s){ if(groupItem.startColumnName.includes(s)){ _name = n } }) var _index = $.inArray(_name, _headIndex); _mergeConf.push([LAY_EXCEL.numToTitle(_index + 1) + _k , LAY_EXCEL.numToTitle(_index + groupItem.numberOfColumns) + _k]) _rowText[_name] = groupItem.titleText for(var k5 = _index ; k5 < _index + _i; k5++){ _exclude.push(_headIndex[k5]) } } // 排除横向（列）合并的name值， 生成纵向（行）合并 if($.inArray(v2 , _exclude) == -1){ if(_headObjArr[v2].length ){ _headObjArr[v2][1] = (LAY_EXCEL.numToTitle(k2 + 1) + _k) }else{ _headObjArr[v2][0] = (LAY_EXCEL.numToTitle(k2 + 1) + _k) } } } _groupHeaderData.push( $.extend({},_head,_rowText)); }) $.each(_headObjArr,function(k,v){ if(v && v.length>1){ _mergeConf.push(v) } }) }else{ _groupHeaderData.push(_head); } /*跨行合并数据*/ config.setRowMerger(); // 根据_head 表头 过滤多余数据 $.each(data,function(index,item){ var _obj = {} $.each(_head,function(key,val){ _obj[key] = item[config.KEY[key]] }) _data.push(_obj) }) config.data = defauls.showData ? _data : []; // 合计行 if(defauls.total.show){ config.total(); } if(defauls.showHead ){ config.data=_groupHeaderData.concat(config.data); } }, downdload:function(){ var def = defauls.download; var data = def.data; var exclude =defauls.exclude; var head; var flag = true; if(def.head){ if($.isArray(def.head)){ if(def.head[0]){ head = def.head[0] }else{ head = data[0]; flag = false; } } }else { flag = false; head = data[0]; } // 过滤 $.each(exclude,function(k,v){ if(head && head[v]){ delete head[v] } $.each(data,function(k1,v1){ if(v1[v]){ delete v1[v] } }) }) // 获取head 的长度 $.each(head,function(k,v){ config.headLength ++ }) config.head = defauls.showHead ? head : []; config.data = defauls.showData ? data : []; if(defauls.total.show){ config.total(); } if(flag){ config.data.unshift(config.head); } }, total:function(){ var total = defauls.total var data = config.data; var _total = {}; // 第一合计行对象 var _head = config.head; var _totalObj = {}; var _totalFirdtKey ; $.extend(_total,_head); // 获取第一个属性key值 if(total.position){ $.each(_total,function(k,v){ _total[k] = '' }) _totalFirdtKey = total.position }else{ // 获取第一个属性值 var _totalFirdtValue = get_object_first(_total); function get_object_first(data){ for (var key in data) return data[key]; } $.each(_total,function(k,v){ if(v == _totalFirdtValue) _totalFirdtKey = k _total[k] = '' }) } // 求和 _totalObj[_totalFirdtKey] = total.label $.each(total.data,function(index,item){ var num = 0; $.each(data,function(k,v){ num += Number(v[item]) || 0; }) _totalObj[item] = num; }) $.extend(_total,_totalObj); config.data.push(_total) } } if(defauls.gridId){ config.gridDown(); }else{ config.downdload(); } // 设置excel 设置文件居中 var rangeRow = LAY_EXCEL.numToTitle( config.headLength) var range = 'A1:'+ rangeRow + config.data.length; // 设置合并 var mergeConf = LAY_EXCEL.makeMergeConfig(config.mergeConf) // 设置列宽度 var colConf = LAY_EXCEL.makeColConfig(config._colConf, 80); // 设置单元格样式 LAY_EXCEL.setExportCellStyle(config.data, range, { s: { alignment: { horizontal: 'center', vertical: 'center' }, border:{ top:{style: 'thin', color: '#FF000000'}, right:{style: 'thin', color: '#FF000000'}, bottom:{style: 'thin', color: '#FF000000'}, left:{style: 'thin', color: '#FF000000'} }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFF0F6F6" }} } }) if(defauls.showHead){ var heardRange = 'A1:'+ rangeRow + (config._groupHeader.length ? config._groupHeader.length : 1); // 设置表头样式 LAY_EXCEL.setExportCellStyle(config.data, heardRange, { s: { fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFE7EBEF" }} } }) } LAY_EXCEL.exportExcel({ sheet1:config.data }, defauls.fileName+ '.' + defauls.fileType, defauls.fileType, { extend: { sheet1: { '!merges': mergeConf, '!cols': colConf } } }) },
jqgrid导出 excel / pdf
<script type="text/javascript" src="jquery-1.11.1.min.js"></script>
<!-- <script type="text/javascript" src="polyfill.js"></script>-->
<!-- <script type="text/javascript" src="exceljs.bare.js"></script>-->
<script type="text/javascript" src="exceljs.js"></script>
<script type="text/javascript" src="FileSaver.js"></script>
<script type="text/javascript" src="jquery.fileDownload.js"></script>
<script type="text/javascript" src="layer.js"></script>
exportReport: function(opts){
var defauls = {
contentWindow:window, // 目标window
fileType: 'xlsx', // 文件类型 支持 xlsx、csv、ods、xlsb、fods、biff2
fileName: '导出excel数据', // 文件名称
isExport:true, // 是否导出excel
excludeName:['rn','cb'], // 过滤不显示列名称
exportPdf:{
show:false, // 是否需要导出pdf
fileType:'.pdf', // pdf 文件后缀
postUrl:'', // 提交Excel生产数据,用于后台转换
downloadUrl:'' // 下载pdf服务url
},
ColWidth:15, // 默认列宽
backgroundColor:'', // 模板背景色
tableHeadColor:'', // 表格表头颜色
tableBodyColor:'', // 表格表身颜色(暂不支持)
groupHeaders: [ // 表头合并参数
// {
// useColSpanStyle: true, // 是否跨行合并，同jqgrid的API 一样
// groupHeaders: [
// { startColumnName: 'a', // 可以合并列名称
// numberOfColumns: 10, // 列合并数量
// titleText: '一级标题合并'// 合并标题
// }
// ]
// },
],
rowMerger:[ // 行合并规律数据
// {name: 'a',gist:['a']}, // name 为a 的列， 相隔行相同值跨行合并
// {name: 'c',gist:['a','c']}, // name 为c 的列,a列并且c列相隔行相同值跨行合并
],
title:{
show: false, // 是否显示
text: '', // 名称
titleStyle:{ // 标题样式
height:60, // 行高
font:{
size: 22, // 字体
color: { argb: 'FF000000' }, // 颜色
bold: true // 是否加粗
},
alignment:{
vertical: 'middle', // 垂直居中
horizontal: 'center' // 水平居中
},
// fill:{ // 背景色填充（可查看api）
// type: 'pattern',
// pattern:'darkTrellis',
// fgColor:{argb:'FFFFFFFF'},
// bgColor:{argb:'FFFFFFFF'}
// }
}
},
tableBefore:{ // 表格前文字排列
show:false, // 是否显示表前文字
data:[
// [{text:'测试ccc'}], // text 显示文字，textStyle 单个样式，规则和标题一样
// [{text:'测试aaa',textStyle:{font:{size: 14,bold: true}}},{text:'测试bbb'}],
],
textStyle:{ // 通用表前文字样式
height:20,
font:{
size: 13,
color: { argb: 'FF000000' },
bold: false
},
alignment:{ vertical: 'middle', horizontal: 'center' },
}
},
tableAfter:{ // 表格后文字排列
show:false,
data:[
// [{text:'说明111 ',textStyle:{font:{size: 13,bold: true},align:'left'}}],
// [{text:'说明222',textStyle:{font:{size: 15,bold: true}, alignment:{ vertical: 'middle', horizontal: 'right' }}}]
],
textStyle:{
height:20,
font:{
size: 12,
color: { argb: 'FF000000' },
bold: false
},
alignment:{ vertical: 'middle', horizontal: 'center' },
}
},
image:{
show:false, // 是否显示图片
data:[
{
title:'', // 图片标题
titleStyle:{ // 标题样式，规则一样
height:25,
font:{
size: 13,
color: { argb: 'FF000000' },
bold: true
},
alignment:{ vertical: 'middle', horizontal: 'center' }
},
echartId:'', // 页面echarts id ，（有则默认优先使用该元素canvs转换的base64图片）
src:{ // 图片类型，可以查看api（将图片添加到工作簿）
base64: '',
extension: 'png',
},
config:{ // 图片位置，可以查看api
tl: { col: 0, row: 0 },
ext: { width: 800, height: 300 },
editAs: 'oneCell'
}
}
]
},
table:{
gridId:'', // 页面jqgrid 表格id (自定义jqrid插件内置适应，有则优先使用jqgrid的数据)
title:'', // 表格标题
titleStyle:{ // 表格表头样式，规则一样
height:25,
font:{
size: 13,
color: { argb: 'FF000000' },
bold: true
},
alignment:{ vertical: 'middle', horizontal: 'center' },
},
name: 'MyTable', // 内部表格name标识（可不设置）
ref: 'A1', // 表格左上角位置（可以不设置内部自动计算位置）
headerRow: true, // 是否显示表头
bodyRow:true, // 是否行显示数据（自定义功能，模板下载时有用）
totalsRow: false, // 合计，工具行是否显示
style: { // 插件内部主题
theme: null, // 默认主题名称
showFirstColumn: false, // 突出显示第一列（粗体）
showLastColumn: false, // 突出显示最后一列（粗体）
showRowStripes: false, // 用交替的背景色显示行
showColumnStripes: false // 用交替的背景色显示列
},
columns: [ // 列表头数据
// _name 数据里的数据里的的属性名
// {name: '时间', _name: 'time', totalsRowLabel: 'Totals:', filterButton: true},
// {name: '总计', _name: 'amount', totalsRowFunction: 'sum', filterButton: false},
],
rows: [ // 行数据（插件原规定数据）
// [new Date('2019-07-20'), 70.10],
// [new Date('2019-07-21'), 70.60],
],
rowData:[ // 行数据（自定义常规数据）
// {time:new Date('2019-07-20'),amount:70.10}
]
},
callBack:null // 返回数据回调
}
var _excludeName = [].concat(defauls.excludeName);
$.extend(true,defauls,opts);
defauls.excludeName = defauls.excludeName.concat(_excludeName);
var workbook = new ExcelJS.Workbook();
workbook.properties.date1904 = true;
workbook.calcProperties.fullCalcOnLoad = true;
workbook.views = [{ x: 0, y: 0, width: 10000, height: 20000, firstSheet: 0, activeTab: 1, visibility: 'visible'}];
var worksheet = workbook.addWorksheet(defauls.fileName);
var opt = {
// 表格默认样式
tableCellStyle:{
border:{top: {style:'thin'},left: {style:'thin'}, bottom: {style:'thin'},right: {style:'thin'}},
alignment:{vertical: 'middle', horizontal: 'center'}
},
// 表头数据
header:[],
// numsToTitle备忘录提效
numsTitleCache: {},
// titleToTitle 备忘录提效
titleNumsCache: {},
// 模块数据统计
rowNumObj:{
title:{num:0,startNum:0},
tableBefore:{num:0,startNum:0},
table:{num:0,startNum:0},
tableHeard:{num:0,startNum:0},
tableAfter:{num:0,startNum:0}
},
rowNum:0,
colNum:0,
excludeIndex:[],
//将数字(从一开始)转换为 A、B、C...AA、AB
numToTitle: function(num) {
if (this.numsTitleCache[num]) {
return this.numsTitleCache[num];
}
var ans = '';
if (num > 26) {
// 要注意小心 26 的倍数导致的无限递归问题
var dec = num % 26;
ans = this.numToTitle((num - dec)/26) + this.numToTitle(dec?dec:26);
this.numsTitleCache[num] = ans;
this.titleNumsCache[ans] = num;
return ans;
} else {
// A 的 ascii 为 0，顺位相加
ans = String.fromCharCode(64 + num);
this.numsTitleCache[num] = ans;
this.titleNumsCache[ans] = num;
return ans;
}
},
// 将A、B、AA、ABC转换为 1、2、3形式的数字
titleToNum: function(title) {
if (this.titleNumsCache[title]) {
return this.titleNumsCache[title];
}
var len = title.length;
var total = 0;
for (var index in title) {
if (!title.hasOwnProperty(index)) {
continue;
}
var char = title[index];
var code = char.charCodeAt() - 64;
total += code * Math.pow(26, len - index - 1);
}
this.numsTitleCache[total] = title;
this.titleNumsCache[title] = total;
return total;
},
// 设置表格表头数据
setHeard:function(){
var _table = defauls.table;
var _columns = [];
var that = this;
if(_table.gridId){
var GridParam = $(defauls.contentWindow.document).find("#"+_table.gridId).jqGrid('getGridParam');
var _head = $.map(GridParam.colModel,function(n){ return !n.hidden ? n : null;});
$.each(_head,function(k,v){
v._name = v.name;
v.name = v.label;
$.each(_table.columns,function(dk,dv){
if(v._name == dv._name ){
$.extend(v,dv);
}
})
})
_table.columns = _head;
}
// 过滤
if(_table.columns.length){
$.each(_table.columns,function(k,v){
var flag = true;
v.style = that.tableCellStyle;
$.each(defauls.excludeName,function(ek,ev){
if(v._name == ev){ that.excludeIndex.push(k); flag = false; }
})
if(flag){_columns.push(v);}
})
}
// 设置列宽
$.each(_columns,function(k,v){
var dobCol = worksheet.getColumn(k+1);
dobCol.width = v.width / 10 || defauls.ColWidth;
})
// console.log(_columns)
that.header = _columns;
defauls.table.columns = _columns;
that.colNum = _columns.length;
},
// 设置大标题
setTitle:function(){
var that = this;
var _title = defauls.title;
// worksheet.spliceRows(1, 1, ['表头']);
if(_title.show){
var row = worksheet.addRow([_title.text]);
worksheet.mergeCells('A1:' + that.numToTitle(that.colNum) + 1);
var cell = worksheet.getCell('A1');
row.height = defauls.title.titleStyle.height;
$.extend(cell,defauls.title.titleStyle);
that.rowNumObj.title.num = 1;
that.rowNumObj.title.startNum = 1;
that.rowNum = 1
}
},
// 设置表格前数据
setTableBefore:function(){
this.setGridFn('tableBefore');
},
// 设置布局方法
setGridFn:function(moduleName){
var that = this;
var _tb = defauls[moduleName];
if(_tb.show) {
var _num = _tb.data.length;
$.each(_tb.data, function (k, v) {
var row = worksheet.getRow(k + that.rowNum + 1);
var space = parseInt(that.colNum / v.length);
row.height = _tb.textStyle.height;
var _rowNum = k + that.rowNum + 1;
var _surplus = that.colNum - v.length * space;
// 累加计算结束列
var _colNum = 1;
$.each(v, function (vk, vv) {
var _vNum = 0;
if (_surplus) {
_vNum = 1;
_surplus--;
}
var colNum_ = _colNum + space + _vNum - 1;
var cell = row.getCell(_colNum);
var _cellObj = vv.textStyle ? $.extend(true, {}, _tb.textStyle, vv.textStyle) : _tb.textStyle;
row.height = _cellObj.height;
$.extend(cell, {value: vv.text}, _cellObj);
worksheet.mergeCells(that.numToTitle(_colNum) + _rowNum + ':' + that.numToTitle(colNum_) + _rowNum);
_colNum = colNum_ + 1;
})
})
that.rowNumObj[moduleName].startNum = that.rowNum;
that.rowNumObj[moduleName].num = _num;
that.rowNum = _num + that.rowNum;
}
},
// 设置合并表头
setGroupHeaders:function(startRowNum){
var that = this;
var groupHeaders = defauls.groupHeaders;
var _headIndex = [];
var _headObjArr = {};
var _configKey = {};
$.each(that.header,function(k,v){
_headIndex.push('e'+k);
_headObjArr['e'+k] = [];
_configKey['e'+k] = v._name
})
if(groupHeaders && groupHeaders.length > 0){
var _k = 0;
$.each(groupHeaders,function(k,v){
var _rowText = {};
_k = startRowNum + k ;
var _i = 0;
var _group = v['groupHeaders'];
var _j = 0;
var _exclude = [];
worksheet.duplicateRow(_k,1,true);
for(var j = 0; j < _headIndex.length; j++){
var k2=j ,v2=_headIndex[j]; //a
// 通过groupHeader 进行横向（列）合并 生成复合数组_mergeConf
if(_group && _group[k2]){
var groupItem = _group[k2];
_i = groupItem.numberOfColumns
_j = k2;
var _name = ''
$.each(_configKey,function(n,s){
if(groupItem.startColumnName.includes(s)){
_name = n
}
})
var _index = $.inArray(_name, _headIndex);
worksheet.mergeCells(that.numToTitle(_index +1 ) + _k +
':'+ that.numToTitle(_index + groupItem.numberOfColumns) + _k);
worksheet.getCell(that.numToTitle(_index +1 ) + _k ).value = groupItem.titleText;
_rowText[_name] = groupItem.titleText
for(var k5 = _index ; k5 < _index + _i; k5++){
_exclude.push(_headIndex[k5])
}
}
// 排除横向（列）合并的name值， 生成纵向（行）合并
if($.inArray(v2 , _exclude) == -1){
if(_headObjArr[v2].length ){
_headObjArr[v2][1] = (that.numToTitle(k2 + 1) + _k)
}else{
_headObjArr[v2][0] = (that.numToTitle(k2 + 1) + _k)
}
}
}
})
// 纵向合并添加原有表头
$.each(_headIndex,function(k,v){
var _key = _k + 1;
if(_headObjArr[v].length ){
_headObjArr[v][1] = (that.numToTitle(k + 1) + _key);
}else{
_headObjArr[v][0] = (that.numToTitle(k + 1) + _key);
}
})
// 表头纵向合并
$.each(_headObjArr,function(k,v){
if(v && v.length > 1){
worksheet.mergeCells(v[0] + ':' + v[1]);
}
})
that.rowNum = that.rowNum + groupHeaders.length;
}
/*
$.each(groupHeaders,function(gk,gv){
var _rowNum = startRowNum + gk,
_row = {},
_num = 0,
_j = 0 ,
_group = gv['groupHeaders'],
_exclude = [];
// console.log(gk,gv)
worksheet.duplicateRow(_rowNum,1,true);
$.each(that.header,function(hk,kv){
var _colStartNum = hk;
if(_group ) {
$.each(_group, function (gk,gv) {
if(gv.startColumnName == kv.name){
var _num = gv.numberOfColumns;
worksheet.mergeCells(that.numToTitle(_colStartNum +1 ) + _rowNum +
':'+ that.numToTitle(_colStartNum + _num) + _rowNum);
worksheet.getCell(that.numToTitle(_colStartNum + 1) + _rowNum).value = gv.titleText;
}
})
}
})
})
*/
},
// 设置行合计方法
setRowMergerFn:function(data){
var that = this;
var table = defauls.table;
var rows = table.rows;
var heard = table.columns;
var _rows = [];
var length = rows.length;
var rowids = [];
$.each(rows,function(rk,rv){
rowids.push(rk);
var _col = {}
$.each(rv,function(ck,cv){
_col[heard[ck]['_name']] = cv
})
_rows.push(_col);
})
function flagFn(start,end,arr){
if(start && end){
var flag = true
$.each(arr,function(k,v){
if(start[v] != end[v]){
flag = false
}
})
return flag
}
}
var cellObj = {}
var deleteObj = {}
$.each(data, function(k,v){
var num = v.num;
var CellName = v.name;
cellObj[CellName] = {};
deleteObj[CellName] = {};
if(!num){
for (var i = 0; i < length; i++) {
var before = _rows[i];//从上到下获取一条信息
var rowSpanTaxCount = 1;//定义合并行数
for (var j = i + 1; j <= length; j++) {
//和上边的信息对比 如果值一样就合并行数+1 然后设置rowspan 让当前单元格隐藏
var end = _rows[j];
if (flagFn(before,end,v.gist)) {
rowSpanTaxCount++;
} else {
rowSpanTaxCount = 1;
break;
}
cellObj[CellName][v.name + "" + rowids[i]] = [i+1, rowSpanTaxCount];
deleteObj[CellName][v.name + "" + rowids[j]] = [j+1, rowSpanTaxCount];
}
}
}
else{
for (var i = 0; i < length; i++) {
for (j = i + 1; j <= length; j++) {
if(j%num != 0){
}
cellObj[CellName][v.name + "" + rowids[i]] = [i+1, num];
deleteObj[CellName][v.name + "" + rowids[j]] = [j+1, num];
}
}
}
})
$.each(cellObj,function(k,v){
$.each(deleteObj[k],function(dk,dv){
if(v[dk]){
delete v[dk]
}
})
})
return cellObj
},
// 设置行合并
setRowMerger:function(startRowNum){
var that = this;
var _heardNum = startRowNum;
var _rowMerger = defauls.rowMerger;
if(_rowMerger,_rowMerger.length){
var _rowMergerData = that.setRowMergerFn(_rowMerger);
$.each(that.header,function(k,v){
$.each(_rowMergerData,function(rk,rv){
if(v._name == rk ){
$.each(rv,function(jk,jv){
worksheet.mergeCells(that.numToTitle(k + 1) + (jv[0] + _heardNum) + ':'
+ that.numToTitle(k + 1) + (jv[0] + _heardNum + jv[1] - 1));
})
}
})
})
}
},
// 获取jqgrid 的数据
setRowData:function(){
var that = this;
var _table = defauls.table;
var _gridData = [];
var _gridRows = [];
var _tableRows = [];
if(_table.gridId){
// console.log($.jgrid,$(defauls.contentWindow.document).find("#"+_table.gridId).jqGrid())
var GridParam = $(defauls.contentWindow.document).find("#"+_table.gridId).jqGrid('getGridParam');
// console.log(GridParam)
_gridData = GridParam.data;
}
_gridData = _gridData.concat(_table.rowData);
// 所有行数据
$.each(that.header,function(k,v){
// 表头数据用以过滤
$.each(_gridData,function(gk,gv){
if(gv){
var _val = gv[v._name] ? gv[v._name] : '';
if(_gridRows[gk]){
_gridRows[gk].push(_val)
}else{
_gridRows[gk] = [_val];
}
}
})
})
// 根据过滤索引去除多余数据
if(that.excludeIndex.length){
$.each(_table.rows,function(rk,rv){
var _tableCols = [];
$.each(rv,function(ck,cv){
var flag = true;
$.each(that.excludeIndex,function(k,v){
if(ck == v){flag = false;}
})
if(flag){ _tableCols.push(cv); }
})
_tableRows.push(_tableCols);
})
_table.rows = _tableRows;
}
_table.rows = _table.rows.concat(_gridRows);
},
// 表格设置
setTable:function(){
var that = this;
var _table = defauls.table;
// 设置表格数据
that.setRowData();
var _num = 0 ;
if(_table.title){
_num = 1;
var titleRow = worksheet.getRow(that.rowNum + 1);
worksheet.mergeCells(that.numToTitle(1) + (that.rowNum + 1) + ':' + that.numToTitle(that.colNum) + (that.rowNum + 1));
var cell = titleRow.getCell(1);
var _cellObj = _table.titleStyle;
titleRow.height = _cellObj.height;
$.extend(cell,{value:_table.title},_cellObj);
}
_table.ref = 'A' + (that.rowNum + 1 + _num);
worksheet.addTable(_table);
var table = worksheet.getTable(_table.name);
that.setGroupHeaders(that.rowNum + 1 + _num);
that.setRowMerger(that.rowNum + 1 + _num);
that.rowNumObj.table.num = table.tableHeight;
that.rowNumObj.table.startNum = that.rowNum + 1 + _num;
that.rowNum = that.rowNum + table.tableHeight + _num;
},
// 设置表格后数据
setTableAfter:function(){
this.setGridFn('tableAfter');
},
// 设置图片
setImg:function(){
var that = this;
var _img = defauls.image;
if(_img.show){
$.each(_img.data,function(k,v){
var _num = 0;
var _colS = v.config.tl.col ? v.config.tl.col : v.config.tl.col + 1;
// 图片标题
if(v.title){
_num ++;
var titleRow = worksheet.getRow(that.rowNum + 1);
worksheet.mergeCells(that.numToTitle(_colS) + (that.rowNum + 1) + ':' + that.numToTitle(that.colNum) + (that.rowNum + 1));
var cell = titleRow.getCell(_colS);
var _cellObj = v.titleStyle ? $.extend(true,{},_img.titleStyle,v.titleStyle) : _img.titleStyle;
titleRow.height = _cellObj.height;
$.extend(cell,{value:v.title},_cellObj);
}
if(v.echartId){
// v.src.base64 = defauls.contentWindow.document.getElementById(v.echartId).toDataURL();
v.src.base64 =$(defauls.contentWindow.document).find("#"+v.echartId).find('canvas').get(0).toDataURL();
}
// 图片
worksheet.mergeCells(that.numToTitle(_colS) + (that.rowNum + 1 + _num) + ':' + that.numToTitle(that.colNum) + (that.rowNum + 1 + _num));
var row = worksheet.getRow(that.rowNum + 1 + _num);
row.height = v.config.ext.height;
var imageId = workbook.addImage(v.src);
v.config.tl.row = that.rowNum + _num ;
worksheet.addImage(imageId, v.config);
that.rowNum = that.rowNum + 1 + _num;
})
}
},
// 设置背景
setBackground:function(){
var that = this;
if(defauls.tableHeadColor){
// table:{num:0,startNum:0},
var gh = groupHeaders && groupHeaders.length ? groupHeaders.length : 0;
worksheet.addConditionalFormatting({
ref: 'A'+ (that.rowNumObj.table.startNum - gh) +':' + that.numToTitle(that.colNum) + (that.rowNumObj.table.startNum),
rules: [
{
type: 'expression',
// formulae: ['MOD(ROW()+COLUMN(),2)=0'],
formulae: ['1'],
style: {fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: defauls.tableHeadColor}}},
}
]
})
}
if(defauls.backgroundColor){
worksheet.addConditionalFormatting({
ref: 'A1:' + that.numToTitle(that.colNum) + (that.rowNum),
rules: [
{
type: 'expression',
// formulae: ['MOD(ROW()+COLUMN(),2)=0'],
formulae: ['1'],
style: {fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: defauls.backgroundColor}}},
}
]
})
}
},
// 下载pdf
downloadPDF:function(fileId,fileName,suffix){
var pdf = defauls.exportPdf;
if(pdf.show && pdf.downloadUrl){
if(!$.fileDownload){return;}
$.fileDownload(pdf.downloadUrl,{
httpMethod: 'post',
data: { "fileId":fileId,"fileName":fileName,"suffix":suffix},
prepareCallback:function(url){
layer.msg('下载开始，请稍等.....')
},
abortCallback:function(url){
layer.msg("文件下载异常",{icon:2});
},
successCallback:function(url){
layer.msg( "下载成功",{icon:1});
},
failCallback: function (html, url,error) {
layer.msg( '下载失败',{icon:2});
}
});
}
},
// 保存下载
saveAs:function(){
workbook.xlsx.writeBuffer().then((data)=>{
if(defauls.isExport){
var blob = new Blob([data], {type:'application/octet-stream'});
var name = defauls.fileName + '.'+ defauls.fileType;
saveAs(blob,name);
}
if(defauls.exportPdf.show && defauls.exportPdf.postUrl){
$.ajax({
url: defauls.exportPdf.postUrl,
contentType: 'application/octet-stream',
type: 'post',
// 设置的是请求参数
data: JSON.stringify(data),
// 用于设置响应体的类型 注意 跟 data 参数没关系！！！
// dataType: 'json',
success: function (res) {
if (res.success==true){
that.downloadPDF(res.data,defauls.fileName,defauls.exportPdf.fileType)
}
}
});
}
if($.isFunction(defauls.callBack)){
defauls.callBack(data);
}
})
},
init:function(){
this.setHeard()
this.setTitle();
this.setTableBefore();
this.setTable();
this.setTableAfter();
this.setImg()
this.setBackground();
this.saveAs()
}
}
opt.init();
}
