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中导出

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","price","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,adLock@R_619_3270@ 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

总结

以上是内存溢出为你收集整理的vb listview、数据库导出Excel文件全部内容，希望文章能够帮你解决vb listview、数据库导出Excel文件所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。