SoftArtisans OfficeWriter

ExcelWriter's ExcelApplication 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 spreadsheet, set a Charts object, representing the set of all charts in a worksheet:

Using the Charts object's Add method, set a Chart object, representing your chart:

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:

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:

Using the SeriesCollection object's Add method, plot a data series or set of data series:

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.

Accessing Data from Multiple Sheets

The following example generates a workbook containing three sheets. The first sheet contains the data from which we will plot two charts. We will plot a 3-dimensional clustered column chart in sheet 2, and a radar chart in sheet 3.

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
'--- Declarations
Dim xlw, cells, ws1, ws2, ws3
Dim r
Dim c
Dim row, column, letterA, cellname
Dim Charts_ws2, Chart2, SeriesCollection2
Dim Charts_ws3, Chart3, SeriesCollection3

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")

'--- Create three worksheets in the workbook
Set ws1 = xlw.Worksheets(1)
Set ws2 = xlw.Worksheets(2)
Set ws3 = xlw.Worksheets(3)

'--- Populate cells A1:C3 in the first worksheet
r = 1
c = 1
Do While r < 4
Do While c < 4
ws1.cells.Item(r, c).Value = r + c
c = c + 1
Loop
r = r + 1
c = 1
Loop

ws1.cells("a5").Value = "Select Sheet2 to see the chart"

'--- Set a Charts object, representing the set of all charts 
'--- in the second worksheet
Set Charts_ws2 = ws2.charts

'--- In the second sheet, create a 3D clustered column chart 
'--- with the upper left corner at row 3, column 0 (A), and 
'--- the lower right corner at row 15, column 7 (G). 
Set Chart2 = Charts_ws2.add(0,3,3,0,15,7)

'--- In the second sheet, plot a SeriesCollection of three
'--- series, from the values in sheet 1, cells A1:C3. 
Set SeriesCollection2 = Chart2.SeriesCollection
SeriesCollection2.CategoryData = "Sheet1!$A$1:$A$3"
SeriesCollection2.Add "Sheet1!$A$1:$C$3"

'--- Set a Charts object, representing the set of all charts in 
'--- the third worksheet.
Set Charts_ws3 = ws3.charts

'--- In the third sheet, create a pie chart with the upper left 
'--- corner at row 3, column 0 (A), and the lower right corner 
'--- at row 15, column 7 (G). 
Set Chart3 = Charts_ws3.add(4,0,3,0,15,7)

'--- In the third sheet, plot a SeriesCollection of three series, 
'--- from the values in sheet 1, cells a1 through c3. 
Set SeriesCollection3 = Chart3.SeriesCollection
SeriesCollection3.Add "Sheet1!$A$1:$C$3"

'--- Stream the workbook to the client.
xlw.Save "chart.xls", saOpenInPlace

Set xlw = nothing
Response.end
%>

Adding Charts

How to Create a Chart in Script

Accessing Data from Multiple Sheets