Home > Others

JS Import Export Excel (instance code) _javascript Tips

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Import:

Copy Code code as follows:



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


<head>


<title>untitled page</title>


</head>


<script language= "javascript" type= "Text/javascript" >


function Importxls (fileName)


{ 


Objcon = new ActiveXObject ("ADODB. Connection ");


Objcon.provider = "Microsoft.Jet.OLEDB.4.0";


objcon.connectionstring = "Data source=" + FileName + "; Extended Properties=excel 8.0; ";


objcon.cursorlocation = 1;


Objcon.open;


var strquery;


//get the SheetName


var strsheetname = "sheet1$";


var rstemp = new ActiveXObject ("ADODB. Recordset ");


rstemp = Objcon.openschema (20);


if (!rstemp.eof)


strSheetName = rstemp.fields ("table_name"). Value;


rstemp = null;


rsexcel = new ActiveXObject ("ADODB. Recordset ");


strquery = "SELECT * FROM [" + strSheetName + "]";


rsexcel.activeconnection = Objcon;


Rsexcel.open (strquery);


while (!rsexcel.eof)


     {


for (i = 0;i<rsexcel.fields.count;++i)


     {


Alert (Rsexcel.fields (i). value);


     }


Rsexcel.movenext;


     }


//Close the connection and dispose the file


Objcon.close;


Objcon =null;


rsexcel = null;


}


</script>


</head>


<body>


<input type= "file" id= "F"/>


<input type= "button" id= "B" value= "import" onclick= "if (f.value==") Alert (' Please select XLS file '); else Importxls (F.value) "/>


</body>


</html>





Export:
Copy Code code as follows:



function Automateexcel ()


{


　　


Start Excel and get Application object.


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


　　


oxl.visible = true;


　　


//Get a new workbook.


var owb = OXL.Workbooks.Add ();


var osheet = Owb.activesheet;


　　


//ADD table headers going cell by cell.


osheet.cells (1, 1). Value = ' Name ';


osheet.cells (1, 2). Value = "Last Name";


osheet.cells (1, 3). Value = "Full Name";


osheet.cells (1, 4). Value = "Salary";


　　


//Format a1:d1 as bold, vertical alignment = center.


Osheet.range ("A1", "D1"). Font.Bold = true;


Osheet.range ("A1", "D1"). VerticalAlignment =-4108; xlVAlignCenter


　　


//Create An array to set multiple values at once.


　　


//Fill a2:b6 with a array of values (from VBScript).


Osheet.range ("A2", "B6"). Value = Createnamesarray ();


　　


//Fill c2:c6 with a relative formula (=a2 & "" & B2).


var orng = Osheet.range ("C2", "C6");


Orng.formula = "=a2 &" "& B2";


　　


//Fill D2:D6 with a formula (=rand () *100000) and apply format.


orng = Osheet.range ("D2", "D6");


Orng.formula = "=rand () *100000";


Orng.numberformat = "$0.00";


　　


//AutoFit columns a:d.


orng = Osheet.range ("A1", "D1");


ORng.EntireColumn.AutoFit ();


　　


//Manipulate A variable number of columns for quarterly Sales Data.


dispalyquarterlysales (osheet);


　　


//Make sure Excel are visible and give the user control


//Of Excel ' s lifetime.


oxl.visible = true;


Oxl.usercontrol = true;


}<html>


<HEAD>


<TITLE> Import data from a specified table in a page into Excel </TITLE>


<script language= "JavaScript" >


<!--


function Automateexcel ()


{


　　


var oXL = new ActiveXObject ("Excel.Application"); Create an object that should be


var owb = OXL.WORKBOOKS.ADD ()//Create a new Excel workbook


var osheet = owb.activesheet;//Specifies that the worksheet to write to the content is the active sheet


var table = document.all.data;//Specifies the ID of the data source to write


var hang = table.rows.length;//Fetch data source row number


var lie = table.rows (0). cells.length;//fetch number of data source columns


　　


//ADD table headers going cell by cell.


for (i=0;i<hang;i++) {//write rows in Excel


for (j=0;j<lie;j++) {//write columns in Excel


//Definition Format


osheet.cells (i+1,j+1). NumberFormatLocal = "@";


//!!!!!!! The above sentence defines the format of a cell as a text


Osheet.cells (i+1,j+1). Font.Bold = true;//Bold


osheet.cells (i+1,j+1). font.size = 10;//Font Size


osheet.cells (i+1,j+1). Value = Table.rows (i). Cells (j). innertext;//writes a value to a cell


}


}


oxl.visible = true;


Oxl.usercontrol = true;


}


//-->


</SCRIPT>


</HEAD>


　　


<BODY>


<table border= "0" width= "id=" "Data" bgcolor= "Black" cellspacing= "1" >


<tr bgcolor= "White" >


<td> numbering </td>


<td> name </td>


<td> Age </td>


<td> Gender </td>


</tr>


<tr bgcolor= "White" >


<td>0001</td>


<td> John </td>


<td>22</td>


<td> Women </td>


</tr>


<tr bgcolor= "White" >


<td>0002</td>


<td> Dick </td>


<td>23</td>


<td> men </td>


</tr>


</table>


<input type= "button" Name= "Out_excel" onclick= "Automateexcel" (); "value=" Export to Excel >


</BODY>


</HTML>





Copy Code code as follows:



<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en"


"HTTP://WWW.W3.ORG/TR/XHTML1/DTD/XHTML1-TRANSITIONAL.DTD" >


<!--three ways to export excle the "Initialize and script run for ActiveX controls that are not marked as safe"--> to be prompted or enabled by IE browser


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


<head>


<meta http-equiv= "Content-type" content= "text/html"; charset=gb2312 ">


Methods for exporting <title>web pages to Excel documents </title>


</head>


<body>


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


<tr>


<td colspan= "5" align= "Center" >


Web page Export as an Excel document


</td>


</tr>


<tr>


<td>


Column Heading 1


</td>


<td>


column Heading 2


</td>


<td>


column Heading 3


</td>


<td>


column Heading 4


</td>


<td>


column Heading 5


</td>


</tr>


<tr>


<td>


AAA


</td>


<td>





</td>


<td>


CCC


</td>


<td>


DDD


</td>


<td>


Eee


</td>


</tr>


<tr>


<td>


AAA


</td>


<td>





</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= "first method import 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) {//Full table Copy to Excel

var curtbl = document.getElementById (TableID);

var oXL = new ActiveXObject ("Excel.Application"); Create an Ax object Excel

var owb = OXL.Workbooks.Add (); Get Workbook Object

var osheet = Owb.activesheet; Activate current sheet

var sel = Document.body.createTextRange ();

Sel.movetoelementtext (CURTBL); Move the contents of the table to TextRange

Sel.select (); Full selection of TextRange contents

Sel.execcommand ("Copy"); Copy content in TextRange

Osheet.paste (); Paste into active Excel

oXL.Visible = true; Set Excel Visible Properties

}

function Method2 (tableid)/read each cell in the table to Excel
{

var curtbl = document.getElementById (TableID);

var oXL = new ActiveXObject ("Excel.Application"); Create an Ax object Excel

var owb = OXL.Workbooks.Add (); Get Workbook Object

var osheet = Owb.activesheet; Activate 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; assigning values

}

}

oXL.Visible = true; Set Excel Visible Properties

}

function Getxlsfromtbl (intblid, Inwindow) {

try {

var allstr = "";

var curstr = "";

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 exception occurred:" + e.name + "->" + e.description + "!");

}

}

function Gettbldata (INTBL, Inwindow) {

var rows = 0;

var tbldocument = document;

if (!! Inwindow && Inwindow!= "") {

if (!document.all (Inwindow)) {

return null;

}
else {

Tbldocument = eval (inwindow). Document;

}

}

var curtbl = Tbldocument.getelementbyid (INTBL);

var outstr = "";

if (curtbl!= null) {

for (var j = 0; J < CurTbl.rows.length; J + +) {

for (var i = 0; i < curtbl.rows[j].cells.length; i++) {

if (i = = 0 && rows > 0) {

Outstr + = "T";

rows = 1;

}

Outstr + = Curtbl.rows[j].cells[i].innertext + "/t";

if (Curtbl.rows[j].cells[i].colspan > 1) {

for (var k = 0; k < curtbl.rows[j].cells[i].colspan-1; k++) {

Outstr + = "T";

}

}

if (i = = 0) {

if (rows = = 0 && curtbl.rows[j].cells[i].rowspan > 1) {

rows = curtbl.rows[j].cells[i].rowspan-1;

}

}

}

Outstr + = "/r/n";

}

}

else {

OUTSTR = null;

Alert (intbl + "does 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";

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>

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More