xlsgen > overview > Chart data source

Attaching a data source to a chart is mandatory step to chart creation.

There are two data source types :

• static, also known as regular data sources
• dynamic data sources

Static, regular data sources

A picture speaks a thousand words. Let's take a look at the data source attached to this chart :

Range B1:F3 is the boundary of the data table we are interested in. This data source is called regular because by setting such explicit boundaries, no dynamic computation is going to alter the definition of the data source. Range B1:F3 is really the most obvious example of regular data source. You could make a union of such ranges, for instance B1:F3;B5:F7;B9:F11 and, assuming relevant data is stored in those cells, the chart will draw the corresponding values.

With that said, what is a data source? It's a collection of series. Each series has a number of values. Each series may or may not have a title, and this title can spread across several cells. For each series value, a title can be associated. This title is called a data label.

The boundaries define the extent of series, but don't define how series of values should be read, horizontally or vertically. In this example, if the series values are read horizontally, we have two series, each with 4 values. If series are read vertically, then we have 4 series, with 2 values in each. See the screen below :

By default, xlsgen reads series vertically. Anytime you need series to be taken care of horizontally, the following data source property must be used :

// series should be read horizontally
datasource.SeriesInRows = TRUE;

xlsgen automatically detects titles versus values based on cell types, whether cells contain strings or numerical values. That said, it is always possible to override xlsgen autodetection mechanism thanks to the following properties :

// how many titles per series (auto-detected)
datasource.SeriesTitleDepth = ___;
// how many data labels per value (auto-detected)
datasource.DataLabelDepth = ___;
// how many points per serie
datasource.PointsPerSeries = ___;

The properties should accomodate a lot of scenarios but not all. Here are two scenarios :

• you have more than ranges of data to deal with. In this case, simply combine all ranges using the semi-colon character when defining the data source range.
• you may want to use only a fraction of values in a table. For instance, in the example above where series are read vertically, you may be interested in series 2000 and 2002 only. To achieve this, simply indicate each series in the range, separating those with a semi-colon character : Range = B1C3;B1E3. And use a property known as SeriesPerRangeArea and set it to 1.
• you have a chart with many points, and the resulting chart will not look good. It is probably a good thing to reduce the number of points that are taken into account for series. To do so, the points per series can be manually set, as a ratio. A value of 0.9 (ratio of 90%) means that you want 90% of points to be taken into account, and 10% ignored. A value of 0.5 (ratio of 50%) means half points should be ignored. And so on. By default, all points are taken into account.

A number of cases are summarized in a sample called charts_basic2.

A special attention must be paid for bubble charts as a series of values is made of two actual collections of values. The first collection of values serves the same purpose than for other chart types. The second collection of values is meant to govern bubble sizes. Make sure to take a look at bubble chart samples for more information.

Dynamic data sources

While regular data sources should provide support for most needs, there sometimes is a need to change the boundaries of series, titles and values dynamically over time. Dynamic data sources are aimed to do just that, allow to define formulas which govern series, titles and values in such a way that the underlying data will be automatically taken into account without having to edit chart properties.

A code sample called charts_dynamic_datasource provides an example of this.