<%
''
'*********************************************************
' Purpose: After reading the excel data, insert it into the database and record the number of successful and failed records at the same time.
' Transfer:
' return:
'*********************************************************
Function GetExcel()
Dim conn
Dim StrConn
Dim rs
Dim Sql
file=""
Set conn=("")
StrConn="Driver={Microsoft Excel Driver (*.xls)};DBQ="& ("EXCEL_DATA.xls")
''StrConn = "Provider=.4.0;Data Source=;Extended Properties=Excel 8.0"
StrConn
Set rs = ("")
Sql="select * from [Sheet1$]"
Sql,conn,2,2
''Read the field name in Excel and check whether the field order is correct
for i=0 to -1
FILE_HEAD=FILE_HEAD&rs(i).Name
next
'' FILE_HEAD
IF trim(FILE_HEAD)<>"version using single category table description table description table description file name storage period common classification number" THEN
"<SCRIPT LANGUAGE='JAVASCRIPT'>alert('ExCEL file field order error or field number is incorrect!!')</SCRIPT>"
exit Function
END IF
''Read information in excel
do while Not
''Introduce the read data INSERT to the oracle database
for i=0 to -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 (CHECED_SQL) Then
If Then
FILE_CASE="0001"
CASE_DESC="General"
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 (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)&"')"
'' INS_SQL& "<BR>"
IF (INS_SQL) THEN
InCount=InCount+1
ELSE
NoCount=NoCount+1
file=file&TODAY&" "&NOWTIME&" "&EDITION&" "&FILE_CODE & VBCRLF
END IF
exit for
next
Loop
set rs=nothing
set StrConn=nothing
if file<>"" then
CALL CreateFolder()
call SetFile(file)
strpath=("EXCEL_DATA.xls")
call DeleteFolder(strpath)
file=""
end if
End Function
'*********************************************************
' Purpose: Create a new specified file, if it exists, it will not create it, and add records to the file
'Transfer: file: The data to be added
' return:
'*********************************************************
Function SetFile(file)
file_path="C:\LOG\"
set fstemp=("")
IF ((file_path)) THEN
ELSE
set filetemp=(file_path,true)
"Record import failed data"
END IF
''Add failed data information OpenTextFile
set filetemp=(file_path,8,true)
file
set filetemp=Nothing
set fstemp=Nothing
End Function
'*********************************************************
' Purpose: Create a new specified file folder, and if it exists, it will not be created.
' Transfer:
' return:
'*********************************************************
Function CreateFolder()
Dim fso, f
folder="c:\LOG"
Set fso = CreateObject("")
IF (folder) THEN
ELSE
Set f = (folder)
CreateFolderDemo =
END IF
End Function
'*********************************************************
' Purpose: Delete the uploaded files,
' Transfer: Virtual path to upload files
' return:
'*********************************************************
Function DeleteFolder(filepath)
Dim fso, f
folder="EXCEL_DATA.xls"
Set fso = CreateObject("")
'' (filepath)
IF (filepath) THEN
filepath
END IF
End Function
%>