Encryption and subtotals (Delphi)

Note

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

Overview

Two unrelated concepts are shown here. First, how to protect and encrypt a report. Second a frequently asked question: how to make a grand total out of sub totals.

Concepts

  • There are 4 ways to protect data in Excel:

    1. A password to open. This is the only way that will actually encrypt the file. All other methods just will add a record telling Excel not to modify the file. In Excel, this option is on ->Tools->Options... Security tab.

    2. A password to modify. Also on ->Tools->Options->Security tab, this password will allow you to open the file, but not to save it. The file will not be encrypted, and you can always use Save As to save the file.

    3. Protecting the workbook. This will cause a pseudo-encryption of the file, but the password used to encrypt will be always the same, no matter what password you enter. On Excel, this option is at ->Tools->Protection->Protect Workbook.

    4. Protecting the sheet. This option will protect the cells, objects, etc on a particular sheet. ->Tools->Protection->Protect Sheet.

  • When the file is encrypted (using a password to open) FlexCel supports four different encryption modes: Excel 95, Standard Excel97/2000 encryption, Excel 2007 xlsx encryption and Excel 2010 "agile" xlsx encryption.

  • To open an encrypted file, you can set the OpenPassword property or the OnPassword event. Use the OnPassword event when you want to interactively ask for a password *if* the file is encrypted.

  • Calculating a grand total. This is not related with encryption, but we used this example to show how it can be done. It is not really 100% FlexCel related either, but it is a frequently asked question. Imagine you have a master-detail report, with subtotals, and you want to calculate the sum of subtotals. You could do this on three ways:

    1. You could manually add a formula like "=Sum(A50, A60, A63...)" after the report is generated to calculate the sum of sub totals. Even when this sound like the natural approach, it is highly not recommended. Besides the effort needed to dynamically calculate the formula, formulas on Excel have a maximum of characters and you could end up on a formula too long message. Also, the sheet will become slow to calculate, and the formulas too complicated to follow. And you would be adding a lot of code to the report, that will not let the final user change the cells on the template.

    2. You can do it all on the template. By using different columns to hold different values and then merging the cells and hiding a column, you can get it to look as if it were done with way 1. But the formulas are much simpler here, and you do not need code to adapt them. This way is what we show here.

    3. There is a third option using the SubTotal Excel function. The SubTotal Excel function is unique in that it does not take other SubTotal() functions in account when doing its job. So you can have multiple SubTotal() functions in the same column, and they will not interfere with each other.

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;
    OpenPassTemplate: TLabeledEdit;
    Panel1: TPanel;
    Panel2: TPanel;
    Label2: TLabel;
    OpenPassGenerated: TLabeledEdit;
    ModifyPassGenerated: TLabeledEdit;
    ProtectWorkbookPass: TLabeledEdit;
    ProtectSheetPass: TLabeledEdit;
    ProtectWorkbook: TCheckBox;
    ProtectSheet: TCheckBox;
    ReservingUser: TLabeledEdit;
    RecommendReadOnly: TCheckBox;
    procedure btnCancelClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    procedure Report_AfterGenerateSheet(const Sender: TObject;
      const e: TGenerateEventArgs);
    procedure Report_AfterGenerateWorkbook(const Sender: TObject;
      const e: TGenerateEventArgs);
    procedure Report_BeforeReadTemplate(const Sender: TObject;
      const e: TGenerateEventArgs);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, DemoProducts;

{$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.BeforeReadTemplate := Report_BeforeReadTemplate;
    Report.AfterGenerateSheet := Report_AfterGenerateSheet;
    Report.AfterGenerateWorkbook := Report_AfterGenerateWorkbook;

    Report.AddTable('Products', DemoTables.Products);
    Report.AddTable('Order Details', DemoTables.OrderDetails);
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'Encryption and Subtotals.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;

procedure TMainForm.Report_BeforeReadTemplate(const Sender: TObject; const e: TGenerateEventArgs);
begin
  e.DataFile.Protection.OpenPassword := OpenPassTemplate.Text;
end;

procedure TMainForm.Report_AfterGenerateSheet(const Sender: TObject; const e: TGenerateEventArgs);
begin
  e.DataFile.Protection.SetSheetProtection(ProtectSheetPass.Text, TSheetProtectionOptions.Create(ProtectSheet.Checked));
end;

procedure TMainForm.Report_AfterGenerateWorkbook(const Sender: TObject; const e: TGenerateEventArgs);
begin
  e.DataFile.Protection.OpenPassword := OpenPassGenerated.Text;
  e.DataFile.Protection.SetModifyPassword(ModifyPassGenerated.Text, RecommendReadOnly.Checked, ReservingUser.Text);
  e.DataFile.Protection.SetWorkbookProtection(ProtectWorkbookPass.Text, TWorkbookProtectionOptions.Create(false, ProtectWorkbook.Checked));
end;

end.