使用 VB.NET 操作 Excel 数据

• 下载源代码 - 32.21 KB

引言

这将帮助您使用 VB.NET 操作 Excel 文件。

背景

如果您想自动化 Excel 文件操作，这可以帮助您。

Using the Code

只需解压缩项目并将 Excel 文件加载到目录中。然后单击“处理”按钮，它将打开与 Excel 文件的连接，对其进行操作并关闭连接。

'' 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

备注

别忘了使用页面底部的投票选项对本文进行评分！

欢迎读者提出任何意见/建议。