js导出table中的EXCEL总结
一、js导出EXCEl带单元格合并【已验证,比较好用】
- // JavaScript Document
- //调用方法
- // var test=new PageToExcel("data",0,255,"测试.xls");//table id , 第几行开始,最后一行颜色 ,保存的文件名
- // test.CreateExcel(false);
- // test.Exec();
- // test.SaveAs();
- // test.CloseExcel();
- //LastRowColor 0黑色 255红色
- //
- function PageToExcel(TableID,FirstRow,LastRowColor,SaveAsName){
- this.lastRowColor=LastRowColor==""?0:LastRowColor;
- var today=new Date();
- this.saveAsName=(SaveAsName==""?today.getYear()+"年"+(today.getMonth()+1)+"月"+today.getDate()+"日.xls":SaveAsName);
- this.tableId=TableID;
- this.table=document.getElementById(this.tableId);//导出的table 对象
- this.rows=this.table.rows.length;//导出的table总行数
- this.colSumCols=this.table.rows[0].cells.length;//第一行总列数
- this.fromrow=FirstRow;
- this.beginCol=0; //起始列数
- this.cols=this.colSumCols;
- this.oXL=null;
- this.oWB=null;
- this.oSheet=null;
- this.rowSpans=1; //行合并
- this.colSpans=1; //列合并
- this.colsName={0:"A",1:"B", 2:"C", 3:"D", 4:"E", 5:"F", 6:"G", 7:"H", 8:"I",9:"J", 10:"K", 11:"L", 12:"M", 13:"N", 14:"O", 15:"P", 16:"Q", 16:"R" ,18:"S", 19:"T", 20:"U", 21:"V", 22:"W", 23:"X", 24:"Y", 25:"Z"};
- }
- PageToExcel.prototype.DeleteExcelCols=function(NotShowColList){//数组NotShowColList
- //this.notShowColList=NotShowColList;//不显示列集合,1,2,3,1
- //删除excel中的列
- var m=0;
- for(var i=0;i<NotShowColList.length;i++){
- if(i>0){
- m++;
- }
- var temp=NotShowColList[i]- m;
- var index=this.colsName[temp];
- this.oSheet.Columns(index).Delete;//删除
- }
- m=0;
- }
- PageToExcel.prototype.CreateExcel=function(ExcelVisible)
- {
- try{
- this.oXL = new ActiveXObject("Excel.Application"); //创建应该对象
- this.oXL.Visible = ExcelVisible;
- this.oWB = this.oXL .Workbooks.Add();//新建一个Excel工作簿
- this.oSheet = this.oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表
- //不显示网格线
- this.oXL.ActiveWindow.DisplayGridlines=false;
- }catch(e){
- alert("请确认安装了非绿色版本的excel!"+e.description);
- CloseExcel();
- }
- }
- PageToExcel.prototype.CloseExcel=function()
- {
- this.oXL.DisplayAlerts = false;
- this.oXL.Quit();
- this.oXL = null;
- this.oWB=null;
- this.oSheet=null;
- }
- PageToExcel.prototype.ChangeElementToLabel=function (ElementObj){
- var GetText="";
- try{
- var childres=ElementObj.childNodes;
- }catch(e){ return GetText}
- if(childres.length<=0) return GetText;
- for(var i=0;i<childres.length;i++){
- try{if(childres[i].style.display=="none"||childres[i].type.toLowerCase()=="hidden"){continue;}}
- catch(e){}
- try{
- switch (childres[i].nodeName.toLowerCase()){
- case "#text" :
- GetText +=childres[i].nodeValue ;
- break;
- case "br" :
- GetText +="\n";
- break;
- case "img" :
- GetText +="";
- break;
- case "select" :
- GetText +=childres[i].options[childres[i].selectedIndex].innerText ;
- break;
- case "input" :
- if(childres[i].type.toLowerCase()=="submit"||childres[i].type.toLowerCase()=="button"){
- GetText +="";
- }else if(childres[i].type.toLowerCase()=="textarea"){
- GetText +=childres[i].innerText;
- }else{
- GetText +=childres[i].value;
- }
- break;
- default :
- GetText += this.ChangeElementToLabel(childres[i]);
- break;
- }
- }catch(e){}
- }
- return GetText;
- }
- PageToExcel.prototype.SaveAs=function (){
- //保存
- try{
- this.oXL.Visible =true;
- var fname = this.oXL.Application.GetSaveAsFilename(this.saveAsName, "Excel Spreadsheets (*.xls), *.xls");
- if(fname){
- this.oWB.SaveAs(fname);
- this.oXL.Visible =false;
- }
- }catch(e){};
- }
- PageToExcel.prototype.Exec=function()
- {
- //寻找列数,考虑到第一行可能存在
- for (var i=0; i<this.colSumCols;i++) {
- var tmpcolspan = this.table.rows(0).cells(i).colSpan;
- if ( tmpcolspan>1 ) {
- this.cols += tmpcolspan-1;
- }
- }
- //定义2维容器数据,1:行;2:列;值(0 可以填充,1 已被填充)
- var container=new Array(this.rows);
- for (var i=0;i<this.rows;i++) {
- container[i]=new Array(this.cols);
- for (j=0;j<this.cols;j++) {
- container[i][j]=0;
- }
- }
- //将所有单元置为文本,避免非数字列被自动变成科学计数法和丢失前缀的0
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).NumberFormat = "@";
- // 循环行
- for (i=0;i<this.rows;i++){
- //循环列
- for (j=0;j<this.cols;j++){
- //寻找开始列
- for (k=j;k<this.cols;k++){
- if (container[i][k]==0) {
- this.beginCol=k;
- k=this.cols; //退出循环
- }
- }
- //try{
- //赋值
- //此处相应跟改 根据 标签的类型,替换相关参数
- this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1).value = this.ChangeElementToLabel(this.table.rows(i).cells(j));
- //计算合并列
- try{
- this.colSpans = this.table.rows(i).cells(j).colSpan;
- }catch(e){
- this.colSpans=0
- }
- if (this.colSpans>1) {
- //合并
- this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+1+this.fromrow,this.beginCol+this.colSpans)).Merge();
- }
- //将当前table位置填写到对应的容器中
- for (k=0; k<this.colSpans;k++) {
- container[i][this.beginCol+k]= 1;
- }
- // 计算合并行
- try{
- this.rowSpans = this.table.rows(i).cells(j).rowSpan;
- }catch(e){
- this.rowSpans = 0;
- }
- if (this.rowSpans>1) { //行合并
- this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+this.rowSpans+this.fromrow,this.beginCol+this.colSpans)).Merge();
- //将当前table位置填写到对应的容器中
- for (k=1; k<this.rowSpans;k++) { //由于第0行已经被colSpans对应的代码填充了,故这里从第1行开始
- for (l=0;l<this.colSpans;l++) {
- container[i+k][this.beginCol+l]=1;
- }
- }
- }
- //如果开始列+合并列已经等于列数了,故不需要再循环html table
- if (this.beginCol+this.colSpans>=this.cols) j=this.cols;
- }
- if(i==0)
- {
- //标题栏
- this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Size=20;
- this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Bold = true;
- this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).HorizontalAlignment = -4108; //居中
- this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Rows.RowHeight = 40;
- }
- //自动调整行高
- }
- //最后一行是否空色
- try{
- this.oSheet.Range(this.oSheet.Cells(this.rows,1), this.oSheet.Cells(this.rows,1)).Font.Color=this.lastRowColor;
- }catch(e){}
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Rows.RowHeight=20;
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Font.Size=10;
- //自动换行
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).WrapText = true;
- //自动调整列宽
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Columns.AutoFit();
- //点虚线
- this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Borders.LineStyle = -4118;
- return this.rows;
- }
注意:要改IE浏览器安全设置
二、js导出table中的EXCEL.该方法只能在IE内核下运行,相比其他方法的好处是,不
用再设置什么属性或者安装什么插件了,思路如下:
- function getXlsFromTbl(inTblId, inWindow) {
- try {
- var allStr = "";
- var curStr = "";
- //alert("getXlsFromTbl");
- if (inTblId != null && inTblId != "" && inTblId != "null") {
- curStr = getTblData(inTblId, inWindow);
- }
- if (curStr != null) {
- allStr += curStr;
- }
- else {
- alert("你要导出的表不存在!");
- return;
- }
- var fileName = getExcelFileName();
- doFileExport(fileName, allStr);
- }
- catch(e) {
- alert("导出发生异常:" + e.name + "->" + e.description + "!");
- }
- }
- function getTblData(inTbl, inWindow) {
- var rows = 0;
- //alert("getTblData is " + inWindow);
- var tblDocument = document;
- if (!!inWindow && inWindow != "") {
- if (!document.all(inWindow)) {
- return null;
- }
- else {
- tblDocument = eval(inWindow).document;
- }
- }
- var curTbl = tblDocument.getElementById(inTbl);
- var outStr = "";
- if (curTbl != null) {
- for (var j = 0; j < curTbl.rows.length; j++) {
- for (var i = 0; i < curTbl.rows[j].cells.length; i++) {
- if (i == 0 && rows > 0) {
- outStr += " \t";
- rows -= 1;
- }
- outStr += curTbl.rows[j].cells[i].innerText + "\t";
- if (curTbl.rows[j].cells[i].colSpan > 1) {
- for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) {
- outStr += " \t";
- }
- }
- if (i == 0) {
- if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {
- rows = curTbl.rows[j].cells[i].rowSpan - 1;
- }
- }
- }
- outStr += "\r\n";
- }
- }
- else {
- outStr = null;
- alert(inTbl + "不存在!");
- }
- return outStr;
- }
- function getExcelFileName() {
- var d = new Date();
- var curYear = d.getYear();
- var curMonth = "" + (d.getMonth() + 1);
- var curDate = "" + d.getDate();
- var curHour = "" + d.getHours();
- var curMinute = "" + d.getMinutes();
- var curSecond = "" + d.getSeconds();
- if (curMonth.length == 1) {
- curMonth = "0" + curMonth;
- }
- if (curDate.length == 1) {
- curDate = "0" + curDate;
- }
- if (curHour.length == 1) {
- curHour = "0" + curHour;
- }
- if (curMinute.length == 1) {
- curMinute = "0" + curMinute;
- }
- if (curSecond.length == 1) {
- curSecond = "0" + curSecond;
- }
- var fileName = "91zaojia" + "_" + curYear + curMonth + curDate + "_"
- + curHour + curMinute + curSecond + ".xls";
- return fileName;
- }
- function doFileExport(inName, inStr) {
- var xlsWin = null;
- if (!!document.all("glbHideFrm")) {
- xlsWin = glbHideFrm;
- }
- else {
- var width = 6;
- var height = 4;
- var openPara = "left=" + (window.screen.width / 2 - width / 2)
- + ",top=" + (window.screen.height / 2 - height / 2)
- + ",scrollbars=no,width=" + width + ",height=" + height;
- xlsWin = window.open("", "_blank", openPara);
- }
- xlsWin.document.write(inStr);
- xlsWin.document.close();
- xlsWin.document.execCommand('Saveas', true, inName);
- xlsWin.close();
- }
改代码已经验证,可以使用。 调用很简单,直接用就可以
οnclick="getXlsFromTbl('functionclickExcel',null);就可以了!