User defined formats (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\95b.User Defined Formats and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/95b.User Defined Formats
Overview
User defined formats are a way to format cells in a report with code. Normally you will want to use them if the data to format the cells is in the database and it is not known at compile time, so you can't use conditional formatting or formats defined in the config sheet.
Concepts
How to define and use a user defined format.
You can pass parameters to the user defined format by writing them after the format name. In this example we show passing one parameter (in user format "ZipCode") and two parameters (in user format "ShipFormat").
If you don't want to modify the format then return a null TFlxFormat and a null TFlxApplyFormat. We do this in the ZipCode user format, when the zip code is not numeric. If you wanted to return a format but not apply, you could set only the TFlxApplyFormat to null and the full TFlxFormat would be applied.
As you can see, the colors in this demo don't make much sense. There is a reason for that: trying to find an example of stuff that can only be made with user defined formats is not easy. If you wanted for example to mark red the cells where a value is > 100, then you would just use an Excel conditional format instead. The recommended order of preference would be:
If you can do it with Excel conditional formats, then use that.
If you can't use conditional formats then use formats defined in the config sheet
For more complex formats where you can't use any of the above (like for example if the color of the cell is stored in the database, or the rules are too complex to code them in a conditional format or the config sheet), then use user defined formats.
You can use user defined formats to format rows, columns or cells. As format cell has more priority than format row, in this example there are cells which override the row format.
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, DemoOrders, UZipCodeImp, UShipFormatImp;
{$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('Orders', DemoTables.Orders);
Report.SetUserFormat('ZipCode', TZipCodeImp.Create);
Report.SetUserFormat('ShipFormat', TShipFormatImp.Create);
Report.Run(
TPath.Combine(GetDataPath, 'User Defined Formats.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.
UShipFormatImp.pas
unit UShipFormatImp;
interface
uses SysUtils, StrUtils, FlexCel.Core, FlexCel.Report;
type
TShipFormatImp = class (TFlexCelUserFormat)
public
function Evaluate(const workbook: TExcelFile; const rangeToFormat: TXlsCellRange; const parameters: TFormulaValueArray): TFlxPartialFormat; override;
end;
implementation
{ TShipFormatImp }
function TShipFormatImp.Evaluate(const workbook: TExcelFile; const rangeToFormat: TXlsCellRange; const parameters: TFormulaValueArray): TFlxPartialFormat;
var
len: Int32;
country: String;
color: Int32;
fmt: TFlxFormat;
apply: TFlxApplyFormat;
begin
//Again, this example is not supposed to make sense, only to show how you can code a complex rule.
//This method will format the rows with a color that depends in the length of the first parameter,
//and if the second parameter starts with "B" it will make the text red.
if (Length(parameters) <> 2) then
raise Exception.Create('Bad parameter count in call to ShipFormat() user-defined format');
len := Length(parameters[0].ToString);
country := parameters[1].ToString;
color := $FFFFFF - (len * 100);
fmt := workbook.GetDefaultFormat;
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := TExcelColor.FromArgb(color);
fmt.FillPattern.BgColor := TExcelColor.Automatic;
apply := TFlxApplyFormat.Create;
apply.FillPattern.SetAllMembers(true);
if StartsText('B', country) then
begin
fmt.Font.Color := Colors.OrangeRed;
apply.Font.Color := true;
end;
Result := TFlxPartialFormat.Create(fmt, apply, false);
end;
end.
UZipCodeImp.pas
unit UZipCodeImp;
interface
uses SysUtils, FlexCel.Core, FlexCel.Report;
type
TZipCodeImp = class (TFlexCelUserFormat)
public
function Evaluate(const workbook: TExcelFile; const rangeToFormat: TXlsCellRange; const parameters: TFormulaValueArray): TFlxPartialFormat; override;
end;
implementation
{ TZipCodeImp }
function TZipCodeImp.Evaluate(const workbook: TExcelFile; const rangeToFormat: TXlsCellRange; const parameters: TFormulaValueArray): TFlxPartialFormat;
var
color: double;
fmt: TFlxFormat;
apply: TFlxApplyFormat;
begin
if (Length(parameters) <> 1) then
raise Exception.Create('Bad parameter count in call to ZipCode() user-defined format');
//If the zip code is not valid, don't modify the format.
if parameters[0].IsEmpty or not (parameters[0].TryToDouble(color)) then
exit(TFlxPartialFormat.Create(TFlxFormat.Null, TFlxApplyFormat.Create, false));
//This code is not supposed to make sense. We will convert the zip code to a color based in the numeric value.
fmt := workbook.GetDefaultFormat;
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := TExcelColor.FromArgb(Round(color));
fmt.FillPattern.BgColor := TExcelColor.Automatic;
fmt.Font.Color := TExcelColor.FromArgb(not Round(color));
apply := TFlxApplyFormat.Create;
apply.FillPattern.SetAllMembers(true);
apply.Font.Color := true;
Result := TFlxPartialFormat.Create(fmt, apply, false);
end;
end.