Import data from an Excel file to ASP.Net application

My customers very often ask how to import their products in our SilverNet inventory system. Usually they have Excel files in various formats. Therefore we are forced to write a new utility for each client. First we create the upload function.Add the the Label, Fileupload and Button controls to our aspx file:


<asp:Label ID="ExceptionDetails" runat="server" CssClass="Warning" EnableViewState="False"
Visible="False" Width="568px"></asp:Label>
<asp:FileUpload ID="FileUpload1" runat="server" Width="500px" />

<asp:Button ID="Button1" runat="server" Text="Upload file" Width="130px" />

Then we have to add the Button click handler to our vb file for checking uploaded files and import data in the inventory database.


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

If Not FileUpload1.HasFile Then

'-- Missing file selection

ExceptionDetails.Text = "Please choose a file to upload."

ExceptionDetails.Visible = True

ElseIf InStr(UCase(FileUpload1.FileName), ".XLS") = 0 Then

'-- Selection of non-JPG file

ExceptionDetails.Text = "You can upload only xls files."

ExceptionDetails.Visible = True

ElseIf FileUpload1.PostedFile.ContentLength > 5000000 Then

'-- File too large

ExceptionDetails.Text = "Uploaded file size must be less than 5 MB."

ExceptionDetails.Visible = True

Else

'-- File upload


Dim fileName As String = "products.xls"


FileUpload1.SaveAs(Server.MapPath("~/files/") & fileName)



Now we have uploaded the Excel file on server and we have to open this file and load all records to a dataset. Add the special function ExcelConnection:


Protected Function ExcelConnection() As OleDbCommand


' Connect to the Excel Spreadsheet

Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & Server.MapPath("~/Pictures/products.xls") & ";" & _

"Extended Properties=Excel 8.0;"


' create your excel connection object using the connection string

Dim objXConn As New OleDbConnection(xConnStr)

objXConn.Open()


' use a SQL Select command to retrieve the data from the Excel Spreadsheet

' the "table name" is the name of the worksheet within the spreadsheet

' in this case, the worksheet name is "Sheet1" and is coded as: [Sheet1$]

Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)

Return objCommand


End Function


Using this function we can extend our sub for the Button click handler:


' Create a new Adapter

Dim objDataAdapter As New OleDbDataAdapter()


' retrieve the Select command for the Spreadsheet

objDataAdapter.SelectCommand = ExcelConnection()


' Create a DataSet

Dim objDataSet As New DataSet()

' Populate the DataSet with the spreadsheet worksheet data

objDataAdapter.Fill(objDataSet)


ExceptionDetails.Visible = True

ExceptionDetails.Text = "File Uploaded"

ExceptionDetails.Text &= "File Name: " & FileUpload1.FileName & "<br/>"

ExceptionDetails.Text &= "File Size: " & FileUpload1.PostedFile.ContentLength & _
" bytes<br/>"


Now we have the dataset with data about products from the customer's Excel file but we want save all these records in our sever database. Usually we use MS SQL Server database and have the special table "Product" . Create a new function for inserting one record in the inventory database.


Function Add_ProductFull( ByVal InvDescription As String, ByVal ProductName As String, ByVal Serial As String) As long

Dim DBConnection As SqlConnection

Dim DBCommand As SqlCommand, InsCommand As SqlCommand

Dim SQLString As String, SQLInString As String, pKey As Long


DBConnection = New
SqlConnection(WebConfigurationManager.ConnectionStrings.Item(1).ConnectionString)

DBConnection.Open()

SQLString = "SELECT ProductKey FROM Product WHERE ProductName = @ProductName"

DBCommand = New SqlCommand(SQLString, DBConnection)

DBCommand.Parameters.AddWithValue("@ProductName", ProductName)

pKey = DBCommand.ExecuteScalar

'check the availability of the product in our database

If pKey > 0 Then


Add_ProductFull = pKey

Else

SQLInString = "INSERT INTO Product (ProductName,InvDescription, Serial)VALUES (@ProductName, @InvDescription,@Serial)"

InsCommand = New SqlCommand(SQLInString, DBConnection)

InsCommand.Parameters.AddWithValue("@ProductName", ProductName)

InsCommand.Parameters.AddWithValue("@InvDescription", InvDescription)

InsCommand.Parameters.AddWithValue("@Serial", Serial)

InsCommand.ExecuteNonQuery()

pKey = DBCommand.ExecuteScalar

Add_ProductFull = pKey

End If

DBConnection.Close()

End Function


It's a very simple example with three columns and you can add more fields. Sometime our customers send us file with 20-30 columns.

Finally we can finish our function for our Button handler.


Dim i As Integer = 0

Try

For i = 0 To objDataSet.Tables(0).Rows.Count - 1

Dim InvDescription As String = objDataSet.Tables(0).Rows(i).Item("Item Name").ToString

Dim Serial As String = objDataSet.Tables(0).Rows(i).Item("Serial").ToString

Dim ProductName As String = objDataSet.Tables(0).Rows(i).Item("Product ID").ToString

Add_ProductFull(ProductName, InvDescription, Serial)

Next

Catch ee As System.InvalidCastException


ExceptionDetails.Text &= "Problem with import file " & ee.Message.ToString "

Exit Sub

End Try

ExceptionDetails.Text &= objDataSet.Tables(0).Rows.Count & " records "


End If


End Sub


That is all. I think this is useful information for those who use ASP.NET in their work.