Detailed explanation of JavaScript export to Excel examples_javascript skills

The example in this article describes how to export Excel using JavaScript. Share it with everyone for your reference. The specific implementation method is as follows:

Copy code The code is as follows:

How to export WEB pages to EXCEL documents
Column header 1	Column header 2	Column header 3	Column header 4	Column header 5
aaa	bbb	ccc	ddd	eee
AAA	BBB	CCC	DDD	EEE
FFF	GGG	HHH	III	JJJ

下面是处理excel 进程关闭问题

复制代码 代码如下:

// JavaScript中的析构问题(ActiveX Object示例)
//---------------------------------------------------------
<script><br> var strSaveLocation = 'file:///E:/1.xls'<br> function createXLS() {<br>   var excel = new ActiveXObject("Excel.Application");<br>   var wk = excel.Workbooks.Add();<br>   wk.SaveAs(strSaveLocation);<br>   wk.Saved = true; <p>  excel.Quit();<br> }</p> <p>function writeXLS() {<br>   var excel = new ActiveXObject("Excel.Application");<br>   var wk = excel.Workbooks.Open(strSaveLocation);<br>   var sheet = wk.Worksheets(1);<br>   sheet.Cells(1, 1).Value = '测试字符串';<br>   wk.SaveAs(strSaveLocation);<br>   wk.Saved = true;</p> <p>  excel.Quit();<br> }<br> </script>

  创建
  重写

在这个例子中，在本地文件操作时并不会出现异常。——最多只是有一些内存垃圾而已。然而，如果strSaveLocation是一个远程的URL，这时本地将会保存一个文件存取权限的凭证，而且同时只能一个(远程的)实例来开启该excel文档并存储。于是如果反复点击"重写"按钮，就会出现异常。

——注意，这是在SPS中操作共享文件时的一个实例的简化代码。因此，它并非“学术的”无聊讨论，而且工程中的实际问题。

解决这个问题的方法很复杂。它涉及到两个问题：
① 本地凭证的释放
② ActiveX Object实例的释放

下面我们先从JavaScript中对象的“失效”问题说起。简单的说：
① 一个对象在其生存的上下文环境之外，即会失效。
② 一个全局的对象在没有被执用(引用)的情况下，即会失效。

例如：

复制代码 代码如下:

//--------------------------------------------- ------------
//When does a JavaScript object expire
//------------------------------------------------ ----------
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 function testObject(), but when the function exits, it has left the context of the function, so _obj1 is invalid;
- In "Example 2", an object _obj2 is also constructed in testObject2() and passed out, so the object has an "outside function" context (and life cycle). However, since the return value of the function is not used by other variables " Hold", so _obj2 is also immediately invalid;
- In "Example 3", _obj2 constructed by testObject2() is held by the external variable obj3. At this time, until the "obj3=null" line of code takes effect, _obj2 will become invalid because the reference relationship disappears.
- For the same reason as Example 3, _obj2 in "Example 4" will become invalid after the "arr=[]" line of code.

However, the "invalidation" of an object does not wait until it is "released". Within the JavaScript runtime environment, there is no way to tell the user exactly when an object will be released. This relies on JavaScript's memory recycling mechanism. ——This strategy is similar to the recycling mechanism in .NET.

In the previous Excel operation sample code, the owner of the object, that is, the process of "EXCEL.EXE" can only occur after the "release of the ActiveX Object instance". File locks and operating system permission credentials are process-related. So if the object is merely "invalidated" rather than "released", there will be problems for other processes handling the file and referencing the operating system's permission credentials.

——Some people say this is a BUG in JavaScript or COM mechanism. Actually no, this is caused by a complex relationship between OS, IE and JavaScript, rather than an independent problem.

Microsoft has disclosed a strategy to solve this problem: actively calling the memory recycling process.

A CollectGarbage() process (usually referred to as the GC process) is provided in (Microsoft) JScript. The GC process is used to clean up the "invalid object exceptions" in the current IE, that is, to call the object's destructor process.

The code to call the GC process in the above example is:

Copy code The code is as follows:

//---------------- ----------------------------------------
// When dealing with ActiveX Object, the standard calling method of the GC process
//------------------------------------------------ ----------
function writeXLS() {
//(omitted...)

excel.Quit();
excel = null;
setTimeout(CollectGarbage, 1);
}

The first line of code calls the excel.Quit() method to cause the excel process to terminate and exit. At this time, because the JavaScript environment holds an excel object instance, the excel process does not actually terminate.

The second line of code makes excel null to clear the object reference, thereby "invalidating" the object. However, since the object is still in the function context, if the GC process is called directly, the object will still not be cleaned up.

The third line of code uses setTimeout() to call the CollectGarbage function, and the time interval is set to '1', which only causes the GC process to occur after the writeXLS() function is executed. In this way, the excel object meets the two conditions of "can be cleaned by GC": no reference and leaving the context.

The use of GC process is very effective in JS environment using ActiveX Object. Some potential ActiveX Objects include XML, VML, OWC (Office Web Componet), flash, and even VBArray in JS.

From this point of view, since the ajax architecture adopts XMLHTTP and must meet the "no page switching" feature, actively calling the GC process at the appropriate time will result in a better efficient UI experience.

In fact, even if the GC process is used, the excel problem mentioned above will still not be completely solved. Because IE also caches the permission credentials. The only way to update the page's permission credentials is to "switch to a new page", so in fact in the SPS project mentioned earlier, the method I used was not GC, but the following code:

Copy code The code is as follows:

//--------------------------------------------- ------------
// Page switching code used when processing ActiveX Object
//------------------------------------------------ ----------
function writeXLS() {
//(omitted...)

excel.Quit();
excel = null;

// The following code is used to solve a BUG in IE call Excel, the method provided in MSDN:
// setTimeout(CollectGarbage, 1);
// Since the trusted status of the web page cannot be cleared (or synchronized), methods such as SaveAs() will be used in
// It will be invalid the next time it is called.
location.reload();
}

Finally, a supplementary note about GC: when the IE form is minimized, IE will actively call it once
CollectGarbage() function. This allows the memory usage to be significantly improved after the IE window is minimized.

I hope this article will be helpful to everyone’s web programming based on JavaScript.