Excel Data Manipulation Using VB.NET

• Download source - 32.21 KB

Introduction

This will help you to manipulate an Excel file using VB.NET.

Background

If you want to automate Excel files manipulation, this can help you.

Using the Code

Just extract the project and load the Excel file in the directory. Then click process button, it will open a connection with Excel file, manipulate it and close the connection.

'' Using OleDB namespace for connecting with EXCEL as data source. 

Imports System.Data.OleDb
Public Class Form1

''''''''''' A structure is used to store worksheet name and IDs with the Excel file
Private Structure WorkSheetName
Private WorkSheetName As String
Private WorkSheetId As Integer
Public Sub New(ByVal name As String, ByVal id As Integer)
WorkSheetName = name
WorkSheetId = id
End Sub

Public ReadOnly Property getWorkSheetName() As String
Get
Return WorkSheetName
End Get
End Property

Public ReadOnly Property getWorkSheetId() As Integer
Get
Return WorkSheetId
End Get
End Property
End Structure

Private Sub btnLoadFile_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles btnLoadFile.Click
OpenFileDialog1.ShowDialog()
txtFilePath.Text = OpenFileDialog1.FileName

End Sub

Private Sub GetExcelSheetData(ByVal excelFile As String, _
	ByRef alworkSheetName As ArrayList)

' The Connection used to connect to Excel File
Dim oOleDbConnection As OleDbConnection = Nothing
Dim dt As System.Data.DataTable = Nothing

Try

'' Connection String. 
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;"
' The Total no of Sheets in Excel File
Dim excelSheets() As String
Dim i As Integer = 0
Dim row As DataRow
Dim TempDataSet As DataSet
Dim TempDataTable() As DataTable
Dim MainDataSet As New DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim j As Integer

'' Create connection object by using the preceding connection string.
oOleDbConnection = New OleDbConnection(connString)
'' Open connection with the database.
oOleDbConnection.Open()
'' Get the data table containing the schema guid.
dt = oOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

' If there is nothing return.
If dt Is Nothing Then
Exit Sub
End If

' If schema contains some record re Initialize 
' excelSheets array to the desired dimensions
ReDim excelSheets(dt.Rows.Count)

'' Add the sheet name to the string array.

For Each row In dt.Rows
If Integer.Parse(row("TABLE_NAME").ToString().IndexOf("$")) <> -1 Then
excelSheets(i) = row("TABLE_NAME").ToString()
alworkSheetName.Add(New WorkSheetName(row("TABLE_NAME").ToString(), i))
i += 1
End If
Next

'' Loop through all of the sheets if you want too...

ReDim TempDataTable(excelSheets.Length)
For j = 0 To excelSheets.Length - 2
Try
'' Command used to select from Excel file
MyCommand = New System.Data.OleDb.OleDbDataAdapter_
	("select * from [" & excelSheets(j) & "]", connString)
TempDataSet = New System.Data.DataSet
' Fill the dataset with Excel Data.
MyCommand.Fill(TempDataSet)

' Check the desired data type is the same you want to replace with
If TempDataSet.Tables(0).Columns(3).DataType.ToString() = "System.Double" Then
' Change a row. YOU CAN ALTER THIS AS PER YOUR REQUIREMENT.
TempDataSet.Tables(0).Rows(4)(4) = "1000000"
End If

Dim UpdateString As String

' Update the column of the sheet you have changed.
UpdateString = "Update [Sheet1$] set " & _
"Salary = @Salary WHERE Pk = @Pk"

MyCommand.UpdateCommand = New OleDbCommand(UpdateString, oOleDbConnection)
' Add parameters which you have used in Update
MyCommand.UpdateCommand.Parameters.Add("@Salary", OleDbType.LongVarChar, 1000, "Salary")
MyCommand.UpdateCommand.Parameters.Add("@Pk", OleDbType.Numeric, 4, "Pk")

MyCommand.Update(TempDataSet, "Table")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Next

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
'' Clean up.
If Not oOleDbConnection Is Nothing Then
oOleDbConnection.Close()
oOleDbConnection.Dispose()
End If
If Not dt Is Nothing Then
dt.Dispose()
End If
End Try
End Sub

Private Sub btnProcess_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles btnProcess.Click
Dim alworkSheetName As New ArrayList
GetExcelSheetData(txtFilePath.Text, alworkSheetName)

End Sub
End Class

Remarks

DON'T FORGET TO RATE THIS ARTICLE USING VOTE OPTION AT THE BOTTOM.

Any comments/suggestions from the reader are more than welcome.