vb.net 把excell文件导入到DataGridView
1、打开Microsoft Visual Basic 2010 Express
2、文件-新建项目-Windows窗体应用程序-确定
3、工具箱-所有windows窗体-Button，Combox，Label1和OpenFileDialog1，在Form1上贴加1个Button1按钮和Combox，Label1，OpenFileDialog1，
并且设置DataGridView1的Anchor属性为Bottom, Left, Right
4、在代码的地方写入
Imports System.Data.OleDb
Imports System.IO
Public Class Form1
Private _FilePath As String = ""
Private _DS As DataSet
Private Const _SelectData = "Select * from [<TableName>$]"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.OpenFileDialog1.FileName = _FilePath
OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx|CSV Files (*.csv)|*.csv"
OpenFileDialog1.FilterIndex = 2
If Not Me.OpenFileDialog1.ShowDialog(Me) = vbOK Then
Exit Sub
End If
_FilePath = Me.OpenFileDialog1.FileName
Me.Label1.Text = _FilePath
Dim file As FileInfo = New FileInfo(_FilePath)
Dim extension As String = file.Extension
Dim _Connectstring As String = ""
If extension = ".xlsx" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1;READONLY=TRUE"""
ElseIf extension = ".xls" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;READONLY=TRUE"""
ElseIf extension = ".csv" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Text;HDR=NO;FORMAT=Delimited;READONLY=TRUE"""
End If
Try
Using cn As OleDb.OleDbConnection = New OleDbConnection(_Connectstring.Replace("<FilePath>", _FilePath))
cn.Open()
_DS = New DataSet
Dim tb As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Me.ComboBox1.Items.Clear()
For Each r As DataRow In tb.Rows
If r("TABLE_TYPE") = "TABLE" Then
Me.ComboBox1.Items.Add(r("TABLE_NAME").ToString.Replace("$", ""))
End If
Next
If Me.ComboBox1.Items.Count > 0 Then
Me.ComboBox1.SelectedIndex = 0
End If
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Try
Dim TBName As String = Me.ComboBox1.SelectedItem.ToString
If _DS.Tables.Contains(TBName) Then
Me.DataGridView1.DataSource = _DS.Tables(TBName)
Exit Sub
End If
Dim _Connectstring As String = ""
'HDR=Yes，这代表第一行是标题，不做为数据使用
'如果用HDR=No，则表示第一行不是标题，做为数据来使用
If Microsoft.VisualBasic.Right(Me.Label1.Text, 5) = ".xlsx" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1;READONLY=TRUE"""
ElseIf Microsoft.VisualBasic.Right(Me.Label1.Text, 4) = ".xls" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;READONLY=TRUE"""
ElseIf Microsoft.VisualBasic.Right(Me.Label1.Text, 4) = ".csv" Then
_Connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FilePath>;Extended Properties=""Text;HDR=NO;FORMAT=Delimited;READONLY=TRUE"""
End If
Using cn As OleDb.OleDbConnection = New OleDbConnection(_Connectstring.Replace("<FilePath>", _FilePath))
cn.Open()
Dim sql As String = _SelectData.Replace("<TableName>", TBName)
Using ad As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
ad.Fill(_DS, TBName)
Me.DataGridView1.DataSource = _DS.Tables(TBName)
End Using
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
5、写完以上代码后，点击调试-启动调试。就可以打开excell把数据导入到datagridview中了。
