vb数据库excel

㈠ 如何用vb把数据库中的数据，写到 excel并导出

Private Sub Command3_Click()
On Error Resume Next
Dim irow, icol, count, i As Integer
Dim irowcount, icolcount As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim bl As Boolean
Dim key As Integer
Dim RsUserTemp As Recordset
Dim RsOrderTemp As Recordset
Dim a, b
Dim aa As String
aa = Trim(Now)
Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'If rs.RecordCount < 1 Then
'MsgBox ("Error 没有记录")
'Exit Sub
'End If
irowcount = rs.RecordCount
icolcount = 18
count = 0
rs.MoveFirst
For icol = 0 To 2
'xlSheet.Cells(1, 1).Value = "查询数据" '加标头；
Next icol
xlSheet.Cells(1, 1).Value = "时间" '加标头；
xlSheet.Cells(1, 2).Value = "药开度" '加标头；
xlSheet.Cells(1, 3).Value = "药瞬时流量" '加标头；
xlSheet.Cells(1, 4).Value = "药累计流量" '加标头；
xlSheet.Cells(1, 5).Value = "矿浆浓度" '加标头；
xlSheet.Cells(1, 6).Value = "矿浆流量" '加标头
xlSheet.Cells(1, 7).Value = "酸1开度" '加标头；
xlSheet.Cells(1, 8).Value = "酸1瞬时流量" '加标头；
xlSheet.Cells(1, 9).Value = "酸1累计流量" '加标头
xlSheet.Cells(1, 10).Value = "酸2开度" '加标头；
xlSheet.Cells(1, 11).Value = "酸2瞬时流量" '加标头；
xlSheet.Cells(1, 12).Value = "酸2累计流量" '加标头
xlSheet.Cells(1, 13).Value = "酸3开度" '加标头；
xlSheet.Cells(1, 14).Value = "酸3瞬时流量" '加标头；
xlSheet.Cells(1, 15).Value = "酸3累计流量" '加标头
xlSheet.Cells(1, 16).Value = "酸4开度" '加标头；
xlSheet.Cells(1, 17).Value = "酸4瞬时流量" '加标头；
xlSheet.Cells(1, 18).Value = "酸4累计流量"
xlSheet.Cells(1, 19).Value = "酸5开度"
xlSheet.Cells(1, 20).Value = "酸5瞬时流量"
xlSheet.Cells(1, 21).Value = "酸5累计流量"
Adodc1.Recordset.MoveFirst
For a = 2 To 200
b = 1
If Not Adodc1.Recordset.EOF Then
xlSheet.Cells(a, b) = Adodc1.Recordset("时间")
xlSheet.Cells(a, b + 1) = Adodc1.Recordset("药开度")
xlSheet.Cells(a, b + 2) = Adodc1.Recordset("药瞬时流量")
xlSheet.Cells(a, b + 3) = Adodc1.Recordset("药累计流量")
xlSheet.Cells(a, b + 4) = Adodc1.Recordset("酸1开度")
xlSheet.Cells(a, b + 5) = Adodc1.Recordset("酸1瞬时流量")
xlSheet.Cells(a, b + 6) = Adodc1.Recordset("酸1累计流量")
xlSheet.Cells(a, b + 7) = Adodc1.Recordset("酸2开度")
xlSheet.Cells(a, b + 8) = Adodc1.Recordset("酸2瞬时流量")
xlSheet.Cells(a, b + 9) = Adodc1.Recordset("酸2累计流量")
xlSheet.Cells(a, b + 10) = Adodc1.Recordset("酸3开度")
xlSheet.Cells(a, b + 11) = Adodc1.Recordset("酸3瞬时流量")
xlSheet.Cells(a, b + 12) = Adodc1.Recordset("酸3累计流量")
xlSheet.Cells(a, b + 13) = Adodc1.Recordset("酸4开度")
xlSheet.Cells(a, b + 14) = Adodc1.Recordset("酸4瞬时流量")
xlSheet.Cells(a, b + 15) = Adodc1.Recordset("酸4累计流量")
xlSheet.Cells(a, b + 16) = Adodc1.Recordset("酸5开度")
xlSheet.Cells(a, b + 17) = Adodc1.Recordset("酸5瞬时流量")
xlSheet.Cells(a, b + 18) = Adodc1.Recordset("酸5累计流量")
Else
Exit For
End If
Adodc1.Recordset.Move 1
Next
rs.MoveFirst
xlSheet.Cells(2, 2).Value = Trim(Text1.Text) & Trim(Text2.Text)
For irow = 0 To irowcount - 1
Set RsUserTemp = New Recordset
RsUserTemp.CursorLocation = adUseClient
RsUserTemp.Open "select * from 状态数据 " _
& "where user0_id=" & rs!user0_id, Cn, adOpenStatic, adLockReadOnly
xlSheet.Cells(irow + 4, 1).Value = count + 1
xlSheet.Cells(irow + 4, 2).Value = RsUserTemp!user0_id
xlSheet.Cells(irow + 4, 3).Value = RsUserTemp!user0_name
xlSheet.Cells(irow + 4, 4).Value = RsUserTemp!Address
xlSheet.Cells(irow + 4, 5).Value = RsUserTemp!callno1
Set RsUserTemp = Nothing
Set RsOrderTemp = New Recordset
RsOrderTemp.CursorLocation = adUseClient
RsOrderTemp.Open "select * from 状态数据 where user0_id = " _

If RsOrderTemp.RecordCount = 0 Then
Else

RsOrderTemp.MoveFirst
Do While (Not RsOrderTemp.EOF)
key = 0
key = Val(Mid(str(RsOrderTemp!Order_Time), 6, 2))
Select Case key
Case 0
Exit Do
Case 1
xlSheet.Cells(irow + 4, 6).Value = RsOrderTemp!Order_Amount
Case 2
xlSheet.Cells(irow + 4, 7).Value = RsOrderTemp!Order_Amount
Case 3
xlSheet.Cells(irow + 4, 8).Value = RsOrderTemp!Order_Amount
Case 4
xlSheet.Cells(irow + 4, 9).Value = RsOrderTemp!Order_Amount
Case 5
xlSheet.Cells(irow + 4, 10).Value = RsOrderTemp!Order_Amount
Case 6
xlSheet.Cells(irow + 4, 11).Value = RsOrderTemp!Order_Amount
Case 7
xlSheet.Cells(irow + 4, 12).Value = RsOrderTemp!Order_Amount
Case 8
xlSheet.Cells(irow + 4, 13).Value = RsOrderTemp!Order_Amount
Case 9
xlSheet.Cells(irow + 4, 14).Value = RsOrderTemp!Order_Amount
Case 10
xlSheet.Cells(irow + 4, 15).Value = RsOrderTemp!Order_Amount
Case 11
xlSheet.Cells(irow + 4, 16).Value = RsOrderTemp!Order_Amount
Case 12
xlSheet.Cells(irow + 4, 17).Value = RsOrderTemp!Order_Amount
End Select
RsOrderTemp.MoveNext
Loop
End If
Set RsOrderTemp = Nothing
count = count + 1
rs.MoveNext
If bl Then '因为第一条记录还未导出所以让指针回滚；
rs.MovePrevious
End If
Next
xlApp.Visible = True
xlBook.Save
Set xlApp = Nothing
End Sub
这是我的一个代码，参考一下吧。。。导出到EXCEL的 '网络Hi群&飞度编程学社 1195277

㈡ vb镐庝箞镆ユ垒鏁版嵁搴扑腑镄勬暟鎹瀛桦叆鍒癳xcel閲寁b镐庝箞镆ユ垒鏁版嵁搴扑腑镄勬暟鎹瀛桦叆鍒癳xcel閲岄溃

鎭ㄥ彲浠ヤ娇鐢╒B璇瑷缂栧啓涓涓绋嫔簭𨱒ヨ繛鎺ユ暟鎹搴掳纴镓ц屾煡璇㈣鍙ュ苟灏嗙粨鏋滃瓨鍏Excel銆
棣栧厛锛屾偍闇瑕佷娇鐢ˋDODB瀵硅薄寤虹珛涓庢暟鎹搴撶殑杩炴帴锛岀劧钖庝娇鐢≧ecordset瀵硅薄镓ц屾煡璇㈠苟銮峰彇缁撴灉闆嗐
鎺ョ潃锛屾偍鍙浠ヤ娇鐢‥xcel瀵硅薄鍒涘缓涓涓宸ヤ綔绨匡纴骞跺皢镆ヨ㈢粨鏋滈愯屽啓鍏ュ埌Excel涓銆傛渶钖庯纴淇濆瓨骞跺叧闂宸ヤ綔绨垮嵆鍙銆傝繖镙峰氨鑳藉疄鐜板皢鏁版嵁搴扑腑镄勬暟鎹瀛桦叆鍒癊xcel閲屻傝繖涓杩囩▼闇瑕佺粏镊寸殑澶勭悊杩炴帴銆佹煡璇㈠拰Excel镎崭綔镄勫纾甯革纴纭淇濇暟鎹鑳藉熷嗳纭鍦颁缭瀛桦埌Excel涓銆
鐢⊿QL璇鍙ワ纴灏呜〃瀵煎嚭涓篍XCEL銆
SELECT
瀛楁佃〃
INTO
[Excel
8.0;database=D:\ emp\\200803.XLS].Data
FROM 琛

㈢ VB 从sql数据库中把数据导出到Excel表格中，怎么写

SubdataToExcel()
DimconnAsString
DimrsAsNewADODB.Recordset
DimexcelAsObject
DimworkbookAsObject
DimsheetAsObject
Dimi&,j&
'SQL数据库的连接字串
conn="Provider=SQLOLEDB.1;Datasource=服务器地址;initialcatalog=数据库名称;UserId=数据库登录账号;Password=数据库登录账号"
'打开到sql的连接，读取xxx表数据到rs
rs.Open"select*fromxxx",conn,adOpenKeyset,adLockReadOnly
'excel对象、工作簿、工作表
Setexcel=CreateObject("Excel.Application")
Setworkbook=excel.workbooks().Add()
Setsheet=workbook.sheets(1)
excel.Visible=True
'循环读取rs并写入到excel
Fori=1Tors.Fields.Count
j=1
rs.MoveFirst
DoWhileNotrs.EOF
sheet.cells(j,i)=rs.Fields(i-1).Value
rs.MoveNext
j=j+1
Loop
Next
rs.Close
Setrs=Nothing

EndSub

㈣ vb,连接EXCEl数据库

Sub ReadExcel()'读取Excel文件内容
Dim x As Integer
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim AppExcel As New Excel.Application
Set AppExcel = CreateObject("Excel.Application")'创建Excel对象
Set xlBook = AppExcel.Workbooks.Open("D:\数据表.xls") '打开工作簿
If AppExcel Is Nothing Then Exit Sub'错误则退出
For x = 1 To AppExcel.Sheets.Count '找到对应的工作表
If AppExcel.Sheets(x).Name ="工作表名" Then
Set xlSheet = AppExcel.Sheets(x)
Exit For
End if

Next
'下面为单元格的定义
xlSheet.Cells([RowIndex],[ColumnIndex]).Value'单元格(行 , 列)
xlSheet.Range("B2:B2").Value'单元格选择

Set xlSheet = Nothing
Set xlBook = Nothing
Set AppExcel = Nothing
End Sub
'VB6.0必需先引用Microsoft Excel 11.0 Object Library

㈤ VB已读取到数据库数据，如何将数据写入Excel表（OLE控件）的某一单元格中，谢谢！

可对Ole控件关联对象的Excel表格的单元格的Value 属性赋值来写入VB已读到的数据。

Value 属性，返回或设置一个Variant型数，它指定该属性之值，此属性可读/写。

以下是实现代码：

OptionExplicit
DimoBokAsObject'定义oBok为Object数据类型

PrivateSubCommand1_Click()
OLE1.CreateLink"C:zhang2012.xls"'打开要写入的Excel表
SetoBok=OLE1.object'将对象引用赋给变量或属性。
oBok.Sheets(1).Range("A1").Value="VB已读取到数据库数据"'将数据写入Excel表的A列第一行
SetoBok=Nothing'使用Nothing关键字被将对象变量从实际对象中分离开来。
EndSub

Object 变量存储为 32 位（4 个字节）的地址形式，其为对象的引用。利用 Set 语句，声明为 Object 的变量可以赋值为任何对象的引用。

注意 虽然以 Object 类型声明的变量足以适应包含对各种对象的引用，但是绑定到变量引用的对象总是在晚期（运行时）绑定。要强迫在早期（编译时间）绑定的话，须将对象的引用赋值给用特定类名称声明的变量。

使用 Nothing 关键字被将对象变量从实际对象中分离开来。要使用
Set 语句将 Nothing 赋值给对象变量。例如：

Set MyObject = Nothing

Set 语句，将对象引用赋给变量或属性。