This article describes the method of exporting Excel by JavaScript. Share it for your reference. The specific implementation method is as follows:
The code copy is as follows: <html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>How to export WEB pages as EXCEL documents</title>
</head>
<body>
<table id="tableExcel" cellpacing="0" cellpadding="0">
<tr>
<td colspan="5" align="center">How to export WEB pages as EXCEL documents</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>ee</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="The first method is imported to EXCEL">
<input type="button" onclick="javascript:method2('tableExcel');" value="The second method is imported to EXCEL">
<input type="button" onclick="javascript:getXlsFromTbl('tableExcel',null);" value="The third method is imported to EXCEL">
<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 AX object excel
var oWB = oXL.Workbooks.Add();
//Get the workbook object
var oSheet = oWB.ActiveSheet;
//Activate the current sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//Move the contents in the table to TextRange
sel.select();
//Select all the contents in TextRange
sel.execCommand("Copy");
//Copy the content in TextRange
oSheet.Paste();
//Paste into the active EXCEL
oXL.Visible = true;
//Set Excel visible attribute
}
function method2(tableid) //Read each cell in the table into EXCEL
{
var curTbl = document.getElementByIdx_x_x(tableid);
var oXL = new ActiveXObject("Excel.Application");
//Create AX object excel
var oWB = oXL.Workbooks.Add();
//Get the workbook object
var oSheet = oWB.ActiveSheet;
//Activate the current sheet
var Lenr = curTbl.rows.length;
//Get the number of rows in the table
for (i = 0; i < Lenr; i++)
{
var Lenc = curTbl.rows(i).cells.length;
//Get the number of columns per row
for (j = 0; j < Lenc; j++)
{
oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;
//Assignment
}
}
oXL.Visible = true;
//Set 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("Exception occurred in export:" + 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).document;
}
}
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 + "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.document.write(inStr);
xlsWin.document.close();
xlsWin.document.execCommand('Saveas', true, inName);
xlsWin.close();
}
</SCRIPT>
</body>
</html>
The following is the problem of dealing with excel process closure
Copy the code as follows:// Destruction Problems 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, no exception occurs when operating the local file. --At most, there is just some memory garbage. However, if strSaveLocation is a remote URL, a local file access credential will be saved, and only one (remote) instance can be used to enable the excel document and store it. So if you click the "Rewrite" button repeatedly, an exception will appear.
- Note that this is a simplified code for an instance of a shared file when operating in SPS. Therefore, it is not a "academic" boring discussion, and it is a practical problem in engineering.
The solution to this problem is complicated. It involves two issues:
① Release of local vouchers
② Release of ActiveX Object instance
Let's start with the "failure" problem of objects in JavaScript. Simply put:
① An object will be invalid outside the context in which it lives.
② A global object will be invalid if it is not executed (referenced).
For example:
Copy the code as follows: //------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
// When will JavaScript object fail
//---------------------------------------------------------
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 function's context, so _obj1 is invalid;
- In "Example 2", an object _obj2 is also constructed in testObject2() and passed out, so the object has a context environment (and lifetime) "outside the function", but since the return value of the function is not "held" by other variables, _obj2 is immediately invalid;
- In "Example 3", the _obj2 constructed by testObject2() is held by the external variable obj3. At this time, until the line of code "obj3=null" takes effect, _obj2 will not be invalid because the reference relationship disappears.
- For the same reason as in Example 3, _obj2 in "Example 4" will not be invalid after the line of code "arr=[]".
However, the "failure" of the object does not wait to be "released". Inside the JavaScript runtime, there is no way to tell the user exactly "when will the object be released". This depends on JavaScript's memory recovery mechanism. ―This strategy is similar to the recycling mechanism in .NET.
In the previous Excel operation example code, the owner of the object, that is, "EXCEL.EXE", will only occur after "release of ActiveX Object instance". The file locks and the operating system's permission credentials are related to the process. So if the object is only "failed" and not "release", then other processes will have problems when processing files and referring to the operating system's permission credentials.
- Some people say this is a bug in JavaScript or COM mechanism. Actually, it is not. This is caused by a complex relationship between OS, IE and JavaScript, rather than an independent problem.
Microsoft has exposed a strategy to solve this problem: proactively call 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 "failed object misalignment" in the current IE, that is, the destructor process of calling the object.
In the above example, the code that calls the GC procedure is:
Copy the code as follows: //------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Standard calling method of GC procedures when processing ActiveX Object
//---------------------------------------------------------
function writeXLS() {
//(slightly...)
excel.Quit();
excel = null;
setTimeout(CollectGarbage, 1);
}
The first line of code calls the excel.Quit() method to make the excel process abort and exit. At this time, since the JavaScript environment has an excel object instance, the excel process does not actually abort.
The second line of code makes excel null to clear object references, thus "invalidating" the object. However, since the object is still in the function context, if the GC procedure is called directly, the object will still not be cleaned.
The third line of code uses setTimeout() to call the CollectGarbage function, and the time interval is set to '1', which only makes the GC process happen after the writeXLS() function is executed. In this way, the excel object meets two conditions of "can be cleaned up by GC": no reference and leaving the context.
The use of GC procedures is very effective in JS environments 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, because the ajax architecture adopts XMLHTTP and also needs to meet the "no page switching" feature, actively calling the GC process at appropriate times will give you a better efficiency experience using the UI.
In fact, even with the GC process, the aforementioned excel problem will not be completely solved. Because IE also caches permission credentials. The only way to make the page's permission credentials be updated 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 the code as follows: //------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Page switching code used when processing ActiveX Object
//---------------------------------------------------------
function writeXLS() {
//(slightly...)
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 state of the web page cannot be cleared (or synchronized) will cause SaveAs() and other methods to be
// Invalid next time you call it.
location.reload();
}
Finally, a supplementary note about GC: When the IE form is minimized, IE will actively call it once.
CollectGarbage() function. This makes the memory usage significantly improve after the IE window is minimized.
I hope that the description in this article will be helpful to everyone's web programming based on javascript.