table in Jsp multi table header export excel file concrete implementation
- 2020-11-25 07:28:29
- OfStack
First introduce two copies of JS: copyhtmltoexcel.js and tableToExcel.js
/*
* The default transformation implementation function should be extended if additional functionality is required
* Parameters:
* tableID : HTML In the Table object id Attribute values
* See below for detailed usage TableToExcel Object definitions
*/
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: HTML In the Table Object converted to Excel Generic object .
* The author: Jeva
* Time: 2006-08-09
* Parameters: tableID HTML In the Table The object's ID Attribute values
* Description:
* Able to adapt to complexity HTML In the Table Automatic transformation of objects, which automatically extends information based on rows and columns
* merge Excel The cell in which the client needs to have installed Excel
* For detailed property and method references, see: Excel the Microsoft Excel Visual Basic reference
* Demonstration:
* var tb = new TableToExcel('demoTable');
* tb.setFontStyle("Courier New");
* tb.setFontSize(10); // The recommended values 10
* tb.setFontColor(6); //1 Do not set it in general
* tb.setBackGround(4); //1 Do not set it in general
* tb.setTableBorder(2); // The recommended values 2
* tb.setColumnWidth(10); // The recommended values 10
* tb.isLineWrap(false);
* tb.isAutoFit(true);
*
* tb.getExcelFile();
* If cell adaptation is set, the cell width is not set
* Version: 1.0
*/
function TableToExcel(tableID) {
this.tableBorder = -1; // Border type, -1 No border desirable 1/2/3/4
this.backGround = 0; // Background color: white Color numbering in the palette is desirable 1/2/3/4....
this.fontColor = 1; // Font color: black
this.fontSize = 10; // The font size
this.fontStyle = " Song typeface "; // Font type
this.rowHeight = -1; // Line height
this.columnWidth = -1; // Column width
this.lineWrap = true; // Whether to wrap lines automatically
this.textAlign = -4108; // Content alignment The default is center
this.autoFit = false; // Adaptive width or not
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(" Unable to start Excel!\n\n" + e.message +
"\n\n If you are sure it is already installed on your computer Excel . "+
" So please adjust IE Security level. \n\n Specific operation: \n\n"+
" tool - Internet options - security - Custom level - ActiveX Controls and plug-ins \n\n" +
" - To enable the : No, it's not marked safe ActiveX Control to initialize and run the script ");
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 ;
// The search requires conversion Table Object to get the corresponding number of 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 the 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 tables
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 This is the outer table of the exported content, mainly Settings border Something like that, elDiv It's the whole export html Part of the
function onhtmlToExcel(elTableOut,elDiv){
try{
// Sets the data before the export and the format for the return after the export
var elDivStrBak = elDiv.innerHTML;
// Set up the table the border=1 So that to excel I have the line in the table 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.execCommand("Copy");
// Returns the content before the format change
elDiv.innerHTML = elDivStrBak;
// The content data can be large, so empty
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 the JS method on the JSP page
// generate Excel
function onTableToExcel(){
var elTableOut = document.getElementById("elTableOut");
var elDiv = document.getElementById("elDiv");
onhtmlToExcel(elTableOut,elDiv);
}
Note: Exporting excel using JS requires setting active for IE. Note that the best is the IE8 browser. As for the implementation of excel in the background of java has not been studied, I hope you can advise.