Encrypting XLSX output with AES in Delphi: what HotXLS SaveAsEncrypted writes

A workbook that refuses every edit until someone types the protection password can still hand over every number to whoever renames it from .xlsx to .zip. Inside the archive, xl/worksheets/sheet1.xml holds the values in plain UTF-8 and xl/sharedStrings.xml holds the names. That password belongs to sheet protection, an editing flag that cooperating editors agree to honor. The other password Excel offers, the one it asks for before showing anything at all, keys an AES cipher, and a file carrying it is not a zip

The first eight bytes of the file settle which of the two it has. An ordinary OOXML package starts with 50 4B 03 04, the "PK" of a zip local file header. An encrypted workbook starts with D0 CF 11 E0 A1 B1 1A E1, the OLE compound file signature, which is the same signature a 1997-era .xls carries. A payroll export that relies on sheet protection fails this check in a second, and a reviewer only has to read one line of a hex dump to see it

Sheet protection in HotXLS is an editing control

The Protect method on TXLSXWorksheet and ProtectWorkbook on TXLSXWorkbook record a short password hash, the 16-bit legacy algorithm that OOXML inherited from early binary Excel and that shows up in the saved XML as four hex digits. The format never claimed this does more than discourage accidental edits. Everything else in the package stays where an archive tool can list it: cell values, formulas, shared strings, defined names. And because every cell starts with Locked set to True, a bare Protect call freezes the whole sheet against editing while every value stays in plain sight

Protection still has real work to do. It keeps users inside an input range, stops a careless sort from scrambling a layout, and holds a printed form steady; the articles on worksheet protection and page setup and on the 15 allow options of XLSX sheet protection go through those jobs. Once a requirement says confidentiality, though, the answer is one of the two encrypted save calls on TXLSXWorkbook: SaveAsEncrypted or SaveAsEncryptedAgile

What SaveAsEncrypted writes to disk

TXLSXWorkbook.SaveAsEncrypted writes ECMA-376 Standard Encryption as [MS-OFFCRYPTO] §2.3.4 defines it. The call serializes the workbook into an ordinary XLSX zip in memory, so the plaintext package never lands on disk, and then wraps that zip. Key generation follows §2.3.4.7: SHA-1 over a random 16-byte salt and the UTF-16 password, then 50,000 further SHA-1 rounds that each mix in the iteration counter, then a final derivation step that cuts the result down to a 128-bit AES key

Two encrypted things go into the file. The password verifier from §2.3.4.8 is a random 16-byte value plus its SHA-1 hash, both encrypted with AES-128 in ECB mode, so a reader can test a password before it touches the payload. The package follows as an 8-byte little-endian length and then the zip bytes, encrypted with AES-128 block by block, again in ECB mode, with no initialization vector anywhere. ECB looks odd to anyone used to modern cipher modes. Standard Encryption specifies it anyway, and a producer that chains the blocks with CBC writes a file whose password check passes while the body decrypts to garbage; the article on ECB and RC4 interop failures takes that trap apart

What lands on disk is an OLE compound file with an EncryptionInfo stream, an EncryptedPackage stream and the \005DataSpaces storage that tells a consumer which transform to undo. There is no xl/ directory for an archive tool to list, so the rename test turns up nothing readable. Excel 2007 and later opens the file with the password alone

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  rc: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Payroll');
    Sheet.Cells[1, 1].Value := 'Employee';
    Sheet.Cells[1, 2].Value := 'Net pay';
    Sheet.Cells[2, 1].Value := 'A. Garcia';
    Sheet.Cells[2, 2].Value := 4815.16;

    // Reopen the written container with the same password before
    // reporting success; a failed reopen turns rc into -1
    Book.VerifyEncryptedSave := True;

    // PasswordFromVault is your own secret-store lookup
    rc := Book.SaveAsEncrypted('payroll-2026-06.xlsx', PasswordFromVault);
    if rc <> 1 then
      raise Exception.CreateFmt('Encrypted save failed (rc=%d)', [rc]);
  finally
    Book.Free;
  end;
end;

An empty password makes SaveAsEncrypted return -1 before anything is written. Handle the real password the way you handle a connection string: fetch it from a vault or a generated-secret service at the last moment, never log it, never put it in a cell or a document property of the workbook it protects. The return-code check carries more weight than it looks. When an encrypted save fails, the only fallback within reach of the calling code is a plain SaveAs, and a job that ships that copy "so the customer gets something" has produced exactly the incident encryption was meant to rule out. Abort the delivery instead

Standard or Agile: which encrypted save fits a new delivery?

For a file leaving your system today, SaveAsEncryptedAgile is the call to reach for first. It takes the same two arguments and runs the same in-memory serialization, then writes Agile Encryption from [MS-OFFCRYPTO] §2.3.4.10 through §2.3.4.15, the scheme Excel 2010 and later uses by default for password-protected workbooks. The password goes through SHA-512 with a spin count of 100,000. A fresh random 256-bit key encrypts the package with AES-256 in CBC mode, cut into 4096-byte segments that each get their own IV, derived from the key-data salt and the segment index. An HMAC-SHA-512 dataIntegrity block lets a reader detect a tampered package before it trusts the contents

The algorithm question tends to arrive late, usually as a line on a security questionnaire asking for "AES-256 encryption of files at rest." SaveAsEncrypted cannot meet that line however long the password is: Standard Encryption fixes the cipher at AES-128 and the key derivation at SHA-1, and no parameter changes what it emits. SaveAsEncryptedAgile does meet it. Whichever you call, write the profile into your security documentation exactly as it is, either "ECMA-376 Standard Encryption, AES-128-ECB, SHA-1 key derivation at 50,000 iterations" or "ECMA-376 Agile Encryption, AES-256-CBC, SHA-512 at 100,000 iterations, HMAC-SHA-512 integrity". "Encrypted with AES" on its own just invites the next question from the auditor

Standard keeps its place where the receiving side decrypts with its own code and that code implements only §2.3.4.7 through §2.3.4.9. Standard is the smaller format to implement — one key, one cipher mode, a binary header instead of an XML descriptor — and a decoder written against that part of the spec has no way to parse an Agile EncryptionInfo stream. If the consumer is Excel, LibreOffice or anything else that reads Agile, use Agile

Verifying the save, and what CanReadEncrypted actually tests

VerifyEncryptedSave belongs in every delivery job. With the property set to True, both encrypted save calls reopen the file they just wrote through the full decrypt path with the same password, load the inner package, and return -1 if either step fails. The same check is available on demand as VerifyEncryptedFile(FileName, Password), which returns True only when the password unlocks the container and the workbook inside it loads

CanReadEncrypted answers a much narrower question than its name suggests. It reads the first eight bytes of the file and returns True when they match the compound file signature. Every encrypted XLSX matches. So does every legacy BIFF .xls, since both formats share that container, and so does a Word 97 document that someone renamed. As a routing probe at intake it does its job well. As evidence that an encrypted save worked it is too weak, because a container whose streams were truncated halfway still starts with the right eight bytes

procedure CheckEncryptedDelivery(const OutFile, Password: WideString);
var
  Probe: TXLSXWorkbook;
begin
  Probe := TXLSXWorkbook.Create;
  try
    // Full decrypt-and-load with the real password
    if not Probe.VerifyEncryptedFile(OutFile, Password) then
      raise Exception.Create(OutFile + ': does not reopen with its password');

    // Negative probe, a check you add yourself: a deliberately wrong
    // password must be rejected by the verifier
    if Probe.VerifyEncryptedFile(OutFile, Password + '#') then
      raise Exception.Create(OutFile + ': accepted a wrong password');
  finally
    Probe.Free;
  end;
end;

The negative probe is cheap and catches a class of mistake the positive one cannot see, such as a pipeline that wrote the right container with a password other than the one it is about to send to the recipient. When either check fails, delete the output file before raising, so nothing downstream picks up a half-verified artifact

Reading encrypted workbooks back with OpenEncrypted

TXLSXWorkbook.OpenEncrypted(FileName, Password) decrypts both schemes. It tries Standard Encryption first, switches to the Agile path when the EncryptionInfo stream carries the version 4.4 XML descriptor, and loads the decrypted zip exactly as if it had come from disk. Agile files open whether Excel or a third-party writer produced them, with AES-128, AES-192 or AES-256 and either SHA-512 or SHA-256 hashing. A plain XLSX passed to OpenEncrypted goes down the ordinary Open path, so intake code can call it on every file. The walkthrough of Agile decryption covers the read-side key derivation in detail

Two failures raise EXlsxEncryptionNotImplemented, and the exception message says which one happened: the password is wrong, or the compound file is neither Standard- nor Agile-encrypted. That second message is how a renamed legacy workbook shows up. An .xls saved under an .xlsx name passes CanReadEncrypted, fails both decryptors, and belongs with TXLSWorkbook.Open instead. The two-argument Open(FileName, Password) on TXLSXWorkbook decrypts too; when the password is missing or wrong it fires OnPassword, whose handler can supply another password and set Retry, and after three attempts it raises the same exception

function LoadIncoming(const FileName, Password: WideString): Boolean;
var
  Book: TXLSXWorkbook;
begin
  Result := False;
  Book := TXLSXWorkbook.Create;
  try
    try
      // Encrypted (Standard or Agile) and plain XLSX both go through here
      if Book.OpenEncrypted(FileName, Password) = 1 then
      begin
        Writeln(FileName + ': opened, ' + IntToStr(Book.Sheets.Count) +
          ' sheet(s), first is ' + Book.Sheets[1].Name);  // Sheets is 1-based
        Result := True;
      end;
    except
      on E: EXlsxEncryptionNotImplemented do
        // Wrong password, or a compound file that is not an encrypted
        // OOXML package (often a legacy .xls with the wrong extension)
        Writeln(FileName + ': ' + E.Message);
    end;
  finally
    Book.Free;
  end;
end;

Encrypt at the delivery edge, even though you can decrypt

Being able to read encrypted files back does not move encryption earlier in a pipeline. Keep the plaintext master inside your trust boundary, in a database, a document store or an access-controlled share, and produce the encrypted copy as the last write before the file leaves the system. Every downstream stage that needs the workbook again reads the master

Password custody is what makes this the better design. A delivery job that generates one password per recipient, sends it out of band and then discards it has made each encrypted copy unreadable to everyone but that recipient, the archive included. The 50,000 or 100,000 rounds of key derivation exist to make guessing expensive, and nothing in the container escrows the secret. Archive the master and treat the encrypted file as a disposable rendering of it. An archive that holds only encrypted deliveries is readable for exactly as long as every one of those passwords survives

Encryption and protection also stack cleanly, because they answer different questions. Encryption decides who can open the file. Protection decides what someone already inside may change. A payroll delivery can do both: lock the formula cells and call Protect, then save through SaveAsEncryptedAgile, and the recipient can filter and sort but cannot quietly rewrite the calculations. The mistake is only ever letting protection stand in for encryption

Legacy XLS: RC4 CryptoAPI through EncryptionPassword

The BIFF engine encrypts by a different route. Setting EncryptionPassword on a TXLSWorkbook before SaveAs makes the writer emit a FILEPASS record straight after the workbook BOF and encrypt the record bodies that follow it. EncryptionType picks the flavor: the default xletAuto writes RC4 CryptoAPI for BIFF8 files and XOR obfuscation for BIFF5, matching what Excel writes for each format, while xletRC4 asks for the older standard RC4. Open(FileName, Password) reads all three schemes back

var
  Writer, Reader: IXLSWorkbook;   // interface refs: no manual Free
begin
  Writer := TXLSWorkbook.Create;
  Writer.Sheets.Add.Cells.Item[1, 1].Value := 'Confidential';
  Writer.EncryptionType := xletRC4CryptoAPI;  // what xletAuto picks for BIFF8
  Writer.EncryptionPassword := 'S3cret!';
  Writer.SaveAs('confidential.xls');

  Reader := TXLSWorkbook.Create;
  if Reader.Open('confidential.xls', 'S3cret!') > 0 then
    Writeln(Reader.Sheets[1].Cells.Item[1, 1].Value);  // Sheets is 1-based
end;

RC4 is broken cryptography and should not protect anything that matters today. Its one remaining job is interoperability with systems that still exchange .xls, so new confidential output goes out as XLSX through one of the AES calls. The read side carries more weight in migration work: a folder of password-protected legacy workbooks opens in a Delphi process with no Excel automation anywhere in the loop, which is usually the step that stalls moving an old archive onto a modern scheme. For high-volume encrypted deliveries, the throughput notes in the article on streaming writes for server batch jobs apply to the content-building phase that runs before the encrypted save

Both AES save calls, the decrypting open path and the legacy RC4 round trip ship with the HotXLS Delphi Component, which runs natively inside Delphi and C++Builder processes and needs no copy of Excel on the machine that encrypts or decrypts