Technical Article

Read Agile-Encrypted Excel Files in Delphi with HotXLS

A customer uploads a workbook that opens fine on their desk, and the import service rejects it with a ZIP error. A hex dump of the first eight bytes shows D0 CF 11 E0 A1 B1 1A E1, the OLE Compound File signature that a 1997-era .xls carries. The file has an .xlsx extension, it came out of a current Excel, and it is not a ZIP package at all. Somebody set a password in the Save As dialog

That is the whole mystery. When Excel saves a workbook with a password, it encrypts the real .xlsx ZIP and wraps the result in a Compound File Binary (CFB) container, using the Agile scheme from [MS-OFFCRYPTO] §2.3.4.10 through §2.3.4.15. Any loader that classifies files by extension, or that hands every .xlsx straight to a ZIP reader, will bounce the upload back to a user who did nothing unusual from their side of the screen

What sits inside an Agile-encrypted workbook

An Agile-encrypted workbook is a CFB container with two streams that matter: EncryptionInfo, which describes how the encryption was done, and EncryptedPackage, which holds the original ZIP as one opaque, encrypted blob. HotXLS reads both streams directly from the container, without Excel, COM, or an external crypto DLL on the machine

Anatomy of the Agile-encrypted workbook that HotXLS opens in Delphi: a CFB container holding an EncryptionInfo descriptor stream and an EncryptedPackage stream of AES-CBC segments
HotXLS reads an Agile-encrypted workbook as a CFB container with a self-describing EncryptionInfo stream and the .xlsx ZIP encrypted as an opaque EncryptedPackage stream

The part that sets Agile apart from the older Standard Encryption is that EncryptionInfo describes itself. After an 8-byte prefix whose major and minor version are both 4, the stream is a UTF-8 XML descriptor. A keyData element names the cipher (AES), the chaining mode (ChainingModeCBC), the hash algorithm, the key length in bits, the block size and a Base64 salt. The password keyEncryptor carries its own salt, a spinCount, and three Base64 payloads: encryptedVerifierHashInput, encryptedVerifierHashValue and encryptedKeyValue. A dataIntegrity element adds an encrypted HMAC key and HMAC value for tamper detection

Excel itself writes AES-256, SHA-512 and a spin count of 100,000. The descriptor is allowed to say otherwise, though, and third-party writers do. HotXLS takes keyBits from the descriptor instead of assuming 256, so AES-128 and AES-192 files decrypt the same way, and it accepts hashAlgorithm="SHA256" as well as SHA-512. Each element's own hash attribute governs the steps that belong to it: the keyEncryptor hash drives password derivation and verification, while the keyData hash drives the segment IVs and the integrity HMAC. A file that mixes the two still opens

Opening the file: OpenEncrypted or Open with a password

TXLSXWorkbook.OpenEncrypted takes a file name and a password and returns 1 on success, the same contract as Open. It sniffs the first eight bytes with CanReadEncrypted. A file without the CFB signature goes through the ordinary Open path and the password is ignored, so an importer that always calls OpenEncrypted needs no branch at the call site. A CFB file is tried as Standard Encryption first, and when the EncryptionInfo prefix carries the Agile 4.4 version, the Agile reader takes over. The decrypted ZIP bytes then go to the regular XLSX loader in memory

Both entry points take a file name. Open(Stream) refuses a stream that starts with the CFB signature and raises an exception whose message points to OpenEncrypted, so an upload held in a TMemoryStream has to land in a temporary file first

uses
  System.SysUtils, System.Classes, System.IOUtils, System.Variants, lxHandleX;

function ReadFirstCell(Upload: TMemoryStream;
  const Password: WideString): WideString;
var
  TempName: string;
  Wb: TXLSXWorkbook;
begin
  Result := '';
  // Encrypted packages are opened from a file, so spill the upload to disk
  TempName := TPath.GetTempFileName;
  Wb := TXLSXWorkbook.Create;
  try
    Upload.SaveToFile(TempName);
    // Plain .xlsx, Standard-encrypted and Agile-encrypted files all work here
    if Wb.OpenEncrypted(TempName, Password) = 1 then
      Result := VarToWideStr(Wb.Sheets[1].Cells[1, 1].Value);
  finally
    Wb.Free;
    TFile.Delete(TempName);
  end;
end;

The two-argument Open(FileName, Password) overload reaches the same decryption code but adds one thing OpenEncrypted does not have: the OnPassword event. When the supplied password fails, Open fires OnPassword, lets the handler assign a new password and set Retry to True, and asks again, up to three times. A desktop application can put a password prompt in that handler. A server can use it for something else, which the last section gets to

How does a password become an AES key?

The password never decrypts anything directly. The first digest is SHA-512 over the keyEncryptor salt followed by the UTF-16LE bytes of the password; that digest is then rehashed spinCount times, each round prepending the 32-bit little-endian round counter to the previous digest. With Excel's 100,000 rounds, every single password attempt costs one hundred thousand serial SHA-512 calls. That is the point of the spin count. A legitimate open pays it once, and a dictionary attack pays it for every guess

How HotXLS derives Agile encryption keys in Delphi: the password is spun through 100,000 SHA-512 rounds, then three fixed 8-byte block keys yield the verifier and package keys
The password never decrypts anything directly: a SHA-512 spin-count chain feeds three block-key hashes that produce the verifier keys and unwrap the random package key

The chain is short enough to reproduce, and doing so is the quickest way to see what one guess costs on your own hardware. The sketch below follows §2.3.4.11 for a SHA-512 descriptor and uses the public XlsSHA512 function from the lxXlsxAgileDecrypt unit

uses
  System.SysUtils, lxXlsxAgileDecrypt;

// H0 = SHA-512(salt + UTF-16LE(password))
// Hn = SHA-512(LE32(n - 1) + H(n-1)), repeated SpinCount times
function SpinHash(const Password: WideString; const Salt: TBytes;
  SpinCount: Integer): TBytes;
var
  Buf: TBytes;
  PwdLen, I: Integer;
begin
  PwdLen := Length(Password) * SizeOf(WideChar);
  SetLength(Buf, Length(Salt) + PwdLen);
  if Length(Salt) > 0 then
    Move(Salt[0], Buf[0], Length(Salt));
  if PwdLen > 0 then
    Move(Password[1], Buf[Length(Salt)], PwdLen);
  Result := XlsSHA512(Buf);
  SetLength(Buf, 4 + 64);
  for I := 0 to SpinCount - 1 do
  begin
    Buf[0] := Byte(I and $FF);           // round counter, little-endian
    Buf[1] := Byte((I shr 8) and $FF);
    Buf[2] := Byte((I shr 16) and $FF);
    Buf[3] := Byte((I shr 24) and $FF);
    Move(Result[0], Buf[4], 64);         // previous digest
    Result := XlsSHA512(Buf);
  end;
end;

The spun hash is still not a key. Three keys come out of it, each by hashing the spun value once more with a fixed 8-byte block key appended: FE A7 D2 76 3B 4B 9E 79 for the verifier input, D7 AA 0F 6D 30 61 34 4E for the verifier hash, and 14 6E 0B E7 AB AC D0 D6 for unwrapping the package key. Each result is cut to the declared key length, 32 bytes for AES-256. The specification also defines what happens when a hash comes out shorter than the key: pad with 0x36 bytes. No AES key size actually triggers that with SHA-256 or SHA-512, but the same 0x36 rule does apply when the password salt is stretched to the block size to serve as the CBC IV

The spin count is read from the file, so it is also an attack surface: an upload declaring a billion rounds would pin a CPU core before failing. §2.3.4.11 caps spinCount at 10,000,000, and HotXLS reports a larger value as an unsupported descriptor before hashing anything. For a public upload endpoint that cap is the right default. Excel's own 100,000 sits far below it

Password verification and the saltSize truncation trap

HotXLS checks the password before it touches the package. It decrypts encryptedVerifierHashInput with the first derived key, hashes the result, decrypts encryptedVerifierHashValue with the second key, and compares the two digests over the full hash length without stopping at the first differing byte. A mismatch ends the open with a wrong-password result, and the package body is never decrypted under a bad key. A wrong password therefore cannot produce a workbook full of plausible garbage

One sentence in the specification is easy to misread here. §2.3.4.13 defines the verifier as saltSize bytes of random data, where saltSize is the length of the key encryptor's salt. The decrypted verifier comes back padded to a multiple of the 16-byte AES block, so it has to be cut back to saltSize before hashing. Excel always writes saltSize and blockSize as 16, which means code that truncates to the block size passes every test against Excel output. It then rejects the correct password on the first file from a producer that picked a 32-byte salt. HotXLS truncates to the salt length, because that is what the text says, and the two numbers agreeing in Excel files is a coincidence

How is the EncryptedPackage decrypted?

The package key is random, not password-derived. The writer generated it, encrypted it into encryptedKeyValue, and HotXLS unwraps it with the third derived key, keeping as many bytes as keyData declares in keyBits. Before decrypting a single segment, HotXLS checks dataIntegrity when the file carries it: the HMAC key and value are decrypted with the package key, and an HMAC over the complete EncryptedPackage stream, size prefix included, must match the stored value. Files written without a usable dataIntegrity element skip this step and still open

HotXLS decrypts the EncryptedPackage stream in Delphi segment by segment, deriving each 4096-byte AES-CBC segment IV from the keyData salt and the segment index
Each 4096-byte AES-CBC segment decrypts independently under its own salt-and-index IV, and the result is truncated to the declared plaintext size

The stream itself starts with an 8-byte little-endian plaintext size, followed by ciphertext in 4096-byte segments. Each segment gets its own IV: the hash of the keyData salt followed by the 32-bit little-endian segment index, cut to the block size. Any segment can therefore be decrypted on its own, which would allow random access in principle; HotXLS decrypts the whole package to memory because the ZIP loader wants the complete archive anyway. The declared size is checked against the actual stream length before decryption starts, so a truncated upload or an inflated size field fails cleanly. After the last segment, the buffer is cut to the declared size, which drops the up to 15 bytes of AES padding, and what remains is byte for byte the ZIP that Excel encrypted

Telling a wrong password from an unsupported file

Every encryption failure in OpenEncrypted and Open raises EXlsxEncryptionNotImplemented, and the message says which case it is: the supplied password is incorrect, or the file uses a scheme HotXLS does not read. HotXLS reads the password key encryptor with AES in CBC mode and SHA-512 or SHA-256 hashing, which covers what current Excel versions write; a container protected only by a certificate key encryptor, a descriptor naming another cipher, or a CFB that is neither Standard nor Agile, such as a renamed BIFF .xls, gets the unsupported message

Matching on message text is brittle, especially if the resource strings are ever localized. The OnPassword event gives a cleaner signal: Open fires it only when the verifier or the integrity check rejects the password, never for an unsupported scheme. For an import service, call Open(FileName, Password) with a handler that records the rejection, rather than OpenEncrypted, which raises without consulting any event

type
  TUploadOutcome = (uoLoaded, uoPasswordRejected, uoUnreadable);

  TUploadLoader = class
  private
    FPasswordRejected: Boolean;
    procedure HandlePassword(Sender: TObject; var PassWord: WideString;
      var Retry: Boolean);
  public
    function Load(Wb: TXLSXWorkbook;
      const FileName, Password: WideString): TUploadOutcome;
  end;

procedure TUploadLoader.HandlePassword(Sender: TObject;
  var PassWord: WideString; var Retry: Boolean);
begin
  // Fired only when the password (or the integrity HMAC) was rejected
  FPasswordRejected := True;
  Retry := False;   // nobody to ask on a server; Open raises next
end;

function TUploadLoader.Load(Wb: TXLSXWorkbook;
  const FileName, Password: WideString): TUploadOutcome;
begin
  FPasswordRejected := False;
  Wb.OnPassword := HandlePassword;
  try
    if Wb.Open(FileName, Password) = 1 then
      Result := uoLoaded
    else
      Result := uoUnreadable;
  except
    on E: EXlsxEncryptionNotImplemented do
      if FPasswordRejected then
        Result := uoPasswordRejected   // safe to ask the user again
      else
        Result := uoUnreadable;        // a retry will not help; log E.Message
  end;
end;

One trap stays even with this split. An integrity mismatch is reported the same way as a bad password, so a workbook whose ciphertext was damaged in transit, by a proxy that rewrote the body or an upload cut off mid-segment, comes back as uoPasswordRejected even when the user typed the right password. If the same user fails twice with a password they insist is correct, ask for the file again before asking for the password again

The write side uses the same machinery in reverse. SaveAsEncryptedAgile produces an Agile container with SHA-512 key derivation, 100,000 spin rounds and AES-256 package encryption, SaveAsEncrypted produces Standard Encryption, and setting VerifyEncryptedSave to True makes either call reopen the file it just wrote through the decryption path and return -1 if that fails. The Standard path is walked through in the article on writing AES-protected XLSX output, and the RC4 and XOR schemes found inside legacy BIFF files are covered in the ECB and RC4 interop article

For a pipeline that never knows in advance whether the next upload carries a password, the reader described above, together with native XLS and XLSX reading and writing for Delphi and C++Builder, is part of the HotXLS Delphi Excel Component