Advanced Reports from Lists (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\22b.Advanced Reports From Lists and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/22b.Advanced 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.
This demo shows some features not shown on the Reports From Lists example.
Concepts
How to do a master-detail report when the details are nested many levels inside the master. In this case, the class Country has a People class, and the People class has a list of Language objects. If People was a TList<> inside Country and you wanted to use that list, you would just define a __People__ band (this is shown in the Reports From Lists example). But as the TList<> is inside People which in turn is inside Country, you need to define a __People.Language__ band.
How to reference a table with dots using [square brackets]. If you write in a cell <#tablename.section.field> FlexCel will interpret this as table "tablename", field "section.field". The text up to the first dot is always the table, and the rest is the field. But sometimes you might want this to being interpreted as table "tablename.section", field "field". To do so, you need to write <#[tablename.section].field>. In this particular case, we have a table "People.Language" which we defined in the previous point. If we wrote in cell B1: "<#people.language.speakers.percent> FlexCel would interpret this is the table "people", not "people.language" which is what we need. To make FlexCel understand that we want a table "people.language" we use <#[people.language].speakers.percent>
Files
DataModel.pas
unit DataModel;
interface
uses Generics.Collections;
type
TArea = class
strict private
FWater: Int32;
FLand: Int32;
function Get_Total: Int32;
public
constructor Create(const aWater: Int32; const aLand: Int32);
property Total: Int32 read Get_Total;
property Water: Int32 read FWater;
property Land: Int32 read FLand;
end;
TGeography = class
strict private
FArea: TArea;
public
constructor Create(const aArea: TArea);
destructor Destroy; override;
property Area: TArea read FArea;
end;
TLanguageSpeakers = class
strict private
FAbsoluteNumber: Int32;
FPercent: double;
public
constructor Create(const aAbsoluteNumber: Int32; const aPercent: double);
property AbsoluteNumber: Int32 read FAbsoluteNumber;
property Percent: double read FPercent;
end;
TLanguageName = class
strict private
FShortName: string;
FLongName: string;
public
constructor Create(const aShortName: string; const aLongName: string);
property ShortName: string read FShortName;
property LongName: string read FLongName;
end;
TLanguage = class
strict private
FName: TLanguageName;
FSpeakers: TLanguageSpeakers;
public
constructor Create(const aName: TLanguageName; const aSpeakers: TLanguageSpeakers);
destructor Destroy; override;
property Name: TLanguageName read FName;
property Speakers: TLanguageSpeakers read FSpeakers;
end;
TPeople = class
strict private
FPopulation: Int32;
FLanguage: TObjectList<TLanguage>;
public
constructor Create(const aPopulation: Int32);
destructor Destroy; override;
property Population: Int32 read FPopulation;
property Language: TObjectList<TLanguage> read FLanguage;
end;
TCountry = class
strict private
FName: string;
FPeople: TPeople;
FGeography: TGeography;
public
constructor Create(const aName: string; const aPeople: TPeople; const aGeography: TGeography);
destructor Destroy; override;
property Name: string read FName;
property People: TPeople read FPeople;
property Geography: TGeography read FGeography;
end;
implementation
{ TArea }
constructor TArea.Create(const aWater, aLand: Int32);
begin
inherited Create;
FWater := aWater;
FLand := aLand;
end;
function TArea.Get_Total: Int32;
begin
Result := Water + Land;
end;
{ TGeography }
constructor TGeography.Create(const aArea: TArea);
begin
inherited Create;
FArea := aArea;
end;
destructor TGeography.Destroy;
begin
FArea.Free;
inherited;
end;
{ TLanguageSpeakers }
constructor TLanguageSpeakers.Create(const aAbsoluteNumber: Int32;
const aPercent: double);
begin
inherited Create;
FAbsoluteNumber := aabsoluteNumber;
FPercent := aPercent / 100.0;
end;
{ TLanguageName }
constructor TLanguageName.Create(const aShortName, aLongName: string);
begin
inherited Create;
FShortName := aShortName;
FLongName := aLongName;
end;
{ TLanguage }
constructor TLanguage.Create(const aName: TLanguageName;
const aSpeakers: TLanguageSpeakers);
begin
inherited Create;
FName := aName;
FSpeakers := aSpeakers;
end;
destructor TLanguage.Destroy;
begin
FName.Free;
FSpeakers.Free;
inherited;
end;
{ TPeople }
constructor TPeople.Create(const aPopulation: Int32);
begin
inherited Create;
FPopulation := aPopulation;
FLanguage := TObjectList<TLanguage>.Create;
end;
destructor TPeople.Destroy;
begin
FLanguage.Free;
inherited;
end;
{ TCountry }
constructor TCountry.Create(const aName: string; const aPeople: TPeople;
const aGeography: TGeography);
begin
FName := aName;
FPeople := aPeople;
FGeography := aGeography;
end;
destructor TCountry.Destroy;
begin
FPeople.Free;
FGeography.Free;
inherited;
end;
end.
UDataReader.pas
unit UDataReader;
interface
uses FlexCel.Report, Generics.Collections, DataModel;
procedure LoadTables(const Report: TFlexCelReport);
implementation
procedure LoadTables(const Report: TFlexCelReport);
var
Countries: TObjectList<TCountry>;
country: TCountry;
begin
Countries := TObjectList<TCountry>.Create;
Countries.Add(TCountry.Create('China', TPeople.Create($5288AD5A), TGeography.Create(TArea.Create($420D6, $8E4F4A))));
country := Countries[Countries.Count - 1];
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Md', 'Mandarin'), TLanguageSpeakers.Create(0, 66.2)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Yue', 'Yue'), TLanguageSpeakers.Create(0, 4.9)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Wu', 'Wu'), TLanguageSpeakers.Create(0, 6.1)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mb', 'Minbei'), TLanguageSpeakers.Create(0, 6.2)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mn', 'Minnan'), TLanguageSpeakers.Create(0, 5.2)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Xi', 'Xiang'), TLanguageSpeakers.Create(0, 3)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Gan', 'Gan'), TLanguageSpeakers.Create(0, 4)));
Countries.Add(TCountry.Create('India', TPeople.Create($4D4C1DFA), TGeography.Create(TArea.Create($4CAD6, $2D5E09))));
country := Countries[Countries.Count - 1];
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Hi', 'Hindi'), TLanguageSpeakers.Create(0, 43.6)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Bg', 'Bengali'), TLanguageSpeakers.Create(0, 8)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ma', 'Marath'), TLanguageSpeakers.Create(0, 6.9)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Te', 'Telugu'), TLanguageSpeakers.Create(0, 6.7)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ta', 'Tamil'), TLanguageSpeakers.Create(0, 5.7)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Gu', 'Gujarati'), TLanguageSpeakers.Create(0, 4.6)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ur', 'Urdu'), TLanguageSpeakers.Create(0, 4.2)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ka', 'Kannada'), TLanguageSpeakers.Create(0, 3.6)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Od', 'Odia'), TLanguageSpeakers.Create(0, 3.1)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ma', 'Malayalam'), TLanguageSpeakers.Create(0, 2.9)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Pu', 'Punjabi'), TLanguageSpeakers.Create(0, 2.7)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('As', 'Assamese'), TLanguageSpeakers.Create(0, 1.3)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mi', 'Maithili'), TLanguageSpeakers.Create(0, 1.1)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('O', 'Other'), TLanguageSpeakers.Create(0, 5.6)));
Countries.Add(TCountry.Create('United States', TPeople.Create($13A00E11), TGeography.Create(TArea.Create($A7764, $8B94C9))));
country := Countries[Countries.Count - 1];
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('En', 'English'), TLanguageSpeakers.Create(0, 78.2)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Sp', 'Spanish'), TLanguageSpeakers.Create(0, 13.4)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ch', 'Chinese'), TLanguageSpeakers.Create(0, 1.1)));
country.People.Language.Add(TLanguage.Create(TLanguageName.Create('O', 'Other'), TLanguageSpeakers.Create(0, 7.3)));
report.AddTable<TCountry>('country', Countries, TDisposeMode.DisposeAfterRun);
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;
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils, UDataReader;
{$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;
begin
if not SaveDialog.Execute then exit;
Report := TFlexCelReport.Create(true);
try
LoadTables(Report);
Report.Run(
TPath.Combine(GetDataPath, 'Advanced Reports From Lists.template.xlsx'),
SaveDialog.FileName);
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;
end.