Adding Charts |
 |
How to Create a Chart in Script
ExcelWriter currently supports fifteen Microsoft Excel chart types, and over seventy sub-types. For chart type and subtype codes, see Chart Codes. For information on the ExcelWriter's charting objects, methods, and properties, see Charts, Chart, SeriesCollection, Series, View3D, Axis, ChartFrame, and Chart Codes. Note: ExcelWriter is not a charting engine and cannot be used to produce static gif or jpeg representations of charts for inclusion in an HTML page. |
|
This section explains how to create a chart in script. Alternatively, create your charts in Excel, and use ExcelApp.Open* or ExcelTemplate to open and modify the spreadsheets. ExcelApp.Open and ExcelTemplate preserve all charts, including chart types that ExcelWriter cannot create.
To add a chart to a table, set a Charts object, representing the set of all charts in a worksheet:
Set objCharts = ws.Charts
Using the Charts object's Add method, set a Chart object, representing your chart:
Set Chart = Charts.Add(ChartType,[SubType],[UpperLeftRow],[UpperLeftCol],
[LowerRightRow],[LowerRightCol])
Consult Chart Codes to select ChartType
(pie, column, etc.) and SubType. UpperLeftRow, UpperLeftColumn, LowerRightRow,
and LowerRightColumn determine the position of the chart in your spreadsheet.
Set a SeriesCollection object, representing the set of all data series in your chart:
Set objSeriesCollection = objChart.SeriesCollection
Using the CategoryData property, specify the range of category (x) axis values. Assign
CategoryData as a range of cells (such as, "A1:A3"),
or as a sequence of values (such as,"{5,7,9}"). Examples:
objSeriesCollection.CategoryData = "A1:A3"
objSeriesCollection.CategoryData = "{5,7,9}"
Using the SeriesCollection object's Add method, plot a data series or set
of data series:
objSeriesCollection.Add(Area, , [Vertical])
The parameter Area specifies the values from which to plot the data series. Area may be a range of cells (such as, "A1:C3"), or a sequence of values (such as,"{5,7,9}"). The optional parameter Vertical specifies whether to plot the series from a range of cell values by row or by column. When Vertical is set to 0, ExcelWriter will plot the series by row. When Vertical is set to 1, ExcelWriter will plot the series by column. The default value of Vertical is 0.
* This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.
Accessing Data from Multiple Sheets
The following example uses data from a single sheet to plot a chart. You can also use data from multiple sheets in the same workbook. The syntax for accessing data in multiple sheets is,
SheetName!$Column$Row:$Column$Row, SheetName!$Column$Row:$Column$Row
SeriesCollection3.Add "Sheet1!$A$1:$C$3,Sheet4!$A$1:$C$3"
ExcelWriter does not support the following formulations:
Sheet1:Sheet2!$A$1:$C$3Sheet1!$A$1:$C$3;Sheet4!$A$1:$C$3
Example: Generating Charts from Data in a Different Worksheet
The following script generates a workbook containing three sheets. The first sheet contains the data from which we will plot two charts. We will plot a 3D clustered column chart in sheet 2, and a radar chart in sheet 3.
Copyright © 2003, SoftArtisans, Inc.