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:

    1. If you can do it with Excel conditional formats, then use that.

    2. If you can't use conditional formats then use formats defined in the config sheet

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