Print complex reports using VB Design

Developers of database management systems often lament that our Chinese reports are too complex! The absence of rules, nesting, diagonal lines, and cross lines has always been the biggest problem for developers. Although designing a database has certain skills, designing data operations also requires certain logic analysis capabilities, but these problems should not be a problem for general developers. No matter how flexible the algorithms you use and how convenient the operations are, what they are most interested in is how beautiful their reports are, how easy is the output report operation (preferably a button to solve all the problems ). I have some experience in developing a database management system. From FoxPro, Delphi, and PowerBuilder to the current VB, I have encountered the problem of designing and printing reports, these software have their own merits in designing reports. I will not elaborate on them here. Here I will only introduce you to a method that I have been most satisfied with so far to design and print reports: Using VB to Operate Excel to generate complex reports.

 

1. Use VB to create an external Excel Object

 

Most large ActiveX-enabled applications and other ActiveX components provide a top-level external creation object in their object hierarchy. This object provides access to other objects in this level, and also provides methods and attributes that work for the entire application.

 

For example, each Microsoft Office application provides a top-level application object. The following statement shows a reference to the Microsoft Excel application object:

 

Dim xlapp as Excel. Application

Set xlapp = new excel. Application

---- Then, you can use these variables to access the subordinate objects in the Excel application, and the attributes and methods of these objects. For example:

 

Set xlapp = Createobject ("Excel. application ")

'Activate the Excel application

Xlapp. Visible = false' hide the Excel application window

Set xlbook = xlapp. workbooks. Open (strdestination)

'Open the workbook. strdestination is an Excel report file.

Set xlsheet = xlbook. worksheets (1)

'Set the worksheet

---- 2. design the report template file with Excel 97

 

---- Excel 97 is an excellent tool for creating reports. It provides the ability to merge, split, and plot cells at will to meet the needs of designing all complex reports. It controls the format of any cell at will, and provides powerful support for designing reports as you like.

 

---- Based on the reports provided by users, we can quickly generate template files in Excel. The so-called template file is only designed to meet the needs of users in many aspects. It is also a bit of preparation work to adapt to future changes in the report. For example, if you need to print hundreds of employee records, but the format is the same, and the table format may need to be changed as time and actual conditions change, we can obviously design a template file to "remain unchanged.

 

---- When generating a worksheet, we should record the cell number of the content to be filled and the data fields to be filled in the cell. In this way, a table can be written at a glance. For example:

 

Cell (4, 2) employee name cell (6, 6) Graduation School

Cell (4, 4) Employee Gender Cell (6, 7) majors

Cell () Working hours

(Table 1)

---- In the program, we certainly do not need to operate on the template file, we only need to copy a template file (this is also a purpose and benefit of designing a template file ). Example:

 

Dim strsource, strdestination as string

Strsource = app. Path & "/excels/registerfee.xls"

'Registerfee.xls is a template file.

Strdestination = app. Path & "/excels/temp.xls"

Filecopy strsource, strdestination

'Copy the template file to a temporary file

---- 3. Generate the worksheet content

 

---- With the above two steps, we can assign values to each cell according to the format of (table 1. For example:

 

Datprimaryrs. recordset. movefirst

'Datprimaryrs is the Data Control

If isnull (datprimaryrs. recordset! Name) = false then

Xlsheet. cells (4, 2) = datprimaryrs. recordset! Name

End if

If isnull (datprimaryrs. recordset! Gender) = false then

Xlsheet. cells (4, 4) = datprimaryrs. recordset! Gender

End if

If isnull (datprimaryrs. recordset! Ethnic group) = false then

Xlsheet. cells (4, 6) = datprimaryrs. recordset! Ethnicity

End if

..................

4. Print reports

 

After a worksheet is generated, you can issue a print command to excel.

 

Note that you should save a temporary Excel file before executing the print operation to avoid prompting you to save the modified file after exiting the application. Statement:

 

Xlbook. Save 'Save the file

Xlsheet. Printout

Xlapp. Quit exit Excel

Now, you can see that the design of report printing is implemented in the background using an excel program. Users cannot see the specific process at all. They only see a beautiful report that is easily printed out.