Manual formulas (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\48.Manual Formulas and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​20.​Reports/​48.​Manual Formulas

Overview

Here we will create a small fully functional Minesweeper in Excel, and without any macros.

Most of the time, you will just want to write the formulas directly on the report, and let FlexCel change them when inserting rows. But sometimes, you might want to create a formula using flexcel tags. You can do it by using <#formula> and <#ref> tags, but don't do it unless you really need to.

Concepts

• How to replace <#tags> inside a formula.

• When you use the <#Formula> tag, formulas are just strings on the template. So we could define inside a cell: "<#formula>=b3", but then if we want to change the template, and insert a row below row 1, the string will still be "<#formula>=b3". That is why we need to use a <#Ref> tag. For example <#formula>=<#ref(0;-1)> on cell c3 will point to b3, but if you insert a row below row 1, it will point to b4 as it should be.

• There is never a real need to use the <#formula> tag, you can always workaround it. On this particular case we used it so we could change the font, (since it is not possible to change the font on a conditional format), but we could have also used the approach we used on the Expression Parameters demo. In fact, it might have been better that way (by adding the <#format range> tag in the data). But this demo is to show how to use this feature, it is up to you to decide if it is worth or not.

• How to use conditional formatting, locked cells and data validation to make a nice application.

Files

DataModel.pas

unit DataModel;

interface
type
  //Delphi doesn't provide RTTI in record properties:
  //http://qc.embarcadero.com/wc/qcmain.aspx?d=78110
  //
  //As we want to use records instead of classes for simplicity,
  //we can't use properties. We'll use public fields instead.

  TMine = record
  public
    Value: integer;
  end;

  TColOfMines = record
  public
    datacol: TArray<TMine>;

    procedure CreateCol(const count: integer);
  end;

  TMines = TArray<TColOfMines>;


implementation

{ TColOfMines }

procedure TColOfMines.CreateCol(const count: integer);
begin
  SetLength(DataCol, count);
end;

end.

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, Generics.Collections,
  Controls, Forms, Dialogs, StdCtrls;

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;
    procedure SetupMines(const MinesReport: TFlexCelReport);
    procedure AfterGenerateWorkbook(const sender: TObject;
      const e: TGenerateEventArgs);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, DataModel;

{$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.SetupMines(const MinesReport: TFlexCelReport);
var
  Mines: TMines;
  i: Integer;
  k: Integer;
  M: TDictionary<integer, boolean>;
begin
  Randomize;
  SetLength(Mines, 9);

  M := TDictionary<integer, boolean>.Create;
  try
    //We'll create 10 mines
    while M.Count < 10 do
    begin
      M.AddOrSetValue(Random(9 * 9 - 1), true);
    end;


    for i := 0 to High(Mines) do
    begin
      Mines[i].CreateCol(9);
      for k := 0 to High(Mines[i].datacol) do if M.ContainsKey(i * 9 + k) then Mines[i].datacol[k].Value := 1;

    end;
  finally
    M.Free;
  end;

  MinesReport.ClearTables;
  MinesReport.AddTable<TColOfMines>('datarow', Mines);

end;

procedure TMainForm.AfterGenerateWorkbook(const sender: TObject; const e: TGenerateEventArgs);
var
  r, c: integer;
begin
  //do some "pretty" up for the final user.
  //we could do this directly on the template, but doing it here allows us to keep the template unprotected and easier to modify.

  e.DataFile.ActiveSheet := 2;
  e.DataFile.SheetVisible := TXlsSheetVisible.Hidden;
  e.DataFile.ActiveSheet := 1;
  e.DataFile.Protection.SetSheetProtection('', TSheetProtectionOptions.Create(true));
  for r := 20 to TFlxConsts.Max_Rows97_2003 do e.DataFile.SetRowHidden(r, true);
  for c := 12 to TFlxConsts.Max_Columns97_2003 do e.DataFile.SetColHidden(c, true);

end;

procedure TMainForm.RunReport;
var
  Report: TFlexCelReport;
begin
  if not SaveDialog.Execute then exit;

  Report := TFlexCelReport.Create(true);
  try
    SetupMines(Report);
    Report.AfterGenerateWorkbook := AfterGenerateWorkbook;
    Report.Run(
      TPath.Combine(GetDataPath, 'Manual Formulas.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.