這篇文章主要介紹了Jsp中的table多表頭導出excel文件具體實現，有需要的朋友可以參考一下
首先引入兩份JS:copyhtmltoexcel.js以及 tableToExcel.js 代碼如下: /* * 默認轉換實現函數，如果需要其他功能，需自行擴展 * 參數： * tableID : HTML中Table對象id屬性值 * 詳細用法參見以下 TableToExcel 對象定義 */ function saveAsExcel(tableID){ var tb = new TableToExcel(tableID); tb.setFontStyle("Courier New"); tb.setFontSize(10); tb.setTableBorder(2); tb.setColumnWidth(7); tb.isLineWrap(false); tb.isAutoFit(true); tb.getExcelFile(); } /* * 功能：HTML中Table對象轉換為Excel通用對象. * 作者：Jeva * 時間：2006-08-09 * 參數：tableID HTML中Table對象的ID屬性值 * 說明： * 能適應復雜的HTML中Table對象的自動轉換，能夠自動根據行列擴展信息 * 合並Excel中的單元格，客戶端需要安裝有Excel * 詳細的屬性、方法引用說明參見：Excel的Microsoft Excel Visual Basic參考 * 示范： * var tb = new TableToExcel('demoTable'); * tb.setFontStyle("Courier New"); * tb.setFontSize(10); //推薦取值10 * tb.setFontColor(6); //一般情況不用設置 * tb.setBackGround(4); //一般情況不用設置 * tb.setTableBorder(2); //推薦取值2 * tb.setColumnWidth(10); //推薦取值10 * tb.isLineWrap(false); * tb.isAutoFit(true); * * tb.getExcelFile(); * 如果設置了單元格自適應，則設置單元格寬度無效 * 版本：1.0 */ function TableToExcel(tableID) { this.tableBorder = -1; //邊框類型，-1沒有邊框 可取1/2/3/4 this.backGround = 0; //背景顏色：白色 可取調色板中的顏色編號 1/2/3/4.... this.fontColor = 1; //字體顏色：黑色 this.fontSize = 10; //字體大小 this.fontStyle = "宋體"; //字體類型 this.rowHeight = -1; //行高 this.columnWidth = -1; //列寬 this.lineWrap = true; //是否自動換行 this.textAlign = -4108; //內容對齊方式 默認為居中 this.autoFit = false; //是否自適應寬度 this.tableID = tableID; } TableToExcel.prototype.setTableBorder = function (excelBorder) { this.tableBorder = excelBorder ; }; TableToExcel.prototype.setBackGround = function (excelColor) { this.backGround = excelColor; }; TableToExcel.prototype.setFontColor = function (excelColor) { this.fontColor = excelColor; }; TableToExcel.prototype.setFontSize = function (excelFontSize) { this.fontSize = excelFontSize; }; TableToExcel.prototype.setFontStyle = function (excelFont) { this.fontStyle = excelFont; }; TableToExcel.prototype.setRowHeight = function (excelRowHeight) { this.rowHeight = excelRowHeight; }; TableToExcel.prototype.setColumnWidth = function (excelColumnWidth) { this.columnWidth = excelColumnWidth; }; TableToExcel.prototype.isLineWrap = function (lineWrap) { if (lineWrap == false || lineWrap == true) { this.lineWrap = lineWrap; } }; TableToExcel.prototype.setTextAlign = function (textAlign) { this.textAlign = textAlign; }; TableToExcel.prototype.isAutoFit = function(autoFit){ if(autoFit == true || autoFit == false) this.autoFit = autoFit ; } //文件轉換主函數 TableToExcel.prototype.getExcelFile = function () { var jXls, myWorkbook, myWorksheet, myHTMLTableCell, myExcelCell, myExcelCell2; var myCellColSpan, myCellRowSpan; try { jXls = new ActiveXObject('Excel.Application'); } catch (e) { alert("無法啟動Excel!nn" + e.message + "nn如果您確信您的電腦中已經安裝了Excel，"+ "那麼請調整IE的安全級別。nn具體操作：nn"+ "工具 → Internet選項 → 安全 → 自定義級別 → ActiveX控件和插件 nn" + "→ 啟用 : 對沒有標記為安全的ActiveX控件進行初始化和腳本運行"); return false; } jXls.Visible = true; myWorkbook = jXls.Workbooks.Add(); jXls.DisplayAlerts = false; myWorkbook.Worksheets(3).Delete(); myWorkbook.Worksheets(2).Delete(); jXls.DisplayAlerts = true; myWorksheet = myWorkbook.ActiveSheet; var readRow = 0, readCol = 0; var totalRow = 0, totalCol = 0; var tabNum = 0; //設置行高、列寬 if(this.columnWidth != -1) myWorksheet.Columns.ColumnWidth = this.columnWidth; else myWorksheet.Columns.ColumnWidth = 7; if(this.rowHeight != -1) myWorksheet.Rows.RowHeight = this.rowHeight ; //搜索需要轉換的Table對象，獲取對應行、列數 var obj = document.all.tags("table"); for (x = 0; x < obj.length; x++) { if (obj[x].id == this.tableID) { tabNum = x; totalRow = obj[x].rows.length; for (i = 0; i < obj[x].rows[0].cells.length; i++) { myHTMLTableCell = obj[x].rows(0).cells(i); myCellColSpan = myHTMLTableCell.colSpan; totalCol = totalCol + myCellColSpan; } } } //開始構件模擬表格 var excelTable = new Array(); for (i = 0; i <= totalRow; i++) { excelTable[i] = new Array(); for (t = 0; t <= totalCol; t++) { excelTable[i][t] = false; } } //開始轉換表格 for (z = 0; z < obj[tabNum].rows.length; z++) { readRow = z + 1; readCol = 1; for (c = 0; c < obj[tabNum].rows(z).cells.length; c++) { myHTMLTableCell = obj[tabNum].rows(z).cells(c); myCellColSpan = myHTMLTableCell.colSpan; myCellRowSpan = myHTMLTableCell.rowSpan; for (y = 1; y <= totalCol; y++) { if (excelTable[readRow][y] == false) { readCol = y; break; } } if (myCellColSpan * myCellRowSpan > 1) { myExcelCell = myWorksheet.Cells(readRow, readCol); myExcelCell2 = myWorksheet.Cells(readRow + myCellRowSpan - 1, readCol + myCellColSpan - 1); myWorksheet.Range(myExcelCell, myExcelCell2).Merge(); myExcelCell.HorizontalAlignment = this.textAlign; myExcelCell.Font.Size = this.fontSize; myExcelCell.Font.Name = this.fontStyle; myExcelCell.wrapText = this.lineWrap; myExcelCell.Interior.ColorIndex = this.backGround; myExcelCell.Font.ColorIndex = this.fontColor; if(this.tableBorder != -1){ myWorksheet.Range(myExcelCell, myExcelCell2).Borders(1).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(2).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(3).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(4).Weight = this.tableBorder ; } myExcelCell.Value = myHTMLTableCell.innerText; for (row = readRow; row <= myCellRowSpan + readRow - 1; row++) { for (col = readCol; col <= myCellColSpan + readCol - 1; col++) { excelTable[row][col] = true; } } readCol = readCol + myCellColSpan; } else { myExcelCell = myWorksheet.Cells(readRow, readCol); myExcelCell.Value = myHTMLTableCell.innerText; myExcelCell.HorizontalAlignment = this.textAlign; myExcelCell.Font.Size = this.fontSize; myExcelCell.Font.Name = this.fontStyle; myExcelCell.wrapText = this.lineWrap; myExcelCell.Interior.ColorIndex = this.backGround; myExcelCell.Font.ColorIndex = this.fontColor; if(this.tableBorder != -1){ myExcelCell.Borders(1).Weight = this.tableBorder ; myExcelCell.Borders(2).Weight = this.tableBorder ; myExcelCell.Borders(3).Weight = this.tableBorder ; myExcelCell.Borders(4).Weight = this.tableBorder ; } excelTable[readRow][readCol] = true; readCol = readCol + 1; } } } if(this.autoFit == true) myWorksheet.Columns.AutoFit; jXls.UserControl = true; jXls = null; myWorkbook = null; myWorksheet = null; }; copyhtmltoexcel.js 代碼如下: //elTalbeOut 這個為導出內容的外層表格，主要是設置border之類的樣式，elDiv則是整個導出的html部分 function onhtmlToExcel(elTableOut,elDiv){ try{ //設置導出前的數據，為導出後返回格式而設置 var elDivStrBak = elDiv.innerHTML; //設置table的border=1，這樣到excel中就有表格線 ps:感謝雙面提醒 elTableOut.border=1; //過濾elDiv內容 var elDivStr = elDiv.innerHTML; elDivStr = replaceHtml(elDivStr,"<A",">"); elDivStr = replaceHtml(elDivStr,"</A",">"); elDiv.innerHTML=elDivStr; var oRangeRef = document.body.createTextRange(); oRangeRef.moveToElementText( elDiv ); oRangeRef.execCommand("Copy"); //返回格式變換以前的內容 elDiv.innerHTML = elDivStrBak; //內容數據可能很大，所以賦空 elDivStrBak = ""; elDivStr = ""; var oXL = new ActiveXObject("Excel.Application") var oWB = oXL.Workbooks.Add ; var oSheet = oWB.ActiveSheet ; oSheet.Paste(); oSheet.Cells.NumberFormatLocal = "@"; oSheet.Columns("D:D").Select oXL.Selection.ColumnWidth = 20 oXL.Visible = true; oSheet = null; oWB = null; appExcel = null; }catch(e){ alert(e.description) } } function replaceHtml(replacedStr,repStr,endStr){ var replacedStrF = ""; var replacedStrB = ""; var repStrIndex = replacedStr.indexOf(repStr); while(repStrIndex != -1){ replacedStrF = replacedStr.substring(0,repStrIndex); replacedStrB = replacedStr.substring(repStrIndex,replacedStr.length); replacedStrB = replacedStrB.substring(replacedStrB.indexOf(endStr)+1,replacedStrB.length); replacedStr = replacedStrF + replacedStrB; repStrIndex = replacedStr.indexOf(repStr); } return replacedStr; } 在JSP頁面中寫JS方法 代碼如下: //生成Excel function onTableToExcel(){ var elTableOut = document.getElementById("elTableOut"); var elDiv = document.getElementById("elDiv"); onhtmlToExcel(elTableOut,elDiv); } 注意事項：使用JS導出excel需要設置IE的active。注意最佳的是IE8浏覽器。至於在java後台實現excel還沒去研究，希望各位大神指教。
