How to make a simple report with an Excel control

The concept of ① report

This report contains the basic elements of the report, namely, headers, footers, horizontal headers, vertical headers, header crosses, and cells.

Header: Located in the top section of the report. As the descriptive information of the report, the name of the statement, the form number, the approving authority, the approval number and so on are summarized.

Footer: In the bottom section of the report, for additional information about the report, such as the unit owner, department head, watchmaker, and so on.

Horizontal head: Also called the Object Bar, is located in the report of the horizontal head of the table, it is a statistical constraint, may also be descriptive information. For statistical tables, a horizontal head can be layered, and the upper-level conditions work for each layer underneath it.

Vertical head: Also called a column, the report vertical direction of the table, it in the statistical table with the head of the same role, but also statistical constraints, may also be descriptive information. In a hierarchical structure, the upper-level conditions work for each layer underneath it.

Header Cross area: In the upper-left corner of the report, you can specify the horizontal and vertical headers of the report in columns.

Cell: The body part of a report. In the statistical report, it is the statistic of the two conditions for the relative transverse head and the vertical header, in the query table is the query information of the horizontal head, or it may be a simple calculation statistic result. The cell content is empty after the new report is filled, and can be modified after the fill.

Make a report in ②visual basic

A report is produced in Visual Basic, typically with the Data Environment Designer (environment Designer), with the Data Report Designer (database Designer), or by using a third-party product such as a very famous crystal ) to complete and output through the ActiveX control. But for most programmers, designing reports is often a painstaking effort. However, we can have a simpler way to meet their requirements. Because of the scalability of Visual Basic, it is quite convenient to use and incorporate Office2000 features, including Excel. Excel can be used as an OLE server to export certain properties, methods, and events externally. Visual Basic can take advantage of these features to achieve integration with Excel. Below, we will elaborate on a VB to create an Excel report example.

③excel objects

Microsoft's Excel object model includes 128 different objects, from simple objects such as rectangles, text boxes, to complex objects such as pivot tables, graphs, and so on. Here's a brief introduction to the most important and the four most used objects.

A, application objects

The Application object is at the top level of the Excel object hierarchy, representing the environment in which Excel itself runs.

B, Workbook objects

The workbook object is directly below the Application object, representing an Excel workbook file.

C, Worksheet objects

The worksheet object is contained in the Workbook object, representing an Excel worksheet.

D, Range objects

A Range object is contained in a Worksheet object that represents one or more cells in an Excel worksheet.

④access2000 Report Solutions

A, make a template in Excel

Before you can generate Excel reports in Visual Basic, you should do a template in Excel, including headers, footers, table headers, and so on, as required by the user.

B, create Excel object in VB, open Excel file

As with other third-party controls, they must be referenced or imported as part before use. To create and work with Excel objects in VB, you must first refer to the Microsoft Excel 9.0 Object Library. Then we can operate the Excel object in VB programming.

Critical code in:

Dim Excelfile as String

Excelfile = App.Path & "Myreport.xls" Excel file name

' Define the Application object for Excel, Application object is equivalent to an Excel program

Dim xlapp as Excel.Application

' Defines the workbook object for Excel, the workbook object is equivalent to an Excel file

Dim Xlbook as Excel.Workbook

' Defines the worksheet object for Excel, worksheet an object that is equivalent to a table in an Excel file

Dim Xlsheet as Excel.Worksheet

' Allocate memory space to application and instantiate it

Set xlapp = New Excel.Application

' Create a Application object

Set xlapp = CreateObject ("Excel.Application")

' Hide Excel programs that are opened by VB

Xlapp.visible = False

' Open the workbook, excelfile as an Excel report file

Set xlbook = XlApp.Workbooks.Open (excelfile)

' Open Excel Worksheet

Set xlsheet = xlbook. Worksheets (1)

C, control Excel worksheet, edit it

If you open a recordset through ADO, and you want to display the data in the recordset in a cell, the key code is:

Do Until Rs. Eof

For i = 0 to Rs. Fields.count-1

Xlsheet. Cells (j + 1, i + 1) = Rs. Fields (i)

Next

Rs. MoveNext

j = j + 1

Loop