|
Инструмент
CONTEST:
1.Task for the laboratory
work.
2.Theory part.
2.1. Build-in FoxProw commands
2.2. OLE
3. Practical usage of data of data transfer.
4. Technical facilities & literature used.
5. Applications
1. Task for the laboratory
work.
The
aim of this work is to show our ability to work with different Microsoft
applications & to produce the data exchange between them, using
build-in commands, OLE instruments & other possible facilities.
In
the practical examples we will show the data transfer process using the
following Microsoft Applications: FoxPro, Excel & Word.
Also
it is necessary to present the theory about the data transfer for it is
impossible to use all of them in the examples.
2. Theory part.
There are several different ways to transfer
data from FoxPro to different Microsoft Applications & vice versa.
2.1. Built-in FoxPro commands.
Operator
can use several built-in commands of
FoxPro such as Export, Import, Copy and Append
from.
Now we would like to present some
helpful information about these operands.
The
format of EXPORT is:
EXPORT TO <file>
[FIELDS
<field list>]
[<scope>]
[FOR
<expL1>]
[WHILE
<expL2>]
[NOOPTIMIZE]
[[TYPE]
DIF | MOD | SYLK | WK1 | WKS | WR1 | WRK | XLS]]
Remarks
Use
EXPORT to use FoxPro data in other software packages.
If
the table/.DBF you are exporting from is indexed, the new file created is
created in the indexed order.
Clauses
<file>
Include
the name of the file to which FoxPro exports data. If you do not include an extension with the file name, the
default extension for the specified file type is assigned.
FIELDS
<field list>
Include
FIELDS <field list> to specify which fields are copied to the new
file. If the FIELDS clause is omitted,
all fields are copied to the new file.
Memo and general fields are not copied to the new file even if their
names are included in the field list.
<scope>
The scope
clauses are: ALL, NEXT <expN>,
RECORD <expN>, and REST. These
are explained in the Overview of the FoxPro Language chapter in the FoxPro
Language Reference. Commands which
include <scope> operate only on the table/.DBF in the active work area.
You
can specify a scope of records to copy to the new file. Only the records that fall within the range
of records specified by the scope are copied to the new file.
The
default scope for EXPORT is ALL records.
FOR
<expL1>
If
the FOR clause is included, only records that satisfy the logical condition
<expL1> are copied to the new file.
Including the FOR clause lets you conditionally copy records, filtering
out undesired records.
Rushmore
optimizes an EXPORT ... FOR <expL1> command if <expL1> is an
optimizable expression. For best
performance, use an optimizable expression in the FOR clause. A discussion of Rushmore optimizable
expressions appears in the Optimizing Your Application chapter in the FoxPro
Developer's Guide.
WHILE
<expL2>
If
the WHILE clause is included, records are copied to the new file for as long as
the logical expression <expL2> evaluates to true (.T.).
NOOPTIMIZE
To
cause Rushmore not to optimize EXPORT, include NOOPTIMIZE. For more information, see SET OPTIMIZE and
the Rushmore discussion in the Optimizing Your Application chapter of the
FoxPro Developer's Guide
.
TYPE
You
must specify the type of file to be created.
The TYPE key word is optional, but you must specify one of the following
file types.
DIF
When
you specify DIF, each field from a FoxPro table/.DBF becomes a vector (column)
and each record becomes a tuple (row) in a DIF (Data Interchange Format) file,
used by VisiCalc. The new file name is
assigned a .DIF extension if an extension isn't included in <file>.
MOD
Use
the MOD clause to export to a file in Microsoft Multiplan version 4.01 MOD
format. The new file name is assigned an .MOD extension if you don't include an
extension in <file>.
SYLK
A
SYLK file is a Symbolic Link interchange format (used by Microsoft Multiplan)
in which each field from a FoxPro table/.DBF becomes a column in the
spreadsheet and each record becomes a row.
By default, SYLK file names have no extension.
WK1
Include
this option to create a Lotus 1-2-3 spreadsheet from a FoxPro table/.DBF. A .WK1 extension is assigned to the
spreadsheet file name for use with Lotus 1-2-3 revision 2.x. Each field from the table/.DBF becomes a
column in the new spreadsheet, and each record in the table/.DBF becomes a
spreadsheet row.
WKS
A
Lotus 1-2-3 spreadsheet can be created from a FoxPro table/.DBF with this
option. A .WKS extension is assigned to
the spreadsheet file name for use with Lotus 1-2-3 revision 1-A. Each field from the table/.DBF becomes a
column in the new spreadsheet, and each record becomes a row in the
spreadsheet.
WR1
Include
this option to create a Lotus Symphony spreadsheet from a FoxPro
table/.DBF. A .WR1 extension is
assigned to the spreadsheet for use with Symphony version 1.01. Each field from the table/.DBF becomes a
column in the new spreadsheet, and each record in the table/.DBF becomes a row
in the spreadsheet.
WRK
A
Lotus Symphony spreadsheet can be created from a FoxPro table/.DBF with this
option. A .WRK extension is assigned to
the spreadsheet file name for use with Symphony version 1.10. Each field from the table/.DBF becomes a
column in the new spreadsheet, and each record in the table/.DBF becomes a row
in the spreadsheet.
XLS
Include
XLS to create a spreadsheet you can use in Microsoft Excel. Each field in the +selected table/.DBF
becomes a column in the spreadsheet; each table/.DBF record becomes a row in
the spreadsheet. An .XLS file name
extension is assigned to the newly created spreadsheet file unless you specify
a different extension.
The format of IMPORT is:
IMPORT FROM <file>
[TYPE]
FW2 | MOD | PDOX | RPD | WK1 | WK3 | WKS | WR1 | WRK |
XLS
As it shown in format (TYPE) there are many possible Microsoft
applications to import from to FoxPro but the meaning of these properties,
description of which you can see in the previous chapter, that’s why it’s not
necessary to list all of them again. So we will mention those most common
used.
Remarks
Most
software packages store their data in a file format that cannot be opened
directly in FoxPro. IMPORT creates a
new FoxPro table/.DBF from data stored in file formats that FoxPro cannot read.
A
new table/.DBF is created with the same name as the file the data is imported
from. A .DBF extension is assigned to
the newly created table/.DBF.
Clauses
<file>
<file>
is the name of the file to import data from.
If you don't include an extension with the file name, the default
extension for the specified file type is assumed.
TYPE
The
key word TYPE is optional, but you must include one of the following file types
described below.
PDOX
Include
PDOX to import Paradox files. Database
files in Paradox versions 3.5 and 4.0 by Borland can be imported by including
the PDOX option.
XLS
Include
XLS to import data from Microsoft Excel spreadsheets versions 2.0, 3.0 and
4.0. Columns from the spreadsheet
become fields in the table/.DBF, and the spreadsheet rows become records in the
table/.DBF. Spreadsheet files created
in Microsoft Excel have an .XLS extension.
The
format of COPY is:
COPY TO <file>
[FIELDS
<field list>]
[<scope>]
[FOR
<expL1>]
[WHILE
<expL2>]
[[WITH]
CDX] | [[WITH] PRODUCTION]
[NOOPTIMIZE]
[[TYPE]
[FOXPLUS | DIF | MOD | SDF | SYLK| WK1 | WKS | WR1 | WRK | XLS |
DELIMITED [WITH <delimiter> WITH BLANK | WITH
TAB]]]
Remarks
If
an index order is set, records are copied in master index order.
Clauses
<file>
The
name of the new file to which COPY TO copies data is specified with
<file>. If you do not include an
extension with the file name, the default extension for the specified file type
is assigned. If you do not specify a
file type, COPY TO creates a new table/.DBF and assigns the table/.DBF file
name the default extension .DBF.
FIELDS
<field list>
If
you include FIELDS and a field list, you can specify which fields are copied to
the new file. If the FIELDS clause is
omitted, all fields are copied to the file.
If the file you are creating is not a database, memo fields aren't
copied to the new file even if memo field names are included in the field list.
<scope>
The
scope clauses are: ALL, NEXT
<expN>, RECORD <expN>, and REST.
These are explained in the Overview of the FoxPro Language chapter in
the FoxPro Language Reference. Commands
which include <scope> operate only on the table/.DBF in the active work
area.
You
can specify a scope of records copied to a file. Only the records that fall within the range of records specified
by the scope are copied.
The
default scope for COPY TO is ALL records.
FOR
<expL1>
If
the FOR <expL1> clause is included, only the records for which the
logical condition <expL1> evaluates to true (.T.) are copied to the
file. Include FOR <expL1> to
conditionally copy records, filtering out undesired records.
Rushmore
optimizes COPY TO with a FOR <expL1> clause if <expL1> is an
optimizable expression. For best
performance, use an optimizable expression in the FOR <expL1>
clause. A discussion of Rushmore
optimizable expressions appears in the Optimizing Your Application chapter in
the FoxPro Developer's Guide.
WHILE
<expL2>
If
WHILE <expL2> is included, records are copied as long as the logical
expression <expL2> evaluates to true (.T.).
[WITH]
CDX | [WITH] PRODUCTION
If
the table/.DBF you copy from has a structural index file, you can create a
structural index file for the new table/.DBF.
Including CDX or PRODUCTION creates an identical structural index file
for the new table/.DBF. The tags and
index expressions from the original structural index file are copied to the new
structural index file. The CDX and
PRODUCTION clauses have the same effect.
Do
not include CDX or PRODUCTION if you are copying to a file other than a new
FoxPro table/.DBF.
NOOPTIMIZE
Include
NOOPTIMIZE to cause Rushmore to not optimize COPY TO. For more information, see SET OPTIMIZE or consult the discussion
of Rushmore optimization in the Optimizing Your Application chapter in the
FoxPro Developer's Guide.
TYPE
If
the file you are creating isn't a FoxPro table/.DBF, you must specify its file
type. Although you must specify a file
type, you need not include the key word TYPE.
You can create a wide variety of different file types including
DELIMITED ASCII text files in which you can specify a field delimiter.
FOXPLUS
FoxPro
memo files have a different structure than FoxBASE+ memo files. If your source FoxPro table/.DBF contains a
memo field, include the FOXPLUS clause to create a table/.DBF that can be used
in FoxBASE+. The FoxPro memo field
cannot contain binary data because FoxBASE+ does not support binary data in
memo fields.
SDF
An
SDF (System Data Format) file is an ASCII text file in which records have a
fixed length and end with a carriage return and line feed. Fields aren't delimited. The SDF file name is assigned a .TXT file
extension if you do not include an extension.
SYLK
A
SYLK file is a Symbolic Link interchange format (used in Microsoft MultiPlan)
in which fields from the FoxPro table/.DBF become columns in the spreadsheet
and records become rows. SYLK file
names have no extension.
DELIMITED
[WITH <delimiter> | WITH BLANK | WITH TAB]
A
DELIMITED file is an ASCII text file in which each record ends with a carriage
return and line feed. The default field
separator is a comma. Since character
data may include commas, character fields are additionally delimited with
double quotation marks.
In
the following example there are 2 character fields ("Smith" and
"TELEPHONE") delimited with double quotation marks. There is one numeric field which is not
delimited since numeric data does not contain commas. The 3 fields are separated with commas.
"Smith",
9999999, "TELEPHONE"
The
DELIMITED WITH <delimiter> option can be used to replace the double
quotation marks with one of your choice.
The
comma is not truly a delimiter.
However, the DELIMITED WITH BLANK or DELIMITED WITH TAB clauses allow you
to replace the comma field separator with either a space or a tab. The data should not contain embedded spaces
or tabs.
You
cannot combine the WITH <delimiter> clause with either the WITH BLANK or
WITH TAB clauses.
Unless
you specify otherwise, a .TXT extension is assigned to all newly created
DELIMITED files.
The
format of APPEND is:
APPEND FROM <file> | ?
[FIELDS
<field list>]
[FOR
<expL>]
[[TYPE]
[DELIMITED [WITH TAB | WITH
<delimiter> | WITH BLANK] |
DIF | FW2 | MOD | PDOX | RPD | SDF | SYLK | WK1 | WK3
| WKS |
WR1 | WRK | XLS]]
Remarks
The
file you are appending from is assumed to be a FoxPro table with a .DBF
extension. If the file you want to
append from is a FoxPro table and doesn't have a .DBF extension, you must
specify its extension. If the file is a
not a FoxPro table, you must specify the type of file you append from.
Before
you can append from a table created in dBASE IV that contains a memo field, you
must first open the table in FoxPro with USE.
You are prompted with "Convert MEMO file to FoxPro
Format?" Choose Yes.
If
you append from a FoxPro table, the table you append from can be open in
another work area. You can also append
from a table that isn't open but is available on disk and a shared table opened
when SET EXCLUSIVE is OFF. When the
table you append from contains records marked for deletion, the records are not
marked for deletion after they are appended.
If
you include the ? clause instead of including a table name, the Open dialog
appears so you can choose a table to append from.
Clauses
<file>
Specify
the name of the file to append from with <file>. If you don't include a file name extension,
the default extension .DBF is assumed.
FIELDS
<field list>
APPEND
FROM supports an optional <field list>.
Data is only appended to the fields specified in the field list.
FOR
<expL>
The
entire source file is appended to the table unless you include the FOR
clause. If the FOR clause is included,
a new record is appended for each record in the file source for which
<expL> evaluates to a logical true (.T.). Records are appended until the end of the file is reached.
TYPE
If
the file you are appending from isn't a FoxPro table, you must specify the file
TYPE. Although you must specify the
file type, you need not include the key word TYPE. You can append from a wide variety of different file types
including DELIMITED ASCII text files in which you can specify a field
delimiter.
If
the file you are appending from doesn't have the usual default file extension
for that type of file, the source file name must include the file's
extension. For example, Microsoft Excel
spreadsheets normally have an .XLS file name extension. If the spreadsheet you are appending from
has an extension other than the expected .XLS, be sure to specify the
extension.
Caution When appending from a spreadsheet, the data
in the spreadsheet must be stored in a row major order rather than a column
major order. This allows the appended
spreadsheet data to match the table structure.
DELIMITED
[WITH TAB | WITH <delimiter> | WITH BLANK]
A
DELIMITED file is an ASCII text file in which each record ends with a carriage
return and line feed. Field contents
are by default assumed to be separated from each other by commas, and character
field values to be additionally delimited by double quotation marks. For example:
"Smith",
9999999, "TELEPHONE"
The
DELIMITED WITH TAB option can be used to specify files which contain fields
separated from each other by tabs rather than commas. The DELIMITED WITH <delimiter> option can be used to
indicate that character fields are delimited by a character other than the
quotation mark. The DELIMITED WITH
BLANK option can be used to specify files which contain fields separated by
spaces instead of commas. The file
extension is assumed to be .TXT for all delimited files.
You
can import dates from delimited files if the dates are in proper date
format. The date format defaults to
'mm/dd/yy'. Including the century
portion of a date is optional. FoxPro will import a date that includes the
century. If the century isn't included
in a date (for example '12/25/92'), the Twentieth century is assumed. Date delimiters can be any non-numeric character
except the delimiter that separates the fields in the delimited file.
Dates
in other formats can be imported if their format matches a date format
available in SET DATE. To import dates
that are not in the default format, issue SET DATE with the proper date format
before using APPEND FROM. To test if a
date format can be successfully imported, use it with CTOD( ). If the date is acceptable to CTOD( ), the
date will import properly.
FW2
FW2
files are created by Framework II. FW2
file names are assumed to have a .FW2 extension.
RPD
RPD
files are created by RapidFile version 1.2.
RPD file names are assumed to have an RPD extension.
SDF
An
SDF (System Data Format) file is an ASCII text file in which records have a
fixed length and end with a carriage return and a line feed. Fields are not delimited. The file name extension is assumed to be
.TXT for SDF files.
WK3
Data
from a Lotus 1-2-3 spreadsheet. Each
column from the spreadsheet becomes a field in the table; each spreadsheet row
becomes a record in the table. A .WK3
file name extension is assigned to a spreadsheet created in Lotus 1-2-3
revision 3.x.
2.2. OLE.
Another
method of data exchange between not only FoxPro but between all Windows based
applications is OLE (OBJECT LINKING AND EMBEDDING)
First
you can use a FoxPro built in command Append General.
APPEND GENERAL <general field> FROM <file>
[LINK]
[CLASS
<ole class>]
APPEND
GENERAL is supported in FoxPro for Windows only.
If
an OLE object already exists in the general field, it is replaced with the OLE
object from the file.
Clauses
<general
field>
Include
<general field> to specify the name of the general field the OLE object
is placed in. You can specify a general
field in a table open in a noncurrent work area by including the table alias
with the field name.
<file>
Include
<file> to specify the file containing the OLE object. You must include the entire file name,
including its extension. If the file is
located in a directory other than the current default directory, include the
path with the file name.
LINK
If
LINK is included, a link is created between the OLE object and the file that
contains the object. The OLE object
appears in the general field but the object's definition remains in the file. If LINK is omitted, the OLE object is
embedded in the general field.
CLASS
<ole class>
Include
the CLASS clause to specify an OLE class for an OLE object other than the
default class.
Tip To determine the class for an OLE object,
run REGEDIT and double-click on the OLE object. The class name is listed under Identifier.
You
can specify a class name when the file extension for the file containing the
OLE object is different than the default extension and you want to force the
behavior for the class. If the default
extension can be used by multiple OLE servers, include the class to specify a
particular server.
3. Practical usage of data transfer.
In
applications you can find the examples of the above mentioned commands usage
and their results in different Microsoft Applications (FoxPro, Word &
Excel).
4. Technical facilities & literature
used.
During creating this program the following
equipment & software was used:
Pentium
100 MHz PC with 8 Mb RAM
Microsoft
Windows’95
Microsoft
Visual FoxPro 3.0
Microsoft
Word 7.0
Microsoft
Excel 7.0
Printer
Hewlett Packard LaserJet5L
No special literature but build-in HELP of
Visual FoxPro 3.0, Word 7.0, Excel 7.0 & useful advices was used to create
this laboratory work.
5. Applications.
Application
1 “Transfering
data form FoxPro to Word & Excel”
Application2
“FoxPro database converted to Excel (.xls) file”
Application
3 “FoxPro
database converted to Word (.sdf) file”
Application
4 “FoxPro
base converted in Word file - in Excel”
Application
5 “Excel
file in Word
|
|
|
