Knowing how to read Microsoft Excel rows from your Visual Basic.NET application can make your application more flexible. In VB, you can create SQL strings and query Excel spreadsheets based on criteria to avoid reading the entire sheet. This is accomplished through the use of a data adaptor and the data set object. You can use LINQ to manage the data returned and manipulate one row at a time in the data set.
- Skill level:
Other People Are Reading
Launch Microsoft Excel and type "Name" in cell "A1," "Age" in "B1," "John" in "A2," "10" in "B2," "Susan" in "A3," "15" in "B3," "Tom" in "A4," "10" in "B4," "Bob" in "A5" and "10" in "B5." Save the spreadsheet in "C:\" as "ExcelFile.xlsx." Close Excel.
Launch Microsoft Visual Basic.NET and click "New Project" from the left pane of your screen to launch the "New Project" dialogue window. Click "Windows Forms Application" and click "OK" to start a new project.
Double-click "ListBox" from the "Toolbox" pane to add a new list box control to the form. Add a new button using the same technique.
Double-click the form to create a form load event. Add the following code to define the path of the Excel spreadsheet you want to read and create the object variables you will use to read the file:
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFile.xlsx;Extended Properties=Excel 12.0;"
Dim my Connection As OleDbConnection = New OleDbConnection Dim ds As DataSet Dim da As OleDbDataAdapter Dim strSQL As String
Define the SQL string to query rows where "Age" is equal to 10 and read the Excel spreadsheet using a data adaptor and a data set.
strSQL = "SELECT NAME, AGE FROM [Sheet 1$] WHERE Age = 10" myConnection.ConnectionString = connString da = New OleDbDataAdapter(strSQL, myConnection) ds = New DataSet da.Fill(ds, "dataTable") myConnection.Close()
Read the data returned from the data set and store it in the "ExcelRows" global array for future use.
Dim excel Data = ds.Tables("dataTable").AsEnumerable() Dim row Query = From p In excel Data _ Select p!NAME, p!AGE rowCntReturned = rowQuery.Count ExcelRows = rowQuery.ToArray
Add the following global variables above the "Form1_Load" event:
Dim rowCntr As Integer Dim rowCntReturned As Integer
Switch back to the form design and double-click the button to create a click event. Add the following code to add one row at a time to the list box control.
If (rowCntr < rowCntReturned) Then
Me.ListBox1.Items.Add(ExcelRows(rowCntr)) rowCntr += 1 End If
Press "F5" to run the program and click "Button1" to add the first row of the spreadsheet. Click the button again to add the second row and click it one last time to add the third row to the list box control.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for