Easy way to create chart in Flexcel

[Novo_David1 (Novo David)]

I am trying to use the chart features for the first time. I found the chartAPI demo at
https://doc.tmssoftware.com/flexcel/vcl/samples/delphi/api/chart-api/index.html

First thing that struck me is that I have to copy/paste a 100 of lines of code just to insert a single chart? That is crazy.

If I was using Excel, I simply select my rows/columns with data and put it "insert chart". It them creates a chart with default settings that at least 50% of the time is perfectly fine. If I customize something it is often just a title or axis labels.

I was expecting a method something like
AddLineChart(aDataRange, aTopLeft, aWidth, aHeight)

maybe an overload that allowed you to add common properties (xAxisTile, yAxisTile, ChartTitle)

That is much easier for people like me who dont want (or need) to get into the weeds of setting custom TextFillOptions, LabelTextOptions, DataLabelOptions, ChartFillOptions, ChartFrameOptions. The Excel defaults are all fine for me.

Is there an easier way that I did not find compared to the boatload of code in the API demo example?

[adrian]

Well, now you get an idea why it took us more than a year to add support for charts in FlexCel 7

Now, more seriously, there are some things to consider here:

1. Charts are indeed complex, you can add them without any parameters but the defaults in Excel aren't great. But the demo tries to show everything, so it is more complex than it needs to be. Many defaults are ok as they are.

2. You are not supposed to write that code manually. I myself wouldn't know from where to start. As mentioned in the demo, you create the chart in Excel (if you don't want to modify anything, don't modify anything). Then open the file in APIMate and get the code needed for that chart. It shouldn't be that big, unless you do a lot of customizations.

3. A method like the one you propose would be very tricky. There are a lot of heuristics when you select a range and add a chart from Excel. Say for example, if the range has more columns than rows it will use the rows as series, but if not it will use the columns. There is a lot of more "magic" going on, and as you say, Excel normally gets it well. Except when it doesn't (the other 50%), but when you are using Excel, which is interactive, you can see the chart is not what you want and start playing with it until you get the one you want. With a code tool like FlexCel, it is much more difficult and frustrating to start changing parameters, and in many cases you might not even know the final result until the user runs your app.

   A simple example, let's say you want the series by column. So you have fruits in column A, vegetables in B, and meats in C. You run a simple test, with 10 rows, and as there are more rows than columns, AddLineChart picks the correct heuristic (the series go in columns, as you have 10 rows and 3 columns) and the chart looks great. But now you sent this app to a customer, and he runs it with a dataset with 2 rows. Now the heuristic says that the series are the rows, and the chart is completely wrong.

   For this reason FlexCel tries as hard as it can to be "dumb". It doesn't try to be smart and guess what you want to do, because even if the heuristic picks the right choice 90% of the times, the 10% where it doesn't means bugs. So you have to tell it explicitly if you want the series in rows or columns, etc. It is a rule for all FlexCel design: For instance in Excel if you enter 2-2-2000 it will convert it to a date, because it is what you normally want. But in FlexCel, xls.SetCellValue(1,1,'2-2-2000') will enter a string, because that's what you said you wanted to do. We follow the principle of least surprise, and we find it works well for a code tool like Excel, because the least heuristic or random decisions in your code the better. It is better to be explicit, even if it is a little more code. It is an extra hour once you spend reading the 100 lines of code, and 10 hours less you spend debugging bugs that you can't even understand why they are happening.

4. Charts can indeed take a lot of code, but another option which I use myself a lot if you don't want to write the code is to create a template in Excel, then fill it with data (you can use the API for that, no need to use reports), or just InsertAndCopyRange from that template to the file you are creating. Then you change the range of the chart with code and that is it. The advantage of this approach is that whenever you want to customize the chart you open the template in Excel and customize it, without having to change code. But this is as you prefer, adding the full chart with code is many times a better approach.