Reading and writing encrypted files (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\10.API\25.Encrypted Files and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​10.​API/​25.​Encrypted Files

Overview

How to read and write Encrypted Excel files using FlexCel.

Concepts

• Concepts on this demo are similar to the ones shown in Encryption And Subtotals example, but are repeated here so you can see them even if you are not using reports.

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

Files

UEncryptedFiles.pas

unit UEncryptedFiles;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ShellAPI, UPaths,
  {$IFDEF FPC} LResources,{$ENDIF}
  {$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
  FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter;

type
  TFEncryptedFiles = class(TForm)
    btnCreateFile: TButton;
    Memo1: TMemo;
    SaveDialog: TSaveDialog;
    procedure btnCreateFileClick(Sender: TObject);
  private
    procedure CreateFile;
    procedure AddData(const Xls: TExcelFile);
    procedure ShowOpenDialog(const Xls: TExcelFile);
    procedure GetPassword(const e: TOnPasswordEventArgs);
  end;

var
  FEncryptedFiles: TFEncryptedFiles;

implementation
uses IOUtils, UPasswordDlg;

{$R *.dfm}

procedure TFEncryptedFiles.CreateFile;
var
  Xls: TExcelFile;
begin
  Xls := TXlsFile.Create(true);
  try
    AddData(Xls);
    ShowOpenDialog(Xls);
  finally
    FreeAndNil(Xls);
  end;
end;

procedure TFEncryptedFiles.GetPassword(const e: TOnPasswordEventArgs);
var
  Pwd: TPasswordDlg;
begin  //The event that will actually provide the password to open the empty form.
  Pwd := TPasswordDlg.Create(self);
  try
    e.Password := '';
    if Pwd.ShowModal <> mrOk then exit;
    e.Password := Pwd.Password;
  finally
    Pwd.Free;
  end;
end;

procedure TFEncryptedFiles.AddData(const Xls: TExcelFile);
var
  Names: TArray<String>;
  Quantities: Int32Array;
  i: Int32;
begin
  // On this demo we will fill data on an existing file with the api, starting with an encrypted file holding the starting formatting.
  // Declare some data for the chart.
  Names := TArray<String>.Create('Dog', 'Cat', 'Cow', 'Horse', 'Fish');
  Quantities := Int32Array.Create($7B, $C8, $96, 0, $B4);

   // We will use the OnPassword event here to show how to
   // open a file if you don't know a priory if it is encrypted or not.
   // If you already knew the file was encrypted, (as in this case)you could use:
   // xls.Protection.OpenPassword := '42';
  xls.Protection.OnPassword := GetPassword;
  xls.Open(TPath.Combine(DataFolder, 'EmptyForm.xls'));

  // Insert rows so the chart range grows. On this case we assume the data is at least 2 rows long. If not, we should handle
  // the case and do a xls.DeleteRange.
  xls.InsertAndCopyRange(TXlsCellRange.Create(1, 1, 1, 2), 5, 1, Length(Names) - 2, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.None);
   // Fill the data.
  for i := 0 to Length(Names) - 1 do
  begin
    xls.SetCellValue(4 + i, 1, Names[i]);
    xls.SetCellValue(4 + i, 2, Quantities[i]);
  end;

   // Set a new password for opening.
  xls.Protection.OpenPassword := '43';
  xls.Protection.SetModifyPassword('43', false, 'Ford Prefect');
end;

procedure TFEncryptedFiles.btnCreateFileClick(Sender: TObject);
begin
  CreateFile;
end;


procedure TFEncryptedFiles.ShowOpenDialog(const Xls: TExcelFile);
begin
  if not SaveDialog.Execute then exit;
  Xls.Save(SaveDialog.FileName); //No need to delete the file first, since AllowOverWriteFiles is true in XlsAdapter.

  if MessageDlg('Do you want to open the generated file? (password is 43)', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
  end;
end;

end.

UPasswordDlg.pas

unit UPasswordDlg;

interface

uses
  Windows, Messages, SysUtils, Variants,Classes, Graphics,
  Controls, Forms, Dialogs, StdCtrls;

type
  TPasswordDlg = class(TForm)
    Label1: TLabel;
    Button1: TButton;
    Button2: TButton;
    edPassword: TEdit;
    Label2: TLabel;
  public
    function Password: string;
  end;

implementation

{$R *.dfm}

{ TPasswordDialog }

function TPasswordDlg.Password: string;
begin
  Result := edPassword.Text;
end;

end.