|
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,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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
