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
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.