VB中用excel加载数据库数据

首先在画面上添加控件ADODC，在画面上增加按钮。引用MICROSOFTEXCEL 11.0 OBJECT LIBRAIRY.

1. Dim EXCEAPP As Excel.Application
   
2. Dim rs As New ADODB.Recordset
   
3. Dim j As Integer
   
4. Private Sub Command1_Click()
   
5. Adodc1.CommandType = adCmdText
   
6. Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=Ifixrecords;Data Source=LAIGANG"
   
7. Adodc1.RecordSource = "select * from inbill"
   
8. Adodc1.Refresh
   
9. Set EXCEAPP = Excel.Application
   
10. EXCEAPP.Visible = True
    
11. EXCEAPP.Workbooks.Add
    
12. Set rs = Adodc1.Recordset
    
13. EXCEAPP.Range("D2").Value = "装车报表"
    
14. EXCEAPP.Range("A3").Value = "序号"
    
15. EXCEAPP.Range("B3").Value = "日期"
    
16. EXCEAPP.Range("C3").Value = "提单号"
    
17. EXCEAPP.Range("D3").Value = "油品名称"
    
18. EXCEAPP.Range("E3").Value = "预装量"
    
19. EXCEAPP.Range("F3").Value = "实装量"
    
20. EXCEAPP.Range("G3").Value = "客户名称"
    
21. EXCEAPP.Range("H3").Value = "鹤位号"
    
22. EXCEAPP.Range("I3").Value = "操作员"
    
23. If rs.RecordCount > 0 Then
    
24. rs.MoveFirst
    
25. For i = 1 To rs.RecordCount
    
26. j = i + 3
    
27. EXCEAPP.Cells(j, 1).Value = rs.Fields("id")
    
28. EXCEAPP.Cells(j, 2).Value = rs.Fields("日期")
    
29. EXCEAPP.Cells(j, 3).Value = rs.Fields("提单号")
    
30. EXCEAPP.Cells(j, 4).Value = rs.Fields("油品名称")
    
31. EXCEAPP.Cells(j, 5).Value = rs.Fields("预装量")
    
32. EXCEAPP.Cells(j, 6).Value = rs.Fields("实装量")
    
33. EXCEAPP.Cells(j, 7).Value = rs.Fields("客户名称")
    
34. EXCEAPP.Cells(j, 8).Value = rs.Fields("鹤位号")
    
35. EXCEAPP.Cells(j, 9).Value = rs.Fields("操作员")
    
36. Next i
    
37. Else
    
38. Exit Sub
    
39. EXCEAPP.Selection.CurrentRegion.Select
    
40. End If
    
41. End Sub

复制代码