导出到Excel最简单。或者用打印控件。 Option Explicit '在原来的ExportToExcel基础上又改写而成 '参数说明:strOpen 一条Sql语句 ' id 格式化编号,可改成String型的,有时候需要再对生成的Excel文件进行一些格式的处理 ' Argu 设置格式时所用到的参数 ' 机子需要有Microsoft Excel 9.0 Object Library或更高版本,使用前不需要引用(但在有的计算机上会显示不了数据,这时需引用) ' 设置更多的格式可录制Excel的宏,然后结合VB的对象浏览器查看这些宏 ' 直接向Excel写入数据时,可以的xlapp.Range(A2)=""或xlapp.Cells(1,2)="",Range(A2)格就是Cells(1,2),是一样的 ' 当合并单元格时,如合并A1-B2,其中A2,B1,B2都会存在,向其中写入数据是无用的,只能向A1写入数据,由于Cells和Range是同一样东西,所以也同理 Public Function ExportToExcel(strOpen As String, Optional id As Integer = -1, Optional Argu As Variant) On Error GoTo Err Dim Rs_Data As New ADODB.Recordset Dim Irowcount As Integer Dim Icolcount As Integer Dim cn As New ADODB.Connection 'Dim xlapp As New Excel.Application 'Dim xlbook As Excel.Workbook 'Dim xlsheet As Excel.Worksheet 'Dim xlQuery As Excel.QueryTable Dim xlapp As Object Dim xlbook As Object Dim xlsheet As Object Dim xlQuery As Object Dim strStartSpread As String With Rs_Data If .state = adStateOpen Then .Close End If .ActiveConnection = g_strConnectString .CursorLocation = adUseClient .CursorType = adOpenKeyset .Source = strOpen .Open End With With Rs_Data If .RecordCount < 1 Then 'MsgBox ("没有记录!") Exit Function End If Irowcount = .RecordCount Icolcount = .Fields.Count End With Set xlapp = CreateObject("Excel.Application") Set xlbook = Nothing Set xlsheet = Nothing Set xlbook = xlapp.Workbooks().Add Set xlsheet = xlbook.Worksheets("sheet1") xlapp.Visible = True strStartSpread = "a1" Select Case id Case 1 strStartSpread = "a3" xlsheet.Range("A1:I1").Select '选中A1至I1 xlapp.Selection.Merge '合并选中的 xlapp.Selection.HorizontalAlignment = xlCenter '水平居中 xlapp.Range("A2:I2").Select xlapp.Selection.Merge xlapp.Cells(1, 1).Value = "国内出货(出库)统计表" xlapp.Cells(2, 1).Value = Format(Argu, "long date") Case Else '2 End Select Set xlQuery = xlsheet.QueryTables.Add(Rs_Data, xlsheet.Range(strStartSpread)) xlQuery.FieldNames = True xlQuery.Refresh xlapp.Application.Visible = True Set xlapp = Nothing Set xlbook = Nothing Set xlsheet = Nothing Exit Function Err: MsgBox "无法导入Excel,请确认计算机正确安装Excel!", vbInformation End Function
晕倒,不用那么麻烦吧!这样就可以了,我就是照着这种方法搞出来的. 添加DATA Environment 再添加一个datareport(打印设计报表)再在你的打印按钮里面插入下面的代码,当然前面的那个打印设置就不说了. DataE1.rsCommand1.Open "select * from 进货单据临时表 where 进货票号 like '" + Trim(Text1.Text) + "'+ '%'order by 进货票号" If DataE1.rsCommand1.RecordCount > 0 Then DR1_yfzkdy.Show Else MsgBox "此进货票号不存在,请重新输入!" DataE1.rsCommand1.Close End If
2、用打印控件,比如水晶报表
建个EXCEL模板
只需要将数据加入到EXCEL中,然后使用EXCEL打印功能
声明--------
EXCEL打印功能可以用vb调哦!
我曾经用VB。NET写过一个这样的程序。
呵呵
Option Explicit
'在原来的ExportToExcel基础上又改写而成
'参数说明:strOpen 一条Sql语句
' id 格式化编号,可改成String型的,有时候需要再对生成的Excel文件进行一些格式的处理
' Argu 设置格式时所用到的参数
' 机子需要有Microsoft Excel 9.0 Object Library或更高版本,使用前不需要引用(但在有的计算机上会显示不了数据,这时需引用)
' 设置更多的格式可录制Excel的宏,然后结合VB的对象浏览器查看这些宏
' 直接向Excel写入数据时,可以的xlapp.Range(A2)=""或xlapp.Cells(1,2)="",Range(A2)格就是Cells(1,2),是一样的
' 当合并单元格时,如合并A1-B2,其中A2,B1,B2都会存在,向其中写入数据是无用的,只能向A1写入数据,由于Cells和Range是同一样东西,所以也同理
Public Function ExportToExcel(strOpen As String, Optional id As Integer = -1, Optional Argu As Variant)
On Error GoTo Err
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim cn As New ADODB.Connection
'Dim xlapp As New Excel.Application
'Dim xlbook As Excel.Workbook
'Dim xlsheet As Excel.Worksheet
'Dim xlQuery As Excel.QueryTable
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlQuery As Object
Dim strStartSpread As String With Rs_Data
If .state = adStateOpen Then
.Close
End If
.ActiveConnection = g_strConnectString
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Source = strOpen
.Open
End With
With Rs_Data
If .RecordCount < 1 Then
'MsgBox ("没有记录!")
Exit Function
End If
Irowcount = .RecordCount
Icolcount = .Fields.Count
End With Set xlapp = CreateObject("Excel.Application")
Set xlbook = Nothing
Set xlsheet = Nothing
Set xlbook = xlapp.Workbooks().Add
Set xlsheet = xlbook.Worksheets("sheet1")
xlapp.Visible = True strStartSpread = "a1" Select Case id
Case 1
strStartSpread = "a3"
xlsheet.Range("A1:I1").Select '选中A1至I1
xlapp.Selection.Merge '合并选中的
xlapp.Selection.HorizontalAlignment = xlCenter '水平居中
xlapp.Range("A2:I2").Select
xlapp.Selection.Merge
xlapp.Cells(1, 1).Value = "国内出货(出库)统计表"
xlapp.Cells(2, 1).Value = Format(Argu, "long date")
Case Else
'2
End Select
Set xlQuery = xlsheet.QueryTables.Add(Rs_Data, xlsheet.Range(strStartSpread)) xlQuery.FieldNames = True
xlQuery.Refresh xlapp.Application.Visible = True
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Exit Function
Err:
MsgBox "无法导入Excel,请确认计算机正确安装Excel!", vbInformation
End Function
添加DATA Environment 再添加一个datareport(打印设计报表)再在你的打印按钮里面插入下面的代码,当然前面的那个打印设置就不说了.
DataE1.rsCommand1.Open "select * from 进货单据临时表 where 进货票号 like '" + Trim(Text1.Text) + "'+ '%'order by 进货票号"
If DataE1.rsCommand1.RecordCount > 0 Then
DR1_yfzkdy.Show
Else
MsgBox "此进货票号不存在,请重新输入!"
DataE1.rsCommand1.Close
End If