ASP.NET(vb.net) & Export excel Spreadsheet Data into Database (Excel.Application) - This article example scripts you will learn how to Export excel Spreadsheet data into database using ASP.NET Scripts.
ShotDev Focus:
- ASP.NET(vb.net) & Export excel Spreadsheet Data into Database (Excel.Application)
Example
AspNetImportExcelToDatabase.aspx
<%@ Import Namespace="Excel"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
IF NOT Page.IsPostBack() Then
BindData()
End IF
End Sub
Sub BindData()
Dim OpenFile As String
Dim i As Integer
OpenFile = "MyXls/MyCustomer.xls"
'*** Create Excel.Application ***'
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile))
xlBook.Application.Visible = False
xlSheet1 = xlBook.Worksheets(1)
'*** Create DataTable ***'
Dim dt As New System.Data.DataTable
Dim dr As System.Data.DataRow
'*** Column ***'
dt.Columns.Add("CustomerID")
dt.Columns.Add("Name")
dt.Columns.Add("Email")
dt.Columns.Add("CountryCode")
dt.Columns.Add("Budget")
dt.Columns.Add("Used")
For i = 2 To 5
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = xlSheet1.Cells.Item(i, 1).Value
dr("Name") = xlSheet1.Cells.Item(i, 2).Value
dr("Email") = xlSheet1.Cells.Item(i, 3).Value
dr("CountryCode") = xlSheet1.Cells.Item(i, 4).Value
dr("Budget") = xlSheet1.Cells.Item(i, 5).Value
dr("Used") = xlSheet1.Cells.Item(i, 6).Value
dt.Rows.Add(dr)
Next
'*** End DataTable ***'
'*** BindData To Repeater ***'
Me.myRepeater.DataSource = dt
Me.myRepeater.DataBind()
'*** Quit and Clear Object ***'
xlApp.Application.Quit()
xlApp.Quit()
xlSheet1 = Nothing
xlBook = Nothing
xlApp = Nothing
End Sub
Sub Button1_Click(sender As Object, e As EventArgs)
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim strConnString As String
Dim strSQL As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim chkCusID As System.Web.UI.WebControls.CheckBox
Dim lblCustomerID,lblName,lblEmail,lblCountryCode,lblBudget,lblUsed As System.Web.UI.WebControls.Label
Dim i As Integer
For i = 0 To myRepeater.Items.Count - 1
chkCusID = myRepeater.Items(i).FindControl("chkCustomerID")
lblCustomerID = myRepeater.Items(i).FindControl("lblCustomerID")
lblName = myRepeater.Items(i).FindControl("lblName")
lblEmail = myRepeater.Items(i).FindControl("lblEmail")
lblCountryCode = myRepeater.Items(i).FindControl("lblCountryCode")
lblBudget = myRepeater.Items(i).FindControl("lblBudget")
lblUsed = myRepeater.Items(i).FindControl("lblUsed")
IF chkCusID.Checked = True Then
'*** Insert Record ***'
strSQL = "INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
" VALUES ('" & lblCustomerID.Text & "','" & lblName.Text & "','" & lblEmail.Text & "', " & _
"'" & lblCountryCode.Text & "','" & lblBudget.Text & "','" & lblUsed.Text & "') "
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
End IF
Next
Me.pnlForm.Visible = False
Me.lblText.Text = "Record Inserted."
objConn.Close()
objConn = Nothing
End Sub
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Panel id="pnlForm" runat="server">
<table border="1">
<asp:Repeater id="myRepeater" runat="server">
<HeaderTemplate>
<tr>
<th>Select</th>
<th>CustomerID</th>
<th>Name</th>
<th>Email</th>
<th>CountryCode</th>
<th>Budget</th>
<th>Used</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><asp:CheckBox id="chkCustomerID" runat="server">
</asp:CheckBox></td>
<td align="center"><asp:Label id="lblCustomerID" runat="server" Text='<%#Container.DataItem("CustomerID") %>'>
</asp:Label></td>
<td><asp:Label id="lblName" runat="server" Text='<%#Container.DataItem("Name") %>'>
</asp:Label></td>
<td><asp:Label id="lblEmail" runat="server" Text='<%#Container.DataItem("Email") %>'>
</asp:Label></td>
<td align="center"><asp:Label id="lblCountryCode" runat="server" Text='<%#Container.DataItem("CountryCode") %>'>
</asp:Label></td>
<td align="right"><asp:Label id="lblBudget" runat="server" Text='<%#Container.DataItem("Budget") %>'>
</asp:Label></td>
<td align="right"><asp:Label id="lblUsed" runat="server" Text='<%#Container.DataItem("Used") %>'>
</asp:Label></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<br />
<asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Submit"></asp:Button>
<hr />
</asp:Panel>
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
.
.
.
Download this script.
Loading ...