JS Excel read and write operations (template operations) implementation code

I found out in my actual project that JS reads and writes Excel (Template) data, including how to insert images into Excel using JS.
First, add a public template address JS, as shown below:
AddZDaddress. js
Copy codeThe Code is as follows:
//////////////////////////////////////// //////////////////////////////////////// ///////////////////
/// DocName: Add a trusted site address -- AddZDaddress. js
//// Author: lify
/// Company: Wavenet
/// Date: 2009-11-04
/// EditDate: 2010-03-11
/// MainContent: Findings, Ajax And So On;
//////////////////////////////////////// //////////////////////////////////////// //////////////////
Var BelieveAddress = '192. 18.1.25/psc1 '; // The configuration address required for configuring the Address Configuration program
/// ********************************** Excel Import go to the template address set on the WEB interface ********************************** *********//
//////////////////////// Address Configuration of the flight detection excel template //////// //////////////////////////////////////// /////////////
Var template_path_FxjcExcelDatasIntoSqlDatas = "http: // % 22 + believeaddress + % 22/NewReports/xls_template/fly detection city sewage factory to be contaminated. xls ";
//////////////////////////////////////// //////////////////////////////////////// ////////////////////////////////
////************************************ **************************************** *******************************//
/// *********************************** Excel entry Template import address set ************************************* *********//
//// // Configure the excel address for monthly report import ///////// /////////////////////////
Var template_path_month = "http: // % 22 + believeaddress + % 22/NewReports/xls_template/production operation table of the Shanghai Municipal Sewage enterprise. xls ";
//////////////////////////////////////// /////////////////////////////////////////
//// // Import the excel address configuration for the Annual Report ///////// /////////////////////////
Var template_path_year = "http: // % 22 + believeaddress + % 22/NewReports/xls_template/shanghai.xls ";
////************************************ **************************************** *****************************//
// Image address
Var pic_path = "http: // % 22 + believeaddress + % 22/NewReports /";
//////////////////////////////////////// /////////////////////////////////////////

Again, how do I read the page data to JS in Excel:
YearReportLuRu. jsYearReportLuRu. js
Copy codeThe Code is as follows:
/// DocName: sewage treatment facility information table (Annual Report)-input Report-YearReportLuRu. js
//// Author: lify
/// Company: Wavenet
/// Date: 2009-08-14
/// EditDate: 2009-08-14
/// MainContent: Findings, Ajax And So On;
//////////////////////////////////////// //////////////////////////////////////// ///////////////////
/// // Add regular Expression class ///////////////////////////////////// ////////////////////////
Document. write ("<script language = \" javascript \ "type = \" text/javascript \ "src = \" js/RegularExpression. js \ "> <\/script> ");
//////////////////////////////////////// //////////////////////////////////////// ////////////////////////////////////
Function AutomateExcel3 ()
{
// Alert (pic_path + document. getElementById ("ImgUrl"). value); // Test
Var xls = new ActiveXObject ("Excel. Application ");
Xls. Visible = true;
Var newBook = xls. Workbooks. Open (template_path_year); // the parameters in the Add method here are the template path.
Var oSheet = newBook. ActiveSheet;
//////////////////////////////////////// //// Write data to the template /////////////////////////////// /////////////////////////////
//// // The name of the legal representative unit ////////////////////// //////////////////////////////////////// ///////////////////////////////
OSheet. Cells (2, 3). value = Trim (PT. rows [0]. cells [1]. innerText );
//// // The Organization Code and name of the legal representative /////////////////// //////////////////////////////////////// //////////////////////////////////////
OSheet. Cells (2,9). value = Trim (PT. rows [0]. cells [3]. innerText );
OSheet. Cells (2,13). value = Trim (PT. rows [0]. cells [5]. innerText );
/////////// Three tables for Import and Export /////////////////////// //////////////////////////////////////// //////////////////////////
OSheet. Cells (3, 2). value = Trim (PT. rows [1]. cells [1]. innerText );
OSheet. Cells (3,6). value = Trim (PT. rows [1]. cells [3]. innerText );
OSheet. Cells (3,8). value = Trim (PT. rows [1]. cells [5]. innerText );
OSheet. Cells (3,10). value = Trim (PT. rows [1]. cells [7]. innerText );
OSheet. Cells (3,13). value = Trim (PT. rows [1]. cells [9]. innerText );
OSheet. Cells (4, 2). value = Trim (PT. rows [2]. cells [1]. innerText );
OSheet. Cells (4, 4). value = Trim (PT. rows [2]. cells [3]. innerText );
OSheet. Cells (4, 6). value = Trim (PT. rows [2]. cells [5]. innerText );
OSheet. Cells (4, 8). value = Trim (PT. rows [2]. cells [7]. innerText );
OSheet. Cells (4, 10). value = Trim (PT. rows [2]. cells [9]. innerText );
OSheet. Cells (4,12). value = Trim (PT. rows [2]. cells [11]. innerText );
OSheet. Cells (4, 14). value = Trim (PT. rows [2]. cells [13]. innerText );
OSheet. Cells (5, 2). value = Trim (PT. rows [3]. cells [1]. innerText );
OSheet. Cells (5, 5). value = Trim (PT. rows [3]. cells [3]. innerText );
OSheet. Cells (5, 8). value = Trim (PT. rows [3]. cells [5]. innerText );
OSheet. Cells (5, 10). value = Trim (PT. rows [3]. cells [7]. innerText );
OSheet. Cells (5, 12). value = Trim (PT. rows [3]. cells [9]. innerText );
OSheet. Cells (5, 14). value = Trim (PT. rows [3]. cells [11]. innerText );
/////////// Annual running brief ///////////////////////
OSheet. Cells (6, 4). value = Trim (PT. rows [4]. cells [3]. innerText );
OSheet. Cells (6, 6). value = Trim (PT. rows [4]. cells [5]. innerText );
OSheet. Cells (6, 8). value = Trim (PT. rows [4]. cells [7]. innerText );
OSheet. Cells (6, 10). value = Trim (PT. rows [4]. cells [9]. innerText );
OSheet. Cells (6, 12). value = Trim (PT. rows [4]. cells [11]. innerText );
OSheet. Cells (6, 14). value = Trim (PT. rows [4]. cells [13]. innerText );
OSheet. Cells (7,4). value = Trim (PT. rows [5]. cells [2]. innerText );
OSheet. Cells (7,6). value = Trim (PT. rows [5]. cells [4]. innerText );
OSheet. Cells (7,8). value = Trim (PT. rows [5]. cells [6]. innerText );
OSheet. Cells (7,10). value = Trim (PT. rows [5]. cells [8]. innerText );
OSheet. Cells (7,12). value = Trim (PT. rows [5]. cells [10]. innerText );
OSheet. Cells (7,14). value = Trim (PT. rows [5]. cells [12]. innerText );
////////// // Annual processing volume ///////////////////////
OSheet. Cells (8, 2). value = Trim (PT. rows [6]. cells [1]. innerText );
OSheet. Cells (8, 5). value = Trim (PT. rows [6]. cells [3]. innerText );
OSheet. Cells (8, 8). value = Trim (PT. rows [6]. cells [5]. innerText );
OSheet. Cells (8, 11). value = Trim (PT. rows [6]. cells [7]. innerText );
OSheet. Cells (8, 14). value = Trim (PT. rows [6]. cells [9]. innerText );
////////// Process Overview of the sewage plant ///////////////////////
OSheet. Cells (10, 3). value = Trim (PT. rows [8]. cells [1]. innerText );
OSheet. Cells (10, 4). value = Trim (PT. rows [8]. cells [2]. innerText );
OSheet. Cells (10, 5). value = Trim (PT. rows [8]. cells [3]. innerText );
OSheet. Cells (10, 7). value = Trim (PT. rows [8]. cells [4]. innerText );
OSheet. Cells (11,3). value = Trim (PT. rows [9]. cells [1]. innerText );
OSheet. Cells (11,4). value = Trim (PT. rows [9]. cells [2]. innerText );
OSheet. Cells (11,5). value = Trim (PT. rows [9]. cells [3]. innerText );
OSheet. Cells (11,7). value = Trim (PT. rows [9]. cells [4]. innerText );
OSheet. Cells (12, 3). value = Trim (PT. rows [10]. cells [1]. innerText );
OSheet. Cells (12, 4). value = Trim (PT. rows [10]. cells [2]. innerText );
OSheet. Cells (12, 5). value = Trim (PT. rows [10]. cells [3]. innerText );
OSheet. Cells (12, 7). value = Trim (PT. rows [10]. cells [4]. innerText );
// -------------------------- Analysis of running expenses -------------------------------------//
OSheet. Cells (13, 3). value = Trim (PT. rows [11]. cells [2]. innerText );
OSheet. Cells (13, 5). value = Trim (PT. rows [11]. cells [4]. innerText );
OSheet. Cells (13, 7). value = Trim (PT. rows [11]. cells [6]. innerText );
OSheet. Cells (13, 9). value = Trim (PT. rows [11]. cells [8]. innerText );
/// // The pumping station outside the factory //// ////////////////////////
// Copy (document. getElementById ("ImgUrl "));
// OSheet. Pictures. Insert (pic_path + document. getElementById ("ImgUrl"). value); //. Cells (13, 11)
Var msoShaoeRectangle = 1; // AddShape (transparency, left, top, width, height)
OSheet. Shapes. AddShape (msoShaoeRectangle, 560,330,200,150). Fill. UserPicture (pic_path + document. getElementById ("ImgUrl"). value );
// OSheet. Cell (). select (); // select the excel Cell
// OSheet. Pictures. Insert (PT. rows [11]. cells [8]. getElementById ("ImgUrl"). src );
//////////////////////////////////////// //////////////////////////////////////
OSheet. Cells (14,3). value = Trim (PT. rows [12]. cells [1]. innerText );
OSheet. Cells (14,5). value = Trim (PT. rows [12]. cells [3]. innerText );
OSheet. Cells (14,7). value = Trim (PT. rows [12]. cells [5]. innerText );
OSheet. Cells (14,9). value = Trim (PT. rows [12]. cells [7]. innerText );
OSheet. Cells (15,3). value = Trim (PT. rows [13]. cells [1]. innerText );
OSheet. Cells (15, 5). value = Trim (PT. rows [13]. cells [3]. innerText );
OSheet. Cells (15, 7). value = Trim (PT. rows [13]. cells [5]. innerText );
OSheet. Cells (15,9). value = Trim (PT. rows [13]. cells [7]. innerText );
OSheet. Cells (16, 5). value = Trim (PT. rows [14]. cells [1]. innerText );
OSheet. Cells (16, 7). value = Trim (PT. rows [14]. cells [3]. innerText );
OSheet. Cells (16, 9). value = Trim (PT. rows [14]. cells [5]. innerText );
OSheet. Cells (17,3). value = Trim (PT. rows [15]. cells [1]. innerText );
OSheet. Cells (17,5). value = Trim (PT. rows [15]. cells [3]. innerText );
OSheet. Cells (17,7). value = Trim (PT. rows [15]. cells [5]. innerText );
//---------------------------------------------------------------------------//
/////////// Information under the sewage plant table ///////////////////////
OSheet. Cells (18, 2). value = Trim (RT. rows [0]. cells [1]. innerText );
OSheet. Cells (18, 7). value = Trim (RT. rows [0]. cells [3]. innerText );
OSheet. Cells (18, 11). value = Trim (RT. rows [0]. cells [5]. innerText );
OSheet. Cells (18, 14). value = Trim (RT. rows [0]. cells [7]. innerText );
//////////////////////////////////////// //////////////////////////////////////// ///////////////////////////////////
Xls. Visible = true;
Xls. UserControl = true;
Xls = null;
IdTmr = window. setInterval ("Cleanup ();", 1 );
// Xls. quit ();
}
Function Cleanup ()
{
Window. clearInterval (idTmr );
CollectGarbage ();
}
Function copy (tabid ){
Var oControlRange = document. body. createControlRange ();
OControlRange. add (tabid, 0 );
OControlRange. select ();
Document.exe cCommand ("Copy ");
}

Finally, how to read the data in the Excel template to the page, and then save the edited data to the database (this step can be sent to the background for Interaction Using xml, which is not further described here) JS, as shown below:
Fxjc_ExcelDatasIntoSqlDatas.js
Copy codeThe Code is as follows:
/// DocName: Template data import function-Fxjc_ExcelDatasIntoSqlDatas.js
//// Author: lify
/// Company: Wavenet
/// Date: 2010-03-12
/// EditDate: 2010-03-12
/// MainContent: Findings, Ajax And So On;
//////////////////////////////////////// //////////////////////////////////////// //////////////////
Var excelFileName = ""; // local EXCEL address
Var oWB;
Var idTmr = "";
Function AutomateExcel3 ()
{
// Delete all rows except the first two rows in the PT table
DeleteTablePT ();
// Alert (pic_path + document. getElementById ("ImgUrl"). value); // Test
Var xls = null;
Var newBook;
Try {
Xls = new ActiveXObject ("Excel. Application ");
} Catch (e) {openBg (0); openTS (0 );
Alert ("Open ActiveX control. For details, click" Internet Options "in" Tools "in IE ", click ActiveX controls and plug-ins in Custom Level in security. Select enable as the third option. Detailed error: "+ e. message );
Return;
}
If (xls = null) {openBg (0); openTS (0 );
Alert ("An error occurred while creating the Excel file. It may be that Microsoft Office Excel software is not correctly installed on your computer or your browser's security level settings are too high! ");
Return;
}
Try {
NewBook = xls. Workbooks. Open (excelFileName); // the parameters in the Add method here are the template path.
} Catch (e) {openBg (0); openTS (0 );
Alert ("An error occurred while loading the Excel file path! Details: "+ e. message );
Return;
}
Var oSheet = newBook. ActiveSheet;
//////////////////////////////////////// //// The template writes data to the WEB page ///////////////////////////// ///////////////////////////////
Var xhnum = 0;
// Alert ("oSheet. cells (3, 18 ). value = "+ oSheet. cells (3, 18 ). value + "|" + (oSheet. cells (3, 5 ). value = "<30") + "| oSheet. cells (3, 20 ). value = "+ oSheet. cells (3, 20 ). value );
For (var I = 3; I <108; I ++)
{
If (oSheet. Cells (I, 1). value)
{
Xhnum ++;
Var newRow = document. getElementById ("PT"). insertRow (-1 );
Var newCell;
NewRow. align = "center ";
NewRow. height = "35px ";
For (var j = 0; j <28; j ++)
{
If (j = 4)
{
// Do nothing
}
Else if (j <4)
{
NewCell = newRow. insertCell (j );
If (j = 0) newCell. innerHTML = (xhnum). toString (); // serial number
Else if (j = 1)
{
If (! OSheet. Cells (I, 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 1). value = "--" | oSheet. Cells (I, 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 1). value = ""? "": OSheet. Cells (I, 1). value );
}
Else if (j = 2)
{
If (! OSheet. Cells (I, 2). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 2). value = "--" | oSheet. Cells (I, 2). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 2). value = ""? "": OSheet. Cells (I, 2). value );
// NewCell. innerHTML = (oSheet. Cells (I, 2). value = ""? "": OSheet. Cells (I, 2). value );
}
Else if (j = 3)
{
If (! OSheet. Cells (I, 4). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 4). value = "--" | oSheet. Cells (I, 4). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 4). value = ""? "": OSheet. Cells (I, 4). value );
// NewCell. innerHTML = (oSheet. Cells (I, 4). value = ""? "": OSheet. Cells (I, 4). value );
}
Else if (j = 9)
{
If (! OSheet. Cells (I, j + 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "<30 ")
NewCell. innerHTML = "30 ";
Else if (oSheet. Cells (I, j + 1). value = "--" | oSheet. Cells (I, j + 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, j + 1). value = ""? "": OSheet. Cells (I, j + 1). value );
}
Else
{
If (! OSheet. Cells (I, j + 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "<30 ")
NewCell. innerHTML = "30 ";
Else if (oSheet. Cells (I, j + 1). value = "<DL ")
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "--" | oSheet. Cells (I, j + 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, j + 1). value = ""? "": OSheet. Cells (I, j + 1). value );
}
}
Else
{
NewCell = newRow. insertCell (J-1); // you can insert less than one sample number.
If (j = 0) newCell. innerHTML = (xhnum). toString (); // serial number
Else if (j = 1)
{
If (! OSheet. Cells (I, 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 1). value = "--" | oSheet. Cells (I, 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 1). value = ""? "": OSheet. Cells (I, 1). value );
}
Else if (j = 2)
{
If (! OSheet. Cells (I, 2). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 2). value = "--" | oSheet. Cells (I, 2). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 2). value = ""? "": OSheet. Cells (I, 2). value );
// NewCell. innerHTML = (oSheet. Cells (I, 2). value = ""? "": OSheet. Cells (I, 2). value );
}
Else if (j = 3)
{
If (! OSheet. Cells (I, 4). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, 4). value = "--" | oSheet. Cells (I, 4). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, 4). value = ""? "": OSheet. Cells (I, 4). value );
// NewCell. innerHTML = (oSheet. Cells (I, 4). value = ""? "": OSheet. Cells (I, 4). value );
}
Else if (j = 9)
{
If (! OSheet. Cells (I, j + 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "<30 ")
NewCell. innerHTML = "30 ";
Else if (oSheet. Cells (I, j + 1). value = "--" | oSheet. Cells (I, j + 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, j + 1). value = ""? "": OSheet. Cells (I, j + 1). value );
}
Else
{
If (! OSheet. Cells (I, j + 1). value)
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "<30 ")
NewCell. innerHTML = "30 ";
Else if (oSheet. Cells (I, j + 1). value = "<DL ")
NewCell. innerHTML = "";
Else if (oSheet. Cells (I, j + 1). value = "--" | oSheet. Cells (I, j + 1). value = "--")
NewCell. innerHTML = "";
Else
NewCell. innerHTML = (oSheet. Cells (I, j + 1). value = ""? "": OSheet. Cells (I, j + 1). value );
}
}
}
}
}
//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// ///////
NewBook. Close ();
NewBook = null;
Xls = null;
OpenBg (0); openTS (0 );
//// // The edit and save button function is enabled /// ////////////////////////////////
OnOrOffBtn (1 );
//////////////////////////////////////// //////////////////////////////////////// ////////
IdTmr = window. setInterval ("Cleanup ();", 1 );
//// // Table number and character conversion /// ///////////////////////////////////
Table‑datas (); // convert table numbers and characters
//////////////////////////////////////// //////////////////////////////////////// ////////
}

In fact, it is very painful to make a report, but it can also experience a lot. I'm glad to use this article to help those report experts to guide and make bricks. Thank you!
The above are some of the methods summarized in the excel template. You are welcome to come and take a brick! You are also welcome to exchange ideas!