Explorations with Excel
Phil Bartow
3/1/01
Getfile can be used to list Excel Workbooks. The listing is not as nice as those found in other applications in that the worksheets are not listed. It is possible to expose the Excel object and get the Workbook object .Sheet.Count and display the workbook and selected worksheets using Automation. I have been exploring the use of Excel to graph data generated in VFP. The effort has been a bit tortuous (for me anyway) in that the Microsoft information on automating Excel is more oriented to VB. If one goes to http://msdn.microsoft.com/library/officedev/off2000/xltocObjectModelApplication.htm (whew! It is easier to search for EXCEL&OBJECTS, select and bookmark) it is possible to list the Microsoft Excel Objects. I was particularly interested in the ChartType property. The problem is the property is accessed using a named constant. There might be an obvious way to evoke (import) the constants but I took a dumb pill and brute forced my way through and developed a table of Chart types shown in my exploration form.
Figure 1
There was one curious issue which somebody might explain to me; for some charts I need to use .Type(id constant) marked with ** and for others I need to use the .Charttype(id constant).
LOADING THE EXPLORATION FORM
For the exercise I use a prg file called LOADEXCL.PRG which defines a number of PUBLIC variables.
***
CLOSE DATA
CLEAR
CLEAR ALL
PUBLIC ExcelOpened
PUBLIC sheetnum
PUBLIC ab_sheet
PUBLIC chtype
PUBLIC mybook
PUBLIC fname
PUBLIC mviewer
PUBLIC ChRANGE
PUBLIC rangbeg
PUBLIC rangend
PUBLIC chrttitle
PUBLIC chrtxaxis
PUBLIC chrtyaxis
ExcelOpened = .F.
Fname = "F:\data\VF2EXCL\BOOK1.XLS"
sheetnum = 1
ab_sheet = 1
chtype = 1
mybook = ""
mviewer = 1
chrttitle = "TITLE"
chrtxaxis = "X-Axis"
chrtyaxis = "Y-Axis"
rangbeg = "A2"
rangend = "A10"
ChRANGE = ALLTRIM(rangbeg)+":"+ALLTRIM(rangend)
IF USED('chrttype')
SELECT chrttype
ELSE
SELECT SELECT(1)
USE chrttype ALIAS chrttype
ENDIF
SET SAFETY OFF
INDEX ON CODE TO CODE
INDEX ON grping TO grping
SET INDEX TO CODE,grping
DO FORM getexcl &&===============>
READ EVENTS
CLEAR EVENTS
CLOSE DATA
CLEAR ALL
Listing the Work Books
The LISTXLS click event
fname = getfile('XLS')
thisform.text1.refresh()
This evokes the list of .XLS files shown in Figure 2
Figure 2
SHEET COUNT - control click event
oExcel = CREATEOBJECT("Excel.Application")
IF ISNULL(oExcel)
ExcelOpened = .F.
ELSE
ExcelOpened = .T.
ENDIF
IF ExcelOpened = .T.
Activesheet = ALLTRIM(fname) &&fname is filname includingpath
WITH oExcel
.APPLICATION.Workbooks.OPEN(Activesheet)
xxx= .sheets.count
sheetnum = xxx
ab_sheet = xxx
.QUIT()
ENDWITH
thisform.text2.refresh() && initialize sheet to be used
thisform.text5.refresh() && set maximum value
ENDIF
GET SPREAD SHEET CONTROL click()
This evokes a display of the Workbook/Sheet.
oExcel = CREATEOBJECT("Excel.Application")
IF ISNULL(oExcel)
ExcelOpened = .F.
ELSE
ExcelOpened = .T.
ENDIF
IF ExcelOpened = .T.
Activesheet = ALLTRIM(fname)
csheet = CHR(34)+"sheet"+LTRIM(STR(sheetnum))+CHR(34) &¬e use of chr(34)
WITH oExcel
.APPLICATION.Workbooks.OPEN(Activesheet)
.APPLICATION.worksheets(&csheet).ACTIVATE
.VISIBLE=.T.
ENDWITH
THISFORM.text2.REFRESH()
THISFORM.text5.REFRESH()
ENDIF
Figure 3
CREATE CHART COMMAND Click()
The code for the click() event pulls everything together
chrange = alltrim(rangbeg)+":"+alltrim(rangend)
oExcel = CREATEOBJECT("Excel.Application")
IF ISNULL(oExcel)
ExcelOpened = .F.
ELSE
ExcelOpened = .T.
ENDIF
IF ExcelOpened = .T.
Activesheet = ALLTRIM(fname)
oExcel.Workbooks.OPEN(Activesheet)
WITH oExcel
.RANGE(CHrange).SELECT
.charts.ADD()
.charts("chart1").hastitle=.T.
.charts("chart1").chartTitle.TEXT= chrttitle
* these could be made into variables
.charts("chart1").chartTitle.font.size = 18 && make a variable
.charts("chart1").plotarea.WIDTH=600
.charts("chart1").plotarea.HEIGHT=300
.charts("chart1").axes(1).hastitle=.t.
.charts("chart1").axes(1).AxisTitle.text= chrtxaxis
.charts("chart1").axes(1).AxisTitle.font.Size = 14 && make a variable
.charts("chart1").axes(2).hastitle=.t.
.charts("chart1").axes(2).axisTitle.text= chrtyaxis
.charts("chart1").axes(2).axisTitle.Font.Size= 14 && make a variable
IF viewer = 1
.charts("chart1").charttype= chtype
ELSE
.charts("chart1").TYPE= chtype
ENDIF
.VISIBLE = .T.
ENDWITH
ENDIF
oExcel.QUIT
The Chart
Form with data
Figure 4
The Chart
Figure 5
To get a different view of the data simple click on the Chart type table and then the Chart Control.
CHRTTYPE DATA
Code is a numeric, Descript is character, Viewer is numeric (1 if Chartype is use and 2 if Type is used)
code descript viewer
1 Area 1
76 Area Stacked 1
77 Area 100% stacked 1
9 Area with 3D visual effect 2
78 Area Stacked witth 3D visual 1
79 Area 100% stacked with 3D visual 1
57 Bar Clustered 1
58 Bar Stacked 1
59 Bar 100% Stacked 1
10 Bar 3D Clustered ** 2
60 Bar 3D Cluster 1
61 Bar 3d Stacked 1
62 Bar 100% Stacked 3D 1
3 Column ** 2
51 Column Clustered 1
52 Column Stacked 1
53 Column 100% Stacked 1
54 Clustered with 3d 1
55 Column Stacked 3d 1
56 Column 100% Stacked 3d 1
11 Column 3D ** 2
4 Line With Markers 1
63 Line Stacked . 1
66 Stacked line with markers 1
65 Line with markers 1
64 Line 100% Stacked 1
67 100% stacked line 1
12 3DLine ** 2
5 Pie 1
13 Pie 3D ** 2
68 Pie of Pie 1
69 Exploded Pie 1
70 3DPieXpl 1
71 Pie-Bar 1
82 Radar filled 1
7 Radar 2
81 Radar with points 1
72 Scatter with data points 1
73 Scatter Smooth line with markers 1
74 Scatter with data points connect by lines 1
75 Scatter pnt -line without markers 1
83 Surface chart - needs two seri 1
84 Surface chart 1
85 Surface chart 1
86 Surface chart-needs two series 1
92 Cylinder column 1
93 Cylinder stacked column 1
94 Cylinder 100% stacked column 1
95 Cylinder Hori Bar with cyl sh 1
96 Cylinder Stacked Bar 1
97 Cylinder 100% Stacked 1
99 Cone Column 1
100 Cone stacked column 1
101 Cone 100% stacked column 1
102 Cone bar 1
103 Cone Stacked bar 1
104 Cone 100% Stacked Bar 1
105 Cone column 3D 1
106 Pyramid Column 1
107 Pyramid Stacked Column 1
108 Pyramid 100% Stacked Column 1
109 Pyramid Bar 1
110 Pyramid Stacked Bar 1
111 Pyramid 100% Stacked Bar 1
112 Pyramid Column 3D 1
6 Doughnut ** 2
80 Doughnut Exploded 1
88 High-low close need 3 points 1
89 Open-HIgh-Low-Close 1
90 Stock Chart 1
91 Stock chart 1
87 Bubble-two value/point 1
No attempt has been made herein (yet) to make all graph options into variables. This would only require developing a table of chart options that could be filled in and the field names made into variables that could be placed in the chart click event. I did enough here to get some insight into reading the Chart object information from the Microsoft site.
Phil@bartowassoc.com