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
editIn 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, fpsutilsto theusesclause of the project's main form - the standard for all FPSpreadsheet projects. Since we will create xlsx and ods files we also need thexlsxooxmlandfpsopendocumentunits in theusesclause. - Add a button and call a method
CreateChartfrom itsOnClickevent handler.CreateChartour 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
CreateChartwe first create aTsWorkbook(namedwbook) and add aTsWorksheet(namedwsheet) 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
editAll 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
editA 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
editIn 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 toTsLineSeries, but the area between the series and the x axis is filled by a color, a pattern or a gradient..TsScatterSeries: Similar toTsLineSeries, but the x values can be placed arbitrarily (in the other series they are equidistant).TsBubbleSeries: Similar toTsBubbleSeries, data points are displayed as circles with varying size.TsPieSeries: Draws data as sectors of a circular shapeTsRadarSeries: Spider-like, x values are interpreted as angle.TsFilledRadarSeries: likeTsRadarSeries, but filled by a colorTsStockSeries: 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
editBy 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;
Colordetermines 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 functionChartColor()is available which accepts theTsColorvalue, as well as, optionally, theTransparencysingle:
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;
Widthdenotes the width of the line, given in millimeters.StyleandPatternIndexidentify 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 theStyleenumerationTsChartLinePatternStyle = (clsSolid, clsNoLine, clsFineDot, clsDot, clsDash, clsDashDot, clsLongDash, clsLongDashDot, clsLongDashDotDot). Additionally, users can register their own special patterns by callingRegisterRawLinePattern()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
editIn 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;
Styleof typeTsChartFillStyle = (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.Colorspecifies the color of the solid fill-Gradient,PatternandImageare indices into chart-internal lists containing the corresponding items - see below.
Changing the series fill color
editAt 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
editThe 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
editSpreadsheet 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
AddLinearGradientmethod 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
Gradientslist which must be assigned to theFill.Gradientproperty.
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
editIn 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
editThe 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
editSo 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
TsWorkbookSourcecomponent on the form, set itsFileNameproperty 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
TAChartcomponent on the form. Position and size it as needed. - Drop a
TsWorkbookChartLinkon the form. Set itsWorkbookSourceandChartproperties to the components that you just had added. When the workbook contains several charts, enter its index inWorkbookChartIndex- 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
TsWorkbookTabControland - into it - aTsWorksheetGrid. 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
editSource code
editThe source code developed during this tutorial can be found in folder examples/visual/fpschart/fpschart_tutoral of the FPSpreadsheet installation.