Loop through dataset

Imports System.Data.OleDb

Imports System.Data

Imports System.Diagnostics

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

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

"Data Source=E:\Vikas\AutoExcel\myexcel.xls;" & _

"Extended Properties=""Excel 8.0;HDR=no"""

Dim conn2 As New OleDbConnection(m_sConn1)

Dim da As New OleDbDataAdapter("Select * From [Cities$]", conn2)

Dim ds As DataSet = New DataSet()

Dim i = 0

Dim j

da.Fill(ds)

Dim dr As DataRow

If ds.Tables(0).Rows.Count > 0 And ds.Tables(0).Rows.Count > 10 Then

For Each dr In ds.Tables(0).Rows

For j = 0 To 7

If UCase(ds.Tables(0).Rows(i).Item(j).ToString) = "TOTAL" Then

MsgBox(ds.Tables(0).Rows(i).Item(j + 1).ToString)

End If

Next

i = i + 1

Next

End If

GridView1.DataSource = ds.Tables(0)

GridView1.DataBind()

conn2.Close()

End Sub

Updating excel with ADO.NET

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

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

"Data Source=E:\Vikas\AutoExcel\myexcel.xls;" & _

"Extended Properties=""Excel 8.0;HDR=Yes"""

Dim conn2 As New OleDbConnection(m_sConn1)

Dim da As New OleDbDataAdapter("Select * From [Cities$]", conn2)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "MyInventoryTable")

' Generate the UpdateCommand and add the parameters for the command.

da.UpdateCommand = New OleDbCommand( _

"UPDATE [Cities$] SET Qty = ?, Price=? WHERE Product = ?", conn2)

da.UpdateCommand.Parameters.Add("@Qty", OleDbType.Numeric).SourceColumn = "Qty"

da.UpdateCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"

da.UpdateCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")

' Update the first two records.

ds.Tables(0).Rows(0)("Qty") = 1000

ds.Tables(0).Rows(0)("Price") = 10.1

ds.Tables(0).Rows(1)("Qty") = 2000

ds.Tables(0).Rows(1)("Price") = 20.2

' Apply the dataset changes to the actual data source (the workbook).

da.Update(ds, "MyInventoryTable")

conn2.Close()

End Sub

Add rows in excel workbook

Code below shows how to add new row in excel sheet using ADO.NET

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

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

"Data Source=E:\Vikas\AutoExcel\myexcel.xls;" & _

"Extended Properties=""Excel 8.0;HDR=Yes"""

Dim conn2 As New OleDbConnection(m_sConn1)

Dim da As New OleDbDataAdapter("Select * From [Cities$]", conn2)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "myexcel")

' Generate the InsertCommand and add the parameters for the command.

da.InsertCommand = New OleDbCommand( _

"INSERT INTO [Cities$] (Product, Qty, Price) VALUES (?, ?, ?)", conn2)

da.InsertCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")

da.InsertCommand.Parameters.Add("@Qty", OleDbType.Double).SourceColumn = "Qty"

da.InsertCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"

' Add two new records to the dataset.

Dim dr As DataRow

dr = ds.Tables(0).NewRow

dr("Product") = "Bread" : dr("Qty") = 390 : dr("Price") = 1.89 : ds.Tables(0).Rows.Add(dr)

dr = ds.Tables(0).NewRow

dr("Product") = "Milk" : dr("Qty") = 99 : dr("Price") = 2.59 : ds.Tables(0).Rows.Add(dr)

' Apply the dataset changes to the actual data source (the workbook).

da.Update(ds, "myexcel")

conn2.Close()

End Sub

Excel with Asp.net

Following is the code which will retrieve records from excel file and show up in output window. You can modify it to show up in grid as well. Myexcel file contains a sheet named cities and three columns Product, qty and price.

Imports System.Data.OleDb

Imports System.Data

Imports System.Diagnostics

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

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

"Data Source=E:\AutoExcel\myexcel.xls;" & _

"Extended Properties=""Excel 8.0;HDR=Yes"""

Dim conn2 As New OleDbConnection(m_sConn1)

Dim da As New OleDbDataAdapter("Select * From [Cities$]", conn2)

Dim ds As DataSet = New DataSet()

da.Fill(ds)

Debug.WriteLine(vbCrLf & "InventoryData:" & vbCrLf & "==============")

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows 'Show results in output window

Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _

dr("Product"), dr("Qty"), dr("Price")))

Next

conn2.Close()

End Sub

End Class