Using List<T> as datasource (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\22.Reports From Lists and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/22.Reports From Lists
Overview
Most of the demos here use datasets as datasources. This is just for convenience, so we share the same data layer in all demos, and also because the focus is in the Excel templates, not so much in the data layer. But you can use any TList<T> and TArray<T> as a datasource in a FlexCel report, and this is what we will show here.
Concepts
How to run a report from a TList<> of objects.
When using TList<T> and TArray<T> as datasource, you can use any public property of T in the report, any public field and any public function which has no parameters. So if type T has a public property "LastName", you can access it with <#dt.LastName>.
Master detail with implicit relationships. When a public property of a collection of objects is other collection of objects, the property is considered as a detail of the main collection. In this example "Elements" is a property of "Categories", and so there is an implicit relationship between them.
Master detail with explicit relationships. While when using TLists you will normally use implicit relationships, you can also relate any two collections of objects with a relationship, as you could with datasets. In this example, "ElementName" is explicitly related to "Elements" by calling TFlexCelReport.AddRelationship.
Files
DataModel.pas
unit DataModel;
interface
uses Generics.Collections;
type
//We can use classes or records as data sources
TElements = class
private
FElementId: integer;
FName: string;
public
constructor Create(const aElementId: integer; const aName: string);
//We will relate this property with the table of colors by adding a relationship.
property ElementId: integer read FElementId;
property Name: string read FName;
end;
TCategories = class
private
FName: string;
FElements: TObjectList<TElements>;
public
constructor Create(const aName: string);
destructor Destroy; override;
//In classes we can use public properties, fields and functions without parameters.
//In records we can't use public properties. In records we can use fields and functions only in XE2 or newer.
property Name: string read FName;
//Elements is in master-detail relationship with this element, even when we don't explicitly add a relationship.
//Relationship is inferred because Elements is a property of this object
property Elements: TObjectList<TElements> read FElements;
end;
//Here we will show a record instead of a class. A record can be used easier in Arrays
//since it doesn't need free. But due to limitations in Delphi RTTI, we can only access the
//fields of the method or functions (functions in Delphi XE2 or newer). Not properties.
TElementName = record
public
ElementId: integer;
Name: string;
constructor Create(const aElementId: integer; const aName: string);
end;
implementation
{ TCategories }
constructor TCategories.Create(const aName: string);
begin
FName := aName;
FElements := TObjectList<TElements>.Create;
end;
destructor TCategories.Destroy;
begin
Elements.Free;
inherited;
end;
{ TElementName }
constructor TElementName.Create(const aElementId: integer; const aName: string);
begin
ElementId := aElementId;
Name := aName;
end;
{ TElements }
constructor TElements.Create(const aElementId: integer; const aName: string);
begin
FElementId := aElementId;
FName := aName;
end;
end.
UMainForm.pas
unit UMainForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics,
FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Report, FlexCel.Render,
{$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
ShellApi, Generics.Collections, DataModel,
Controls, Forms, Dialogs, StdCtrls, ExtCtrls;
type
TMainForm = class(TForm)
btnCancel: TButton;
btnGo: TButton;
SaveDialog: TSaveDialog;
Label1: TLabel;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
procedure LoadCategories(const Categories: TObjectList<TCategories>);
function GetElementNames: TArray<TElementName>;
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils;
{$R *.dfm}
procedure TMainForm.btnCancelClick(Sender: TObject);
begin
Close;
end;
procedure TMainForm.btnGoClick(Sender: TObject);
begin
RunReport;
end;
function TMainForm.GetDataPath: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..');
end;
procedure TMainForm.RunReport;
var
Report: TFlexCelReport;
Categories: TObjectList<TCategories>;
begin
if not SaveDialog.Execute then exit;
Report := TFlexCelReport.Create(true);
try
Categories := TObjectList<TCategories>.Create;
try
LoadCategories(Categories);
Report.AddTable<TCategories>('Categories', Categories, TDisposeMode.DoNotDispose);
//We don't need to call AddTable for elements since it is already added when we add Categories.
Report.AddTable<TElementName>('ElementName', GetElementNames);
//ElementName doesn't have an intrinsic relationship with categories, so we will have to manually add a relationship.
//Non intrinsic relationships should be rare, but we do it here to show how it can be done.
Report.AddRelationship('Elements', 'ElementName', 'ElementID', 'ElementID');
Report.Run(
TPath.Combine(GetDataPath, 'Reports From Lists.template.xls'),
SaveDialog.FileName);
finally
Categories.Free;
end;
finally
Report.Free;
end;
if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
end;
end;
procedure TMainForm.LoadCategories(const Categories: TObjectList<TCategories>);
begin
Categories.Add(TCategories.Create('Animals'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(1, 'Penguin'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(2, 'Cat'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(3, 'Unicorn'));
Categories.Add(TCategories.Create('Flowers'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(4, 'Daisy'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(5, 'Rose'));
Categories[Categories.Count - 1].Elements.Add(TElements.Create(6, 'Orchid'));
end;
function TMainForm.GetElementNames: TArray<TElementName>;
begin
SetLength(Result, 7);
Result[0] := TElementName.Create(1, 'Linus');
Result[1] := TElementName.Create(1, 'Gerard');
Result[2] := TElementName.Create(2, 'Rover');
Result[3] := TElementName.Create(3, 'Mike');
Result[4] := TElementName.Create(5, 'Rosalyn');
Result[5] := TElementName.Create(5, 'Monica');
Result[6] := TElementName.Create(6, 'Lisa');
end;
end.