VHP Computing Group. Home Page.
TechnologiesSpecialistsDownloadLinksSign GuestbookView Guestbook

4 June, 2000
Vladimir Trukhin
Senior Software Engineer
JSC "Votkinsk Hydroelectric Power Plant"
Fax: +7 (34241) 63297
E-mail:
vlt@votges.ru

Create a report in the Microsoft WORD format.

The beautiful and visual report is fine, but beautiful and visual report saved in a file for further usage is even better.

The Problem

Each, who uses Visual FoxPro, certainly, uses its powerful resource for creation of reports. You can output results of a report on the screen, the printer and even in the file without difficulty.

There is a small problem. If you output a report in the file it loses all units of formatting which you have applied to place accents and to make the report effective.

It is possible to involve the OLE Automation mechanism to resolve this problem. It can be the MS Word OLE server. However, in this case will come to write a fair amount of the code to program appearance of the report. And how much it is necessary to do operations, if there will be a necessity to change it.

The Solution

Considerably to simplify the process of creation of such report it is possible in the event that to apply the Automation mechanism not to the all document but only to its part which contains the data.

Appearance of the report including layout of units and the formatting creates immediately as the MS Word document by resources MS Word itself. Thus the special fields representing expressions Visual FoxPro can be inserted into a body of the document. The end and the beginning of the fields signed by the predetermined characters.

As a matter of fact creates some template of the report. The application written on Visual FoxPro should open the MS Word document and to find fields with expressions, to calculate values of expressions and to substitute fields of the template with result of calculation of expressions using the Automation.

As all data fields are expressions Visual FoxPro that usage of the Automation mechanism it is possible to reduce to creation of one class handling the template of the report and creating the file of the report.

Both lacks thus are eliminated: the necessity passes to write the long code and user of the application can freely change appearance of the report including the order of following of the fields. Besides the user can add in the report own fields containing correct Visual FoxPro expressions.

The structure of the class, which handles the report, can look like this:

WordReport class definition:
**************************************************
*-- Class: WordReport
*-- ParentClass: Custom
*-- BaseClass: Custom
DEFINE CLASS WordReport AS custom

Name = "WordReport"
*-- Template file name
TemplateFile = ''

*-- Report file name
ReportFile = ''

*-- MS Word application object
PROTECTED WordApplication

*-- MS Word document object
PROTECTED WordDocument

*-- Reference to MS Word application object
Reference = .F.

*-- Create document from template
PROCEDURE create
* It is the code handling the template and creating
* the document.
    LOCAL loApp, loDoc
    loApp=THIS.WordApplication
    loDoc=THIS.WordDocument
    loDoc.SaveAs(THIS.ReportFile)
    local lcFoundText, lcCommand
    loApp.Selection.Find.Execute('\<*\>',,,.T.,,,.T.,1)
    lcFoundText=loApp.Selection.Text
    DO WHILE SUBSTR(lcFoundText,1,1)='<' AND ;
      SUBSTR(lcFoundText,LEN(lcFoundText),1)='>'
        lcCommand=loApp.Selection.Text
        lcCommand=SUBSTR(lcCommand,1,LEN(lcCommand)-1)
        lcCommand=SUBSTR(lcCommand,2)
        loApp.Selection.Text=EVALUATE(lcCommand)
        loApp.Selection.Find.Execute('\<*\>',,,.T.,,,.T.,1)
        lcFoundText=loApp.Selection.Text
    ENDDO
    loDoc.Save()
ENDPROC

*-- Show MS Word window
PROCEDURE show
    LPARAMETERS nStyle
    LOCAL loApp
    loApp=this.WordApplication
    loApp.Visible=.T.
ENDPROC

*-- Hide MS Word window
PROCEDURE hide
    LOCAL loApp
    loApp=THIS.WordApplication
    loApp.Visible=.F.
ENDPROC

*-- Print report
PROCEDURE print
    LPARAMETERS cText
    LOCAL loApp
    loApp=THIS.WordApplication
    IF loApp.Visible
        loApp.Dialogs(88).Show()
    ELSE
        THIS.Show()
        loApp.Dialogs(88).Show()
        THIS.Hide()
    ENDIF
ENDPROC

PROCEDURE reference_access
    RETURN THIS.WordApplication
ENDPROC

PROCEDURE reference_assign
    LPARAMETERS vNewVal
ENDPROC

PROCEDURE Init
    LPARAMETERS lcTempaleName, lcReportName
    THIS.TemplateFile=lcTempaleName
    THIS.ReportFile=lcReportName
    THIS.WordApplication=CREATEOBJECT('Word.Application')
    LOCAL loApp
    loApp=THIS.WordApplication
    THIS.WordDocument=loApp.Documents.Open(THIS.TemplateFile)
ENDPROC

PROCEDURE Destroy
    LOCAL loApp
    loApp=THIS.WordApplication
    loApp.Quit()
ENDPROC

ENDDEFINE
*
*-- EndDefine: WordReport
**************************************************

The Example

Example of the report creation with fields containing variables and functions defined in the application is offered below.

Expressions
date() - standard Visual FoxPro function returning current date
GetEmployeeList() - function defined by the developer handling records of the table "EMPLLIST.DBF". Creates the list of the employees.
gcFoot - variable defined by the developer. Creates string of a signature of the report.

File of the template of the report


Employees List


<date()>

  • <GetEmployeeList()>

<gcFoot>

Usage of the WordReport class

PROCEDURE MAIN

LOCAL lcTemplate, lcReport
* To set the file of the template
lcTemplate='EmplTamp.doc'
* To set the file of the report
lcReport='EmployeeList.doc'
* To define a variable for a signature of the report
PUBLIC gcFoot
* To assign a value
gcFoot='This list includes the employees, '+;
'which help the customers to cope with their problems.'+;
CHR(13)+CHR(13)+;
'David Lickin,'+CHR(13)+;
'Personnel Manager'
* To construct the report
LOCAL loReport
loReport=CREATEOBJECT('WordReport', lcTemplate, lcReport)
loReport.Show()
loReport.Create()
* To print the report
loReport.Print()
loReport.Hide()
DO WHILE MESSAGEBOX('Print is complete?',;
    4+32+256,;
    'WordReport Class')=7
ENDDO
RETURN

* Function of records processing of the table 'EMPLLIST.DBF'
FUNCTION GetEmployeeList()
* To verify existence of the table file
IF !FILE('EMPLLIST.DBF')
    RETURN 'There is no file EMPLLIST.DBF'
ELSE
    * To open the table
    USE ('EMPLLIST.DBF') IN 0 SHARED ALIAS EMPLLIST
ENDIF
IF !USED('EMPLLIST')
    RETURN 'The list is inaccessible'
ENDIF
LOCAL lcList
lcList=''
* To generate string(line) of the employees list
DO WHILE !EOF('EMPLLIST')
    lcList= lcList+;
    PADR(ALLT(empllist.FIRST_NAME)+' '+;
     ALLT(empllist.LAST_NAME),30,' ')+;
    ' - '+empllist.TITLE+;
    CHR(13)
    SKIP IN EMPLLIST
ENDDO
* To delete the last line feed character
lcList=SUBSTR(lcList,1,len(lcList)-1)
* To enclose the table of the list
USE IN EMPLLIST
* To return string of the list in the processing function
* of the report template
RETURN lcList

Obtained file of the report


Employees List


06.15.00

  • Steven Buchanan - Sales Manager
  • Michael Suyama - Sales Representative
  • Robert King - Sales Representative
  • Laura Callahan - Inside Sales Coordinator
  • Anne Dodsworth - Sales Representative
  • Albert Hellstern - Business Manager
  • Tim Smith - Mail Clerk
  • Caroline Patterson - Receptionist
  • Justin Brid - Marketing Director
  • Xavier Martin - Marketing Associate
  • Laurent Pereira - Advertising Specialist
  • Nancy Davolio - Applications Developer
  • Andrew Fuller - Entry Clerk
  • Janet Leverling - Applications Developer
  • Margaret Peacock - Sales Manager

This list includes the employees, which help the customers to cope with their problems.

David Lickin,
Personnel Manager


The Conclusion

The offered report creation way allows to dispose Visual FoxPro expressions fields in the MS Word document, using such units of formatting as the tables. It is possible to place Visual FoxPro expression in each cell of the table. It is possible to find many other ways of allocation and formatting of fields. Wherever the fields were disposed, they will be substituted by data with saving of formatting given by the template after construction of the report.

In other words, this technology is an automation of the Word Automation.


Home Page | Technologies | Specialists | Download | Links | Sign Guestbook | View Guestbook