Chart API - Secondary Value axis issue

FLX TMS FlexCel for VCL & FMX
1

Version 7.10. I will be upgrading soon, but waiting for the new release you said was coming. If this is an issue that was already fixed then please forgive me.

I am trying to get the chart API working with setting up graphs with primary and secondary axes. Getting Access violations in my main project, so reduced it to a simple test. This is a screen shot of a very simple file I set up with 2 curves one against primary axis and one against secondary axis. [I even changed it to be an embedded chart instead of a chart sheet in case that was the issue, but it was not...]

image
image1066×527 29.5 KB

I then loaded this in API mate and simply copied the code in to a minimal project with a form and a save dialog. I do not get any error using the code from APImate which is good, but the file is not right... I get 2 axes on the graph, but both are plotted on the left side of the graph.

image
image1047×518 22.4 KB

If I change the data series to plot against primary axis, and then back to secondary, then everything becomes as I would expect.

So am I missing something in terms of forcing some form of internal rendering? Whatever I am missing APImate is not giving it to me.

2

In case it is useful to have the APImate generated code, here it is

procedure CreateExcelFile(const xls: TExcelFile);
var
ChartOptions1: IShapeProperties;
Chart1: IExcelChart;
Title: IDataLabel;
TextFillOptions: IChartFillOptions;
LabelTextOptions: IChartTextOptions;
LabelOptions: TDataLabelOptions;
ChartLineOptions: IChartLineOptions;
ChartFillOptions: IChartFillOptions;
PlotAreaFrame: IChartFrameOptions;
PlotAreaPos: TChartPlotAreaPosition;
Series: IChartSeries;
SeriesFill: IChartSeriesFillOptions;
SeriesLine: IChartSeriesLineOptions;
SeriesMarker: TChartSeriesMarkerOptions;
SeriesMisc: TChartSeriesMiscOptions;
AxisFont: TFlxChartFont;
AxisLine: TAxisLineOptions;
AxisTicks: TAxisTickOptions;
CatAxis: TBaseAxis;
ValAxis: TBaseAxis;

begin
xls.NewFile(1, TExcelFileFormat.v2019); //Create a new Excel file with 1 sheet.

//Set the names of the sheets
xls.ActiveSheet := 1;
xls.SheetName := 'Sheet1';

xls.ActiveSheet := 1; //Set the sheet we are working in.

//Global Workbook Options
//Note that in xlsx files this option is ignored by FlexCel unless you also set OptionsForceUseCheckCompatibility to true. This is because Excel disables Autosave in files which have this option.
xls.OptionsCheckCompatibility := false;

//This setting is only needed if you have names that refer to other names in the wrong order.
xls.AllowEnteringUnknownFunctionsAndNames := true;

//Set up rows and columns
xls.DefaultRowHeight := 300;

//Set the cell values
xls.SetCellValue(1, 1, 'X');
xls.SetCellValue(1, 2, 'Y');
xls.SetCellValue(1, 3, 'Y2');
xls.SetCellValue(2, 1, 1);
xls.SetCellValue(2, 2, 1);
xls.SetCellValue(2, 3, 4);
xls.SetCellValue(3, 1, 2);
xls.SetCellValue(3, 2, 2);
xls.SetCellValue(3, 3, 6);
xls.SetCellValue(4, 1, 3);
xls.SetCellValue(4, 2, 3);
xls.SetCellValue(4, 3, 8);
xls.SetCellValue(5, 1, 4);
xls.SetCellValue(5, 2, 4);
xls.SetCellValue(5, 3, 10);

//Objects
ChartOptions1 := TShapeProperties_Create();
ChartOptions1.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndResize, 1, 89, 4, 960, 24, 89, 16, 704);
ChartOptions1.ShapeName := 'Chart 1';
ChartOptions1.Print := true;
ChartOptions1.Visible := true;
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fLockText, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.LockRotation, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fAutoTextMargin, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fillColor, 134217806);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fLine, false);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.shadowColor, 16777215);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.shadowOpacity, 0);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.wzName, 'Chart 1');
Chart1 := xls.AddChart(ChartOptions1, TChartType.Scatter, TChartStyle.Create(102), false);

Title := TDataLabel_Create();
Title.PositionZeroBased := nil;
TextFillOptions := TChartFillOptions_Create(TShapeFill_Create(TSolidFill_Create(TDrawingColor.FromRgb($59, $59, $59)), true, TFormattingType.Subtle, TDrawingColor.FromRgb($00, $00, $00, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.Alpha, 0))), false));
LabelTextOptions := TChartTextOptions_Create(TFlxChartFont.Create('Calibri', 280, TExcelColor.FromArgb($59, $59, $59), [], TFlxUnderline.None, TFontScheme.Minor), THFlxAlignment.center, TVFlxAlignment.center, TBackgroundMode.Transparent, TextFillOptions);
Title.TextOptions := LabelTextOptions;
LabelOptions := TDataLabelOptions.Create();
Title.LabelOptions := LabelOptions;
ChartLineOptions := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TNoFill_Create(), nil), nil, TFormattingType.Subtle));
ChartFillOptions := TChartFillOptions_Create(TShapeFill_Create(TNoFill_Create(), false, TFormattingType.Subtle, nil, false));
Title.Frame := TChartFrameOptions_Create(ChartLineOptions, ChartFillOptions, false);

Chart1.SetTitle(Title);

Chart1.Background := TChartFrameOptions_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.15), TColorTransform.Create(TColorTransformType.LumOff, 0.85))), 9525, TDrawingColor.FromTheme(TThemeColor.Background1), false);

ChartLineOptions := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TNoFill_Create(), nil), nil, TFormattingType.Subtle));
ChartFillOptions := TChartFillOptions_Create(TShapeFill_Create(TNoFill_Create(), false, TFormattingType.Subtle, nil, false));
PlotAreaFrame := TChartFrameOptions_Create(ChartLineOptions, ChartFillOptions, false);
PlotAreaPos := TChartPlotAreaPosition.Create(true, TChartRelativeRectangle.Automatic, TChartLayoutTarget.Inner, true);
Chart1.PlotArea := TChartPlotArea_Create(PlotAreaFrame, PlotAreaPos, false);

Chart1.SetChartOptions(1, TScatterChartOptions_Create(false));
Series := TChartSeries_Create('=Sheet1!$B$1', '=Sheet1!$B$2:$B$5', '=Sheet1!$A$2:$A$5');
SeriesFill := TChartSeriesFillOptions_Create(nil, nil, true, false);
SeriesLine := TChartSeriesLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Accent1)), 19050, NullableTPenAlignment.Null, TLineCap.Round, NullableTCompoundLineType.Null, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle), false);
SeriesMarker := TChartSeriesMarkerOptions.Create(TChartLineOptions_Create(nil), nil, TChartMarkerType.None, true, true, 0, TUISize.Create(0, 0), false);
SeriesMisc := TChartSeriesMiscOptions.Create(false, false, false);
Series.Options.Add(TChartSeriesOptions.CreateNoClone(-1, SeriesFill, SeriesLine, nil, SeriesMarker, SeriesMisc, true));
Chart1.AddSeries(Series);
Chart1.AddSubchart(TChartType.Scatter, 1);
Chart1.SetChartOptions(2, TScatterChartOptions_Create(false));
Series := TChartSeries_Create('=Sheet1!$C$1', '=Sheet1!$C$2:$C$5', '=Sheet1!$A$2:$A$5');
SeriesFill := TChartSeriesFillOptions_Create(nil, nil, true, false);
SeriesLine := TChartSeriesLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Accent2)), 19050, NullableTPenAlignment.Null, TLineCap.Round, NullableTCompoundLineType.Null, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle), false);
SeriesMarker := TChartSeriesMarkerOptions.Create(TChartLineOptions_Create(nil), nil, TChartMarkerType.None, true, true, 0, TUISize.Create(0, 0), false);
SeriesMisc := TChartSeriesMiscOptions.Create(false, false, false);
Series.Options.Add(TChartSeriesOptions.CreateNoClone(-1, SeriesFill, SeriesLine, nil, SeriesMarker, SeriesMisc, true));
Chart1.AddSeries(Series);

Chart1.PlotEmptyCells := TPlotEmptyCells.NotPlotted;
Chart1.ShowDataInHiddenRowsAndCols := false;

AxisFont := TFlxChartFont.Create('Calibri', 180, TExcelColor.FromArgb($59, $59, $59), [], TFlxUnderline.None, TFontScheme.Minor);
AxisLine := TAxisLineOptions.Create();
AxisLine.MainAxis := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.25), TColorTransform.Create(TColorTransformType.LumOff, 0.75)))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle));
AxisLine.MajorGridLines := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.15), TColorTransform.Create(TColorTransformType.LumOff, 0.85)))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis := false;
AxisTicks := TAxisTickOptions.Create(TTickType.None, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb($59, $59, $59), nil);
CatAxis := TValueAxis.Create(0, 0, 0, 0, 0, [TValueAxisOptions.AutoMin, TValueAxisOptions.AutoMax, TValueAxisOptions.AutoMajor, TValueAxisOptions.AutoMinor, TValueAxisOptions.AutoCross], AxisFont, 'General', true, AxisLine, AxisTicks, nil, TChartAxisPos.Bottom, 0);
AxisFont := TFlxChartFont.Create('Calibri', 180, TExcelColor.FromArgb($59, $59, $59), [], TFlxUnderline.None, TFontScheme.Minor);
AxisLine := TAxisLineOptions.Create();
AxisLine.MainAxis := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.25), TColorTransform.Create(TColorTransformType.LumOff, 0.75)))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle));
AxisLine.MajorGridLines := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.15), TColorTransform.Create(TColorTransformType.LumOff, 0.85)))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis := false;
AxisTicks := TAxisTickOptions.Create(TTickType.None, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb($59, $59, $59), nil);
ValAxis := TValueAxis.Create(-1, 6, 0, 0, 0, [TValueAxisOptions.AutoMajor, TValueAxisOptions.AutoMinor, TValueAxisOptions.AutoCross], AxisFont, 'General', true, AxisLine, AxisTicks, nil, TChartAxisPos.Left, 0);
Chart1.SetChartAxis(TChartAxis_Create(0, CatAxis, ValAxis));
AxisFont := TFlxChartFont.Null;
AxisLine := TAxisLineOptions.Create();
AxisLine.DoNotDrawLabelsIfNotDrawingAxis := true;
AxisTicks := TAxisTickOptions.Create(TTickType.Outside, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromSystem(TSystemColor.WindowText), nil);
CatAxis := TValueAxis.Create(0, 0, 0, 0, 0, [TValueAxisOptions.AutoMin, TValueAxisOptions.AutoMax, TValueAxisOptions.AutoMajor, TValueAxisOptions.AutoMinor, TValueAxisOptions.MaxCross], AxisFont, 'General', true, AxisLine, AxisTicks, nil, TChartAxisPos.Bottom, 0);
CatAxis.Deleted := true;
AxisFont := TFlxChartFont.Create('Calibri', 180, TExcelColor.FromArgb($59, $59, $59), [], TFlxUnderline.None, TFontScheme.Minor);
AxisLine := TAxisLineOptions.Create();
AxisLine.MainAxis := TChartLineOptions_Create(TShapeLine_Create(true, TLineStyle_Create(TSolidFill_Create(TDrawingColor.FromTheme(TThemeColor.Foreground1, TColorTransformArray.Create(TColorTransform.Create(TColorTransformType.LumMod, 0.25), TColorTransform.Create(TColorTransformType.LumOff, 0.75)))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, NullableTLineDashing.Null, TLineJoin.Round, nil, nil, nil), nil, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis := false;
AxisTicks := TAxisTickOptions.Create(TTickType.Outside, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb($59, $59, $59), nil);
ValAxis := TValueAxis.Create(2, 15, 0, 0, 0, [TValueAxisOptions.AutoMajor, TValueAxisOptions.AutoMinor, TValueAxisOptions.AutoCross], AxisFont, 'General', true, AxisLine, AxisTicks, nil, TChartAxisPos.Right, 0);
Chart1.SetChartAxis(TChartAxis_Create(1, CatAxis, ValAxis));

//Cell selection and scroll position.
xls.SelectCell(17, 20, false);

//Standard Document Properties - Most are only for xlsx files. In xls files FlexCel will only change the Creation Date and Modified Date.
xls.DocumentProperties.SetStandardProperty(TPropertyId.Author, 'David Martel');

//You will normally not set LastSavedBy, since this is a new file.
//If you don't set it, FlexCel will use the creator instead.
// xls.DocumentProperties.SetStandardProperty(TPropertyId.LastSavedBy, 'David Martel');

//You will normally not set CreateDateTime, since this is a new file and FlexCel will automatically use the current datetime.
//But if you are editing a file and want to preserve the original creation date, you need to either set PreserveCreationDate to true:
// xls.DocumentProperties.PreserveCreationDate := true;
//Or you can hardcode a creating date by setting it in UTC time, ISO8601 format:
// xls.DocumentProperties.SetStandardProperty(TPropertyId.CreateTimeDate, '2021-11-04T19:40:25Z');

end;

3

Interesting. It looks like this does not behave the way one might expect IN EXCEL.

I took the file and some googling and set this value to "HighEnd" for the right axis and it worked properly. I have added to my code in Flexcel and now the axis labels and tick labels are where I want.

My understanding would be that it should work as follows in Excel
LowEnd: Always left
HighEnd: Always right
NextToAxis: Left for the primary axis, right for the secondary.

The only way I could see this behaviour is if both primary and secondary are on the left (in which case I need to look at other bits in the axis set up (which came from API mate).

Anyway have some progress and if you have any further insight then it would be welcome.

4

We are still investigating it, but this has to do not with the label positions, but with the "crossesAt" from the horizontal axis. To give you an idea, the first thing you need to do is to unhide the secondary horizontal axis:

c1
c11494×664 60.8 KB

Then, selecting that axis, you should be able to change where the vertical secondary axis crosses:

c2
c21402×962 73.3 KB

The label position should affect which side of the axis labels are drawn, (they might even go into the other extreme, say axis is at left of the chart, labels at right). But the axis itself is positioned according with the "Crosses at" from the other axis. In this case, since the horizontal seconday axis is hidden, is difficult to see, and I guess that could be what is causing the wrong behavior in FlexCel. As said, we are still investigating, I'll write back when we find out more

5

Ok, the code generated by APIMate was ok, the problem was in the way FlexCel read those values.

To be more specific when creating the deleted Secondary X Axis, it specified that the Y axis should cross at max:

CatAxis := TValueAxis.Create(0, 0, 0, 0, 0, [TValueAxisOptions.AutoMin, TValueAxisOptions.AutoMax, TValueAxisOptions.AutoMajor, TValueAxisOptions.AutoMinor, TValueAxisOptions.MaxCross]

But FlexCel was reading the 5th "0" ("CrossesAt") and making the Y axis cross at 0. That was a bug, it should favour the "MaxCross" option over having a value in CrossesAt, because there is always a value in CrossesAt, so MaxCross was never used.

We've fixed it here, and your other issue is fixed too. We will be doing some more testing in the weekend and also finishing some unrelated stuff, and we should have a new version by next week.

6

OK, great thanks. I will keep working on other stuff and wait on the new version before I do much more on this side of it. It is almost the end of day here and I am going to the archery range and then for a beer.

As always, many thanks for your tireless work, good humour and speed of getting stuff fixed.

7

@adrian

Just a quick thankyou to confirm that I seem to have this working now after getting 7.13

8

great! thanks for letting me know :)