I am in the actual project to find out, JS Read and write Excel (template) data, including how to use JS to insert the picture in Excel.
First, add a common template address JS, as follows:
Addzdaddress.js
Copy Code code as follows:
///////////////////////////////////////////////////////////////////////////////////////////////////
DocName: Trust Site Address Add--addzdaddress.js
Author:lify
Company:wavenet
date:2009-11-04
Editdate:2010-03-11
Maincontent:findings,ajax and so on;
//////////////////////////////////////////////////////////////////////////////////////////////////
var believeaddress= ' 172.18.1.25/psc1 ';////configuration address to configure address configuration program
Excel imports to the Web Interface template address collection *******************************************//
Flight detection Excel template address configuration/////////////////////////////////////////////////////////////
var Template_path_fxjcexceldatasintosqldatas = "http://%22+believeaddress+%22/NewReports/xls_template/ Flight test the concentration of main pollutant in the effluent of town sewage plant. xls ";
/////////////////////////////////////////////////////////////////////////////////////////////////////////////// /
////*********************************************************************************************************** //
Excel Input Template import address collection **********************************************//
Monthly Report Import Excel address configuration//////////////////////////////////
var template_path_month = "http://%22+believeaddress+%22/NewReports/xls_template/Shanghai Municipal Wastewater Treatment Enterprise production running table. xls";
/////////////////////////////////////////////////////////////////////////////////
Annual Report Import Excel address configuration//////////////////////////////////
var template_path_year = "http://%22+believeaddress+%22/NewReports/xls_template/Shanghai Municipal Wastewater Treatment Facility Information sheet. xls";
////*********************************************************************************************************//
Picture Address
var pic_path= "http://%22+believeaddress+%22/NewReports/";
/////////////////////////////////////////////////////////////////////////////////
Again, how to read the page data to excel in JS, as follows:
YearReportLuRu.jsYearReportLuRu.js
Copy Code code as follows:
DocName: Sewage Treatment facilities 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 parameter in the Add method here is the path of the template
var Osheet=newbook.activesheet;
Write data to the template/////////////////////////////////////////////////////////// /
Legal Entity Name////////////////////////////////////////////////////////////////////////////////////////// ///
Osheet.cells (2,3). Value = Trim (Pt.rows[0].cells[1].innertext);
Legal entity code and representative name//////////////////////////////////////////////////////////////////////////////////// /////////////
Osheet.cells (2,9). Value = Trim (Pt.rows[0].cells[3].innertext);
Osheet.cells (2,13). Value = Trim (Pt.rows[0].cells[5].innertext);
Import and export three sheets/////////////////////////////////////////////////////////////////////////////////////////
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 Operation Profile///////////////////////
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 treatment of water///////////////////////
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 survey of wastewater treatment 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);
--------------------------Operating Cost Analysis-------------------------------------/
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);
Schematic////////////////////////////of 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 (13,11). Select ();//select cells for Excel
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 treatment 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.execcommand ("Copy");
}
Finally, how to read to the Excel template data to the page of the total customer edit data and then save to the database (this step can be sent in XML to background interaction, this is not further explained) JS, as follows:
Fxjc_exceldatasintosqldatas.js
Copy Code code as follows:
DocName: Importing template Data features--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 lines of the PT table except the first 2 lines
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 (please open the ActiveX control by clicking Internet Options in Tools in IE, clicking ActiveX controls and plugins in the custom level in security), select the third item to enable it.) Detailed error: "+e.message);
return;
}
if (xls = = null) {OPENBG (0); opents (0);
Alert ("The creation of an Excel file failed, it may be that your computer does not install the Microsoft Office Excel software correctly or the browser's security level setting is too high!");
Return
}
try{
Newbook = xls. Workbooks.Open (Excelfilename)//The parameter in the Add method here is the path of the template
}catch (e) {OPENBG (0); opents (0);
Alert ("The loaded Excel file path has errors!):" +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)
{
Don't do anything.
}
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);//Less one on it, less sample number insert
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);
Turn on Edit and save button function///////////////////////////////////
ONOROFFBTN (1);
////////////////////////////////////////////////////////////////////////////////////////
IDTMR = Window.setinterval ("Cleanup ();", 1);
Table numbers and character conversions//////////////////////////////////////
Tableintodatas ();////table numbers and character conversions
////////////////////////////////////////////////////////////////////////////////////////
}
In fact, the report is painful, but also can experience a lot. I am pleased to use this article to let those report masters come to guide and pat Bricks. Thank you, sir!
Above for the Excel template summary of some methods, welcome you come to shoot bricks! Also welcome everyone to exchange and exchange!
