001.
<%@ Import
Namespace
=
"System.Data"
%>
002.
<%@ Import
Namespace
=
"System.Data.OleDb"
%>
003.
<%@ Import
Namespace
=
"Excel"
%>
004.
<%@ Page Language=
"VB"
%>
005.
<script language=
"VB"
runat=
"server"
>
006.
007.
Sub
btnUpload_OnClick(sender
As
Object
, e
As
EventArgs)
008.
009.
If
Not
IsNothing(myFile1.PostedFile)
Then
010.
011.
Dim
UlFileName
As
String
012.
Dim
i
As
Integer
013.
014.
UlFileName =
"MyXls/"
& System.IO.Path.GetFileName(myFile1.Value)
015.
016.
017.
myFile1.PostedFile.SaveAs(Server.MapPath(UlFileName))
018.
019.
020.
Dim
xlApp
As
New
Excel.Application
021.
Dim
xlBook
As
Excel.Workbook
022.
Dim
xlSheet1
As
Excel.Worksheet
023.
024.
xlBook = xlApp.Workbooks.Open(Server.MapPath(UlFileName))
025.
xlBook.Application.Visible =
False
026.
027.
xlSheet1 = xlBook.Worksheets(1)
028.
029.
030.
Dim
dt
As
New
System.Data.DataTable
031.
Dim
dr
As
System.Data.DataRow
032.
033.
034.
dt.Columns.Add(
"CustomerID"
)
035.
dt.Columns.Add(
"Name"
)
036.
dt.Columns.Add(
"Email"
)
037.
dt.Columns.Add(
"CountryCode"
)
038.
dt.Columns.Add(
"Budget"
)
039.
dt.Columns.Add(
"Used"
)
040.
041.
i = 2
042.
Do
While
Not
Trim(xlSheet1.Cells.Item(i, 1).Value) =
""
043.
044.
dr = dt.NewRow
045.
dr(
"CustomerID"
) = xlSheet1.Cells.Item(i, 1).Value
046.
dr(
"Name"
) = xlSheet1.Cells.Item(i, 2).Value
047.
dr(
"Email"
) = xlSheet1.Cells.Item(i, 3).Value
048.
dr(
"CountryCode"
) = xlSheet1.Cells.Item(i, 4).Value
049.
dr(
"Budget"
) = xlSheet1.Cells.Item(i, 5).Value
050.
dr(
"Used"
) = xlSheet1.Cells.Item(i, 6).Value
051.
dt.Rows.Add(dr)
052.
i = i + 1
053.
Loop
054.
055.
056.
057.
058.
Dim
objConn
As
OleDbConnection
059.
Dim
objCmd
As
OleDbCommand
060.
Dim
strConnString
As
String
061.
Dim
strSQL
As
String
062.
strConnString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
&Server.MapPath(
"database/mydatabase.mdb"
)&
";"
063.
objConn =
New
OleDbConnection(strConnString)
064.
objConn.Open()
065.
066.
For
i = 0
To
dt.Rows.Count - 1
067.
strSQL =
"INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) "
& _
068.
" VALUES ('"
& dt.Rows(i)(
"CustomerID"
) &
"','"
& dt.Rows(i)(
"Name"
) &
"' "
& _
069.
",'"
& dt.Rows(i)(
"Email"
) &
"','"
& dt.Rows(i)(
"CountryCode"
) &
"' "
& _
070.
",'"
& dt.Rows(i)(
"Budget"
) &
"','"
& dt.Rows(i)(
"Used"
) &
"') "
071.
objCmd =
New
OleDbCommand(strSQL, objConn)
072.
objCmd.ExecuteNonQuery()
073.
Next
074.
075.
Me
.lblText.Text =
"Record Inserted."
076.
077.
078.
objConn.Close()
079.
objConn =
Nothing
080.
081.
xlApp.Application.Quit()
082.
xlApp.Quit()
083.
xlSheet1 =
Nothing
084.
xlBook =
Nothing
085.
xlApp =
Nothing
086.
End
IF
087.
088.
End
Sub
089.
090.
</script>
091.
<html>
092.
<head>
093.
<title>ThaiCreate.Com ASP.NET - Excel Application</title>
094.
</head>
095.
<body>
096.
<form id=
"form1"
runat=
"server"
>
097.
<input id=
"myFile1"
type=
"file"
runat=
"server"
>
098.
<input id=
"btnUpload"
type=
"button"
OnServerClick=
"btnUpload_OnClick"
value=
"Upload"
runat=
"server"
/>
099.
<hr />
100.
<asp:Label id=
"lblText"
runat=
"server"
></asp:Label>
101.
</form>
102.
</body>
103.
</html>
104.
</form>