利用VB.Net开发出一个通用的报表组件。该组件具有灵活、动态报表功能,用户可以选择其报表内容,报表数据不仅用户可以控制打印,还可以多种文件格式保存。现在把它实现的方法及过程阐述如下:
实现原理:利用EXCEL强大的电子表格功能,把我们需要报表的数据送往EXCEL。同时经过一定的控制,提供给用户一个操作界面就达到目的。这一总个过程,都封装到一个组件中,供 软件直接调用。
开发步骤
1、启动Visual Studio.NET。在新建项目中选择Visual Basic项目,在模板中选择类库,在名称中输入类库文件名如Report确定,则进入类库开发环境中,把Class1换名为CReport。在项目菜单中加入引用,找到 Microsoft Excel 9.0 Object Library 确定后则引用了Excel。在类名最上面写上Imports System Imports System.Windows.Forms,Imports Excel.ApplicationClass,Imports Excel.XlLineStyle,Imports Excel.XlPattern,Imports Excel.XlBorderWeight,Imports Excel.Constants,Imports Excel.XlBordersIndex八条引用语句。这里我们引用了有关.NET的系统命名空间和Excel应用及常量的命名空间。即:
Imports System
Imports System.Windows.Forms
Imports Excel.ApplicationClass
Imports Excel.XlLineStyle
Imports Excel.XlPattern
Imports Excel.XlBorderWeight
Imports Excel.Constants
Imports Excel.XlBordersIndex
2、定义命名空间为Reopot,即:
Namespace Reopot
3、定义公共类CReport,该类为供应用软件调用。定义类CReport的私有变量frmSetup,该变量将启动一个供用户操作的界面。定义类CReport的属性DataGrid,该属性供用户传送一待打印的数据网格名,定义类CReport的属性DataTable,该属性供用户传送一待打印的数据表格名。定义类CReport的公有方法Run,该方法启动该组件。分别编写它们的功能。代码如下:
Public Class CReport
Dim frmSetup As New frmPrintSetup()
Public Property DataGrid() As DataGrid
Get
' DataTable = mDataTable
End Get
Set(ByVal Value As DataGrid)
frmSetup.DataTable = Value.DataSource
frmSetup.MainTitle = Value.CaptionText
End Set
End Property
Public Property DataTable() As DataTable
Get
' DataTable = mDataTable
End Get
Set(ByVal Value As DataTable)
frmSetup.DataTable = Value
End Set
End Property
Public Sub Run()
frmSetup.ShowDialog()
frmSetup = Nothing
End Sub
End Class
4、定义公共类CPublic Excel,该类主要定义Excel的程序对象,启动Excel并打印及保存报表。代码如下:
'这个类是不改变的 (公共性)
Public Class CPublicExcel
Public xlApp As New Excel.Application() 'excel程序对象
'Public gblnSaveReport As Boolean ' 该Report是否输出成为XLS文件
'Public gstrPath As String ' 该Report输出成为XLS的文件名
Private ExcelWasNotRunning As Boolean '原来的excel程序的状态
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal
hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long)
As Long
'识别excel是否运行
Private Function DetectExcel() As Boolean
Const WM_USER = 1024
Dim hWnd As Long
hWnd = FindWindow("XLMAIN", 0)
If hWnd <> 0 Then ' hWnd <> 0 means Excel is
running.
SendMessage(hWnd, WM_USER + 18, 0, 0)
DetectExcel = True
Else
DetectExcel = False
End If
End Function
'打开excel
Public Sub OpenExcelSheet()
ExcelWasNotRunning = Not DetectExcel()
If ExcelWasNotRunning Then
xlApp = GetObject("", "Excel.Application")
Else
xlApp = GetObject(, "Excel.Application")
End If
xlApp.Workbooks.Add() '(ExcelSheetName)
End Sub
'打印预览报表
Public Sub PrintPreview()
xlApp.Caption = "报表"
xlApp.DisplayAlerts = False
xlApp.Visible = True
xlApp.ActiveWorkbook.PrintPreview()
xlApp.Visible = False
End Sub
'打印报表
Public Sub Print()
xlApp.DisplayAlerts = False
xlApp.Visible = False
xlApp.ActiveWorkbook.PrintOut()
End Sub
'保存报表
Public Sub SaveAs(ByVal FileName As String)
Dim file As System.IO.File
Try
'If file.Exists(FileName) = True Then
' file.Delete(FileName)
'End If
xlApp.ActiveWorkbook.SaveAs(FileName)
Catch ex As Exception 'When y = 0 ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
' Beep() ' Beep after error processing.
End Try
End Sub
End Class
实现原理:利用EXCEL强大的电子表格功能,把我们需要报表的数据送往EXCEL。同时经过一定的控制,提供给用户一个操作界面就达到目的。这一总个过程,都封装到一个组件中,供 软件直接调用。
开发步骤
1、启动Visual Studio.NET。在新建项目中选择Visual Basic项目,在模板中选择类库,在名称中输入类库文件名如Report确定,则进入类库开发环境中,把Class1换名为CReport。在项目菜单中加入引用,找到 Microsoft Excel 9.0 Object Library 确定后则引用了Excel。在类名最上面写上Imports System Imports System.Windows.Forms,Imports Excel.ApplicationClass,Imports Excel.XlLineStyle,Imports Excel.XlPattern,Imports Excel.XlBorderWeight,Imports Excel.Constants,Imports Excel.XlBordersIndex八条引用语句。这里我们引用了有关.NET的系统命名空间和Excel应用及常量的命名空间。即:
Imports System
Imports System.Windows.Forms
Imports Excel.ApplicationClass
Imports Excel.XlLineStyle
Imports Excel.XlPattern
Imports Excel.XlBorderWeight
Imports Excel.Constants
Imports Excel.XlBordersIndex
2、定义命名空间为Reopot,即:
Namespace Reopot
3、定义公共类CReport,该类为供应用软件调用。定义类CReport的私有变量frmSetup,该变量将启动一个供用户操作的界面。定义类CReport的属性DataGrid,该属性供用户传送一待打印的数据网格名,定义类CReport的属性DataTable,该属性供用户传送一待打印的数据表格名。定义类CReport的公有方法Run,该方法启动该组件。分别编写它们的功能。代码如下:
Public Class CReport
Dim frmSetup As New frmPrintSetup()
Public Property DataGrid() As DataGrid
Get
' DataTable = mDataTable
End Get
Set(ByVal Value As DataGrid)
frmSetup.DataTable = Value.DataSource
frmSetup.MainTitle = Value.CaptionText
End Set
End Property
Public Property DataTable() As DataTable
Get
' DataTable = mDataTable
End Get
Set(ByVal Value As DataTable)
frmSetup.DataTable = Value
End Set
End Property
Public Sub Run()
frmSetup.ShowDialog()
frmSetup = Nothing
End Sub
End Class
4、定义公共类CPublic Excel,该类主要定义Excel的程序对象,启动Excel并打印及保存报表。代码如下:
'这个类是不改变的 (公共性)
Public Class CPublicExcel
Public xlApp As New Excel.Application() 'excel程序对象
'Public gblnSaveReport As Boolean ' 该Report是否输出成为XLS文件
'Public gstrPath As String ' 该Report输出成为XLS的文件名
Private ExcelWasNotRunning As Boolean '原来的excel程序的状态
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal
hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long)
As Long
'识别excel是否运行
Private Function DetectExcel() As Boolean
Const WM_USER = 1024
Dim hWnd As Long
hWnd = FindWindow("XLMAIN", 0)
If hWnd <> 0 Then ' hWnd <> 0 means Excel is
running.
SendMessage(hWnd, WM_USER + 18, 0, 0)
DetectExcel = True
Else
DetectExcel = False
End If
End Function
'打开excel
Public Sub OpenExcelSheet()
ExcelWasNotRunning = Not DetectExcel()
If ExcelWasNotRunning Then
xlApp = GetObject("", "Excel.Application")
Else
xlApp = GetObject(, "Excel.Application")
End If
xlApp.Workbooks.Add() '(ExcelSheetName)
End Sub
'打印预览报表
Public Sub PrintPreview()
xlApp.Caption = "报表"
xlApp.DisplayAlerts = False
xlApp.Visible = True
xlApp.ActiveWorkbook.PrintPreview()
xlApp.Visible = False
End Sub
'打印报表
Public Sub Print()
xlApp.DisplayAlerts = False
xlApp.Visible = False
xlApp.ActiveWorkbook.PrintOut()
End Sub
'保存报表
Public Sub SaveAs(ByVal FileName As String)
Dim file As System.IO.File
Try
'If file.Exists(FileName) = True Then
' file.Delete(FileName)
'End If
xlApp.ActiveWorkbook.SaveAs(FileName)
Catch ex As Exception 'When y = 0 ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
' Beep() ' Beep after error processing.
End Try
End Sub
End Class