Splitting datasets (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\94.Split Datasets and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/94.Split Datasets
Overview
It is a normal request when doing a report for printing, to be able to manipulate the page breaks so there are no "hanging" lines at the end or beginning of a page. Since Excel does not provide this functionality, it is impossible for us to do it. But, you can use the <#SPLIT> tag on the config sheet to for example fit 40 records on each page.
Concepts
How to use the SPLIT tag to create a master-detail relationship of a dataset with itself, where each detail has a fixed number of records.
The SPLIT tag is a little special in that it does not create a real dataset, but it filters the data on the fly instead. This has some restrictions, the most important being that the master dataset and the clhild must be in direct relationship one with the other. If MasterOrders and Orders are both master and detail, you can not have MasterOrders/Other/Orders ranges each one inside the other. You must have Other/MasterOrders/Orders or Orders/MasterOrders/Other. This restriction normally is harmless.
It is not shown on this demo, but note that you could use a parameter on the "record count" parameter of the split tag. For example: <#Split(table;<#splitcount>)> and dinamically change the split range on the application before running the report.
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, DB, ADODB;
type
TMainForm = class(TForm)
btnCancel: TButton;
btnGo: TButton;
SaveDialog: TSaveDialog;
Label1: TLabel;
ADOConnection: TADOConnection;
Customers: TADODataSet;
Employees: TADODataSet;
DsEmployees: TDataSource;
Orders: TADODataSet;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils;
{$R *.dfm}
function DBFile: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;
procedure TMainForm.FormCreate(Sender: TObject);
begin
ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;
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('Employees', Employees);
Report.AddTable('Orders', Orders);
Report.AddTable('Customers', Customers);
Report.Run(
TPath.Combine(GetDataPath, 'Split Datasets.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.