Of Stack

JavaScript export Excel example details

  • 2020-03-30 04:22:51
  • OfStack

This article illustrates how to export Excel using JavaScript. Share with you for your reference. The specific implementation method is as follows: 

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

<title>WEB Page export as EXCEL Method of documentation </title>

</head>

<body>

<table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0">

<tr>

<td colspan="5" align="center">WEB Page export as EXCEL Method of documentation </td>

</tr>

<tr>

<td> Column headings 1</td>

<td> Column headings 2</td>

<td> Column headings 3</td>

<td> Column headings 4</td>

<td> Column headings 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=" The first method is imported into EXCEL">

<input type="button" onclick="javascript:method2('tableExcel');" value=" The second method is imported into EXCEL">

<input type="button" onclick="javascript:getXlsFromTbl('tableExcel',null);" value=" The third method is imported into EXCEL">

<SCRIPT LANGUAGE="javascript">

function method1(tableid) {//Copy the entire table to EXCEL < br / >
var curTbl = document.getElementByIdx_x_x(tableid);

var oXL = new ActiveXObject("Excel.Application");

//Create the AX object excel

var oWB = oXL.Workbooks.Add();

//Gets the workbook object 

var oSheet = oWB.ActiveSheet;

//Activate the current sheet

var sel = document.body.createTextRange();

sel.moveToElementText(curTbl);

//Move the contents of the table to 
 in TextRange
sel.select();

//Select 
 from TextRange
sel.execCommand("Copy");

//Copy TextRange 

oSheet.Paste();

//Paste into the active EXCEL 

oXL.Visible = true;

//Set the excel visibility property 

}

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 the AX object excel

var oWB = oXL.Workbooks.Add();

//Gets the workbook object 

var oSheet = oWB.ActiveSheet;

//Activate the current sheet

var Lenr = curTbl.rows.length;

//Gets the number of rows in the table 

for (i = 0; i < Lenr; i++)

{

var Lenc = curTbl.rows(i).cells.length;

//Gets 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 < br / >
}

}

oXL.Visible = true;

//Set the excel visibility property 

}

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 abnormal :" + 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 + " There is no !");

}

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>

Here's how to handle the excel process shutdown problem 

//Destruct problem 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 for local file operations. At most, there is only some memory garbage. However, if strSaveLocation is a remote URL, a certificate of file access rights will be saved locally, and only one (remote) instance will be available to open and store the excel document. So if you hit the rewrite button again and again, you get an exception.

Note that this is simplified code for an instance of operating on Shared files in SPS. Therefore, it is not "academic" boring discussion, but practical problems in engineering.

The solution to this problem is very complicated. It involves two questions:
The release of local credentials
ActiveX Object instance release

Let's start with the "invalidation" of objects in JavaScript. Simply put:
An object will become invalid outside the context in which it lives.
(2) a global object will be invalidated if it is not held (referenced).

Such as: 

//---------------------------------------------------------

//When a JavaScript object expires 

//---------------------------------------------------------

function testObject() {

  var _obj1 = new Object();

}
function testObject2() {

  var _obj2 = new Object();

  return _obj2;

}
//Example 1 < br / >
testObject();
//Example 2 < br / >
testObject2()
//Example 3 < br / >
var obj3 = testObject2();

obj3 = null;
//Example 4 < br / >
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 leaves the context of the function, so _obj1 fails;
- in "example 2", an object _obj2 is also constructed in testObject2() and passed out, so that the object has an "out-of-function" context (and lifetime), but since the return value of the function is not "held" by other variables, _obj2 is immediately invalidated;
- in example 3, the _obj2 constructed by testObject2() is held by the external variable obj3 until the line "obj3=null" takes effect.
- for the same reason as in example 3, _obj2 in "example 4" does not fail until after the "arr=[]" line.

However, "invalidation" of an object does not wait for "release." Inside the JavaScript runtime environment, there is no way to tell the user exactly when the object will be released. This relies on JavaScript's memory recovery mechanism. This strategy is similar to the recovery mechanism in.net.

In the previous Excel action sample code, the owner of the Object, "excel.exe," process occurs only after the release of the ActiveX Object instance. The lock of the file, and the permissions of the operating system, are process-related. So if the object is only "invalidated" rather than "freed," other processes have problems working with files and referencing the operating system's credentials.

Some people say this is a BUG in JavaScript or COM mechanics. No, it's a complex relationship between OS, IE, and JavaScript, not a separate problem.

Microsoft has disclosed a strategy to address this problem: actively call the memory recovery process.

A CollectGarbage () procedure (commonly referred to as the GC procedure) is provided in (Microsoft's) JScript, which is used to clean up the "dead object garbage" in current IE, that is, the destructor procedure that invokes the object.

The code to invoke the GC procedure in the above example is: 

//---------------------------------------------------------

//When working with ActiveX objects, the standard way of calling a GC procedure is 

//---------------------------------------------------------

function writeXLS() {

  //(a)...
  excel.Quit();

  excel = null;

  setTimeout(CollectGarbage, 1);

}

The first line of code calls the excel.Quit() method to cause the excel process to abort and exit, when the excel process does not actually abort because the JavaScript environment has an instance of the excel object.

The second line of code nulls excel to clear the object reference, thereby "invalidating" the object. However, because the object is still in the function context, it will still not be cleaned up if the GC procedure is called directly.

The third line of code USES setTimeout() to call the CollectGarbage function, with a time interval of '1', only for the GC to occur after the writeXLS() function has finished executing. This way, the excel object satisfies the two conditions for being gc-cleanable: no references and out of context.

The use of GC procedures is effective in JS environments that use ActiveX objects. Some potential ActiveX objects include XML, VML, OWC(Office Web components et), flash, and even VBArray in JS.

From this point of view, the ajax architecture, with its XMLHTTP and "do not switch pages" feature, provides a more efficient UI experience by actively calling the GC process when appropriate.

In fact, even with the GC process, the aforementioned excel problems are still not completely solved. Because IE also caches permission credentials. The only way to have the permission credentials of a page updated is to "switch to a new page," so in fact in the SPS project mentioned earlier, my method was not GC, but the following code: 

//---------------------------------------------------------

//Page switch code 
 for handling ActiveX objects
//---------------------------------------------------------

function writeXLS() {

  //(a)...
  excel.Quit();

  excel = null;

 

  //The following code is used to solve a BUG in IE call Excel, the method provided in MSDN: < br / >
  //   setTimeout(CollectGarbage, 1);

  //Because the trusted state of the page cannot be cleared (or synchronized), methods such as SaveAs() will result in 

  //Invalid on next call.

  location.reload();

}

Last but not least, a note about GC: when the IE form is minimized, IE will be called once
CollectGarbage () function. This results in a significant improvement in memory usage after the IE window is minimized.

Related articles: