aaaaa.rar_JavaScript Excel_Table_table 导出excel_导出excel

function tableToExcel(){ var rowNum = 0; var oXL = new ActiveXObject("Excel.Application"); //创建应该对象 var oWB = oXL.Workbooks.Add();//新建一个Excel工作簿 var oSheet = oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表 // var table = pageId; // var rows = table.rows.length; // var colSumCols = table.rows(0).cells.length; var beginCol = 0; // var cols = colSumCols; var rowSpans=1; //行合并 var colSpans=1; //列合并 oXL.visible = true; /**********表头**********************/ // var excRange = oSheet.Range(oSheet.Cells(1,1),oSheet.Cells(1,7)); // excRange.Merge(true); // excRange.value="流量水位数据"; // excRange.HorizontalAlignment = 3;//'居中 // excRange.Font.Name = "黑体"; // rowNum = rowNum+1; /****************结束结果设置******************/ //---------富营养化评价结果--------- var obj1 = document.getElementById('mytable1'); rowNum = resultToExcel(rowNum, obj1, oSheet, "流量水位数据"); rowNum = rowNum+2; } function resultToExcel(rowNum, obj, oSheet, tableTile){ if(obj==null){ return rowNum; } var rows = obj.rows.length; var colSumCols = obj.rows(0).cells.length; var beginCol = 0; var cols = colSumCols; var rowSpans=1; //行合并 var colSpans=1; //列合并 excRange = oSheet.Range(oSheet.Cells(rowNum+1,1),oSheet.Cells(rowNum+1,colSumCols)); excRange.Merge(true); excRange.value=tableTile; excRange.HorizontalAlignment = 3; excRange.Font.Name = "黑体"; rowNum = rowNum+1; //寻找列数，考虑到第一行可能存在合并 for (var i=0; i<colSumCols;i++) { var tmpcolspan = obj.rows(0).cells(i).colSpan; if ( tmpcolspan>1 ) { cols += tmpcolspan-1; } } //定义2维容器数据，1：行；2：列；值（0 可以填充，1 已被填充） var container=new Array(rows); for (var i=0;i<rows;i++) { container[i]=new Array(cols); for (j=0;j<cols;j++) { container[i][j]=0; } } //将所有单元置为文本，避免非数字列被自动变成科学计数法和丢失前缀的0 oSheet.Range(oSheet.Cells(rowNum+1,1), oSheet.Cells(rowNum+rows,cols)).NumberFormat = "@"; // Add obj headers going cell by cell. for (i=0;i<rows;i++){ for (j=0;j<cols;j++){ //寻找开始列 for (k=j;k<cols;k++){ if (container[i][k]==0) { beginCol=k; k=cols; //退出循环 } } //赋值 try{ oSheet.Cells(i+1+rowNum,beginCol+1).value = obj.rows(i).cells(j).innerText; colSpans = obj.rows(i).cells(j).colSpan; if (colSpans>1) { //合并 oSheet.Range(oSheet.Cells(i+1+rowNum,beginCol+1), oSheet.Cells(i+1+rowNum,beginCol+colSpans)).Merge(); } //将当前obj位置填写到对应的容器中 for (k=0; k<colSpans;k++) { container[i][beginCol+k]= 1; } rowSpans = obj.rows(i).cells(j).rowSpan; if (rowSpans>1) { //行合并 oSheet.Range(oSheet.Cells(i+1+rowNum,beginCol+1), oSheet.Cells(i+rowSpans+rowNum,beginCol+colSpans)).Merge(); //将当前obj位置填写到对应的容器中 for (k=1; k<rowSpans;k++) { //由于第0行已经被colSpans对应的代码填充了，故这里从第1行开始 for (l=0;l<colSpans;l++) { container[i+k][beginCol+l]=1; } } } //如果开始列＋合并列已经等于列数了，故不需要再循环html table if (beginCol+colSpans>=cols) j=cols; }catch(e){ } } } //字符大小为9 oSheet.Range(oSheet.Cells(rowNum+1,1), oSheet.Cells(rowNum+rows,cols)).Font.Size=9; //自动调整列宽 oSheet.Range(oSheet.Cells(1,1), oSheet.Cells(rowNum+rows,cols)).Columns.AutoFit(); //自动调整行高 oSheet.Range(oSheet.Cells(rowNum+1,1), oSheet.Cells(rowNum+rows,cols)).Rows.AutoFit(); //划边框 oSheet.Range(oSheet.Cells(rowNum+1,1), oSheet.Cells(rowNum+rows,cols)).Borders.LineStyle = 1; rowNum = rowNum +rows; return rowNum; }