51
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
22
lFound = .Execute( , , , , , , , , , , wdReplaceAll )
ENDWITH
With a little creativity, it's possible to find and replace pretty much anything you want in a
document. You can also combine VFP's data handling strength with VBA for power
searching. Imagine putting a collection of search and replacement strings in a table, then
using automation to make all the changes without intervention. (I don't actually have to
imagine this one. I've done it.)
Tables
Word's tables seem like a natural fit for representing VFP data. A table can be formatted as
a whole, but individual cells can be separately formatted, too. Borders of tables and cells
can be visible or invisible, and can take on a range of sizes and styles. Both columns and
rows can be individually sized.
The object hierarchy for tables is a little confusing. Each document has a Tables collection,
which in turn contains individual Table objects. The Table object contains Rows and
Columns collections, which contain Row and Column objects respectively. Those objects
each have a Cells collection that references the individual cells in the row or column, each
represented by a Cell object. While the Table object doesn't have a Cells collection, the
individual Cell objects can be accessed using the Cell method, which accepts row and
column number parameters. Here are several ways to refer to the cell in the third row and
fourth column of the first table in the active document:
oWord.ActiveDocument.Tables[1].Rows[3].Cells[4]
oWord.ActiveDocument.Tables[1].Columns[4].Cells[3]
oWord.ActiveDocument.Tables[1].Cell[3,4]
Table, Row and Cell all have Range properties, so that an entire table, row or a cell can be
easily converted to a range. This means that the same techniques work for inserting text
into a table as for other parts of a document. However, a Range created from a cell
contains a special end-of-cell marker. To access only the text in a cell, move the end of the
range back one character. Either of the following does the trick:
oRange.End = oRange.End – 1
oRange.MoveEnd( wdCharacter, -1 )
The program below opens TasTrade's Order History view and creates a Word table
showing the order history for the chosen customer. It demonstrates a variety of features,
including borders, shading and auto-sizing of columns.
* Create a Word table with order information for one customer
* Assumes: Word is already open and accessible through oWord.
* The table is to be added at the end of the current document.
* Customer table is open and positioned on desired customer
#INCLUDE "..\Word.H"
LOCAL oRange, oTable, nRecCount, nTotalOrders
* Open the Order History view, which contains
* a summary of orders for one customer.
USE "Order History" ALIAS OrderHistory
* Find out how many records.
nRecCount = _TALLY
* Add a table at the end of the document.
58
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
23
* Give it two more rows than records to handle headings and totals.
oRange = oWord.ActiveDocument.Range()
oRange.MoveEnd( wdStory )
oTable = oWord.ActiveDocument.Tables.Add( oRange, nRecCount + 2, 4)
* Set up a font for the table
oRange.Font.Name = "Arial"
oRange.Font.Size = 12
WITH oTable
* Set up borders and shading.
* First, remove all borders
.Borders.InsideLineStyle = .F.
.Borders.OutsideLineStyle = .F.
* Add a double line before the totals
.Rows[nRecCount + 2].Borders[ wdBorderTop ].LineStyle = ;
wdLineStyleDouble
* Shade first row for headings
.Rows[1].Shading.Texture = 100
* Put heading text in and set alignment
.Cell[1,1].Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Cell[1,1].Range.InsertAfter("Order Number")
.Cell[1,2].Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Cell[1,2].Range.InsertAfter("Date")
.Cell[1,3].Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Cell[1,3].Range.InsertAfter("Total")
.Cell[1,4].Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Cell[1,4].Range.InsertAfter("Paid?")
* Add data and format
* Compute total along the way
nTotalOrders = 0
FOR nRow = 1 TO nRecCount
WITH .Rows[nRow + 1]
* Right align first column
.Cells[1].Range.ParagraphFormat.Alignment = ;
wdAlignParagraphRight
.Cells[1].Range.InsertAfter( Order_Id )
.Cells[2].Range.InsertAfter( TRANSFORM(Order_Date, "@D") )
* Right align third column
.Cells[3].Range.ParagraphFormat.Alignment = ;
wdAlignParagraphRight
.Cells[3].Range.InsertAfter( TRANSFORM(Ord_Total, ;
"$$$$$$$$$9.99") )
* Center fourth column
.Cells[4].Range.ParagraphFormat.Alignment = ;
wdAlignParagraphCenter
* Put an X in fourth column, if paid; blank otherwise
IF Paid
.Cells[4].Range.InsertAfter("X")
ENDIF
ENDWITH
nTotalOrders = nTotalOrders + Ord_Total
SKIP
ENDFOR
How to C#: Basic SDK Concept of XDoc.PDF for .NET insert, delete, re-order, copy, paste, cut, rotate, and save or query data and save the PDF document. The PDFPage class presents a single page in a PDFDocument
rotate pages in pdf and save; rotate pdf page few degrees
78
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
24
* Put total row in
WITH .Rows[ nRecCount + 2]
.Cells[1].Range.InsertAfter("Total")
.Cells[3].Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Cells[3].Range.InsertAfter(TRANSFORM(nTotalOrders, ;
"$$$$$$$$$9.99"))
ENDWITH
* Size columns. For simplicity, let Word
* do the work.
.Columns.Autofit
ENDWITH
The results are shown in Figure 5.
Order Number
Date
Total
Paid?
1045 03/17/95
$541.16
999 02/25/95
$637.71
X
907 01/19/95
$432.41
X
871 12/29/94
$164.04
X
793 11/16/94
$403.64
X
612 06/18/94
$807.85
X
375 10/29/93
$439.99
X
128 12/09/92
$169.94
X
Total
$3596.75
Figure 5 Using tables for data
–
A customer's order history looks good when poured into a Word table.
An alternate version of the code creates a two-row table, inserts the headings, then formats
the cells in the second row. The loop then inserts the data and adds a new row. Each new
row picks up the formatting of the previous one, so the formats only have to be applied
once. Here's the key portion, which is inside a WITH oTable … ENDWITH pair.
* Format data cells
.Cell[2,1].Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Cell[2,3].Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Cell[2,4].Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
* Add data and format
* Compute total along the way
nTotalOrders = 0
FOR nRow = 1 TO nRecCount
WITH .Rows[nRow + 1]
.Cells[1].Range.InsertAfter( Order_Id )
.Cells[2].Range.InsertAfter( TRANSFORM(Order_Date, "@D") )
.Cells[3].Range.InsertAfter( TRANSFORM(Ord_Total, ;
"$$$$$$$$$9.99") )
* Put an X in fourth column, if paid; blank otherwise
IF Paid
.Cells[4].Range.InsertAfter("X")
ENDIF
ENDWITH
* Add a new row
.Rows.Add()
* Running Total
nTotalOrders = nTotalOrders + Ord_Total
SKIP
ENDFOR
VB.NET PDF: Basic SDK Concept of XDoc.PDF insert, delete, re-order, copy, paste, cut, rotate, and save or query data and save the PDF document. The PDFPage class presents a single page in a PDFDocument
how to rotate a single page in a pdf document; rotate one page in pdf reader
42
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
25
Combine either version of the code with the code that produced Figure 4, and you have a
reasonably attractive order history report for a customer. Wrap that in a loop with a few
more commands (such as InsertBreak to add page breaks) and you can produce order
histories for all customers or a selected set.
A few more words
The discussion here barely touches the surface of what you can with automation to Word.
With perseverance and creativity, you can put the full power of Word to work in your
applications.
Do Something EXCELlent
Working with Excel through automation has a lot in common with automating Word
–
chalk
one up for polymorphism. Unfortunately, there's also a lot not in common since the two
servers have different abilities and purposes.
The fundamental object in Excel is a Workbook. The Excel application object has a
collection of Workbooks and an ActiveWorkBook property. Each Workbook has two main
collections, Worksheets and Charts, which represent the pages of the workbook and the
graphs it contains, respectively. Workbook has ActiveSheet and ActiveChart properties
containing references to the current worksheet and chart objects. Excel also provides a
shortcut by offering ActiveSheet, ActiveChart, and ActiveCell properties at the Application
level. So, you can reference the current cell of the current worksheet with oExcel.ActiveCell.
Like the Word Application object, Excel's Application object has properties and methods
that relate to Excel as whole. A number, like StartupPath, Version and WindowState, are
the same, while others like Calculation, which determines when calculations occur, are
specific to Excel.
Again, as with Word, the Excel Visual Basic Help file contains a live diagram of the object
model.
Managing Worksheets
Polymorphism means that you already know how to open and close workbooks. The Open
method of the Workbooks collection opens an existing workbook, given the filename:
oWorkBook = oExcel.Workbooks.Open("d:\writing\confs\la99\sample.xls")
All the Excel examples here assume that you've already created an Excel server object,
accessed by oExcel.
To create a new workbook, use the Add method. As in Word, you can specify a template to
begin with a particular kind of worksheet.
Both Open and Add return a reference to the new workbook.
52
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
26
Continuing down the polymorphic path, the Save and SaveAs methods of the WorkBook
object let you store a workbook, either back to its source or to a new file. SaveAs allows an
alternate format to be specified, along with many other options.
The Close method closes an individual workbook or all open workbooks.
Accessing Parts of a Worksheet
The analogies to Word continue somewhat when it comes to working with parts of a
worksheet. Like Word, there's a Range object. There's also a way to select part of a
worksheet and operate on it, but that approach is frowned on even more in Excel than in
Word.
Creating and working with Excel ranges is different than working with Word ranges,
however. A worksheet is composed of cells, which have addresses in the form X9, where X
is one or two letters indicating the column and 9 is one or more digits indicating the row.
The top left cell in a worksheet is A1. The 29
th
cell in the 32
nd
column is AF29. To specify a
range, you provide the addresses for the range boundaries. For example, to access the
data in cell C5, use:
?oExcel.ActiveSheet.Range("C5").Value
To create a range containing a rectangular group of cells from D12 to F19, use:
oRange = oExcel.ActiveSheet.Range("D12:F19")
Another way to access cells is using the Rows, Columns and Cells properties of Worksheet
and Range. These properties takes appropriate index values and return a range containing
the specified cells. To display the value of all cells in the third row of a range, you can write:
FOR nColumn = 1 TO oRange.Columns.Count
?oRange.Cells[3, nColumn].Value
ENDFOR
To see the contents of all cells in a range, use:
FOR nRow = 1 TO oRange.Rows.Count
FOR nColumn = 1 TO oRange.Columns.Count
?oRange.Cells[nRow, nColumn].Value
ENDFOR
ENDFOR
Note that the indexes for Cells list the row, then the column (just as arrays in VFP do), but
the addresses of cells (in the "X9" format) list the column first.
Ranges can be specified without hard-coding the cell addresses. To create a range relative
to another range, use the Offset property. This example creates a range 20 rows down and
30 rows to the left of oRange. The new range has the same size and shape:
oRange2 = oRange.Offset(20, 30)
Range don't have to consist of a single rectangle. Multiple groups can be listed when
creating a range:
oRangeMixed = oExcel.ActiveSheet.Range("F21:F30, H21:H30")
The Union method combines several ranges into one. Here, the two ranges oRange and
oRange2 are consolidated into a single range referenced by oBigRange:
oBigRange = oExcel.Union(oRange, oRange2)
53
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
27
Traversing a range with a loop like the one above doesn't work for a range composed of
non-contiguous cells. The Areas collection has an entry for each rectangular portion (called
an area) of the range, so to traverse all the cells in a range, whether or not it's rectangular,
you can use code like this:
FOR nArea = 1 TO oRange.Areas.Count
FOR nRow = 1 TO oRange.Areas[nArea].Rows.Count
FOR nColumn = 1 TO oRange.Areas[nArea].Columns.Count
?oRange.Areas[nArea].Cells[nRow, nColumn].Value
ENDFOR
ENDFOR
ENDFOR
Manipulating Worksheet Contents
As the previous examples indicate, you can access the contents of a cell through its Value
property. But, when working with spreadsheets, it's not always the value of a cell we're
interested in. To access the formula contained in a cell, use the Formula property:
?oExcel.ActiveSheet.Range("C22").Formula
If a cell contains only a value, Formula returns the value as a string, while Value returns
numbers as numbers. If there's a real formula in the cell, it's returned in the format you'd
use to enter it in Excel, beginning with "=".
You can set values and formulas by assigning them to the appropriate cells:
oExcel.ActiveSheet.Range("C13").Value = 100
oExcel.ActiveSheet.Range("C22").Formula = "=SUM(C5:C20)"
Returning to the TasTrade Order History view, we can send the information to Excel instead
of Word so that the number-crunchers can work with it. While it's possible to copy VFP
tables and views to XLS format using the COPY TO or EXPORT command, automation
provides more flexibility in the process, including the ability to put data from more than one
table into a worksheet.
* Put order information for one customer into an Excel worksheet.
* This results of this version are fairly unattractive.
* Assumes: Excel is already open and accessible through oExcel.
* Customer table is open and positioned on desired customer
LOCAL oBook, oRange
* Open the Order History view, which contains
* a summary of orders for one customer.
IF NOT USED("OrderHistory")
USE "Order History" AGAIN IN 0 ALIAS OrderHistory
ENDIF
SELECT OrderHistory
* Add a workbook, using default settings
oBook = oExcel.Workbooks.Add()
WITH oExcel.ActiveSheet
* Put customer name at top
.Range("B2").Value = Customer.Company_Name
* Put column headings in Row 5
.Range("A5").Value = "Order Number"
.Range("B5").Value = "Date"
.Range("C5").Value = "Amount"
37
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
28
oRange = .Range("A6:C6")
ENDWITH
* Loop through orders and send data
SCAN
WITH oRange
.Columns[1].Value = Order_Id
.Columns[2].Value = Order_Date
.Columns[3].Value = Ord_Total
ENDWITH
* Move range down one row
oRange = oRange.Offset(1,0)
ENDSCAN
* Now add total row
nLastRow = oRange.Row && Row property always give first row of range
&& This range has only one row
nTotalRow = nLastRow + 2
WITH oExcel.ActiveSheet
.Cells( nTotalRow, 1 ) = "Total"
* Need to convert nLastRow to char to use in formula for sum
oExcel.ActiveSheet.Cells( nTotalRow, 3 ).Formula = ;
"=SUM( C6:C" + LTRIM(STR(nLastRow)) + " )"
ENDWITH
USE IN OrderHistory
Figure 6 shows the results. Clearly, some formatting is called for.
Figure 6 Creating a spreadsheet
–
It's easy to send both data and formulas from VFP to Excel and to extract
them from Excel for use in VFP. Making it look good take a little more work.
Formatting Cells
Excel's Font object is much like Word's with properties for Name, Size, Bold, Italic and so
forth. To set cell D32 to 14-point bold Times New Roman, use:
52
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
29
WITH oExcel.ActiveSheet.Range("D32").Font
.Name = "Times New Roman"
.Size = 14
.Bold = .T.
ENDWITH
(It turns out that the two applications don't use the same font class, but for most purposes,
they're indistinguishable.)
In line with its purpose, Excel offers more formatting options than Word does. The
NumberFormat object lets you specify a format for the cell data, using a "picture" like VFP's
InputMask. Although the property is called NumberFormat, it applies to dates and times as
well, since Excel sees them as numbers. This code tells column B to use dd-MMM-yyyy
format (like 01-Feb-1999):
oExcel.ActiveSheet.Columns(2).NumberFormat = "dd-MMM-yyyy"
A number of other properties, such as HorizontalAlignment, VerticalAlignment, WrapText,
and Borders, control other aspects of a cell's appearance.
As in Word, it's better to work with Styles than to format individual cells with their properties.
A few styles are built-in to handle common tasks, but to really make styles useful, you need
to add a set of your own. This code adds a style that formats dates as above and right
aligns them.
oStyle = oExcel.ActiveWorkbook.Styles.Add("DateDMY")
WITH oStyle
.NumberFormat = "dd-MMM-yyyy"
.HorizontalAlignment = xlHAlignRight
ENDWITH
Excel has its own set of constants like Word's. VFP #DEFINE's for Excel constants used in
these examples can be found in Excel.H on the conference CD.
Here's an expanded version of the program above. This one handles formatting of the cells,
as well. The results are shown in Figure 7.
* Put order information for one customer into an Excel worksheet.
* Includes formatting.
* Assumes: Excel is already open and accessible through oExcel.
* Customer table is open and positioned on desired customer
#INCLUDE "Excel.H"
LOCAL oBook, oRange, lFound, oStyle
LOCAL nLastRow, nTotalRow
* Open the Order History view, which contains
* a summary of orders for one customer.
IF NOT USED("OrderHistory")
USE "Order History" AGAIN IN 0 ALIAS OrderHistory
ENDIF
SELECT OrderHistory
* Add a workbook, using default settings
oBook = oExcel.Workbooks.Add()
WITH oExcel.ActiveSheet
* Put customer name at top
.Range("B2").Value = Customer.Company_Name
59
3
rd
Annual Southern California Visual FoxPro Conference
Sponsored by Microcomputer Engineering Services, LLC
Copyright 1999, Tamar E. Granor
30
* Put column headings in Row 5
.Range("A5").Value = "Order Number"
.Range("B5").Value = "Date"
.Range("C5").Value = "Amount"
oRange = .Range("A6:C6")
ENDWITH
* Loop through orders and send data
SCAN
WITH oRange
.Columns[1].Value = Order_Id
.Columns[2].Value = Order_Date
.Columns[3].Value = Ord_Total
ENDWITH
* Move range down one row
oRange = oRange.Offset(1,0)
ENDSCAN
* Now add total row
nLastRow = oRange.Row && Row property always give first row of range
&& This range has only one row
nTotalRow = nLastRow + 2
WITH oExcel.ActiveSheet
.Cells( nTotalRow, 1 ) = "Total"
* Need to convert nLastRow to char to use in formula for sum
oExcel.ActiveSheet.Cells( nTotalRow, 3 ).Formula = ;
"=SUM( C6:C" + LTRIM(STR(nLastRow)) + " )"
ENDWITH
* Format appropriately
WITH oExcel.ActiveSheet
* Wrap heading for column 1
.Range("A5").WrapText = .T.
* Set up a style for dates in column 2
* First make sure it doesn't already exist
lFound = .F.
FOR EACH oStyle IN oExcel.ActiveWorkbook.Styles
IF UPPER(oStyle.Name) = "DateDMY"
lFound = .T.
EXIT
ENDIF
ENDFOR
IF NOT lFound
oStyle = oExcel.ActiveWorkbook.Styles.Add("DateDMY")
WITH oStyle
.NumberFormat = "dd-MMM-yyyy"
.HorizontalAlignment = xlHAlignRight
ENDWITH
ENDIF
.Columns(2).Style = "DateDMY"
* Don't want this style for the company name
* Use built-in normal style instead
.Range("B2").Style = oExcel.ActiveWorkbook.Styles("Normal")
* Set column 3 to built-in currency style
.Columns(3).Style = "Currency"
ENDWITH
Documents you may be interested
Documents you may be interested
