Generating Chart from Dynamic Data Table

I cannot determine how to generate a chart when the source does not have a constant column count. I'm generating a table which is passed to FlexCell. Because the table is generated, I do not know the column count and column names at design time. Therefore, I'm generating the report data using:

When the chart is generated the series does not encompass the data, but is split in two. The first series range covers the first two rows, where the first row is column headers. The second series range covers three rows, but skips three. The report template has the series defined across 27 columns and 5 consecutive rows. In other words, the template chart series is defined as:
but the generated report has the series changed to:
What needs to be done differently to generate a chart given a dynamic table?


FlexCel won't add series to a chart when you insert cells, this is done mostly for Excel compatibility (because Excel doesn't add series either), but also because when adding series we would have to assign them random properties (like the color or the marker type for the new serie)

You can see the exact thing if you do it in Excel, open the generated file, make the chart range use all the cells, and then insert a column in the middle. You will see that the range is split in 2.

Now, looking at your result (series are horizontal instead of vertical) I think there is another problem besides this, and I'll need to explain this a little more in depth.

Even when Excel (2007 or up) will show you a "data range" like ='Worksheet'!$A$25:$AA$29 internally what you have are "Categories" and "Series".

Normally we want to have the less number of series, so if you have a "data range" going from A1 to C1000, Excel will create 3 vertical series, one for column A, the other for column B and the last for Column C. Excel will always look at your data range, and assign the smallest dimension to the series.

And this is normally what you want, but no when you are using a template. This is because if you have a "dummy" datarange in the template of A25:AA29, Excel will see about 27 columns and 5 rows. So it will assign the series horizontally, because in the template there are more columns than rows.

But of course, in the generated file you might have about 1000 rows and the same 27 columns, and so the original decision was wrong, we should have put the series in the columns, not in the rows. (a chart with 1000 series is virtually unreadable) You want 27 series, not 1000.

So, when designing the template, in the "Select Data" dialog, press the button "Switch Row/Column"  As said, you will need to press this button in almost all templates, because Excel will guess wrong the rows and the columns when the chart has no data.

Once your series are in columns, not in rows, the data will expand when you insert rows. FlexCel will still not add series, so you will have the original 27 series, but this is as expected.