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.OleDbImports System.IOPublic Class Form1 Private _FileP锾攒揉敫ath 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 IfTryUsing cn As OleDb.OleDbConnection = New OleDbConnection(_Connectstring.Replace("<FilePath>", _FilePath)) cn.Open() _DS = New DataSetDim 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 SubPrivate 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 IfUsing 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 SubEnd Class
5、写完以上代码后，点击调试-启动调试。就可以打开excell把数据导入到datagridview中了。
