SoftArtisans OfficeWriter

When you generate a spreadsheet from a template with the ExcelTemplate object, you can create charts in Excel and the charts will be preserved in the generated spreadsheet. You can use template data markers as source data for the chart, and Excel will automatically adjust charts according to the values ExcelWriter assigned to the data marker fields. But ExcelTemplate cannot modify chart features at runtime.

With the ExcelApplication object you can generate a chart from script alone, and modify every aspect of the chart at runtime. ExcelApplication supports 15 chart types and their sub-types.

The example below shows you how to create a complex chart with the ExcelApplication object. The sample demonstrates only a few of the many chart features that you can script with ExcelWriter. You will find all the available chart objects, methods, and properties in the following object reference pages:

To add a chart to a spreadsheet, use the method Charts.Add. Pass the method a chart type and sub-type, and the location of the chart in the specified worksheet (first row, first column, last row, and last column).

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
...
Set XlwChart = XlwApp.Worksheets(2).Charts.Add(ChartType, ChartSubType, _
		ChartFirstRow, ChartFirstCol, _
		ChartLastRow, ChartLastCol)
...

To plot a data series in a chart, use SeriesCollection.Add. This method takes a set of values from which to plot the data series, a null second parameter, and an optional boolean parameter that specifies whether to plot the series by row or by column. The method returns a Series object that represents the data series.

'--- Add three data series to the chart.
XlwChart.SeriesCollection.Add "Sheet1!B2:B6",,1
XlwChart.SeriesCollection(1).Name = "March"
XlwChart.SeriesCollection.Add "Sheet1!C2:C6",,1
XlwChart.SeriesCollection(2).Name = "April"
XlwChart.SeriesCollection.Add "Sheet1!D2:D6",,1
XlwChart.SeriesCollection(3).Name = "May"

Each of the data series added represents a range of Y-axis values. The property SeriesCollection.CategoryData assigns a range of X-axis values from data in the first worksheet:.

XlwChart.SeriesCollection.CategoryData = "Sheet1!A2:A6"

A label can be displayed for each data point in a data series. The labels may show X values, Y values, Y values as percentages, or both X and Y values as percentages. In the example, if the user selected "Actual Value Labels" in the form, labels will display Y values on all charts except pie charts, where labels will display Y values as percentages. If the user selected "Category Labels," labels will display X values on all charts except pie charts, where labels will display X and Y values as percentages.

Select Case LabelValues
  Case "ActualValue"
    If ChartType = saxlsPieChart Then
      '--- For a pie chart, show values as percentages of the whole.
      XlwChart.SeriesCollection.LabelShowValueAsPercent = True
    Else
      '--- For non-pie chars, show the actual value above the bars.
      XlwChart.SeriesCollection.LabelShowActualValue = True
    End If
  Case "CategoryLabel"
    If ChartType = saxlsPieChart Then
      '--- For a pie chart, shows percentages and category labels.
      XlwChart.SeriesCollection.LabelShowCategoryAndValueAsPercent = True
    Else
      '--- For a non-pie chart, show the category labels above the bars.
      XlwChart.SeriesCollection.LabelShowCategoryLabel = True
    End If
End Select

XlwChart.Title.Text = ChartTitleString

A data table is a grid - usually attached to the X-axis - that displays the source values that the chart is based on. To display a data table, enable the property Chart.ShowDataTable.

XlwChart.ShowDataTable = true

Set ChartAreaFrame = XlwChart.ChartArea
'--- Stylize the chart area with a shadow (default is False).
ChartAreaFrame.HasShadow = true
'--- Set Pattern=0 to make it transparent
ChartAreaFrame.Area.Pattern = 0

'--- Indicate whether or not to show the legend
XlwChart.ShowLegend = true
XlwChart.LegendLocation = saxlsObjectCorner
		
'--- Should the legend appear vertically or horizontally?
'--- Default is LegendVertical=True.
XlwChart.LegendVertical = false

XlwChart.CategoryAxis.Title = CategoryAxisTitle
XlwChart.ValueAxis.Title = ValueAxisTitle

Creating a Complex Chart

See Also