自动合并多行列标题-CSDN博客

function Co_Export(formObj,sSingleCmd, sCtrlId,nPos) {

var vscmd = sSingleCmd.substring(nPos + 9,sSingleCmd.length);

vscmd = ScanStr(formObj,vscmd,0,2,0,0);

var ywbdid=vscmd.substring(1,vscmd.length-1);//获取后台传入的业务表单编号

/**

* 声明属性

var btmc="";//标题名称

var year="";//标题名称

/**

* 获取动态加载值,导出excel

var oXL = new ActiveXObject("Excel.Application"); //创建excel应用程序对象

var oWB = oXL.Workbooks.Add(); //创建工作簿

var oSheet = oWB.ActiveSheet; //获取当前活动的工作簿

oSheet.Rows("1:30").ColumnWidth =18;

var table = document.all.myTaskTable; //获取当前页面中的表格

//标题设置

oSheet.Cells(1,3).RowHeight=40;

oSheet.Columns('A:E').ColumnWidth = 15;

//提取对应导出表单的表头信息

$.post("exportAction_getCfg.do",{id:ywbdid},

function(data){

var str=data[0].lbm.split(";");

var liecell;

for(var ii=0;ii<str.length;ii++){

var strs=str[ii].split(",");

oSheet.Cells(1,1).Font.Bold = true;//加粗

var a=String.fromCharCode(65+strs.length-1);//ASCII 值的提取获得对应字符，0对应的ASCII值是A所以用strs.length-1

if(strs.length-1>25){//25对应的ASCII值是Z ，如果超过Z

a="A"+String.fromCharCode(65+strs.length-1-25);

}

oXL.Range("A"+(ii+2)+":"+a+'3').Font.Bold = true;//加粗

oXL.Range("A1:"+a+'1').MergeCells = true;//合并

oSheet.Cells(1,strs.length).HorizontalAlignment = 7;//居中

oSheet.Cells(1,1).Font.Size = 20;

oSheet.Cells(2,1).Font.Size = 12;

oSheet.Cells(1,1).Value = data[0].xznf+" "+data[0].bt;//标题

var hangcell;

var hang2cell;

var currentcell;

var n;

for(var i=0;i<strs.length;i++){

currentcell=strs[i];

oSheet.Cells(2+ii,i+1).Value = strs[i];

var hq=String.fromCharCode(65+i);//合并的前cell

debugger;

if(i>25){//25对应的ASCII值是Z,超过Z

for(var j=0;j<(i-25);j++){

hq="A"+String.fromCharCode(65+j);

}

var hh=String.fromCharCode(65+(i+1));//合并的当前cell

if((i+1)>25){//25对应的ASCII值是Z

for(var j=0;j<(i+1)-25;j++){

hh="A"+String.fromCharCode(65+j);

}

var qiancurrentcell;

var houcurrentcell;

if(ii>0){

qiancurrentcell=str[ii-1].split(",")[(i-1)];

houcurrentcell=str[ii-1].split(",")[(i+1)];

}

if(currentcell!=qiancurrentcell && currentcell!=houcurrentcell){//判断当前单元格与左上角单元格值是否相同与右上角

if(hangcell==currentcell){//针对每行的处理，如果上个单元格与当前单元格内容相同，择执行合并

hq=String.fromCharCode(65+(i-1));//合并的前cell

if((i-1)>25){//25对应的ASCII值是Z

for(var j=0;j<(i-1)-25;j++){

hq="A"+String.fromCharCode(65+j);

}

hh=String.fromCharCode(65+i);//合并的当前cell

if(i>25){//25对应的ASCII值是Z

for(var j=0;j<i-25;j++){

hh="A"+String.fromCharCode(65+j);

}

var hbys=hq+(ii+2)+":"+hh+(ii+2);//组合的元素，示例："A2:B2"

//oSheet.Cells(i,(i+1)).HorizontalAlignment =3;//居中

oXL.Range(hbys).MergeCells = true;//合并

}

hangcell=strs[i];

if(ii<str.length-1){

var strs2=str[ii+1].split(",");

var strs21=str[ii].split(",")[(i+1)];

hang2cell=strs2[i];

if(hang2cell!=strs21){//判断当前单元格与右上角单元格不同

if(hang2cell==hangcell){//如果第二行的列与第一行的列单元格值相同，择执行合并

var lq=hq+(ii+2);

var lh=hq+(ii+(2+1));//默认是从第三行进行处理

var lbys=lq+":"+lh;//组合的元素，示例："A2:A3"

oXL.Range(lbys).MergeCells = true;//合并

n=hang2cell;

}

else{

oSheet.Cells(2+(ii+1),i+1).Value = strs2[i];

}

//数据动态填充

$.ajax({

type:"post",

url:"exportAction_exportData.do?id="+ywbdid+"",

dataType:"json",

async: false,

success:function(data){

var start=str.length+2;

var ki=0;

for(ki;ki<data.length;ki++){

var h=ki;

var o=1;

//获取json 的键

for(var j in data[h]){

//通过键值赋值

oSheet.Cells(start,o).Value = data[h][j];

//oSheet.Cells(i+(str.length),o).Value = data[h][j];

o++;

}

start++;

}

error:function(){

alert('暂无数据！');

}

});

oXL.Visible = true; //设置Excel的属性

oXL.UserControl = true;

},"json");

}

webform.CoReg('Co_Export', Co_Export);