VB.NET 使用ADODB連接資料庫滙出到EXCEL

‘導入命名空間

Imports ADODB

Imports Microsoft.Office.Interop

?

Private Sub A1()

Dim Sql As String
Dim Cnn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim S As String

?

S = "Provider=OraOLEDB.Oracle.1;Password=密碼;Persist Security Info=True;User ID=用戶名;Data Source=連線位置"
Cnn.Open(S)
Rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient

Sql = "SELECT IMG_FILE.IMG01 FROM SH01.IMG_FILE"
Rs.Open(Sql,Cnn,3,3)
Dim Xls As Excel.Application = New Excel.Application ‘定义excel应用程序
Dim Xlsbook As Excel.Workbook ‘定义工作簿
Dim Xlssheet As Excel.Worksheet ‘定义工作表
Xlsbook = Xls.Application.Workbooks.Add ‘添加新工作簿
Xlssheet = Xlsbook.Sheets(1) ‘第1个工作表的控制句柄
Xls.Visible = True ‘显示excel 程序

Xlssheet.Cells(1,1).value = "表頭1"

Xlssheet.Range("a2").CopyFromRecordset(Rs)
Xlssheet.Cells.EntireColumn.AutoFit()
Xls = Nothing ‘释放EXCEL对象
Rs.Cancel()

End Sub