2011-12-12

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.


2011-06-21

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