vb listview、数据库导出Excel文件
vb 实现导出excel首先要在工程中引用Microsoft Excel 11.0 Object Library库或者其他版本，操作数据库则可以引用Microsoft ActiveX Data Objects 2.0 Library库
代码如下：
Dim Con As New ADODB.Connection
Dim Res As New ADODB.Recordset
'从listview中导出excel文件
Private Sub CmdExcel_Click()
Dim VBExcel As Excel.Application '定义Excel服务器应用程序
Dim ExcelBook As Excel.Workbook '定义Excel工作簿对象
Dim ExcelSheet As Excel.Worksheet '定义Excel工作表对象
Set VBExcel = CreateObject("Excel.Application") '创建一个Excel应用程序
VBExcel.Visible = True '可见
Set ExcelBook = VBExcel.Workbooks.Add '添加Excel工作簿
Set ExcelSheet = ExcelBook.Worksheets("Sheet1") '添加工作表
'指定Excel表的列宽
ExcelSheet.Columns.ColumnWidth = 13
With ListView_Show '所打开的记录集对象
Dim i, j, k As Integer
For i = 1 To .ColumnHeaders.Count
ExcelSheet.Cells(1, i).Value = .ColumnHeaders(i)
Next
For j = 1 To .ListItems.Count
ExcelSheet.Cells(j + 1, 1).Value = .ListItems(j).Text
For k = 1 To .ColumnHeaders.Count - 1
ExcelSheet.Cells(j + 1, k + 1).Value = .ListItems(j).ListSubItems(k)
Next
Next
ExcelBook.SaveAs (App.Path & "myExcel.xlsx")
ExcelBook.RunAutoMacros (1)
ExcelBook.RunAutoMacros (2)
VBExcel.Quit
Set VBExcel = Nothing
Set ExcelBook = Nothing
Set ExcelSheet = Nothing
End With
End Sub
'从数据库中直接导出Excel文件
Private Sub Command1_Click()
Dim VBExcel As Excel.Application '定义Excel服务器应用程序
Dim ExcelBook As Excel.Workbook '定义Excel工作簿对象
Dim ExcelSheet As Excel.Worksheet '定义Excel工作表对象
Set VBExcel = CreateObject("Excel.Application") '创建一个Excel应用程序
VBExcel.Visible = True '可见
Set ExcelBook = VBExcel.Workbooks.Add '添加Excel工作簿
Set ExcelSheet = ExcelBook.Worksheets("Sheet1") '添加工作表
'指定Excel表的列宽
ExcelSheet.Columns.ColumnWidth = 13
Dim intCol As Long
Dim intRow As Long
ExcelSheet.Cells(1, 1).Value = "名称"
ExcelSheet.Cells(1, 2).Value = "数量"
ExcelSheet.Cells(1, 3).Value = "单价"
ExcelSheet.Cells(1, 4).Value = "总价"
Dim strsql As String
strsql = "select * from product"
Set Res = Con.Execute(strsql)
intRow = 1
Res.MoveFirst
Do While Not Res.EOF
For intCol = 0 To Res.Fields.Count - 1
ExcelSheet.Cells(intRow + 1, intCol + 1).Value = Res.Fields(intCol).Value
Next
Res.MoveNext
intRow = intRow + 1
Loop
Res.Close
ExcelBook.SaveAs (App.Path & "myExcel.xlsx") '保存excel
ExcelBook.RunAutoMacros (1)
ExcelBook.RunAutoMacros (2)
VBExcel.Quit
Set VBExcel = Nothing
Set ExcelBook = Nothing
Set ExcelSheet = Nothing
End Sub
Private Sub Form_Load()
ListView_Show.View = lvwReport
ListView_Show.Gridlines = True
ListView_Show.FullRowSelect = True
ListView_Show.ColumnHeaders.Add , , "pname", 1000
ListView_Show.ColumnHeaders.Add , , "pcount", 1000
ListView_Show.ColumnHeaders.Add , , "price", 1000
ListView_Show.ColumnHeaders.Add , , "total", 1000
Call initDB
Call lvwShow(Res)
End Sub
Private Sub initDB()
Con.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User ID=用户名;PWD=密码;Initial Catalog=数据库名;Data Source=服务器名" '连接数据库字符串
Con.Open
Con.CommandTimeout = 20
Res.Open "表名", Con, adOpenDynamic, adLockPessimistic
End Sub
Private Sub lvwShow(Res As ADODB.Recordset) '显示读取数据库的数据
Dim j As Integer
Dim itemA As ListItem
Dim fldName As String
Do While Not Res.EOF
fldName = ListView_Show.ColumnHeaders(1).Text
Set itemA = ListView_Show.ListItems.Add(, , Res.Fields(fldName))
For j = 2 To ListView_Show.ColumnHeaders.Count
fldName = ListView_Show.ColumnHeaders(j)
If IsNull(Res.Fields(fldName)) Then '如果记录为NULL,则给记录赋值为NULL，然后添加记录
itemA.ListSubItems.Add , , Res.Fields(fldName) & "NULL"
Else
itemA.ListSubItems.Add , , Res.Fields(fldName) '记录不为空则添加记录
End If
Next j
Res.MoveNext
Loop
Res.Close
End Sub
