How to export an excel file from multiple table headers in Jsp

First, we will introduce two JavaScript files: copyhtmltoexcel. JS and tableToExcel. js.
Copy codeThe Code is as follows:
/*
* The default conversion function is used. If you need other functions, you need to expand them.
* Parameters:
* TableID: attribute value of the Table Object id in HTML
* For detailed usage, see TableToExcel object definition below
*/
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: ID attribute value of the Table object in tableID HTML
* Note:
* It can adapt to the automatic conversion of Table objects in complex HTML and automatically expand information based on the row and column.
* Merge the cells in the Excel file. The client must have an Excel file installed.
* For detailed attribute and method references, see Microsoft Excel Visual Basic Reference 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 it in general.
* Tb. setBackGround (4); // you do not need to set it in general.
* Tb. 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.
* Release: 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 automatically
This. textAlign =-4108; // The content alignment mode is centered by default.
This. autoFit = false; // whether the adaptive width is used
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;
}

// File conversion Main Function
TableToExcel. prototype. getExcelFile = function (){
Var jXls, myWorkbook, myWorksheet, 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 has been installed on your computer," +
"Adjust the security level of IE. \ N specific operation: \ n "+
"Tools → Internet Options → Security → Custom Level → ActiveX controls and ins \ n" +
"→ Enable: Initialize and Run ActiveX controls that are not marked as secure ");
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 rows and columns
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 converting 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. 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

Copy codeThe Code is as follows:
// ElTalbeOut is the outer table of the exported content. It mainly sets border and other styles. elDiv is the entire html part of the exported content.
Function onhtmlToExcel (elTableOut, elDiv ){
Try {
// Set the data before export to the format returned after export.
Var elDivStrBak = elDiv. innerHTML;
// Set border = 1 for table, so that there is a table line in excel. ps: Thanks for the double-sided reminder.
ElTableOut. border = 1;
// Filter elDiv content
Var elDivStr = elDiv. innerHTML;
ElDivStr = replaceHtml (elDivStr, "<A", "> ");
ElDivStr = replaceHtml (elDivStr, "</A", "> ");
ElDiv. innerHTML = elDivStr;

Var oRangeRef = document. body. createTextRange ();
ORangeRef. moveToElementText (elDiv );
ORangeRef.exe cCommand ("Copy ");

// Return the content before the format change
ElDiv. innerHTML = elDivStrBak;
// The content data may be large, so leave it blank
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;
}

Write JS method in JSP page
Copy codeThe Code is as follows:
// Generate an Excel file
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.