Export an excel file from multiple table headers in Jsp

First, we will introduce two JavaScript files: copyhtmltoexcel. JS and tableToExcel. js.

/** Default conversion implementation function. If you need other functions, you need to expand the * parameter: * tableID: for details about the Table Object id attribute value * in HTML, see TableToExcel object definition */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 ();}/** function: convert a Table object in HTML to a common Excel object. * Author: Jeva * Time: 2006-08-09 * parameter: Tab in tableID HTML * Description: * it can adapt to the automatic conversion of Table objects in complicated HTML, and automatically merge cells in Excel according to row and column extension information, the client must have an Excel * detailed attribute and method references. For details, see Microsoft Excel Visual Basic in Excel. * example: * var tb = new TableToExcel ('demotable'); * tb. setFontStyle ("Courier New"); * tb. setFontSize (10); // the recommended value is 10 * tb. setFontColor (6); // you do not need to set * tb in general. setBackGround (4); // you do not need to set * tb in general. setTableBorder (2); // the recommended value is 2 * tb. setColumnWidth (10); // the recommended value is 10 * tb. isLineWrap (False); * tb. isAutoFit (true); ** tb. getExcelFile (); * If cell adaptability is set, the cell width setting is invalid * version: 1.0 */function TableToExcel (tableID) {this. tableBorder =-1; // border type,-1 no border can be 1/2/3/4 this. backGround = 0; // backGround color: The color number in the white color palette is 1/2/3/4 .... this. fontColor = 1; // font color: black this. fontSize = 10; // font size this. fontStyle = ""; // font type this. rowHeight =-1; // The Row Height. this. columnWidth =-1; // column width this. lineWrap = true; // whether To Wrap thi automatically S. textAlign =-4108; // The content alignment is centered by default. this. autoFit = false; // whether the adaptive width 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. isLin EWrap = 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;} // file conversion main function TableToExcel. prototype. getExcelFile = function () {var jXls, myWorkbook, myWo Rksheet, myHTMLTableCell, myExcelCell, myExcelCell2; var myCellColSpan, myCellRowSpan; try {jXls = new ActiveXObject ('excel. application');} catch (e) {alert ("cannot start Excel! \ N "+ e. message +" \ n if you are sure that Excel is installed on your computer, "+" adjust the security level of IE. \ N specific operations: \ n "+" Tools → Internet Options → Security → Custom Level → ActiveX controls and ins \ n "+" → enabled: initialize ActiveX controls that are not marked as secure and run scripts "); 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; // set the Row Height and column width if (this. columnWidth! =-1) myWorksheet. Columns. ColumnWidth = this. columnWidth; else myWorksheet. Columns. ColumnWidth = 7; if (this. rowHeight! =-1) myWorksheet. rows. rowHeight = this. rowHeight; // search for the Table object to be converted and obtain the corresponding row and column 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 ;}} // Start Component Simulation Table var excelTable = new Array (); for (I = 0; I <= totalRow; I ++) {excelTable [I] = new Array (); for (t = 0; t <= totalCol; t ++) {excelTable [I] [t] = false ;}} // start to convert the table 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. colS Pan; 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. horizon TalAlignment = 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. fon T. 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 is the outer table of the exported content. It mainly sets border and other styles. elDiv is the function onhtmlToExcel (elTableOut, elDiv) of the exported html part) {try {// set the data before export. Set var elDivStrBak = elDiv for the format returned after export. innerHTML; // set the table's border = 1, so that there is a table line in the excel. ps: thanks to the double-sided reminder elTableOut. border = 1; // filter elDiv content var elDivStr = elDiv. innerHTML; elDivStr = replaceHtml (elDivStr, ""); elDivStr = replaceHtml (elDivStr ,""); ElDiv. innerHTML = elDivStr; var oRangeRef = document. body. createTextRange (); oRangeRef. moveToElementText (elDiv extends orangeref.exe cCommand ("Copy"); // The returned content of the previous elDiv format conversion. innerHTML = elDivStrBak; // The content data may be large. Therefore, elDivStrBak = ""; elDivStr = ""; var oXL = new ActiveXObject ("Excel. application ") var oWB = oXL. workbooks. add; var oSheet = oWB. activeSheet; oSheet. paste (); oSheet. cells. numberFormatLocal = "@"; oSheet. col Umns ("D: D "). selectoXL. selection. columnWidth = 20oXL. 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 ;}

Write JS method in JSP page // generate Excel
Function onTableToExcel (){
Var elTableOut = document. getElementById ("elTableOut ");
Var elDiv = document. getElementById ("elDiv ");
OnhtmlToExcel (elTableOut, elDiv );
}

 

Note: To use JS to export an excel file, you must set the IE active state. The best choice is the Internet Explorer 8. As for the implementation of excel in the java background, I hope you will give me some advice.