Difficulties formatting charts and series

FLX TMS FlexCel for VCL & FMX
1

I am trying to export my TCharts to excel using Flexcel - mostly this is working OK but I have some issues that I cannot seem to resolve (I can work around some).  The basic problem is that for some objects/interfaces I am able to read values and then change those value, but the new values are not reflected in the xls file...  

Some example code snippets  (I have written a small demo that illustrates the issues - if anyone wants to look in more detail):
Formatting Chart axes
....
procedure TExportChtToExcel.FormatXLChart(XLChart : IExcelChart; CurChart: TChart);
var
 AxesArr : TArray<IChartAxis>;
 AxesLabelArr : TArray<TCellValue>;
 XLLegend : IChartLegend;
 XLAxis : IChartAxis;
 XAxisVals : TValueAxis;
begin
  AxesArr := XLChart.GetChartAxis;
  if Length(AxesArr) > 0 then
   begin
     XLAxis := AxesArr[0];
     if XLAxis.ValueAxis.Caption <> nil then      // if there is an Y axis caption in the XLChart
      begin
        AxesLabelArr := XLAxis.ValueAxis.Caption.LabelValues;
        if Length(AxesLabelArr)>0 then
         begin
           if CurChart.LeftAxis.Title.Visible then
             AxesLabelArr[0] := CurChart.LeftAxis.Title.Caption // After this line AxesLabelArr[0] has the desired value but it does not show up in the output file.
           else
             AxesLabelArr[0] := '';
         end;
      end;

     if XLAxis.ValueAxis is TValueAxis then
      begin
        XAxisVals := TValueAxis(XLAxis.ValueAxis);
        XAxisVals.AxisOptions := [];  // Again all of these assignments appear to work.... but nothing happens to the output file
       {  some other code }
        XAxisVals.Max := CurChart.LeftAxis.Maximum;
        XAxisVals.Min := CurChart.LeftAxis.Minimum;
        XAxisVals.Major := CurChart.LeftAxis.Increment;
        XAxisVals.Minor := CurChart.LeftAxis.Increment/CurChart.BottomAxis.MinorTickCount;
      end;
   end;
  {  some other code }
end;
....
Formatting Series
.....
function TExportChtToExcel.FormatXLSeries(CurSeries : TChartSeries; XLSeries : IChartSeries;  XLChart : IExcelChart) : boolean;
var
 SeriesPen : TChartPen;
 ChartOpts : TArray<IChartOptions>;
 SeriesOptions : TChartSeriesOptions;
 CurSerChartOptions : IChartOptions;
begin
  XLSeries.TitleValue := CurSeries.Title; // works - output file has the right title
  SeriesOptions := XLSeries.Options[-1];
  // All assigments using SeriesOptions have no impact on output file
  SeriesOptions.LineOptions.LineColor := CurSeries.Color;  
  SeriesOptions.MarkerOptions.LineFormat.LineColor := CurSeries.Color;
  if CurSeries is TCustomSeries then
   begin
     SeriesPen := TCustomSeries(CurSeries).LinePen;
     if (TCustomSeries(CurSeries).Pointer = nil) or (Not TCustomSeries(CurSeries).Pointer.Visible) then // if the pointer is not visible then hide it in Excel
        SeriesOptions.MarkerOptions.MarkerType := TChartMarkerType.None
     else if SeriesOptions.MarkerOptions.MarkerType = TChartMarkerType.None then  // turn on markers (if they are not already on)
        SeriesOptions.MarkerOptions.MarkerType := TChartMarkerType.Circle;
     if SeriesPen.Visible then
      begin
        if SeriesPen.Style = psSolid then SeriesOptions.LineOptions.Style := TChartLineStyle.Solid
        else if SeriesPen.Style = psDash then SeriesOptions.LineOptions.Style := TChartLineStyle.Dash
        else if SeriesPen.Style = psDot then SeriesOptions.LineOptions.Style := TChartLineStyle.Dot
        else if SeriesPen.Style = psDashDot then SeriesOptions.LineOptions.Style := TChartLineStyle.DashDot
        else if SeriesPen.Style = psDashDotDot then SeriesOptions.LineOptions.Style := TChartLineStyle.DashDotDot
        else if SeriesPen.Style = psClear then SeriesOptions.LineOptions.Style := TChartLineStyle.None;
        if SeriesPen.Width > 1 then SeriesOptions.LineOptions.LineWeight := SeriesOptions.LineOptions.LineWeight * SeriesPen.Width
      end
     // otherwise hide the line
     else SeriesOptions.LineOptions.Style := TChartLineStyle.None;
   end
  else if CurSeries is TPointSeries then
   begin
     if SeriesOptions.MarkerOptions.MarkerType = TChartMarkerType.None then // turn on markers (if they are not already)
       SeriesOptions.MarkerOptions.MarkerType := TChartMarkerType.Circle;
     SeriesOptions.LineOptions.Style := TChartLineStyle.None;
   end;
// trying to use the seconday axis (my template has a secondary axis
  ChartOpts := XLChart.ChartOptions;
  if (Length(ChartOpts)>1) then
   begin
     if (CurSeries.VertAxis <> aLeftAxis) then XLSeries.ChartOptionsIndex := 1 // use the right axis if it is defined
     else XLSeries.ChartOptionsIndex := 0; // use the left axis (only 1 set of chart options)
   end;
end;
....

Thanks
2

Hi,
Sadly modifying charts with the API is not fully supported yet. It is something that we are working on, but yes, many properties don't provide a way to modify them back.

For a little more of history, the work in the chart object model was done about the 4.0 FlexCel times, and it started with reading xls charts (this part is finished and it is what the redering engine uses to render the charts). But when we were about to start the writing part, Microsoft release xlsx and all priorities changed. Since then we have been steadily adding xlsx features (and a lot of other stuff too) until today, when virtually the only important thing missing is a way to add charts with the API, to modify them, and to export xlsx charts to pdf. So this is what we are working on right now, and I hope it won't take too long until we have a better API for modifying/adding charts. (with APIMate telling you how to do it too of course).

And going a little more in detail of the missing methods, FlexCel works differently from OLE in that most objects are loosely coupled and don't know about their parents. So if you get a range of cells, this range doesn't know anything about the workbook it is in, and if you modify it, you won't modify the workbooks as you would in OLE Automation with some code like range.value = 7.

So in FlexCel the pattern is:
something := xls.GetSomething;
Modify(something); //This modifies something, but not xls or the chart.
xls.SetSomething(something);

We do have some methods in charts like GetSeries/SetSeries but others like ChartOptions don't have a way to "Set" them.
So you can get the chart options, you can modify them, but until we provide a "SetChartOptions" or a ChartOptions := options; you can't put those values back in the chart.

As said, we are working in adding those missing "set" methods so the API is completed, but right now we only have some basic ones like SetSeries. A lot of others still need to be implemented and that's what we are doing right now (that and of course also fixing stuff. fixing stuff has the higher priority, so that's why progress adding the new methods is slower than I would like it to be)

3

Thanks Adrian,


I had sort of come to that conclusion as I was able to change some things but not others.  I look forward to your completed API being released.
4

Hi Adrian


I wanted to get charts images using RenderObject.

I used for my testing the provided sample 45. Render Objects which works fine for xls files but not for xlsx files.

I just get a white picture.

 private Image GetChart()
        {
            //We could get the chart with the following command, 
            //but it would be fixed size. In this example we are going to be a little more complex.

            //Xls.RenderObject(ChartIndex);

            //A more complex way to retrieve the chart, to show how to use
            //all parameters in renderobject.

            TUIRectangle ImageDimensions;
            TPointF Origin;
            TUISize SizePixels;

            //First calculate the chart dimensions without actually rendering it. This is fast.
            Xls.RenderObject(ChartIndex, 96, ChartProps,
                SmoothingMode.AntiAlias, InterpolationMode.HighQualityBicubic, true, false,
                out Origin, out ImageDimensions, out SizePixels);

            double dpi = 96;  //default screen resolution
            if (SizePixels.Height > 0 && SizePixels.Width > 0)
            {
                double AspectX = (double)chartBox.Width / SizePixels.Width;
                double AspectY = (double)chartBox.Height / SizePixels.Height;

                double Aspect = Math.Max(AspectX, AspectY);
                //Make the dpi adjust the screen resolution and the size of the form.
                dpi = (double)(96 * Aspect);
                if (dpi < 20) dpi = 20;
                if (dpi > 500) dpi = 500;
            }

            return Xls.RenderObject(ChartIndex, dpi, ChartProps,
                SmoothingMode.AntiAlias, InterpolationMode.HighQualityBicubic, true, true,
                out Origin, out ImageDimensions, out SizePixels);


Could you possibly help on that issues?

regards

Marc
5

Hi,
Sadly there is still no a chart rendering engine for xlsx files, and this means charts in xlsx files are not exported to pdf, html, renderobject, or any place. As mentioned in my last post, we are working on it and a xlsx chart engine is the main feature for the upcoming FlexCel 7, but it does take a lot of time to get all the chart rendering details right.

6

Do you have a time frame for the 7 release?

Marc

7

While we do have internal estimates, 15 years of developing FlexCel tend to show me that those are normally completely wrong, so we try not to make them public until we are really near the date of release. Estimating dev time in a project like this is basically impossible, because it depends in how many support questions you get in the day. Lately support has been very active, and this slows down development time a lot, because while charts are the most important dev thing right now, bugs and support have even higher priority. So I estimate I will develop feature x today, and end up using the whole week to debug a weird bug that only happened in very special cases.

For what it is worth, we are already delayed, charts were supposed to be ready for december, but there is still a lof of work pending to do.