FPSpreadsheet: Chart Tutorial

One of the additions to FPSpreadsheet version 2.0.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 the TAChart library.

Preparing data

edit

In this chart we will show sales results of two products of an imaginary company 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

edit

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

edit

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 no vertical offset from the cell corner (default parameter, omitted).

Adding series

edit

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

Styling lines in the chart

edit

By default, the chart is surrounded by a rectangular frame, but sometimes this is not wanted. So, let's remove it. And we also might want to give bars of the series a black outline border.

These items, like any other lines in the FPSpreadsheet charts, are implemented as instances of TsChartLine:

type
  TsChartLine = class
  ...
  public
    Color: TsChartColor;    // in hex: $00bbggrr, r=red, g=green, b=blue; contains Transparency
    Width: Single;          // mm
    PatternIndex: Integer;  // Index into global RawLinePatternList
    property Style: TsChartLinePatternStyle read GetStyle write SetStyle;
  end;
  • Color determines the color of the line. It is an extension of the standard FPSpreadsheet color type, TsColor, in order to support transparency which essentially is a floating point value between 0 (opaque) and 1 (fully transparent = inivisible). To simplify usage, a function ChartColor() is available which accepts the TsColor value, as well as, optionally, the Transparency single:
type
  TsChartColor = record
    Transparency: TsChartTransparency;   // = single in the range 0.0 (opaque) ... 1.0 (transparent)
    case Integer of
      0: (Red, Green, Blue, SystemColorIndex: Byte);
      1: (Color: TsColor);
  end;

function ChartColor(AColor: TsColor; ATransparency: Single = 0.0): TsChartColor;
begin
  Result.Color := AColor;
  Result.Transparency := ATransparency;
end;
  • Width denotes the width of the line, given in millimeters.
  • Style and PatternIndex identify the line pattern to be used for drawing. Both essentially are indices into a global pattern array implemented in unit fpsPatterns. The first patterns are pre-defined and accessibile via the Style enumeration TsChartLinePatternStyle = (clsSolid, clsNoLine, clsFineDot, clsDot, clsDash, clsDashDot, clsLongDash, clsLongDashDot, clsLongDashDotDot). Additionally, users can register their own special patterns by calling RegisterRawLinePattern() where they have to specify the length and count of stroke elements, as well as their distance, given in either millimeters (AUnit = cluMillimeters) or as precentage of the line width (AUnit = cluPercentage):
function RegisterRawLinePattern(AName: String; AElementLength: Single; ADistanceLength: Single; AUnit: TsChartLengthUnit): Integer;
function RegisterRawLinePattern(AName: String; AElement1Length: Single; AElement1Count: Integer; AElement2Length: Single; AElement2Count: Integer; ADistanceLength: Single; AUnit: TsChartLengthUnit): Integer;

OK. Now we have everything to remove the chart's border and to draw the series bar borders in black:

  wChart.Border.Style := clsNoLine; 
  ser1.Line.Color := ChartColor(scBlack);  // This automatically selects the solid "pattern": wChart.Border.Style := clsSolid;
  ser2.Line.Color := ChartColor(scBlack);

Filling chart elements

edit

In the next chapter we want to show how areas can be filled by color or patterns. Class TsChartFill gives access to all related properties: fill style, fill color, fill pattern, pattern parameters, gradient parameters etc.

type
  TsChartFill = class
  public
    Style: TsChartFillStyle;
    Color: TsChartColor;
    Gradient: Integer;     // Index into chart's Gradients list
    Pattern: Integer;      // Index into chart's FillPattern list
    Image: Integer;        // Index into chart's Images list
    [...]
  end;
  • Style of type TsChartFillStyle = (cfsNoFill, cfsSolid, cfsGradient, cfsHatched, cfsSolidHatched, cfsImage) classifies to the type of the fill: no fill at all, solid fill by uniform color, fill by a gradient, fill by a pattern or by an image.
  • Color specifies the color of the solid fill-
  • Gradient, Pattern and Image are indices into chart-internal lists containing the corresponding items - see below.

Changing the series fill color

edit

At first we change the color of the 1st series bars to some brighter blue, and its border to black. The related TsChartFill property is named Fill here:

  ser1.Fill.Color := ChartColor($FFAA88);   // bright blue fill of the bars
  ser1.Fill.Style := cfsSolidFill;          // not absolutely necessary, just for better clarity

Changing the series fill pattern

edit

The 2nd bar series is supposed to be hatched by a pattern of diagonal-up strokes. The library provides a lot of pre-defined fill patterns which can be identified by the TsChartFillPatternStyle enumeration (declared in unit fpsTypes, and implemented in unit fpsPatterns):

type
  TsChartFillPatternStyle = (
    fpsGray05, fpsGray10, fpsGray20, fpsGray25, fpsGray30, fpsGray40,
    fpsGray50, fpsGray60, fpsGray70, fpsGray75, fpsGray80, fpsGray90,
    fpsHorThick, fpsVertThick, fpsDiagUpThick, fpsDiagDownThick, fpsHatchThick, fpsCrossThick,
    fpsHorThin, fpsVertThin, fpsDiagUpThin, fpsDiagDownThin, fpsHatchThin, fpsCrossThin,
    fpsHorNarrow, fpsVertNarrow, fpsDiagUpNarrow, fpsDiagDownNarrow, fpsHatchNarrow, fpsCrossNarrow,
    fpsHorDash, fpsVertDash, fpsDiagUpDash, fpsDiagDownDash, fpsHatchDot, fpsCrossDot,
    fpsBrickDiag, fpsBrickHor, fpsCheckerBoardLarge, fpsCheckerBoardSmall, fpsConfettiLarge, fpsConfettiSmall,
    fpsDiamond, fpsDivot, fpsPlaid, fpsShingle, fpsSphere, fpsTrellis, fpsWave, fpsWeave, fpsZigZag
  );

There are several "diagonal-up" patterns, a nice one is fpsDiagUpNarrow. These patterns do not carry any color, they are just black and white. In order to fill an area, we must add the combination of fill pattern style, foreground and background color to the chart's FillPatterns list (Chart.FillPatterns.AddPattern(name, style, forgroundColor, backgroundColor). This process returns the index of this colored pattern in FillPatterns which is needed as the Pattern index of the TsChartFill class. If this index is already known from a previous fill, of course, you can reuse it instead of calling AddPattern.

  ser2.Fill.Pattern := wchart.FillPatterns.AddPattern('white-on-blue diag-up', fpsDiagUpNarrow, ChartColor(scWhite), ChartColor(scBlue));
    // Drop the last argument if you need a pattern without background
  ser2.Fill.Style := cfsPattern;                // Selector for the pattern fill
  ser2.Line.Color := ChartColor(scBlack);

Alternatively you can also fill the area by any bitmap loaded from file: Add the image file to the workbook's embedded object list (workbook.AddEmbeddedObj(filename)) and then use the returned index in the method AddImage of the chart's Images list:

  ser2.Fill.Image := wchart.Images.AddImage('FillImg', wbook.AddEmbeddedObj('bgimage.jpg'));
  ser2.Fill.Style := cfsImage;                  // Selector for the image fill
  ser2.Line.Color := ChartColor(scBlack);

Drawing a gradient background

edit

Spreadsheet charts provide 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. The related property is accessible as - well - Background.Fill. So, we must set the chart's Background.Fill.Style to cfsGradient, add the specific gradient with its parameters to the chart's Gradients list and put the returned index into the Background.Gradient property:

  wchart.Background.Style := cfsGradient;     // Selector for the gradient fill
  wChart.Background.Gradient := wchart.Gradients.AddLinearGradient('bkGr', ChartColor(scWhite), ChartColor($FFDDAA), 90);
  • The first argument of the AddLinearGradient method is the name of the gradient needed 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 measured in degress and oriented in counter-clockwise direction. The value "90" 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 chart's Gradients list which must be assigned to the Fill.Gradient property.

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

edit

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 = (legRight, legTop, legBottom, legLeft) values to Legend.Position:

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

Adding titles

edit

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:

Displaying the chart in a Lazarus form

edit

So far, a spreadsheet chart cannot be displayed embedded in a worksheet grid, like in the Office applications. But it is possible to use a standard TAChart component to render it. Cooperation between TAChart and workbook/worksheet is done by the TsWorkbookSource and TsWorkbookChartLink components: The TsWorkbookSource gives access to the workbook, its worksheets and the contained data, and the TsWorkbookChartlink passes the chart-related data and properties on to a TAChart instance and its subcomponents.

  • Drop a TsWorkbookSource component on the form, set its FileName property to point to the file containing the chart. Make sure that the spreadsheet reader unit for the file format is listed in the uses clause, e.g. xlsxooxml for an xlsx file.
  • Drop a TAChart component on the form. Position and size it as needed.
  • Drop a TsWorkbookChartLink on the form. Set its WorkbookSource and Chart properties to the components that you just had added. When the workbook contains several charts, enter its index in WorkbookChartIndex - charts are numbered internally from the first chart per worksheet to the last and from the first worksheet to the last worksheet.
  • Voila - here you see the chart!
  • For completeness, you might also want to drop a TsWorkbookTabControl and - into it - a TsWorksheetGrid. This way you can easily switch between the indidvidual worksheets and see their contents in a grid. See FPSpreadsheet_tutorial:_Writing_a_mini_spreadsheet_application of further instructions how to set up a visual spreadsheet application.

The overall result can be seen in the screenshot at the top of this wiki page.

We should note that the chart displayed this way is interactive: If you change cell content which is needed by the chart, the change will be updated immediately in the chart. And you may notice that there are some differences to the Office application charts, here and there. Gradients, for example, are not supported, and some specific line pattern are not either. But the same may happen also within Excel (or Calc) when an ods (or xlsx) file is imported. So, FSpreadsheet is in good company here...

Appendix

edit

Source code

edit

The source code developed during this tutorial can be found in folder examples/visual/fpschart/fpschart_tutoral of the FPSpreadsheet installation.

edit