Microsoft Office Automation with Visual FoxPro

2017-07-07 02:10:07

Setting up the data

To graph your data, the data must reside in an Excel worksheet (or series of worksheets). The section "Handling values and formulas" in Chapter 7 discusses the mechanics of adding data to worksheets. We won t cover that again here, but we will tell you how to set up the worksheet so your data easily translates into a graph.

First, we need to examine how Excel works with data. Excel charts work with sequences of data called data series. Each data series is a sequence of points that are plotted with the same attributes (such as color, pattern, symbol, line style, bar style whatever is relevant to the chosen chart style). On a line chart, many lines can be plotted on a single chart; each line is a data series. On a bar chart with a three groups of color-coded bars, each color is a data series. A pie chart can graph only one data series.

Excel also uses ranges to label the category axis, and to label the data series in the legend. You can specify each data series separately, which is useful when one worksheet of data is used to create many graphs. However, Excel can accept a single rectangular range and use it to build a chart. If you re familiar with Excel s Chart Wizard, you probably already know that it defaults to using the first row and column for the headings, and then uses the remaining columns (or rows) for the data series. Understanding how Excel uses a single range for a chart makes life much easier for you; passing a single range is much easier than explicitly setting half a dozen ranges.

Setting up this single range is easy. Use column A for the category labels. The contents of each cell are the actual labels used on the X-axis, so be sure they aren t too cryptic or too long. Use subsequent columns for data series. Put the label for each series in row 1; start the data in row 2. The category headings in column A should also start in row 2. Figure 1 shows a sample spreadsheet, ready to graph. The code to generate this spreadsheet is shown in Listing 1 (XLGDataSetup.PRG in the Developer Download files available at www.hentzenwerke.com).

Figure 1. A spreadsheet formatted to graph as a single range. The values in column A become the X-axis labels; the text in row 1 becomes the text to label each series in the legend for the data series stored in the column.

Listing 1. Code to populate the graphing data shown in Figure 1. This code uses monthly sales figures from the TasTrade sample database.

* Sets up the monthly sales data by year to graph.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

* For demonstration purposes, make oExcel and oBook

* available after this program executes.

PUBLIC oExcel, oBook

#DEFINE xlColumns 2

#DEFINE xlAutoFill 4

CLOSE ALL

* Open the Sales Summary view, which contains

* a summary of unit prices for each month and year.

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

USE Customer IN 0

SELECT 0

USE "Sales Summary" ALIAS SalesSummary

* Add a workbook, using default settings

oExcel = CREATEOBJECT("Excel.Application")

oExcel.Visible = .T.

oBook = oExcel.Workbooks.Add()

WITH oBook.Sheets[1]

* Put the months down Column A. These are the

* category (x) axis labels.

.Range("A2").Value = "January"

.Range("A2:A13").DataSeries(xlColumns, xlAutoFill, 1, 1)

* Loop through the view. Make each year a column, and

* ensure that there's a header.

CurrentColumn = "A"

CurrentYear = "X"

SCAN

IF CurrentYear <> LEFT(SalesSummary.Exp_1, 4)

* Store the Current Year

CurrentYear = LEFT(SalesSummary.Exp_1, 4)

* Increment the column letter (note: don't exceed

* 25 years with this logic!)

CurrentColumn = CHR(ASC(CurrentColumn) + 1)

* Set the header

.Range(CurrentColumn + "1").Value = CurrentYear

ENDIF

* Calculate the current row (add 1 to the value of the month);

* make sure it's a string

CurrentRow = ALLTRIM(STR(VAL(RIGHT(SalesSummary.Exp_1, 2)) + 1))

* Set the value of the cell.

.Range(CurrentColumn + CurrentRow).Value = SalesSummary.Sum_Unit_Price

ENDSCAN

* Widen the columns so all values are seen

* Convert CurrentColumn from the alpha character to a numeric

FOR I = 1 TO ASC(CurrentColumn) - 63

.Columns[I].ColumnWidth = 12

NEXT I

ENDWITH

What if you want the categories in each column, and each row is a series? You can do that. When you create the graph with the ChartWizard method (see the section "Creating a graph" later in this chapter), there s a parameter to denote whether the series are in rows or in columns.

Range names are your friend

It won t take long until your client requests a series of graphs built from the same data. Using the example data shown in Figure 1, the client may want a graph for each year, then one comparing all years on one graph. While you can build a separate worksheet with only the necessary data for the graph (which leads to redundant data), the easiest way is to put all the data on one worksheet, and then use range names for each series and for the category axis labels. Why range names? It s easier to read and debug "CategoryNames" and "Year1994" than "Sheet1!A2:A13" and "Sheet1!D2:D13."

Names are stored in the Names collection, which is accessible from the Application, Workbook, and Worksheet objects. Use the Range object s Name property to name a range. Using the data shown in Figure 1, the following code shows sample names along with their ranges. Like VFP s field names, range names must start with a letter; hence the name "Year1992" rather than "1992."

WITH oExcel.Sheets[1]

.Range("A1:A13").Name = "CategoryNames"

.Range("B1:B13").Name = "Year1992"

.Range("C1:C13").Name = "Year1993"

.Range("D1:D13").Name = "Year1994"

.Range("E1:E13").Name = "Year1995"

.Range("F1:F13").Name = "Year1996"

ENDWITH

Категории