9.6 Putting it all together | Microsoft Office Automation with Visual FoxPro

Putting it all together

Listing 2 shows a program (XLSample3.PRG in the Developer Download files available at www.hentzenwerke.com) that uses many of the features covered in this chapter. The first chart is a column chart that uses the data generated in Listing 1. Because the data for 1996 is incomplete, the range omits this data. To illustrate adding a Series to a graph, an Average column is created and populated using Excel s AVERAGE function. The Average series is then formatted as a Line chart. Each of the other columns is formatted to a specific color, optimized for printing (that way, it looks good when printed in this book). The axis labels are formatted, too. This chart is shown in Figure 17.

The second chart is a 3D pie chart, which is a chart sheet instead of an embedded chart. It uses some 3D formatting properties, and changes the formatting of some titles and data labels, as well as the color of the pie wedges. Gray was chosen as the color so it would print well. The pie chart is shown in Figure 18.

Table 9. Properties of the ChartTitle and AxisTitle objects.

Property	Type	Description
Border	Object	References a Border object. See "The Border object" topic earlier in this chapter. By default, Titles do not have borders.
Caption, Text	Character	These two identical properties contain the title text.
Font	Object	References a Font object. See "The Font object" topic earlier in this chapter.
HorizontalAlignment	Numeric	Determines the horizontal alignment for the title. Use one of the following: xlHAlignCenter -4108 xlHAlignLeft -4131 xlHAlignDistributed -4117 xlHAlignRight -4152 xlHAlignJustify -4130
VerticalAlignment	Numeric	Determines the vertical alignment for the title. Use one of the following: xlVAlignBottom -4107 xlVAlignJustify -4130 xlVAlignCenter -4108 xlVAlignTop -4160 xlVAlignDistributed -4117
Left, Top	Numeric	Can be set to fine-tune the title s location.
Orientation	Numeric	Determines the text orientation, ranging from -90 to 90. Or a constant can be used: xlDownward -4170 xlHorizontal -4128 xlUpward -4171 xlVertical -4166
Shadow	Logical	Indicates whether there is a drop-shadow on the title box.
Interior	Object	References an Interior object, which controls the color of the title box. See "The Interior object" topic earlier in this chapter.

Listing 2. Tasmanian Traders Annual Sales graphs. This code covers many of the topics covered in this chapter, such as creating graphs, working with multiple ChartTypes within a chart, adding Series, formatting individual Series, and more.
* 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 certain objects

* available after this program executes.

PUBLIC oExcel, oBook, oSheet, oChart

#DEFINE xlColumn 3

#DEFINE xl3DPie -4102

#DEFINE xlColumns 2

#DEFINE xlLegendPositionBottom -4107

#DEFINE xlLineStyleNone -4142

#DEFINE xlCategory 1

#DEFINE xlValue 2

#DEFINE xlPrimary 1

#DEFINE xlFillDefault 0

#DEFINE xlLineMarkers 65

#DEFINE xlMarkerStyleDiamond 2

#DEFINE autoColumnFormat 4

#DEFINE autoPieFormat 7

#DEFINE autoOneSeriesLabel 1

#DEFINE autoOneCategoryLabel 1

#DEFINE autoHasLegend .T.

#DEFINE autoNotHasLegend .F.

#DEFINE rgbWhite RGB(255, 255, 255)

#DEFINE rgbLtGray RGB(192, 192, 192)

#DEFINE rgbMedGray RGB(128, 128, 128)

#DEFINE rgbDkGray RGB( 64, 64, 64)

#DEFINE rgbBlack RGB( 0, 0, 0)

* Create the workbook and add the data

DO XLGDataSetup && Listing 1

* Add the range names

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

Figure 17. The Annual Sales graph. This chart demonstrates axis formatting, using different ChartTypes by Series, moving the legend, and formatting each Series individually.
* Create the first chart

oChart = oExcel.Sheets[1].ChartObjects.ADD(0, 175, 400, 200)

oChart.ACTIVATE()

* Include only the category names and years through 1995

oSourceRange = oExcel.Sheets[1].Range("A1:E13")

oExcel.ActiveChart.ChartWizard(oSourceRange, xlColumn, autoColumnFormat, ;

xlColumns, autoOneCategoryLabel, autoOneSeriesLabel, autoHasLegend, ;

"Annual Sales", "Sales by Month", "Dollars")

WITH oExcel.ActiveChart

* Move the legend to the bottom, and remove the border

.Legend.Position = xlLegendPositionBottom

.Legend.Border.LineStyle = xlLineStyleNone

* Format the axes.

* On the category axis, set the size a little smaller,

* and put the labels on a 45 degree slant.

.Axes(xlCategory, xlPrimary).TickLabels.Orientation = 45

.Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 8

* On the value axis, set the size a bit smaller,

* format the values to currency, and turn on Gridlines

.Axes(xlValue, xlPrimary).TickLabels.Font.Size = 8

.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "$#,###;;$0"

.Axes(xlValue, xlPrimary).HasMajorGridlines = .T.

.Axes(xlValue, xlPrimary).MajorGridlines.Border.Color = rgbLtGray

* Abbreviate the category labels to make more room for the chart

FOR I = 1 TO 12

WITH oExcel.ActiveSheet.Range("A" + ALLTRIM(STR(I + 1)))

.Value = UPPER(LEFT(.Value, 3))

ENDWITH

ENDFOR

* Center the axis within the ChartArea

.Legend.Left = (.ChartArea.Width - .Legend.Width) / 2

ENDWITH

* Add an average column.

WITH oExcel.Sheets[1]

* Insert the Series label

.Range("G1").Value = "Average"

* Insert the AVERAGE formula into each cell

FOR I = 2 TO 13

cI = ALLTRIM(STR(I))

.Range("G" + cI).Value = "=AVERAGE(B" + cI + ":F" + cI + ")"

ENDFOR

* Name the range

.Range("G1:G13").Name = "Average"

ENDWITH

WITH oExcel.ActiveChart

* Add the Average series, and format it to a black line

* with diamond markers (also black).

.SeriesCollection.Add( oExcel.Sheets[1].Range("G1:G13"), ;

xlColumns, .T., .F., .F.)

WITH .SeriesCollection[5]

.ChartType = xlLineMarkers

.MarkerStyle = xlMarkerStyleDiamond

.MarkerForegroundColor = rgbBlack

.MarkerBackgroundColor = rgbBlack

.Border.Color = rgbBlack

ENDWITH

* Change the colors of the bars to shades of gray

.SeriesCollection[1].Interior.Color = rgbWhite

.SeriesCollection[2].Interior.Color = rgbLtGray

.SeriesCollection[3].Interior.Color = rgbMedGray

.SeriesCollection[4].Interior.Color = rgbDkGray

* Format the interior color of the plot area

.PlotArea.Interior.Color = rgbWhite

ENDWITH

Figure 18. The 1995 Sales 3D pie chart. This chart demonstrates some of the 3D chart features, such as Explosion, Rotation, and Elevation, as well as working with non-contiguous data ranges, and formatting titles and data labels.
* Create the second chart. This time it's a chart sheet.

oChart = oExcel.Charts.Add()

oChart.ACTIVATE()

* Use only the category names and 1994 data. Note that the chart

* sheet was added before the data worksheet, which bumps the data

* worksheet to the second instance in the collection of worksheets.

oSourceRange = oExcel.Sheets[2].Range("CategoryNames, Year1994")

WITH oExcel.ActiveChart

* Create a 3D pie chart

.ChartWizard(oSourceRange, xl3DPie, autoPieFormat, ;

xlColumns, autoOneCategoryLabel, autoOneSeriesLabel, ;

autoNotHasLegend, "1995 SALES")

* Set the elevation up a little more than the default

.Elevation = 35

* Rotate the chart. Rotating a pie chart changes the angle

* of the first slice.

.Rotation = 30

* Increase the title's font size

.ChartTitle.Font.Size = 22

WITH .SeriesCollection[1]

* Make all DataLabels bold

.DataLabels.Font.Bold = .T.

* Format the November wedge by exploding it, adding

* a custom label, and changing the label's font size

WITH .Points[11]

.Explosion = 15

.DataLabel.Text = "US HOLIDAY" + CHR(13) + .DataLabel.Text

.DataLabel.Font.Size = .DataLabel.Font.Size + 4

ENDWITH

* Format the December wedge by exploding it, adding

* a custom label, and changing the label's font size

WITH .Points[12]

.Explosion = 15

.DataLabel.Text = "HOLIDAY" + CHR(13) + .DataLabel.Text

.DataLabel.Font.Size = .DataLabel.Font.Size + 4

ENDWITH

ENDWITH

* Identify each quarter: Q1 and Q3 are gray, Q2 and Q4 are white

FOR I = 1 TO 12

IF I <= 3 OR (I >= 7 AND I <=9)

.SeriesCollection[1].Points[I].Interior.Color = rgbLtGray

ELSE

.SeriesCollection[1].Points[I].Interior.Color = rgbWhite

ENDIF

NEXT I

ENDWITH

Excel s charting engine is very robust, and it offers tons of formatting options. You ll amaze your clients (and yourself) with the number of options available.