如何将EXCEL数据导入到DATAGRID

如何将EXCEL数据导入到DA TAGRID

设计人:温源

1,Form窗体设计如下

2,源程序

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

ds.Clear()

OpenFileDialog1.Filter = "EXCEl文件

(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv|所有文件(*.*)|*.*"

OpenFileDialog1.FilterIndex = 1

OpenFileDialog1.CheckFileExists = True

OpenFileDialog1.CheckPathExists = True

OpenFileDialog1.ShowDialog()

Dim filename As String

filename = OpenFileDialog1.FileName

If filename = "" Then

MsgBox("没有选择文件!无法进行数据导入")

Exit Sub

End If

EcxelToDataGridView(filename, DataGrid1)

End Sub

Sub EcxelToDataGridView(ByVal filePath As String, ByVal dgv As DataGrid)

Dim strConn, strExcel, sheetname As String

' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & filePath & ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & filePath &

";Extended Properties ='Excel 8.0;HDR=yes'"

Dim conn As New OleDb.OleDbConnection(strConn)

Try

conn.Open()

Catch ex As Exception

MsgBox("错误代码:" & ex.Message & "请将" & filePath & "文件关闭再导入")

Exit Sub

End Try

strExcel = ""

Dim mycommand As OleDb.OleDbDataAdapter

' strExcel = "select * from [sheet112$]"

mycommand = New OleDb.OleDbDataAdapter(strExcel, strConn)

ds = New DataSet

'objConn.Open()

'指定 SelectCommand 属性

Dim dtDATAExcel As New DataTable

dtDATAExcel = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

'获取EXCEL表中表名

'Dim i As Integer

'For i = 0 To dtDATAExcel.Rows.Count - 1

' sheetname = dtDATAExcel.Rows(i)(2)

' LB1.Items.Add(sheetname)

'Next

'LB1.Visible = True

sheetname = dtDATAExcel.Rows(0)(2)

strExcel = "select * from [" & sheetname & "]"

mycommand.SelectCommand = New OleDb.OleDbCommand(strExcel, conn) '创建 CommandBuilder 对象

Dim objCB As OleDb.OleDbCommandBuilder = New

OleDb.OleDbCommandBuilder(mycommand)

Try

mycommand.Fill(ds, "excel")

Catch ex As Exception

MsgBox(ex.Message)

End Try

dgv.DataSource = ds.Tables(0).DefaultView conn.Close()

End Sub