Read excel to dataset

This sample show how to read data from excel file by using Microsoft.Jet.OLEDB.

More information about excel connectionstring see http://www.connectionstrings.com/excel

Let's sample code.

Private Function ReadExcelFile(ByVal filePath As String, ByVal fileName As String) As DataSet

'' Excel 2003

Dim sConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filePath + fileName)

sConn += ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Dim sSql As String = "SELECT * FROM [Sheet1$]"

'Select excel range command

' Dim sSql As String = "SELECT * FROM [Sheet1$A1:D3]"

Dim pFile As New FileInfo(fileName)

If (pFile.Extension.ToLower() = ".xlsx") Then

' Excel 2007

sConn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", filePath + fileName)

sConn += ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

End If

Dim ds As New DataSet

Dim pConn As New OleDbConnection(sConn)

Try

pConn.Open()

Dim pCmd As New OleDbCommand()

pCmd.Connection = pConn

pCmd.CommandText = sSql

Dim pAdp As New OleDbDataAdapter()

pAdp.SelectCommand = pCmd

pAdp.Fill(ds)

Catch ex As Exception

Return Nothing

Finally

pConn.Close()

End Try

Return ds

End Function

Note

If found error Could not find installable ISAM that mean your connectionstring was wrong.

ListView

1. Design ListView like this picture.



2. Set Listview property

FullRowSelect = True
GridLines = True
UseCompatibleStateImageBehavior = False
View = Details

3. Using System.Windows.Forms.ListView

4. Sample code to add item to listview (When click button add item to listview)
Dim pObj As New ListViewItem("Code")
pObj.SubItems.Add("Item Name")
pObj.SubItems.Add("10.00")
pObj.SubItems.Add("1")
pObj.SubItems.Add("1.50"))
pObj.SubItems.Add("15.00")
pObj.Tag = 1 ' keep materail id ListView1.Items.Add(pObj)

5. Sample code to delete item in listview when press delete on keyboard.
Private Sub ListView1_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles lstMaterial.KeyUp
If e.KeyCode = Keys.Delete AndAlso ListView1.SelectedItems.Count > 0 Then
For i As Integer = ListView1.SelectedItems.Count - 1 To 0 Step -1
ListView1.Items.RemoveAt(ListView1.SelectedItems(i).Index)
Next
End If
End Sub