Table multiple table header in JSP export Excel file to implement _JSP programming

Source: Internet
Author: User

First, two copies of JS:copyhtmltoexcel.js and Tabletoexcel.js are introduced.

Copy Code code as follows:

/*
* The default conversion implementation function, if you need additional functionality, you have to expand
Parameters
* tableid:html value of Table object id attribute
* Detailed usage See the following 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: The Table object in HTML is converted to an Excel generic object.
* Author: Jeva
* Time: 2006-08-09
* Parameter: TableID the id attribute value of the Table object in HTML
Description
* Can adapt to the complex HTML in the Table object's automatic conversion, can automatically according to row and column extension information
* Merge the cells in Excel, the client needs to install Excel
* Detailed properties, method reference descriptions see: Microsoft Excel Visual Basic Reference for Excel
Model
* var TB = new Tabletoexcel (' demotable ');
* Tb.setfontstyle ("Courier New");
* Tb.setfontsize (10); Recommended value 10
* Tb.setfontcolor (6); Generally do not need to set
* Tb.setbackground (4); Generally do not need to set
* Tb.settableborder (2); Recommended value 2
* Tb.setcolumnwidth (10); Recommended value 10
* Tb.islinewrap (FALSE);
* Tb.isautofit (TRUE);
*
* Tb.getexcelfile ();
* Set cell width is invalid if cell adaptation is 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 desirable palette color number 1/2/3/4 ....
This.fontcolor = 1; Font Color: Black
This.fontsize = 10; Font size
This.fontstyle = "Song Body"; Font type
This.rowheight =-1; Row height
This.columnwidth =-1; Width
This.linewrap = true; Whether to wrap lines automatically
This.textalign =-4108; Content Alignment defaults to center
This.autofit = false; is 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.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\n" + E.message +
           \ n \ nplease If you are sure that Excel is already installed on your computer, "+
            "Please adjust the security level of IE." \ n specific action: \ n "+
          " Tools →internet options → security → custom Level → ActiveX controls and Plug-ins \ n "+
       → enable: Initialize and script ActiveX controls that are not marked as safe");
        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 row height, 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 a Table object that needs to be converted, get the corresponding row, column count
var obj = document.all.tags ("table");
for (x = 0; x < Obj.length + +) {
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 Widget 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 conversion 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 Code code as follows:

Eltalbeout This is the outer table for the exported content, mainly setting styles such as border, Eldiv is the entire exported HTML part
function Onhtmltoexcel (eltableout,eldiv) {
try{
Sets the data before the export, and returns the format after the export.
var eldivstrbak = eldiv.innerhtml;
Set the border=1 of the table so that there is a table line PS in Excel: Thanks for two-sided reminders
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 previous content of a format transformation
eldiv.innerhtml = Eldivstrbak;
Content data may 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 in the JSP page

Copy Code code as follows:

Build Excel
function Ontabletoexcel () {
var eltableout = document.getElementById ("Eltableout");
var eldiv = document.getElementById ("Eldiv");
Onhtmltoexcel (ELTABLEOUT,ELDIV);
}

Note: Use JS to export Excel need to set IE active. Note that the best is the IE8 browser. As for the implementation of Excel in the Java background has not been studied, I hope that the great God advice.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.