7.5 Handling values and formulas

Handling values and formulas

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)"

Adding, copying, and moving data (including formulas)

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.

Populating the worksheet cell by cell

Automation allows data values to be input cell by cell. The usual method is to set up a SCAN loop, and set the values for each cell that correspond to the data in each record. Set a Range object to just the cells to be populated for the first record, and use the Columns collection to set the values of the cells. Just before the SCAN loops, use the Offset method to move the range down one row. The Offset method lets you move the Range for each iteration, without having to keep track of the row, like this:

SCAN

* Do your processing and formatting here

* Move range down one row

oRange = oRange.Offset(1,0)

ENDSCAN

Plugging in data using this method is extremely flexible, since you can populate the spreadsheet without having to create a master cursor or table, as you would to COPY TO or EXPORT. You have complete control of the cells and can conditionally decide on the cell values based upon the data. This is an excellent replacement for that report you need that requires four different conditional detail bands (when the report writer supports only one), or multiple columns that list the child records from different child tables (since we can t SET SKIP TO several tables at once).

When you have Excel pull in an XLS generated from a table or view, the column headers are automatically set to the field names. The users might appreciate "Order Num," "Date," and "Amount," instead of "cOrd_ID," "dOrd_Date," and "nOrd_Total." Explicitly setting the column headers (using the Value property of each cell) is necessary whether you pull in an XLS or add the data cell by cell.

Formulas are easy to program, as long as you are familiar with Excel s formulas. Whatever you type into the cell is what you generate in a character string. For example, to sum cells C6 to C11, set the value of a cell to "=SUM(C6:C11)". Just like using Excel interactively, if you forget the equal sign, the formula becomes a character string.

The example shown in Listing 1 demonstrates creating a spreadsheet by entering the data cell by cell. It uses the TasTrade Order History view and adds data for one customer s orders. This example is available as XLAddData.PRG in the Developer Download files available at www.hentzenwerke.com.

Listing 1. Adding data to a spreadsheet. This sample uses TasTrade s Order History view to create a simple spreadsheet for one customer s orders.

* Put order information for one customer into an Excel worksheet

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

* For demonstration purposes, make oExcel available after

* this program executes.

PUBLIC oExcel

LOCAL oBook, oRange

* Open the Order History view, which contains

* a summary of orders for one customer.

OPEN DATABASE _SAMPLES + "\TASTRADE\DATA\Tastrade"

USE CUSTOMER IN 0

SELECT 0

USE "Order History" ALIAS OrderHistory

* Add a workbook, using default settings

oExcel = CREATEOBJECT("Excel.Application")

oExcel.Visible = .T.

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"

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

USE IN Customer

Figure 3 shows the results. The spreadsheet is successfully populated. However, some formatting is called for.

Figure 3. Results of the code shown in Listing 1. Literal headings, data from tables, and formulas are all successfully imported. A little formatting, discussed in subsequent sections, takes care of the display issues.

Pulling in a generated XLS

This method of populating the data cell by cell performs well on small data sets. Once the data gets lengthy (say, more than 300 records), you are better off using EXPORT or COPY TO TYPE XL5, and pulling the data in. The code to do this is straightforward:

m.DataFile = "C:\My Documents\MyWorksheet"

SELECT DataTable

COPY TO (m.DataFile) TYPE XL5

* Open the data worksheet created by COPY TO...

oDataBook = oExcel.Workbooks.Open(m.DataFile + ".XLS")

Moving worksheets

When you use the COPY TO command, you create a workbook with a single worksheet. If you run into a need to combine several of these XLS files into a multi-sheet workbook, use the following trick: use the Worksheet object s Move method to move the sheet between workbooks. The following example assumes you want to move the first (only) worksheet from the oDataBook object (the one created from the XLS file) into the oOtherWorkBook object:

oDataBook.Worksheets(1).Move(oOtherWorkBook.ActiveSheet)

RELEASE oDataBook

This little trick moves the only worksheet from oDataBook to oOtherWorkBook. Since that leaves oDataBook with no worksheets, Excel automatically closes the workbook. All you need to do is delete the XLS file, and release the oDataBook variable to ensure that the reference to the object is released so the Application object closes gracefully.

Fill er up

There are times when you want to take a number or formula and replicate that formula a specific number of times. For example, you may want to copy a formula from the first cell to all of the cells in a column. The FillDown, FillUp, FillLeft, and FillRight methods of the Range object automatically copy the cell contents. For example, the FillDown method takes the cell at the top of the range and copies its contents down. The following code sets the first cell to a value of 1, then uses the FillDown method to copy the top cell of the range to every other cell in the range:

oExcel.ActiveSheet.Range("A1").Value = 1

oSheet.Range("A1:A10").FillDown

After running this code, the first 10 rows in column A are filled with the number 1. "Not so impressive, just copying a number," you say? Well, imagine if that cell were a complex formula, and you had to replicate that formula down a column. The FillDown command begins to look pretty good.

Excel has a feature that allows you to fill up ranges with automatically generated data. For example, you can number a list (say, from 1 to 50), or make a column of dates that represent Fridays for the next 12 weeks. The Range object s DataSeries method calculates the values of cells, according to the parameters of the series. The syntax is as follows:

oExcel.ActiveSheet.Range("A1").DataSeries(nRowCol, nType, nDate, nStep,

xStop, xTrend)

nRowCol	Numeric	A value indicating whether the data series is entered in Rows or Columns. If omitted, it uses the shape of the specified range. (Optional) xlRows 1 xlColumns 2
nType	Numeric	A value indicating the type of series. (Optional) xlDataSeriesLinear -4132 xlGrowth 2 xlChronological 3 xlAutoFill 4
nDate	Numeric	Used only if nType is xlChronological (3). A value indicating the kind of chronological series. (Optional) xlDay (default) 1 xlWeekDay 2 xlMonth 3 xlYear 4
nStep	Numeric	A value representing the increment between each value in the series. The default is 1. (Optional)
xStop	Numeric or date	A value representing the value at which to stop. The type can be numeric or a date (or whatever type of series you are building). The default is the end of the range. (Optional)
xTrend	Logical or Numeric	True (or -1) to set a linear trend line (regression analysis) to forecast future trends, False (or 0) to create a standard series. The default is False (or 0). (Optional)

The following code creates a simple series, numbering the rows from 1 to 50. Note that if you don t fill in a value in the first cell of the series, there is nothing with which to start the series, and the command will appear to execute without error, but the resulting range is blank.

#DEFINE xlColumns 2

#DEFINE xlDataSeriesLinear -4132

#DEFINE xlDay 1

oExcel.ActiveSheet.Range("A1").Value = 1

oExcel.ActiveSheet.Range("A1:A100").DataSeries(xlColumns, ;

xlDataSeriesLinear, xlDay, 1, 50, .F.)

This next code example shows how to format a series of dates. This example shows a greater step value, calculating dates that are a week apart (actually, it s a list of Fridays between the starting date, a Friday, and the end of the year).

#DEFINE xlColumns 2

#DEFINE xlChronological 3

#DEFINE xlDay 1

oExcel.ActiveSheet.Range("B1").Value = "10/22/1999"

oExcel.ActiveSheet.Range("B1:B100").DataSeries(xlColumns, ;

xlChronological, xlDay, 7, "12/31/99", .F.)

Excel interprets the "12/31/99" string as a date data type. You could also use hyphens, too. Excel does some conversion on dates, as they are stored as a numeric value, called a "serial value." This value represents the number of days since December 31, 1899. Therefore, a value of 1 is January 1, 1900.

Excel uses formatting to display the cell as a date value, or you can select a numeric format to see the serial value. Additionally, time values are stored as a fraction of a day; a value of 1.5 is 12 PM on 1/1/1900. Again, you d use Excel s formatting features to display the date, time, or both. See the section "Formatting values" later in this chapter for the details on formatting dates and times.

Note that this method calculates the dates and enters them as a value, not as a formula. If you change one date, don t expect the others to change.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved