FPSpreadsheet: Chart Tutorial

WORK IN PROGRESS, TO BE CHANGED !!!

One of the additions to FPSpreadsheet version 2.0 is the possibility to create, read and write charts. In this tutorial we will give a short introduction how to create a chart from spreadsheet data and how to display it visually by means of TAChart.

Preparing data

In this chart we will show sales results of two products of an imaginary compary in three European cities. At first we must create a workbook, a worksheet and add the data to be plotted:

• Create a new Lazarus LCL project.
• Add the package "laz_fpspreadsheet" to the project requirements in order to get access to the FPSpreadsheet library.
• Add fpspreadsheet, fpstypes, fpsutils to the uses clause of the project's main form - the standard for all FPSpreadsheet projects. Since we will create xlsx and ods files we also need the xlsxooxml and fpsopendocument units in the uses clause.
• Add a button and call a method CreateChart from its OnClick event handler. CreateChart our main work place during the rest of this tutorial.

procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateChart;
end;

procedure TForm1.CreateChart;
begin
  // to be completed...
end;

• In CreateChart we first create a TsWorkbook (named wbook) and add a TsWorksheet (named wsheet) to it. To complete the code skeleton we also add code to save the workbook to xlsx and ods files, and to destroy the workbopok at the end:

procedure TForm1.CreateChart;
var
  wbook: TsWorkbook;
  wsheet: TsWorksheet;
begin
  wbook := TsWorkbook.Create;
  try
    wsheet := wbook.AddWorksheet('Sales');  // 'Sales' is the caption of the worksheet tab.

    // more code to be inserted here...

    wbook.SaveToFile('chartdemo.xlsx', true);
    wbook.SaveToFile('chartdemo.ods', true);
  finally
    wbook.Free;
  end;
end;

• Now let's add data. All the following code will go at the place of the "more code to be inserted here..." comment in above snippet.
• At first, we write some kind of title in cells A1 and A2

    wsheet.WriteText(0, 0, 'Sales Results');             // Cell A1
    wsheet.WriteFontSize(0, 0, 12);                      // Increase the text size in cell A1
    wsheet.WriteText(1, 0, '(in millions of Euros)');    // Cell A2

• Next, starting at row index 3 there will be three colums, the first one with the city names, the second one with the sales data for "product A", and the last one with the sales data for "product B". At first we write the product names to cells B4 and C3:

    wsheet.WriteText(3, 1, 'Product A');    // Cell B4
    wsheet.WriteText(3, 2, 'Product B');    // Cell C4

• Now we write the city names and the sales numbers:

    wsheet.WriteText(4, 0, 'London');  wsheet.WriteNumber(4, 1, 1.6);    wsheet.WriteNumber(4, 2, 2.3);  // Cells A5, B5, C5
    wsheet.WriteText(5, 0, 'Paris');   wsheet.WriteNumber(5, 1, 1.2);    wsheet.WriteNumber(5, 2, 1.0);  // Cells A6, B6, C6
    wsheet.WriteText(6, 0, 'Rome');    wsheet.WriteNumber(6, 1, 1.3);    wsheet.WriteNumber(6, 2, 0.5);  // Cells A7, B7, C7

• This completes the data generation. You can run the project now, it will create files "chartdemo.xlsx" and "chartdemo.ods" which you can open in Excel or LibreOffice Calc to verify the entered data.

Preparing the Chart

All the charting types and classes and routines are contained in unit fpschart. Study at least the interface part of it because here we can address only a small part of it.

TsChart is the main class for the FPSpreadsheet charts. Do not confuse it with the chart of the TAChart library - like all (well, most...) classes in FPSpreadsheet its class name has an "s" after the "T". It has no visual representation and only collects all information related to the chart. All charts of a workbook are collected in an internal workbook list.

Creating the Chart

A chart is created similarly to the a worksheet: call the corresponding "Add..." method of the workbook, here AddChart; the same can be done from the worksheet. There are parameters to specify size and position of the chart within the worksheet:

function TsWorksheet.AddChart(AWidth, AHeight: Double; ARow, ACol: Cardinal; AOffsetX: Double = 0.0; AOffsetY: Double = 0.0): TsChart;
function TsWorkbook.AddChart(ASheet: TsBasicWorksheet; AWidth, AHeight: Double; ARow, ACol: Cardinal; AOffsetX: Double = 0.0; AOffsetY: Double = 0.0): TsChart;

AWidth and AHeight denote the dimensions of the chart, in millimeters, but note that these values are not very exact. ARow and ACol refer to the row/column indices of the cell which contains the top/left corner of the chart. And the optional AOffsetX and AOffetY specify a distance, in millimeters, by which the top/left chart corner is moved away from the top/left corner of this anchor cell.

Let's add the chart to our workbook:

  wchart := wbook.AddChart(wsheet, 150, 90, 0, 3, 10);
// or: wchart := wsheet.AddChart(150, 90, 0, 3, 10);

This means:

• The chart is in our worksheet (wsheet, of course...)
• The size of the chart is 150 x 90 mm.
• The top/left corner is in cell D1 and is moved to the right by 10 mm. There is not vertical offset from the cell corner.

Adding series

In the next step we can begin adding series to the chart. FPSpreadsheet supports a great number of series types:

• TsBarSeries: Draws the data as vertical or horizontal bars.
• TsLineSeries: Connects the data points by straight or smooth lines.
• TsAreaSeries: Similar to TsLineSeries, but the area between the series and the x axis is filled by a color, a pattern or a gradient..
• TsScatterSeries: Similar to TsLineSeries, but the x values can be placed arbitrarily (in the other series they are equidistant).
• TsBubbleSeries: Similar to TsBubbleSeries, data points are displayed as circles with varying size.
• TsPieSeries: Draws data as sectors of a circular shape
• TsRadarSeries: Spider-like, x values are interpreted as angle.
• TsFilledRadarSeries: like TsRadarSeries, but filled by a color
• TsStockSeries: Financial chart series type, displaying high/low/close values of shares.

Let's pick a TsBarSeries here. A series is created like any other object by calling its constructor Create. The constructor gets the chart as argument, and this automatically inserts the series into the chart. The values to be displayed on the y axis are taken from the cell range determined by the series' SetYRange method. As already noted, a bar chart uses equidistant x values, we must specify however which labels will be displayed at the x axis; this can be done by calling the series' SetLabelRange method. In case of a scatter or bubble series, however, we have to call SetXRange to define the x values of the data points. In any case, cell ranges are given by the row/column coordinates of the top/left and bottom/right corners. Note that ranges to be used for series can only be one column wide or one row high. Finally we should also specify the series title for the legend by calling the SetTitleAddr method; otherwise a generic title will be used by the Office applications.

Both series have their labels in column A, from cell A5 to A7. The first series has its y values in the range B5:B7, and the second series in range C5:C7. The axis title is in cell B4 for the first and in C4 for the second series:

var
  ser1, ser2: TsBarSeries
...
  // 1st bar series
  ser1 := TsBarSeries.Create(wChart);
  ser1.SetLabelRange(4, 0, 6, 0);   // A5:A7
  ser1.SetYRange(4, 1, 6, 1);       // B5:B7
  ser1.SetTitleAddr(3, 1);          // B4  

  // 2nd bar series
  ser2 := TsBarSeries.Create(wChart);
  ser2.SetLabelRange(4, 0, 6, 0);   // A5:A7
  ser2.SetYRange(4, 2, 6, 2);       // C5:C7
  ser2.SetTitleAddr(3, 2);          // C4

Colors of chart elements

The standard color type of FPSpreadsheet (TsColor) was extended to support transparency in charts; the new color type it TsChartColor which is the following record:

type
  TsChartColor = record
    Transparency: single;         // 0.0 (opaque) ... 1.0 (transparent)
    case Integer of
      0: (Red, Green, Blue, SystemColorIndex: Byte);
      1: (Color: TsColor);
  end;

To simplify usage, a function ChartColor() is available as follows:

function ChartColor(AColor: TsColor; ATransparency: Single = 0.0): TsChartColor;
begin
  Result.Color := AColor;
  Result.Transparency := ATransparency;
end;

Changing the series color

Now let's change the color of the 1st series bars to some brighter blue, and its border to black:

  ser1.Fill.Color := ChartColor($FFAA88);   // bright blue fill of the bars
  ser1.Line.Color := ChartColor(scBlack);   // black border of the bars

Drawing a gradient background

Spreadsheet charts provides various gradient types:

• axial
• elliptic
• linear
• radial
• rectangular
• square

Note that it is not guaranteed that every gradient type is supported by both XLSX and ODS files.

Let's fill the chart background by a linear gradient. For this purpose we must set the chart.Background.Fill.Style to cfsGradient and add the specific gradient with its parameters to the chart's Gradients list:

  wchart.Background.Style := cfsGradient;
  wChart.Background.Gradient := wchart.Gradients.AddLinearGradient('bkGr', ChartColor(scWhite), ChartColor($FFDDAA), 90.0);

• The first argument of the AddLinearGradient method is the name of the gradient for identification.
• The second argument is the color at which the gradient starts, the third argument the color at which it ends.
• The last argument is the drawing direction of the gradient: normally the gradient runs horizontally, i.e. the startcolor is at the left, and the endcolor is at the right. The angle is oriented in counter-clockwise direction, and this means that "white" is at the bottom and "light blue" is at the top of the chart.
• The function returns the index of the gradient in the Gradients list.

If you know that this specific gradient already exists you can find its index by calling Gradients.IndexOf('bkGr') and assign it to the Fill.Gradient property of another chart element for re-using the gradient.

Showing the chart legend

In order to display the chart legend set the Visible property of the Legend to true (this, however, is the default setting already). You can reposition the legend by applying one of the TsChartLegendPosition = (lpRight, lpTop, lpBottom, lpLeft) values to Legend.Position:

  wChart.Legend.Visible := true;
  wChart.Legend.Position := lpBottom;

Adding titles

The Title.Caption property of the chart defines the text to be shown above the chart as a title. You can change its Font property to emphasize it:

  wChart.Title.Caption := 'Sales Report';
  wChart.Title.Font.Size := 16;
  wChart.Title.Font.Style := [fssBold];

Likewise, there is an axis Title which can be drawn along the axis line:

  wChart.yAxis.Title.Caption := 'Sales (in millions of EUR)';

In total, the chart will look in Excel as follows: