vb.net读取dbf、Excel、Access数据文件

来源：互联网发布：农历转换公历js 编辑：程序博客网时间：2024/04/29 09:55

看到网上很多朋友在查找使用VB.net如何对DBF、XLS 等常用数据文件进行访问，特别写了个小程序，来演示一下。

由于对具体数据文件的操作已经被封装到了oledb中，所以对数据文件的操作在vb。net的代码是一样的，学要我们做的就是为oldb连接字符串设置为正确连接字符串就行了。有个网站http://www.connectionstrings.com/提供了大量的关于连接字符串的信息，大家可以参考。并不是设置好链接字符串就可以访问所有的数据文件，可能还需要安装数据访问驱动程序。

本实例支持dbf文件、Excel、Access（含2007版）文件的支持

实例界面：

下面就是实例的代码：

Imports System.Data.OleDb

Public Class Form1

Dim dbfconn As OleDb.OleDbConnection = New OleDb.OleDbConnection

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

'dbf文件操作

'http://www.connectionstrings.com/?carrier=dbffoxpro

Dim path, FileName As String

Me.OpenFileDialog1.Title = "选择dbf文件"

Me.OpenFileDialog1.Filter = "dbf文件|*.dbf"

If Me.OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

path = System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName)

FileName = System.IO.Path.GetFileName(OpenFileDialog1.FileName)

FileName = Microsoft.VisualBasic.Left(FileName.ToUpper, FileName.Length - 4)

Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=dBASE IV;User ID=Admin;Password=;"

'Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e: mp;Extended Properties=dBASE IV;User ID=Admin;Password=;"

Dim dbfconn As OleDb.OleDbConnection = New OleDb.OleDbConnection

dbfconn.ConnectionString = conn

'Dim cmd As String = "select * from w"

Dim cmd As String = "select * from " & FileName

Dim adapter As New OleDbDataAdapter(cmd, dbfconn)

Dim topics As New DataSet

adapter.Fill(topics)

Me.DataGridView1.DataSource = topics.Tables(0)

Me.DataGridView1.Refresh()

End If

End Sub

Private Sub open_excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles open_excel.Click

Dim path, FileName, FileExname As String

Dim conn As String

Me.OpenFileDialog1.Title = "选择Excel文件"

Me.OpenFileDialog1.Filter = "Excel 文件|*.xls*"

If Me.OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

path = System.IO.Path.GetFullPath(OpenFileDialog1.FileName)

FileName = System.IO.Path.GetFileName(OpenFileDialog1.FileName)

FileExname = System.IO.Path.GetExtension(OpenFileDialog1.FileName).ToUpper

FileName = Microsoft.VisualBasic.Left(FileName.ToUpper, FileName.Length - 4)

If FileExname = "XLSX" Then

conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

Else

conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=YES';"

End If

dbfconn.ConnectionString = conn

'获取数据表列表

Dim table_list As Data.DataTable = GetSchemaTable(dbfconn, "TABLE")

'在combbox列表控件中显示数据库中包含的数据表

Me.cb_table_list.DataSource = table_list.DefaultView

Me.cb_table_list.ValueMember = "TABLE_NAME"

Me.cb_table_list.DisplayMember = "TABLE_NAME"

End If

End Sub

Private Function GetSchemaTable(ByVal connection As Data.OleDb.OleDbConnection, ByVal Type As String)

' 获取数据表列表

'Type 有："TABLE,VIEW,ACCESS TABLE,SYSTEM TABLE",

Type = Type.ToUpper

connection.Open()

Dim table_list As Data.DataTable

table_list = connection.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Type})

connection.Close()

Return table_list

End Function

Private Sub table_list_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cb_table_list.SelectedIndexChanged

''获取数据表的内容

'MsgBox(Me.cb_table_list.SelectedValue)

If Me.cb_table_list.SelectedValue.ToString <> "System.Data.DataRowView" Then

Dim cmd As String = "select * from [" & Me.cb_table_list.SelectedValue.ToString & "]"

Dim adapter As New OleDbDataAdapter(cmd, dbfconn)

Dim topics As New DataSet

adapter.Fill(topics)

Me.DataGridView1.DataSource = topics.Tables(0)

Me.DataGridView1.Refresh()

End If

End Sub

Private Sub Open_Access_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Open_Access.Click

Dim path, FileName, FileExname As String

Dim conn As String

Me.OpenFileDialog1.Title = "选择Access文件"

Me.OpenFileDialog1.Filter = "Access 文件|*.mdb|Access 2007 文件|*.accdb"

If Me.OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

path = System.IO.Path.GetFullPath(OpenFileDialog1.FileName)

FileName = System.IO.Path.GetFileName(OpenFileDialog1.FileName)

FileExname = System.IO.Path.GetExtension(OpenFileDialog1.FileName).ToUpper

FileName = Microsoft.VisualBasic.Left(FileName.ToUpper, FileName.Length - 4)

If FileExname = "MDB" Then

'access 97 -2003 连接字符串

conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Jet OLEDB:Database Password=;"

Else

'access 2007 连接字符串

conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Jet OLEDB:Database Password=MyDbPassword;"

End If

dbfconn.ConnectionString = conn

'获取数据表列表

Dim table_list As Data.DataTable = GetSchemaTable(dbfconn, "TABLE")

Me.cb_table_list.DataSource = table_list.DefaultView

Me.cb_table_list.ValueMember = "TABLE_NAME"

Me.cb_table_list.DisplayMember = "TABLE_NAME"

End If

End Sub

End Class