看到网上很多朋友在查找使用VB.net如何对DBF、XLS 等常用数据文件进行访问,特别写了个小程序,来演示一下。
由于对具体数据文件的操作已经被封装到了oledb中,所以对数据文件的操作在vb。net的代码是一样的,学要我们做的就是为oldb连接字符串设置为正确连接字符串就行了。有个网站http://www.connectionstrings.com/提供了大量的关于连接字符串的信息,大家可以参考。并不是设置好链接字符串就可以访问所有的数据文件,可能还需要安装数据访问驱动程序。
本实例支持dbf文件、Excel、Access(含2007版)文件的支持
实例界面:
下面就是实例的代码:
Imports
System.Data.OleDb
Public Class Form1 Class Form1
Dim dbfconn As OleDb.OleDbConnection = New OleDb.OleDbConnection
Private Sub Button1_Click()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()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()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()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()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
Public Class Form1 Class Form1
Dim dbfconn As OleDb.OleDbConnection = New OleDb.OleDbConnection
Private Sub Button1_Click()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()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()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()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()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