' 目的:讀取excel資料後插入到數據庫中同時紀錄成功和失敗的數目 ' 傳入: ' 返回: '********************************************************* Function GetExcel() Dim conn Dim StrConn Dim rs Dim Sql file="" Set conn=Server.CreateObject("ADODB.Connection") StrConn="Driver={Microsoft Excel Driver (*.xls)};DBQ="& Server.MapPath("EXCEL_DATA.xls") ''StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dd.xls;Extended Properties=Excel 8.0" conn.Open StrConn Set rs = Server.CreateObject("ADODB.Recordset") Sql="select * from [Sheet1$]" rs.Open Sql,conn,2,2 ''讀取excel中的字段名稱,並檢察字段順序是否正確 for i=0 to rs.Fields.Count-1 FILE_HEAD=FILE_HEAD&rs(i).Name next ''response.write FILE_HEAD IF trim(FILE_HEAD)<>"版本使用單位類綱目節類說明綱說明目說明檔名保存年限共同分類號" THEN RESPONSE.WRITE "<SCRIPT LANGUAGE='JAVASCRIPT'>alert('EXCEL文件字段順序錯誤或字段數不對!!')</SCRIPT>" exit Function END IF ''讀取excel中的資料 do while Not rs.EOF ''將讀取的資料INSERT到oracle數據庫 for i=0 to rs.Fields.Count-1 EDITION=rs(0) FILE_CODE=rs(2)+rs(3)+rs(4)+rs(5) FILE_NAME=rs(9) KIND1_DESC=rs(6) KIND2_DESC=rs(7) KIND3_DESC=rs(8) KIND4_DESC=rs(9) SAVE_YEAR=rs(10) FILE_UNIT=rs(1) COM_FILE_CODE=rs(11) ''============================================== CHECED_SQL="Select nvl(FILE_CASE,'') FILE_CASE FROM ODM67 where EDITION='"&TRIM(EDITION)&"' and FILE_CODE='"&TRIM(FILE_CODE)&"' " If mobjDB.OpenSQL(CHECED_SQL) Then If mobjDB.IsEmpty Then FILE_CASE="0001" CASE_DESC="總案" INS_SQL="" INS_SQL=INS_SQL & " INSERT INTO ODM67(" & VBCRLF INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_CASE," & VBCRLF INS_SQL=INS_SQL & " CASE_DESC,CRT_USER,CRT_DATE," & VBCRLF INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF INS_SQL=INS_SQL & " VALUES(" & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(EDITION)&"','"&TRIM(FILE_CODE)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(FILE_CASE)&"','"&TRIM(CASE_DESC)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"','"&TRIM(NOWTIME)&"')" CALL mobjDB.ExecSQL(INS_SQL) End If End If ''============================================== INS_SQL="" INS_SQL=INS_SQL & " INSERT INTO ODM61( " & VBCRLF INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_NAME,KIND1_DESC," & VBCRLF INS_SQL=INS_SQL & " KIND2_DESC,KIND3_DESC,KIND4_DESC,SAVE_YEAR," & VBCRLF INS_SQL=INS_SQL & " FILE_UNIT,COM_FILE_CODE,CRT_USER,CRT_DATE," & VBCRLF INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF INS_SQL=INS_SQL & " VALUES(" & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(EDITION)&"','"&TRIM(FILE_CODE)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(FILE_NAME)&"','"&TRIM(KIND1_DESC)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(KIND2_DESC)&"','"&TRIM(KIND3_DESC)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(KIND4_DESC)&"','"&TRIM(SAVE_YEAR)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(FILE_UNIT)&"','"&TRIM(COM_FILE_CODE)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"','"&TRIM(NOWTIME)&"')" ''RESPONSE.WRITE INS_SQL& "<BR>" IF mobjDB.ExecSQL(INS_SQL) THEN InCount=InCount+1 ELSE NoCount=NoCount+1 file=file&TODAY&" "&NOWTIME&" "&EDITION&" "&FILE_CODE & VBCRLF END IF exit for next rs.MoveNext Loop rs.close set rs=nothing Conn.close set StrConn=nothing if file<>"" then CALL CreateFolder() call SetFile(file) strpath=server.mappath("EXCEL_DATA.xls") call DeleteFolder(strpath) file="" end if
End Function '********************************************************* ' 目的: 新建一個指定的文件,如果存在就不新建,並向文件追加紀錄 ' 傳入: file:要追加的數據 ' 返回: '********************************************************* Function SetFile(file) file_path="C:\LOG\OD60err.log" set fstemp=server.CreateObject("Scripting.FileSystemObject") IF (fstemp.FileExists(file_path)) THEN ELSE set filetemp=fstemp.CreateTextFile(file_path,true) filetemp.writeLine "紀錄匯入失敗資料" filetemp.close END IF ''追加失敗資料信息OpenTextFile set filetemp=fstemp.OpenTextFile(file_path,8,true) filetemp.writeLine file filetemp.close set filetemp=Nothing set fstemp=Nothing End Function '********************************************************* ' 目的: 新建一個指定的文件夾,如果存在就不新建 ' 傳入: ' 返回: '********************************************************* Function CreateFolder() Dim fso, f folder="c:\LOG" Set fso = CreateObject("Scripting.FileSystemObject") IF fso.FolderExists(folder) THEN ELSE Set f = fso.CreateFolder(folder) CreateFolderDemo = f.Path END IF End Function '********************************************************* ' 目的:刪除上傳的文件, ' 傳入:傳入上傳文件的虛擬路徑 ' 返回: '********************************************************* Function DeleteFolder(filepath) Dim fso, f folder="EXCEL_DATA.xls" Set fso = CreateObject("Scripting.FileSystemObject") ''response.write fso.FileExists(filepath) IF fso.FileExists(filepath) THEN fso.DeleteFile filepath END IF End Function %>
先谢谢楼上的大哥,能不能 简单说明一下 小弟我搜了一个比较简单的<script language = "javascript" type = "text/javascript"> function a(){ var fso, f1, ts, s; var ForReading = 1; fso = new ActiveXObject("Scripting.FileSystemObject"); // 创建文件 f1 = fso.CreateTextFile("d:\\testfile.txt", true); // 填写一行数据 f1.WriteLine("Hello World"); f1.WriteBlankLines(1); // 关闭文件 f1.Close(); // 打开文件 ts = fso.OpenTextFile("c:\\testfile.txt", ForReading); // 读取文件一行内容到字符串 s = ts.ReadLine(); // 显示字符串信息 alert("File contents = '" + s + "'"); // 关闭文件 ts.Close(); } </script> <body> <form> <input type="button" value="写入并读取" onclick="a()"/> </form> </body>这个例子只能读取一行 现在,我的问题是,如何让他循环读取所有行, 我知道Java上是while(bufferReader.readLine()!=null){...} 另外 我要分析的是 excel文件,不是 txt文件这个会不会有问题啊?明天还要上班,小弟先睡了!
new ActiveXObject("Excel.Application"); 学习点VBA,具体查看Excel的对象浏览器
' 傳入:
' 返回:
'*********************************************************
Function GetExcel()
Dim conn
Dim StrConn
Dim rs
Dim Sql
file=""
Set conn=Server.CreateObject("ADODB.Connection")
StrConn="Driver={Microsoft Excel Driver (*.xls)};DBQ="& Server.MapPath("EXCEL_DATA.xls")
''StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dd.xls;Extended Properties=Excel 8.0"
conn.Open StrConn
Set rs = Server.CreateObject("ADODB.Recordset")
Sql="select * from [Sheet1$]"
rs.Open Sql,conn,2,2
''讀取excel中的字段名稱,並檢察字段順序是否正確
for i=0 to rs.Fields.Count-1
FILE_HEAD=FILE_HEAD&rs(i).Name
next
''response.write FILE_HEAD
IF trim(FILE_HEAD)<>"版本使用單位類綱目節類說明綱說明目說明檔名保存年限共同分類號" THEN
RESPONSE.WRITE "<SCRIPT LANGUAGE='JAVASCRIPT'>alert('EXCEL文件字段順序錯誤或字段數不對!!')</SCRIPT>"
exit Function
END IF
''讀取excel中的資料
do while Not rs.EOF
''將讀取的資料INSERT到oracle數據庫
for i=0 to rs.Fields.Count-1
EDITION=rs(0)
FILE_CODE=rs(2)+rs(3)+rs(4)+rs(5)
FILE_NAME=rs(9)
KIND1_DESC=rs(6)
KIND2_DESC=rs(7)
KIND3_DESC=rs(8)
KIND4_DESC=rs(9)
SAVE_YEAR=rs(10)
FILE_UNIT=rs(1)
COM_FILE_CODE=rs(11)
''==============================================
CHECED_SQL="Select nvl(FILE_CASE,'') FILE_CASE FROM ODM67 where EDITION='"&TRIM(EDITION)&"' and FILE_CODE='"&TRIM(FILE_CODE)&"' "
If mobjDB.OpenSQL(CHECED_SQL) Then
If mobjDB.IsEmpty Then
FILE_CASE="0001"
CASE_DESC="總案"
INS_SQL=""
INS_SQL=INS_SQL & " INSERT INTO ODM67(" & VBCRLF
INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_CASE," & VBCRLF
INS_SQL=INS_SQL & " CASE_DESC,CRT_USER,CRT_DATE," & VBCRLF
INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF
INS_SQL=INS_SQL & " VALUES(" & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(EDITION)&"','"&TRIM(FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_CASE)&"','"&TRIM(CASE_DESC)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"','"&TRIM(NOWTIME)&"')"
CALL mobjDB.ExecSQL(INS_SQL)
End If
End If
''==============================================
INS_SQL=""
INS_SQL=INS_SQL & " INSERT INTO ODM61( " & VBCRLF
INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_NAME,KIND1_DESC," & VBCRLF
INS_SQL=INS_SQL & " KIND2_DESC,KIND3_DESC,KIND4_DESC,SAVE_YEAR," & VBCRLF
INS_SQL=INS_SQL & " FILE_UNIT,COM_FILE_CODE,CRT_USER,CRT_DATE," & VBCRLF
INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF
INS_SQL=INS_SQL & " VALUES(" & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(EDITION)&"','"&TRIM(FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_NAME)&"','"&TRIM(KIND1_DESC)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(KIND2_DESC)&"','"&TRIM(KIND3_DESC)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(KIND4_DESC)&"','"&TRIM(SAVE_YEAR)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_UNIT)&"','"&TRIM(COM_FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"','"&TRIM(NOWTIME)&"')"
''RESPONSE.WRITE INS_SQL& "<BR>"
IF mobjDB.ExecSQL(INS_SQL) THEN
InCount=InCount+1
ELSE
NoCount=NoCount+1
file=file&TODAY&" "&NOWTIME&" "&EDITION&" "&FILE_CODE & VBCRLF
END IF
exit for
next
rs.MoveNext
Loop
rs.close
set rs=nothing
Conn.close
set StrConn=nothing
if file<>"" then
CALL CreateFolder()
call SetFile(file)
strpath=server.mappath("EXCEL_DATA.xls")
call DeleteFolder(strpath)
file=""
end if
End Function
'*********************************************************
' 目的: 新建一個指定的文件,如果存在就不新建,並向文件追加紀錄
' 傳入: file:要追加的數據
' 返回:
'*********************************************************
Function SetFile(file)
file_path="C:\LOG\OD60err.log"
set fstemp=server.CreateObject("Scripting.FileSystemObject")
IF (fstemp.FileExists(file_path)) THEN
ELSE
set filetemp=fstemp.CreateTextFile(file_path,true)
filetemp.writeLine "紀錄匯入失敗資料"
filetemp.close
END IF
''追加失敗資料信息OpenTextFile
set filetemp=fstemp.OpenTextFile(file_path,8,true)
filetemp.writeLine file
filetemp.close
set filetemp=Nothing
set fstemp=Nothing
End Function
'*********************************************************
' 目的: 新建一個指定的文件夾,如果存在就不新建
' 傳入:
' 返回:
'*********************************************************
Function CreateFolder()
Dim fso, f
folder="c:\LOG"
Set fso = CreateObject("Scripting.FileSystemObject")
IF fso.FolderExists(folder) THEN
ELSE
Set f = fso.CreateFolder(folder)
CreateFolderDemo = f.Path
END IF
End Function
'*********************************************************
' 目的:刪除上傳的文件,
' 傳入:傳入上傳文件的虛擬路徑
' 返回:
'*********************************************************
Function DeleteFolder(filepath)
Dim fso, f
folder="EXCEL_DATA.xls"
Set fso = CreateObject("Scripting.FileSystemObject")
''response.write fso.FileExists(filepath)
IF fso.FileExists(filepath) THEN
fso.DeleteFile filepath
END IF
End Function
%>
小弟我搜了一个比较简单的<script language = "javascript" type = "text/javascript">
function a(){
var fso, f1, ts, s;
var ForReading = 1;
fso = new ActiveXObject("Scripting.FileSystemObject");
// 创建文件
f1 = fso.CreateTextFile("d:\\testfile.txt", true);
// 填写一行数据
f1.WriteLine("Hello World");
f1.WriteBlankLines(1);
// 关闭文件
f1.Close();
// 打开文件
ts = fso.OpenTextFile("c:\\testfile.txt", ForReading);
// 读取文件一行内容到字符串
s = ts.ReadLine();
// 显示字符串信息
alert("File contents = '" + s + "'");
// 关闭文件
ts.Close();
}
</script>
<body>
<form>
<input type="button" value="写入并读取" onclick="a()"/>
</form>
</body>这个例子只能读取一行
现在,我的问题是,如何让他循环读取所有行,
我知道Java上是while(bufferReader.readLine()!=null){...}
另外 我要分析的是 excel文件,不是 txt文件这个会不会有问题啊?明天还要上班,小弟先睡了!
学习点VBA,具体查看Excel的对象浏览器
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<script>
function readExcel()
{
var oXL = new ActiveXObject("Excel.Application");
try{
var oWB = oXL.Workbooks.open("D:\\test.xls");
}catch(e){
alert('打开文件失败!');
}
var oSheet = oWB.ActiveSheet;
var nRows=oSheet.usedrange.rows.count;
var nColumns =oSheet.usedrange.columns.count;
for(var i=0;i<nRows;i++)
{
for(var j=0;j<nColumns;j++)
{
alert(oSheet.Cells(i+1,j+1).value);
}
}
oSheet=null;
oWB.close();
oXL=null;
}
</script>
</HEAD> <BODY>
<input type="button" value="读取Excel" onclick="readExcel();">
</BODY>
</HTML>
var oXL = new ActiveXObject("Excel.Application");
会报 automation服务器不能创建,
我在网上试了很多方法都不管用
http://easonfans.javaeye.com/blog/450693
将EXCEL文件传到服务器,让服务器去完成你要的功能,展示在客户端.
---------------------------------------
在IE Internet选项的安全选项里,设置与ActiveX有关的选项.禁用的设置成启用或提示