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