HotXLS reads and writes Excel Binary Workbook (XLSB) files from Delphi through the same TXLSXWorkbook facade used for XLSX: Open detects a binary workbook from the package content, and SaveAs or SaveAsChecked with xlsxExcelBinaryWorkbook writes one. The backend covers a validated subset and rejects anything it cannot represent before touching the destination
That last clause is the part worth reading slowly. XLSB looks like a simple variation on XLSX, and in packaging terms it is. The trouble lives in the details that the two formats encode differently, and in what a library quietly assumes when it bridges one into the other. The most instructive bug in this backend was not a malformed record at all. It was a default font that went missing, and a date column that turned into #### in Excel even though the value, the format code and the column width were all correct
What is an XLSB file, and how does it differ from XLSX?
An XLSB file is an OPC package with exactly the part structure of an XLSX workbook, but the workbook, worksheet, style and shared-string parts store binary records instead of XML. The format is specified in [MS-XLSB], and the record encoding is often called BIFF12. You still get [Content_Types].xml, relationship parts and a ZIP container; what changes is that xl/workbook.bin replaces xl/workbook.xml, xl/worksheets/sheet1.bin replaces the worksheet XML, and so on
Each BIFF12 record starts with a compact variable-length header. The record type takes one or two bytes, seven bits per byte with the high bit as a continuation flag, so a record type fits in 14 bits. The payload size follows in one to four bytes using the same seven-bit scheme. Strings are a 32-bit character count followed by UTF-16 code units. Compared with BIFF8 this is a much cleaner layout: no 8224-byte CONTINUE splitting, no compressed-versus-wide string flags mid-record. Compared with XLSX it skips XML tokenizing entirely, which is why large XLSB files tend to be smaller on disk and faster for Excel to open
One packaging detail trips up detection code. Excel registers the XLSB workbook part with the content type application/vnd.ms-excel.sheet.binary.macroEnabled.main, even for workbooks that contain no macros. HotXLS checks that registration in [Content_Types].xml first and only falls back to the .bin part extension when the content type is absent, and either path still has to pass the strict record validator. The general rules about part names, relationships and content types are the same ones covered in why Excel repairs a valid XLSX and the OPC package rules behind it
When should you choose XLSB over XLSX?
Choose XLSB when a downstream system requires it or when you exchange large value-heavy workbooks with Excel users who care about open time; otherwise XLSX remains the safer default. XLSX is an ISO standard (ISO/IEC 29500) with broad tool support, it is inspectable with a ZIP tool and a text editor, and every spreadsheet product reads it. XLSB is a Microsoft format with a published specification but a narrower ecosystem
In practice the requests come from two directions. Some reporting pipelines and archives standardized on .xlsb years ago and reject anything else. Other teams generate workbooks with hundreds of thousands of numeric cells and found that users open the binary version noticeably faster. Both are legitimate reasons. "It sounds more efficient" is not, because the HotXLS binary backend stages the complete package in memory, so it is not the tool for bounded-memory server exports. For those, the streaming XLSX writer is the right path
Opening and saving XLSB with TXLSXWorkbook
TXLSXWorkbook.Open needs no format hint: the same call loads XLSX, XLSM or XLSB, and after a successful binary load TXLSXWorkbook.SourceFormat reports xlsxExcelBinaryWorkbook. Writing is explicit. SaveAs(FileName) without a format always produces an Open XML workbook, even when the filename ends in .xlsb, so binary output always names the format
uses
SysUtils, lxHandleX;
procedure ConvertToXlsb(const Source, Target: string);
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
// Open detects XLSX, XLSM or XLSB from the package itself
if Book.Open(Source) <> 1 then
raise Exception.Create('Input is unsupported or invalid');
// The extension alone never selects binary output
if Book.SaveAsChecked(Target, xlsxExcelBinaryWorkbook) <> 1 then
raise Exception.Create('XLSB output is unsupported or failed');
finally
Book.Free;
end;
end;Stream output goes through SaveAs(Stream, xlsxExcelBinaryWorkbook) or the stream overload of SaveAsChecked. Internally both route to the private SaveAsXlsbStream and SaveAsXlsbFile methods; you never call those directly. File output is staged into a sibling temporary file and committed with one final replacement. A stream destination must be readable and seekable, because the writer snapshots its bytes, size and position and restores them if the final copy fails. TXLSXWorkbook.LastSaveUsedStreaming reports False after a binary save, which is accurate: the whole package exists in memory before a single byte reaches your destination
Why a validated subset instead of full fidelity?
HotXLS does not maintain a second object model for XLSB. On open, XlsbToOpenXml converts the binary package into an in-memory Open XML package and the ordinary XML parser loads it; on save, the XML writer builds a package and OpenXmlToXlsb encodes it as binary records. Every feature therefore has to survive two translations, and a feature the bridge cannot translate exactly is rejected rather than approximated
The supported set as of HotXLS 2.384.113 is broad enough for most data workbooks: typed values, shared and inline strings, common fonts, fills, borders and number formats, row heights and column widths, ordinary cell formulas with cached results, defined names, merged cells, hyperlinks, frozen and split panes, AutoFilter ranges with simple custom criteria, and ordinary worksheet tables. Outside it sit shared and array formulas, future and user-defined functions, external-workbook references, charts and drawings, PivotTables and slicers, VBA, conditional formatting, data validation and nondefault calculation settings such as manual calculation or iteration
Rejection is mandatory, not advisory. TXLSXWorkbook.GetConversionReport(xlsConversionExcelBinaryWorkbook) lists unsupported features before you save, and those entries fail the save even with AAllowLossy = True or an unchecked SaveAs. That is deliberate. Silently dropping a PivotTable from a binary copy is the kind of loss nobody notices until the workbook is in front of a client
uses
Classes, lxHandleX, lxDiagnostics;
function CanSaveAsXlsb(Book: TXLSXWorkbook; Log: TStrings): Boolean;
var
Report: TXLSConversionReport;
I: Integer;
begin
Report := Book.GetConversionReport(xlsConversionExcelBinaryWorkbook);
try
Result := True;
for I := 0 to Report.Count - 1 do
if Report[I].Fidelity = xlsConversionUnsupported then
begin
Result := False;
Log.Add(Report[I].SheetName + ' ' + Report[I].Location + ': ' +
Report[I].Feature + ' - ' + Report[I].Message);
end;
finally
Report.Free; // the caller owns the report
end;
end;The report covers the live model. Final package validation is still the last gate for things the model never sees, such as malformed records or unfamiliar attributes in the source file, so a clean report followed by a failed save is possible and the save result is what you act on
Why did the default font and 0.00 format disappear?
The default font and built-in number formats disappeared because the XML style reader made two assumptions that hold for files HotXLS writes itself but not for workbooks Excel writes. The bridge preserved both resources perfectly; the loss happened one layer up, in the public Open path. That gap is the main lesson of this backend: a correct format bridge does not guarantee a faithful public round trip
The first assumption concerned fonts[0]. When HotXLS saves XLSX, it writes an implicit default font into slot zero, and its XML reader skips that slot so the user-facing Fonts collection starts at index zero. An Excel-authored XLSB carries its own explicit default in that slot, and skipping it threw that choice away. The second assumption concerned number formats. The reader only mapped custom numFmtId values (164 and above) into NumberFormats; built-in ids stayed as zero. A styled blank cell carrying built-in format 2, which is 0.00, came back as General
The fix lives entirely in the binary import path. After the full package passes preflight, the XLSB open sets a private style mode for the synchronous XML parse only. In that mode the reader loads every font including slot zero and shifts each fontId by one, converts each supported built-in id into an explicit format code in the model, and still uses the original numFmtId for date detection, so a built-in date format keeps its date semantics. A finally block always clears the mode, and a later plain XLSX or ODS Open clears the retained default-font state as well
How does fonts[0] affect Excel column widths?
Excel computes column widths from the workbook default font in fonts[0], not from the fonts applied to the cells in that column. Column width is stored in character units of the default font's maximum digit width, so changing fonts[0] changes the pixel width of every column, including columns whose cells all use a different font
That produced the most confusing symptom of the whole investigation. The re-saved workbook had identical cell values, identical format codes and identical width attributes, the cell font and the Normal style both pointed to the preserved font, and Excel still rendered a date column as ####. The written fonts[0] had reverted to Calibri 11, the maximum digit width shrank, and the dates no longer fit. HotXLS now keeps the source default font and writes it back as fonts[0], falling back to Calibri 11 only for workbooks that never had a binary source. The full width arithmetic is in Excel column width and max digit width (MDW) in Delphi
Locale-dependent built-in formats are rejected, not guessed
A built-in number format id without an explicit format code is accepted only when its meaning is fixed; locale-dependent ids are rejected during package preflight. HotXLS maps ids 0 to 22, 37 to 40 and 45 to 49 to known codes such as 0.00, 0%, mm:ss and @. The remaining ids below 164 belong to formats whose rendering depends on the Excel installation's regional settings, and the import refuses them with the message "A locale-dependent number format requires an explicit format code"
Inventing a code for those ids would be worse than failing. A guessed East Asian date pattern looks plausible, round-trips without error and displays the wrong thing for half your users. Timing matters too: the check runs in the whole-package preflight, before the existing workbook is cleared, so a rejected file leaves the previously loaded model, filename and source format untouched. The Excel date serial and numFmt article covers how built-in date ids interact with the 1900 and 1904 date systems
procedure LoadBinaryWorkbook(Book: TXLSXWorkbook; const FileName: string);
var
I: Integer;
begin
// Binary input needs eager full loading; selective modes reject up front
Book.MetadataOnly := False;
Book.SelectedSheets.Clear;
if Book.Open(FileName) <> 1 then
begin
for I := 0 to Book.Diagnostics.Count - 1 do
Writeln(Book.Diagnostics[I].Message);
Exit; // the previously loaded workbook is still intact
end;
// Formula output needs a cache unless the save recalculates
Book.RecalcBeforeSave := xrmAlways;
end;Practical rules for XLSB in production code
The binary backend is safe to rely on when you treat its rejections as data rather than as errors to suppress. A few rules follow from the behavior above
- Name the format on every binary save; the
.xlsbextension alone still produces XLSX - Run
GetConversionReport(xlsConversionExcelBinaryWorkbook)before saving user-edited workbooks and surface the unsupported features to the user - Leave
MetadataOnlyoff andSelectedSheetsempty for binary input, since lazy loading would require retaining a converted archive - Give formulas a cached result or recalculate before saving, because a missing cache rejects under
xrmManualand under a cleanxrmIfDirty - Budget memory for the complete converted package plus the binary output; use streaming XLSX when memory must stay bounded
When you test, compare against Excel through the public API rather than at the bridge level. The default-font and 0.00 regressions both passed bridge tests, and both showed up immediately in an Excel-authored workbook opened and re-saved through Open and SaveAsChecked
FAQ
Does HotXLS detect XLSB files automatically?
Yes. TXLSXWorkbook.Open inspects the content type registered for the workbook part and validates the binary records, so the same call loads XLSX and XLSB, and SourceFormat reports which one was read
Why does SaveAs with a .xlsb filename still write XLSX?
HotXLS never infers the output format from the extension. Pass xlsxExcelBinaryWorkbook to SaveAs or SaveAsChecked to write a binary workbook
What happens when a workbook uses features outside the XLSB subset?
The save fails before the destination changes, even with lossy saving allowed. GetConversionReport lists the blocking features in advance, and Diagnostics explains the failure afterward
XLSB support, the conversion report and the rest of the workbook API ship in HotXLS, the native Delphi and C++Builder spreadsheet component