Distance of gridlines in fpspreadsheet chart

[hajolo]

I want to Set the distance or number of gridlines for the x-Axis in a chart of fpspreadsheet. The distance of the gridlines seem to be adjusted according the number of rows of the spreadsheet grid. When the number of rows are high the labels belonging to the gridlines are unreadable.

[wp]

Could you post a small sample project demonstrating the issue? (Pack pas, lfm, lpi, lpi as well as any data files needed into a common zip which you can upload under "Attachments and other options"; do not include any compiler-generated binary files).

[paweld]

How to publish a project: Menu > Project > Publish project ... > https://wiki.lazarus.freepascal.org/IDE_Window:_Publish_Project_Package
As the destination directory, I recommend selecting: $(ProjPath)\published\

[hajolo]

I have packed the project.
I would like to have a gridline at 30, 60, 90, 120,,,,, degree.

[wp]

Thanks for the project. It helped me to find some issues in the spreadsheet charts (using the axis increments of the spreadsheet chart in TAChart, xlsx ignoring axis label rotation in writing).

Besides these issues in fpspreadsheet, I think also that you should replace the TsLineSeries by a TsScatterSeries. Both look very similar, but they work fundamentally different. It is one of the biggest annoyances for technicians working with charts in spreadsheet applications that usually there are two types of axes: a type with numeric values (such as the y axis), and a type with equidistant labels, often strings (such as the x axis of bar charts). The TsLineSeries is similar to a TsBarSeries in that it gets its x data from a label axis. This poses a problem in TAChart because it will place a label at every x value point and needs to apply lots of tricks to avoid the overlappling.

The correct way to handle this is to instruct the spreadsheet chart to use a value axis for x as well. This is automatically done when your series is a TsScatterSeries. Here the x values can be placed arbitrarily (although you do not do this in your example), and the x labels are numbers. And now TAChart can handle the labels in a much better way.

So, the main change in your code is to replace TsLineSeries by TsScatterSeries.

In order to get x axis labels at 0, 30, 60, ... you have to call wChart.xAxis.AutomaticMajorInterval := false and wChart.xAxis.MajorInterval := 30. But you need to get the new fpspreadsheet revision for this to work. Use svn or downlaod the zip snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/.

Find a modified version of your project in the attachment.

[hajolo]

Many thanks,
your solution works with nicestep setting 3.
How can I adjust the interval manually? I could not acchieve this,
By the way: How did you find the solution with tsScatterSeries ?

[wp]

your solution works with nicestep setting 3.

Is this the "NiceStep" of the TAChart's axis intervals? Don't change this, adjust the worksheet chart's xaxis.MajorInterval instead (after switching xaxis.AutomaticMajorInterval to false).

How can I adjust the interval manually? I could not acchieve this

Isn't this contained in the demo that I sent? Simply change the angle steps by the SpinEdit control at the top, and this recreates the spreadsheet (for better clarity this should have been put in a more clearly-named procedure, e.g. CreateDataAndPlot, and TForm1.FormCreate and TForm1.FloatSpinEdit1Change should just call this).

By the way: How did you find the solution with tsScatterSeries ?

I am a physicist, and in my early days my collegues and I were annoyed by the fact the Excel could not create the x-y charts that we were used to. Until we detected the scatter plot. And recently, when I wrote the chart support for fpspreadsheet, I noticed that the xlsx files contain those two axes again: the xml nodes for the x axis are named <c:catAx> for a bar chart,

[hajolo]

Thank you for the quick reply.

Is this the "NiceStep" of the TAChart's axis intervals?   YES

You included the following lines in the code:
  wchart.XAxis.AutomaticMajorInterval := false;
  wChart.XAxis.MajorInterval := 30;
  wChart.XAxis.AutomaticMinorInterval := false;
  wChart.XAxis.MinorInterval := 10;
Unfortunately this does not work.
( When I started your modified program the interval shown on the chart was 50.)
I'm also a (retired) physicist. I reactivated an older Laptop and installed Linux Mint to learn a little bit about Linux.
My starting programming language was Delphi, later C++builder. It was a surprise for me, that Lazarus is such a powerful language.
KR
H.G. Lotz

[wp]

Did you update your FPSpreadsheet from CCR as I had written in the previous post? The older versions did not apply the Major/MinorIntervals to the TAChart.

[hajolo]

I have loaded the actual  Version from Sourceforge.
As a Newbie of Linux and its package loading procedure I am not 100 percent sure, if I have done it correctly. How can I check this ?

[wp]

As a Newbie of Linux and its package loading procedure...

This has nothing to do with Linux package loading: Just unzip the snapshot downloaded from the ccr page (link given above) into the same directory where your old FPSpreadsheet had been. To check that you have the newest version, you could load the file fpSpreadsheetChart and check whether you find a function Mantisse(x: Double): Double. If you don't you still have the old version.

And just to make sure that the modified units are compile you should rebuild the IDE: "Tools" > "Configure Build Lazarus" > check "Clean all" and "Switch to automatic after building" > "Build". Not absolutely necessary but this is one of the most effective ways to get up-to-date compiled units.

[hajolo]

Hello
I'm sorry, but I could not find a file fpSpreadsheetChart containing Mantisse.
Attached you find the zipped download file of fpspreadsheet/source/common using your link
https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/

[wp]

Sorry, I should have been more specific: fpspreadsheetchart.pas is in the folder source/visual of the fpspreadsheet installation.

Another indication of the new version is the method TsSpreadOOXMLChartReader.WriteChartLabels in file source/common/xlsxooxmlchart.pas which has "ARotationAngle: single" as last parameter now (the last argument was "AFont: TsFont" in the old version).

Since you are showing a subfolder common.zip here, just to make sure that your download is correcct: go to https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/ and click on the "Download snapshot" button marked by a red rectangle

[hajolo]

Hello
I found this in xlsxooxmlchart.pas:
procedure WriteChartLabels(AStream: TStream; AIndent: Integer; AFont: TsFont);
So it must be the old version ?
I clicked on the "Download snapshot" button marked by a red rectangle.
This is the file which has been downloaded:
azarus-ccr-svn-r9907-components-fpspreadsheet.zip

[wp]

You must unzip it into the same folder structure in which you had installed FPSpreadsheet before, overwrite all existing files. But keep the directory structure used in the zip (some unzippers allow the option to move all files into the same directory, this will not be working).