导入：
代码如下:
<htmlxmlns="http://www.w3.org/1999/xhtml">
<head>
<title>UntitledPage</title>
</head>
<scriptlanguage="javascript"type="text/javascript">
functionimportXLS(fileName)
{
objCon=newActiveXObject("ADODB.Connection");
objCon.Provider="Microsoft.Jet.OLEDB.4.0";
objCon.ConnectionString="DataSource="+fileName+";ExtendedProperties=Excel8.0;";
objCon.CursorLocation=1;
objCon.Open;
varstrQuery;
//GettheSheetName
varstrSheetName="Sheet1$";
varrsTemp=newActiveXObject("ADODB.Recordset");
rsTemp=objCon.OpenSchema(20);
if(!rsTemp.EOF)
strSheetName=rsTemp.Fields("Table_Name").Value;
rsTemp=null;
rsExcel=newActiveXObject("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;
}
//Closetheconnectionanddisposethefile
objCon.Close;
objCon=null;
rsExcel=null;
}
</script>
</head>
<body>
<inputtype="file"id="f"/>
<inputtype="button"id="b"value="import"onclick="if(f.value=='')alert('请选择xls文件');elseimportXLS(f.value)"/>
</body>
</html>
导出：
代码如下:
functionAutomateExcel()
{
//StartExcelandgetApplicationobject.
varoXL=newActiveXObject("Excel.Application");
oXL.Visible=true;
//Getanewworkbook.
varoWB=oXL.Workbooks.Add();
varoSheet=oWB.ActiveSheet;
//Addtableheadersgoingcellbycell.
oSheet.Cells(1,1).Value="FirstName";
oSheet.Cells(1,2).Value="LastName";
oSheet.Cells(1,3).Value="FullName";
oSheet.Cells(1,4).Value="Salary";
//FormatA1:D1asbold,verticalalignment=center.
oSheet.Range("A1","D1").Font.Bold=true;
oSheet.Range("A1","D1").VerticalAlignment=-4108;//xlVAlignCenter
//Createanarraytosetmultiplevaluesatonce.
//FillA2:B6withanarrayofvalues(fromVBScript).
oSheet.Range("A2","B6").Value=CreateNamesArray();
//FillC2:C6witharelativeformula(=A2&""&B2).
varoRng=oSheet.Range("C2","C6");
oRng.Formula="=A2&""&B2";
//FillD2:D6withaformula(=RAND()*100000)andapplyformat.
oRng=oSheet.Range("D2","D6");
oRng.Formula="=RAND()*100000";
oRng.NumberFormat="$0.00";
//AutoFitcolumnsA:D.
oRng=oSheet.Range("A1","D1");
oRng.EntireColumn.AutoFit();
//ManipulateavariablenumberofcolumnsforQuarterlySalesData.
DispalyQuarterlySales(oSheet);
//MakesureExcelisvisibleandgivetheusercontrol
//ofExcel'slifetime.
oXL.Visible=true;
oXL.UserControl=true;
}<HTML>
<HEAD>
<TITLE>将页面中指定表格的数据导入到Excel中</TITLE>
<SCRIPTLANGUAGE="javascript">
<!--
functionAutomateExcel()
{
varoXL=newActiveXObject("Excel.Application");//创建应该对象
varoWB=oXL.Workbooks.Add();//新建一个Excel工作簿
varoSheet=oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表
vartable=document.all.data;//指定要写入的数据源的id
varhang=table.rows.length;//取数据源行数
varlie=table.rows(0).cells.length;//取数据源列数
//Addtableheadersgoingcellbycell.
for(i=0;i<hang;i++){//在Excel中写行
for(j=0;j<lie;j++){//在Excel中写列
//定义格式
oSheet.Cells(i+1,j+1).NumberFormatLocal="@";
//!!!!!!!上面这一句是将单元格的格式定义为文本
oSheet.Cells(i+1,j+1).Font.Bold=true;//加粗
oSheet.Cells(i+1,j+1).Font.Size=10;//字体大小
oSheet.Cells(i+1,j+1).value=table.rows(i).cells(j).innerText;//向单元格写入值
}
}
oXL.Visible=true;
oXL.UserControl=true;
}
//-->
</SCRIPT>
</HEAD>
<BODY>
<tableborder="0"width="300"id="data"bgcolor="black"cellspacing="1">
<trbgcolor="white">
<td>编号</td>
<td>姓名</td>
<td>年龄</td>
<td>性别</td>
</tr>
<trbgcolor="white">
<td>0001</td>
<td>张三</td>
<td>22</td>
<td>女</td>
</tr>
<trbgcolor="white">
<td>0002</td>
<td>李四</td>
<td>23</td>
<td>男</td>
</tr>
</table>
<inputtype="button"name="out_excel"onclick="AutomateExcel();"value="导出到excel">
</BODY>
</HTML>
代码如下:
<!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!--导出excle的三个方法要把ie浏览器的"对没有标记为安全的ActiveX控件进行初始化和脚本运行设置为提示或启用"-->
<htmlxmlns="http://www.w3.org/1999/xhtml">
<head>
<metahttp-equiv="Content-Type"content="text/html;charset=gb2312">
<title>WEB页面导出为EXCEL文档的方法</title>
</head>
<body>
<tableid="tableExcel"width="100%"border="1"cellspacing="0"cellpadding="0">
<tr>
<tdcolspan="5"align="center">
WEB页面导出为EXCEL文档的方法
</td>
</tr>
<tr>
<td>
列标题1
</td>
<td>
列标题2
</td>
<td>
列标题3
</td>
<td>
列标题4
</td>
<td>
列标题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>
<inputtype="button"onclick="javascript:method1('tableExcel');"value="第一种方法导入到EXCEL">
<inputtype="button"onclick="javascript:method2('tableExcel');"value="第二种方法导入到EXCEL">
<inputtype="button"onclick="javascript:getXlsFromTbl('tableExcel',null);"value="第三种方法导入到EXCEL">
<scriptlanguage="javascript">
functionmethod1(tableid){//整个表格拷贝到EXCEL中
varcurTbl=document.getElementById(tableid);
varoXL=newActiveXObject("Excel.Application");//创建AX对象excel
varoWB=oXL.Workbooks.Add();//获取workbook对象
varoSheet=oWB.ActiveSheet;//激活当前sheet
varsel=document.body.createTextRange();
sel.moveToElementText(curTbl);//把表格中的内容移到TextRange中
sel.select();//全选TextRange中内容
sel.execCommand("Copy");//复制TextRange中内容
oSheet.Paste();//粘贴到活动的EXCEL中
oXL.Visible=true;//设置excel可见属性
}
functionmethod2(tableid)//读取表格中每个单元到EXCEL中
{
varcurTbl=document.getElementById(tableid);
varoXL=newActiveXObject("Excel.Application");//创建AX对象excel
varoWB=oXL.Workbooks.Add();//获取workbook对象
varoSheet=oWB.ActiveSheet;//激活当前sheet
varLenr=curTbl.rows.length;//取得表格行数
for(i=0;i<Lenr;i++){
varLenc=curTbl.rows(i).cells.length;//取得每行的列数
for(j=0;j<Lenc;j++){
oSheet.Cells(i+1,j+1).value=curTbl.rows(i).cells(j).innerText;//赋值
}
}
oXL.Visible=true;//设置excel可见属性
}
functiongetXlsFromTbl(inTblId,inWindow){
try{
varallStr="";
varcurStr="";
if(inTblId!=null&&inTblId!=""&&inTblId!="null"){
curStr=getTblData(inTblId,inWindow);
}
if(curStr!=null){
allStr+=curStr;
}
else{
alert("你要导出的表不存在！");
return;
}
varfileName=getExcelFileName();
doFileExport(fileName,allStr);
}
catch(e){
alert("导出发生异常:"+e.name+"->"+e.description+"!");
}
}
functiongetTblData(inTbl,inWindow){
varrows=0;
vartblDocument=document;
if(!!inWindow&&inWindow!=""){
if(!document.all(inWindow)){
returnnull;
}
else{
tblDocument=eval(inWindow).document;
}
}
varcurTbl=tblDocument.getElementById(inTbl);
varoutStr="";
if(curTbl!=null){
for(varj=0;j<curTbl.rows.length;j++){
for(vari=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(vark=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+"不存在!");
}
returnoutStr;
}
functiongetExcelFileName(){
vard=newDate();
varcurYear=d.getYear();
varcurMonth=""+(d.getMonth()+1);
varcurDate=""+d.getDate();
varcurHour=""+d.getHours();
varcurMinute=""+d.getMinutes();
varcurSecond=""+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;
}
varfileName="leo_zhang"+"_"+curYear+curMonth+curDate+"_"
+curHour+curMinute+curSecond+".csv";
returnfileName;
}
functiondoFileExport(inName,inStr){
varxlsWin=null;
if(!!document.all("glbHideFrm")){
xlsWin=glbHideFrm;
}
else{
varwidth=6;
varheight=4;
varopenPara="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>
|
Copyright©2000-2023