Using VB to control Excel to generate report

Excel| control as a simple, system-wide Windows Application development tool, Visual Basic 5 has powerful data-processing capabilities and provides a variety of methods for access to Microsoft SQL Server, Oracle, Xbase and other databases are widely used in the establishment of various information management systems. However, VB lacks sufficient, conforms to the Chinese custom data table output function, although uses the Crystal report control and the Crystal reports program may output the statement, but the operation is very troublesome, the Chinese processing ability also is not ideal. Excel as a Micorsoft company's table processing software in the table has a powerful function, we can write a direct control of Excel VB5 program, the method is to use VB OLE Automation technology to obtain Excel 97 control handle, thus directly control the Excel A series of 97 operations.

An example is given below:

First create a form (FORM1) to add a data control and a button to a form that references the Microsoft Excel type library: Select the References column from the Project menu, select the Microsoft Excel 8.0 Object Library, and select OK.

In the form's Load event, add:

Data1.databasename = database name
Data1.recordsource = table Name
Data1.refresh
　　
Join in the Click event of the button

Dim IRow, Icol as Integer
Dim Irowcount, Icolcount as Integer
Dim Fieldlen () Save field length value
Dim xlapp as Excel.Application
Dim Xlbook as Excel.Workbook
Dim Xlsheet as Excel.Worksheet
Set xlapp = CreateObject ("Excel.Application")
Set xlbook = XlApp.Workbooks.Add
Set xlsheet = xlbook.worksheets (1)

With Data1.Recordset.MoveLast

If. RecordCount < 1 Then
MsgBox ("Error no record!")
Exit Sub
End If

Irowcount =. RecordCount "Total Records
Icolcount =. Fields.Count "Total number of fields

ReDim Fieldlen (Icolcount). MoveFirst

For irow = 1 to Irowcount + 1
For icol = 1 to Icolcount
Select Case IRow
Case 1 "First row in Excel with title
Xlsheet.cells (IRow, Icol). Value =. Fields (ICOL-1). Name
Case 2 "To save an array Fieldlen () as a section of the first record

If IsNull (. Fields (ICOL-1) = True Then
Fieldlen (Icol) = LenB (. Fields (ICOL-1). Name)
"If the field value is NULL, set the value of the array FileLen (ICOL) to the width of the title name
Else
Fieldlen (Icol) = LenB (. Fields (ICOL-1))
End If

Xlsheet.columns (Icol). ColumnWidth = Fieldlen (icol)
"Excel column widths are equal to character lengths
Xlsheet.cells (IRow, Icol). Value =. Fields (ICOL-1)
"Write field values to cells in Excel
Case Else
Fieldlen1 = LenB (. Fields (ICOL-1))

If Fieldlen (Icol) < Fieldlen1 Then
Xlsheet.columns (Icol). ColumnWidth = Fieldlen1
"Table column width equals long word length
Fieldlen (icol) = Fieldlen1
"Array Fieldlen (ICOL) holds the maximum field length value
Else
Xlsheet.columns (Icol). ColumnWidth = Fieldlen (icol)
End If

Xlsheet.cells (IRow, Icol). Value =. Fields (ICOL-1)
End Select
Next
If irow <> 1 Then
If not. EOF Then. MoveNext
End If
Next
With Xlsheet
. Range (. Cells (1, 1),. Cells (1, Icol-1)). Font.Name = "Blackbody"
"Set title to bold text
. Range (. Cells (1, 1),. Cells (1, Icol-1)). Font.Bold = True
"Title Font Bold
. Range (. Cells (1, 1),. Cells (IRow, ICOL-1)). Borders.LineStyle = xlcontinuous
"Set table border style
End With
Xlapp.visible = True "Show Table
Xlbook.save "Save
Set xlapp = Nothing "return control to Excel
End With


This procedure is passed in Chinese Windows98, Chinese VB5.