Home > Developer > JavaScript
Examples of exporting Excel using JavaScript, and exporting excel using javascript
This document describes how to export an Excel file using JavaScript. Share it with you for your reference. The specific implementation method is as follows:
Copy codeThe Code is as follows: <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. getElementByIdx_x_x (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. getElementByIdx_x_x (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_r(inWindow).doc ument;
}
}
Var curTbl = tbldocument. getElementByIdx_x_x (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 describes how to close the excel process.
Copy codeThe Code is as follows: // destructor 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 some memory spam are available. However, if strSaveLocation is a remote URL, A File Access Credential is saved locally, and only one (remote) instance can be used to open and store the excel file. If you repeatedly click the "Override" button, an exception occurs.
-- Note that this is the simplified code of an instance when operating shared files in SPS. Therefore, it is not a "academic" boring discussion, and practical problems in Engineering.
The solution to this problem is complex. It involves two problems:
① Release of local creden①
② Release an ActiveX Object instance
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.
② If a global object is not executed (referenced), it will become invalid.
For example:
Copy codeThe Code is as follows ://---------------------------------------------------------
// 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 it leaves the context of the function when the function exits, SO _ obj1 becomes invalid;
-In "Example 2", testObject2 () also constructs an object _ obj2 and transmits it out. Therefore, the object has an "out-of-function" Context Environment (and lifecycle ), however, because the return value of the function is not "held" by other variables, _ obj2 also becomes invalid immediately;
-In Example 3, The _ obj2 constructed by testObject2 () is used by the external variable obj3 until the code of "obj3 = null" takes effect, _ obj2 will expire because the reference link disappears.
-For the same reason as Example 3, _ obj2 in "Example 4" will expire after "arr =.
However, the "invalid" of the object will not be "released ". In the JavaScript runtime environment, there is no way to tell the user exactly when the object will be released ". This depends on the memory collection mechanism of JavaScript. -- This policy is similar to the recycling mechanism in. NET.
In the previous Excel operation sample code, the Object owner, that is, the process "EXCEL. EXE", can only occur after the "ActiveX Object instance is released. File locks and operating system permission creden。 are related to processes. Therefore, if the object is "invalid" rather than "released", other processes may encounter problems when processing files and referencing permission creden。 of the operating system.
-- Some people say this is a JavaScript or COM mechanism BUG. Actually not. This is caused by a complex relationship between OS, IE, and JavaScript, rather than independent issues.
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 "invalid object loss Examples" in IE ", that is, the destructor of the called object.
In the above example, the code for calling the GC process is:
Copy codeThe Code is as follows ://---------------------------------------------------------
// 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 abort and exit the excel process. At this time, the excel process is not actually aborted because the JavaScript environment has an excel object instance.
The second line of code sets excel to null to clear object references and invalidate the object ". However, because the object is still in the context of the function, 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', but the GC process occurs after the writeXLS () function is executed. In this way, the excel object meets the two conditions of "GC cleanup": 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, because the ajax architecture adopts XMLHTTP and must meet the "Do not switch pages" feature, it actively calls the GC process when appropriate, it will get a better efficiency with the UI experience.
In fact, even if the GC process is used, the excel problem mentioned above will not be completely solved. Because IE also caches permission creden. The only way to update page permission creden is to "switch to a new page". Therefore, in the SPS project mentioned above, the method I used is not GC, but the following code:
Copy codeThe Code is as follows ://---------------------------------------------------------
// 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.
I hope this article will help you with javascript-based web programming.
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
