Range reports (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\20.Range Reports and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/20.Range Reports
Overview
On the GettingStarted demo we saw how to write tags on a template, and have FlexCel replace them. Now we are going to replace whole ranges with values from a database.
Concepts
To be able to use a database, you have to define a named range on the template, to tell FlexCel which rows or columns to use. Also, you need to add the table on the code.
You can arrange the ranges in many different ways. Take a look at all the sheets, they have different reports from the Northwind demo recreated with FlexCel.
There are four kinds of named ranges:
"__" Ranges: They are the most common, and the ones you should probably use. The table will be inserted horizontally, and the full rows will be moved down.
"_" Ranges: They work as the "__" ranges, but only the range and not the full row is moved down. Row properties (like row height) are not copied because not the full row is copied.
"II_" Ranges: Use them to insert the table by columns, not by rows. Full columns will be inserted.
"I_" Ranges: Tables will be inserted by columns, but only the selected range will move. Column properties (like column width) are not copied.
Ranges can be defined inside ranges, to create Master-Detail reports. On the dataset schema on your application, you have to define the corresponding relations for this to work.
It might happen that you need to use the same datatable, lets say __Categories__ on more than one sheet. You can't define more than one __Categories__ named range, but you can define an alias on the Config sheet, (for example Categories2=Categories) and then use __Categories2__ on the second sheet. Another alternative not shown in this example would be to use Alias Bands.
We used Intelligent Page Breaks in the first sheet so categories are kept in their own pages when printing.
Files
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,
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, DemoData;
{$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
Report.AddTable(DemoTables);
Report.SetValue('Date', Now);
Report.Run(
TPath.Combine(GetDataPath, 'Range Reports.template.xls'),
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.