Dim myConn As OleDbConnection
Dim myAdaptor As OleDbDataAdapter
Dim ds As New DataSet
Try
myConn = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" & path) '路径
If myConn.State <> ConnectionState.Open Then myConn.Open()
myAdaptor = New OleDbDataAdapter("Select * From [" & sheet & "$]", myConn) 'sheet 为 工作表名
myAdaptor.Fill(ds, "ExcelData")
Return ds.Tables(0)
Catch ex As Exception
MsgBox(ex.Message)
Finally
If myConn.State <> ConnectionState.Closed Then myConn.Close()
End Try
//新表插入
SELECT * into Table1 FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
//然后:向已有表插入数据
INSERT INTO Table1
SELECT * FROM OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;
HDR=NO;IMEX=1;database=c:\test2.xls;','select * from [Sheet1$]')