Home > Developer > JavaScript
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = UTF-8"/>
<Title> How to export an Excel document from a Web page </title>
</Head>
<Body>
<Table id = "tableexcel" width = "100%" border = "1" cellspacing = "0" cellpadding = "0">
<Tr>
<TD colspan = "5" align = "center"> How to export an Excel document from a Web page </TD>
</Tr>
<Tr>
<TD> Column Title 1 </TD>
<TD> Column Title 2 </TD>
<TD> Column Title 3 </TD>
<TD> Column Title 4 </TD>
<TD> Column Title 5 </TD>
</Tr>
<Tr>
<TD> AAA </TD>
<TD> BBB </TD>
<TD> CCC </TD>
<TD> DDD </TD>
<TD> EEE </TD>
</Tr>
<Tr>
<TD> AAA </TD>
<TD> BBB </TD>
<TD> CCC </TD>
<TD> DDD </TD>
<TD> EEE </TD>
</Tr>
<Tr>
<TD> fff </TD>
<TD> ggg </TD>
<TD> hhh </TD>
<TD> III </TD>
<TD> jjj </TD>
</Tr>
</Table>
<Input type = "button" onclick = "javascript: Method1 ('tableexcel ');" value = "method 1 import to excel">
<Input type = "button" onclick = "javascript: method2 ('tableexcel ');" value = "method 2 import to excel">
<Input type = "button" onclick = "javascript: getxlsfromtbl ('tableexcel ', null);" value = "method 3">
<Script language = "JavaScript">
Function Method1 (tableid) {// copy the entire table to excel
VaR curtbl = Document. getelementbyid (tableid );
VaR oxl = new activexobject ("Excel. application ");
// Create an ax object Excel
VaR owb = oxl. workbooks. Add ();
// Obtain the workbook object
VaR osheet = owb. activesheet;
// Activate the current sheet
VaR sel = Document. Body. createTextRange ();
Sel. movetoelementtext (curtbl );
// Move the table content to textrange
Sel. Select ();
// Select all content in textrange
Sel.exe ccommand ("copy ");
// Copy the content in textrange
Osheet. paste ();
// Paste it to the Excel file of the activity
Oxl. Visible = true;
// Set the Excel visible attribute
}
Function method2 (tableid) // read each unit in the table to excel.
{
VaR curtbl = Document. getelementbyid (tableid );
VaR oxl = new activexobject ("Excel. application ");
// Create an ax object Excel
VaR owb = oxl. workbooks. Add ();
// Obtain the workbook object
VaR osheet = owb. activesheet;
// Activate the current sheet
VaR lenr = curtbl. Rows. length;
// Obtain the number of rows in the table
For (I = 0; I <lenr; I ++)
{
VaR lenc = curtbl. Rows (I). cells. length;
// Obtain the number of columns in each row
For (j = 0; j <lenc; j ++)
{
Osheet. cells (I + 1, J + 1). value = curtbl. Rows (I). cells (j). innertext;
// Assign a value
}
}
Oxl. Visible = true;
// Set the Excel visible attribute
}
Function getxlsfromtbl (intblid, inwindow ){
Try {
VaR allstr = "";
VaR curstr = "";
// Alert ("getxlsfromtbl ");
If (intblid! = NULL & intblid! = "" & Intblid! = "Null "){
Curstr = gettbldata (intblid, inwindow );
}
If (curstr! = NULL ){
Allstr + = curstr;
}
Else {
Alert ("the table you want to export does not exist! ");
Return;
}
VaR filename = getexcelfilename ();
Dofileexport (filename, allstr );
}
Catch (e ){
Alert ("Export exception:" + E. Name + "->" + E. Description + "! ");
}
}
Function gettbldata (intbl, inwindow ){
VaR rows = 0;
// Alert ("gettbldata is" + inwindow );
VaR tbldocument = document;
If (!! Inwindow & inwindow! = ""){
If (! Document. All (inwindow )){
Return NULL;
}
Else {
Tbldocument = eval(inwindow).doc ument;
}
}
VaR curtbl = tbldocument. getelementbyid (intbl );
VaR outstr = "";
If (curtbl! = NULL ){
For (var j = 0; j <curtbl. Rows. length; j ++ ){
// Alert ("J is" + J );
For (VAR I = 0; I <curtbl. Rows [J]. cells. length; I ++ ){
// Alert ("I is" + I );
If (I = 0 & rows> 0 ){
Outstr + = "";
Rows-= 1;
}
Outstr + = curtbl. Rows [J]. cells [I]. innertext + "";
If (curtbl. Rows [J]. cells [I]. colspan> 1 ){
For (var k = 0; k <curtbl. Rows [J]. cells [I]. colspan-1; k ++ ){
Outstr + = "";
}
}
If (I = 0 ){
If (rows = 0 & curtbl. Rows [J]. cells [I]. rowspan> 1 ){
Rows = curtbl. Rows [J]. cells [I]. rowspan-1;
}
}
}
Outstr + = "";
}
}
Else {
Outstr = NULL;
Alert (intbl + "does not exist! ");
}
Return outstr;
}
Function getexcelfilename (){
VaR d = new date ();
VaR curyear = D. getyear ();
VaR curmonth = "" + (D. getmonth () + 1 );
VaR curdate = "" + D. getdate ();
VaR curhour = "" + D. gethours ();
VaR curminute = "" + D. getminutes ();
VaR cursecond = "" + D. getseconds ();
If (curmonth. Length = 1 ){
Curmonth = "0" + curmonth;
}
If (curdate. Length = 1 ){
Curdate = "0" + curdate;
}
If (curhour. Length = 1 ){
Curhour = "0" + curhour;
}
If (curminute. Length = 1 ){
Curminute = "0" + curminute;
}
If (cursecond. Length = 1 ){
Cursecond = "0" + cursecond;
}
VaR filename = "leo_zhang" + "_" + curyear + curmonth + curdate + "_"
+ Curhour + curminute + cursecond + ". CSV ";
// Alert (filename );
Return filename;
}
Function dofileexport (inname, instr ){
VaR xlswin = NULL;
If (!! Document. All ("glbhidefrm ")){
Xlswin = glbhidefrm;
}
Else {
VaR width = 6;
VaR Height = 4;
VaR openpara = "Left =" + (window. Screen. width/2-width/2)
+ ", Top =" + (window. Screen. Height/2-height/2)
+ ", Scrollbars = No, width =" + width + ", Height =" + height;
Xlswin = Window. Open ("", "_ blank", openpara );
}
Xlswin.doc ument. Write (instr );
Xlswin.doc ument. Close ();
Xlswin.document.exe ccommand ('saveas', true, inname );
Xlswin. Close ();
}
</SCRIPT>
</Body>
</Html>
--- The following is about how to close the Excel process ----------------------
// Structure Analysis in JavaScript (ActiveX Object example)
//---------------------------------------------------------
<SCRIPT>
VaR strsavelocation = 'file: // E:/1.xls'
Function createxls (){
VaR Excel = new activexobject ("Excel. application ");
VaR wk = excel. workbooks. Add ();
WK. saveas (strsavelocation );
WK. Saved = true;
Excel. Quit ();
}
Function writexls (){
VaR Excel = new activexobject ("Excel. application ");
VaR wk = excel. workbooks. Open (strsavelocation );
VaR sheet = wk. worksheets (1 );
Sheet. cells (1, 1). value = 'test string ';
WK. saveas (strsavelocation );
WK. Saved = true;
Excel. Quit ();
}
</SCRIPT>
<Body>
<Button onclick = "createxls ()"> Create </button>
<Button onclick = "writexls ()"> rewrite </button>
</Body>
In this example, there is no exception during local file operations. -- A maximum of memory buffers are available.
. However, if strsavelocation is a remote URL, a local
Only one (remote) instance can be used to enable the coexistence of the Excel file.
Storage. If you repeatedly click the "Override" button, an exception occurs.
-- Note that this is a simplification of an instance when operating shared files in SPSCode. Therefore, it is not
"Academic" is boring for discussion and practical problems in Engineering.
The solution to this problem is complex. It involves two problems:
-Release of local creden
-ActiveX object instance release
Next, let's start with the question of "invalidation" of objects in JavaScript. To put it simply:
-An object becomes invalid in addition to its living context.
-A Global object becomes invalid if it is not used (referenced.
For example:
//---------------------------------------------------------
// When the JavaScript Object expires
//---------------------------------------------------------
Function testobject (){
VaR _ obj1 = new object ();
}
Function testobject2 (){
VaR _ obj2 = new object ();
Return _ obj2;
}
// Example 1
Testobject ();
// Example 2
Testobject2 ()
// Example 3
VaR obj3 = testobject2 ();
Obj3 = NULL;
// Example 4
VaR obj4 = testobject2 ();
VaR arr = [obj4];
Obj3 = NULL;
Arr = [];
In these four examples:
-"Example 1" constructs _ obj1 in the testobject () function, but when the function exits,
It has left the context of the function, SO _ obj1 is invalid;
-In "Example 2", testobject2 () also constructs an object _ obj2 and transmits it out, because
This object has an external context (and lifecycle). However
The returned value is not "held" by other variables, SO _ obj2 also becomes invalid immediately;
-In Example 3, _ obj2 constructed by testobject2 () is used by the external variable obj3,
At this time, the _ obj2 will not be due to the reference relationship until the code in the "obj3 = NULL" line is valid.
It disappears and becomes invalid.
-For the same reason as Example 3, _ obj2 in Example 4 will be in the "arr = []" line of code.
And then it becomes invalid.
However, the "invalid" of the object will not be "released ". In the Javascript runtime environment, no
There is any way to tell the user exactly when the object will be released ". This depends on JavaScript
Memory recovery mechanism. -- This policy is similar to the recycling mechanism in. net.
In the previous Excel operation sample code, the object owner is the process of "Excel. EXE ".
It can only happen after "Release of ActiveX object instance. File locks and operations
The system permission credential is related to the process. Therefore, if the object is "invalid" rather than "released ",
When other processes process files and reference operating system permission creden。, a problem occurs.
-- Some people say this is a JavaScript or com mechanism bug. Actually not. This is OS, ie
And JavaScript.
Microsoft published a policy to solve this problem: actively calling the memory recycle process.
A collectgarbage () process (GC process) is provided in (Microsoft's) JScript ),
The GC process is used to clear the "invalid object loss Examples" in the current IE, that is, the destructor of the called object.
In the above example, the code for calling the GC process is:
//---------------------------------------------------------
// Standard GC call method for processing ActiveX objects
//---------------------------------------------------------
Function writexls (){
// (Omitted ...)
Excel. Quit ();
Excel = NULL;
SetTimeout (collectgarbage, 1 );
}
The first line of code calls the Excel. Quit () method to stop and exit the Excel process.
The environment has an Excel object instance, so the Excel process does not actually stop.
The second line of code sets EXCEL to null to clear object references and invalidate the object ". However
The object is still in the function context. Therefore, if the GC process is called directly, the object will not be cleared.
The third line of code uses setTimeout () to call the collectgarbage function. The interval is set to '1', only
The GC process occurs after the writexls () function is executed. In this way, the EXCEL object can be
Two conditions for GC cleaning: no reference and exit from the context.
The use of GC is very effective in the JS environment where ActiveX object is used. Some potential ActiveX
Objects include XML, VML, owc (Office Web componet), Flash, and even vbarray in Js.
From this point of view, the Ajax architecture adopts XMLHTTP and must meet the requirement of "no page switching"
Feature, so actively calling the GC process when appropriate will get a better UI experience of efficiency.
In fact, even if the GC process is used, the Excel problem mentioned above will not be completely solved. Because IE also
Permission creden are cached. The only way to update the page's permission creden is to "switch to a new page ",
In fact, in the SPS project mentioned above, the method I used is not GC, but the following
Segment code:
//---------------------------------------------------------
// Page switching code used to process ActiveX objects
//---------------------------------------------------------
Function writexls (){
// (Omitted ...)
Excel. Quit ();
Excel = NULL;
// The following code is used to solve an IE call Excel bug. the method provided in msdn:
// SetTimeout (collectgarbage, 1 );
// Because the trusted status of (or synchronous) web pages cannot be cleared, methods such as saveas () are
// It is invalid for the next call.
Location. Reload ();
}
Finally, a supplementary description about GC: When the IE form is minimized, ie will actively call
Collectgarbage () function. This makes the memory usage significantly improved after the IE window is minimized.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
