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.
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.