01.
<%
Option
Explicit %>
02.
<html>
03.
<head>
04.
<title>ThaiCreate.Com ASP Excel.Application Tutorial</title>
05.
</head>
06.
<body>
07.
<%
08.
Dim
xlApp,xlBook,xlSheet1,xlSheet2,OpenFile,i
09.
Dim
Conn,strSQL,objExec
10.
Dim
mySmartUpload
11.
Dim
sFileName
12.
13.
14.
15.
16.
Set
mySmartUpload = Server.CreateObject(
"aspSmartUpload.SmartUpload"
)
17.
18.
19.
mySmartUpload.Upload
20.
21.
22.
sFileName = mySmartUpload.Files(
"file1"
).FileName
23.
24.
If
sFileName <>
""
Then
25.
26.
mySmartUpload.Files(
"file1"
).SaveAs(Server.MapPath(
"MyXls/"
&sFileName))
27.
28.
OpenFile =
"MyXls/"
&sFileName
29.
30.
31.
Set
xlApp = Server.CreateObject(
"Excel.Application"
)
32.
Set
xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile))
33.
Set
xlSheet1 = xlBook.Worksheets(1)
34.
35.
Set
Conn = Server.Createobject(
"ADODB.Connection"
)
36.
Conn.Open
"DRIVER=Microsoft Access Driver (*.mdb);DBQ="
& Server.MapPath(
"MyXls/mydatabase.mdb"
),
""
,
""
37.
38.
For
i = 2
To
5
39.
If
Trim(xlSheet1.Cells.Item(i,1)) <>
""
Then
40.
strSQL =
""
41.
strSQL = strSQL &
"INSERT INTO customer2 "
42.
strSQL = strSQL &
"(CustomerID,Name,Email,CountryCode,Budget,Used) "
43.
strSQL = strSQL &
"VALUES "
44.
strSQL = strSQL &
"('"
&xlSheet1.Cells.Item(i,1)&
"','"
&xlSheet1.Cells.Item(i,2)&
"', '"
&xlSheet1.Cells.Item(i,3)&
"' "
45.
strSQL = strSQL &
",'"
&xlSheet1.Cells.Item(i,4)&
"','"
&xlSheet1.Cells.Item(i,5)&
"', '"
&xlSheet1.Cells.Item(i,6)&
"') "
46.
Set
objExec = Conn.Execute(strSQL)
47.
Set
objExec =
Nothing
48.
End
IF
49.
Next
50.
51.
xlApp.Application.Quit
52.
53.
54.
Conn.Close()
55.
Set
Conn =
Nothing
56.
Set
xlSheet1 =
Nothing
57.
Set
xlBook =
Nothing
58.
Set
xlApp =
Nothing
59.
End
If
60.
61.
Set
mySmartUpload =
Nothing
62.
%>
63.
Data Import/Inserted <a href=
"mydatabase.mdb"
>Click here</a> to Download.
64.
</body>
65.
</html>