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
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
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
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