You format a column with ;;; to hide it, or 0; to drop negatives, yet the report your Delphi code exports still prints the digits. An Excel number format has up to four sections, and HotXLS TXLSXCell.FormattedText treats a section written as empty as "show nothing", which matches Excel and is not the same as a section that was left out
In an .xlsx package the code is the formatCode attribute of a numFmt element in the styles part (ECMA-376 Part 1, §18.8.30), with custom codes numbered from 164 upward; a legacy .xls file stores the same text in a BIFF Format record (MS-XLS). Neither file format renders anything, so Excel, LibreOffice, a reporting tool and your own export routine each parse the code themselves, and the semicolon rules are the part of that small language a hand-written parser most easily gets wrong. Inside HotXLS there is one parser to reason about: the formatter that computes FormattedText is the same class the HTML, RTF and PDF exporters create for cell text, and the cell-formatting help topic calls it the shared display formatter, stating that the section rules below apply to it and to FormattedText while stored values stay as they are
The four sections of an Excel number format
Excel assigns sections by count. One section covers every number, and negatives keep their minus sign. With two, the leading one covers positive numbers and zero, the second covers negatives. Three mean positive; negative; zero, and a fourth, when written, governs text; with fewer than four, text is shown as typed unless the format is itself a text template such as "Ref "@. Once you write a negative section, the minus sign becomes literal text you supply, which is why the zero-hiding idiom spells it out as -0. In the table, "empty" means a zero-length string, not a space
| Format code | 12 | -12 | 0 | Text "pending" |
|---|---|---|---|---|
0; | 12 | empty | 0 | pending |
;;; | empty | empty | empty | empty |
0;-0;;@ | 12 | -12 | empty | pending |
0;-0;0; | 12 | -12 | 0 | empty |
0;-0;0;"n/a" | 12 | -12 | 0 | n/a |
"N/A";; | N/A | empty | empty | pending |
Reading FormattedText shows which section a value lands in. The procedure below writes the four kinds of value into a fresh workbook, applies one format code to all of them, and prints the display text in brackets so an empty result is visible. A new TXLSXWorkbook starts without any worksheet, so add one with Sheets.Add before touching cells; the sheet collection and the Cells[Row, Col] indexer are both 1-based. The expected output follows from the section rules
uses
SysUtils, Variants, lxHandleX;
procedure DumpSections(const AFormat: WideString);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sections'); // a new workbook has no sheets yet
Sheet.Cells[1, 1].Value := 12;
Sheet.Cells[2, 1].Value := -12;
Sheet.Cells[3, 1].Value := 0;
Sheet.Cells[4, 1].Value := 'pending';
for Row := 1 to 4 do
begin
Sheet.Cells[Row, 1].NumberFormat := AFormat;
Writeln(Format('%-8s -> [%s]',
[VarToStr(Sheet.Cells[Row, 1].Value), Sheet.Cells[Row, 1].FormattedText]));
end;
finally
Book.Free;
end;
end;
// DumpSections('0;'); Expected: [12] [] [0] [pending]
// DumpSections(';;;'); Expected: [] [] [] []
// DumpSections('0;-0;;@'); Expected: [12] [-12] [] [pending]
// DumpSections('0;-0;0;'); Expected: [12] [-12] [0] []Why does 0; hide negatives while ;;; hides everything?
Both codes rely on one rule: a section that exists but contains nothing displays nothing. The trailing semicolon in 0; creates a second section of zero characters, negatives use it and vanish, and zero stays with the positive section. ;;; writes all four sections empty. Plain 0 is different, because its other sections were never written and fall back to the defaults. The quick parser most people write splits on semicolons and then backfills every empty piece from the positive section, a shortcut that makes 0; indistinguishable from 0 and turns ;;; into something close to General. HotXLS counts the separators it actually found and fills only the sections that were never written, so an empty string stays empty. An entirely empty format code, with no semicolons at all, still means General display
A section made only of literal text prints that text and nothing else. With "N/A";;, 12 displays as N/A with no 12 glued to it. Treating "no digit placeholder found" as "fall back to General" would print N/A12 or just 12 instead. The literal-only reading is what makes a common spreadsheet idiom work: "Yes";;"No" on a column of 1/0 flags shows Yes for 1, No for 0 and blanks out anything negative, with no IF formula and no helper column. A section that names General outside quotes still renders the value, so "Total: "General prints Total: 12
Once written, the fourth section decides how text looks
When a fourth section exists, it alone applies to text, and its presence is what counts, whether or not it contains @. A trailing semicolon added out of habit, as in 0;-0;0;, therefore blanks every text cell in the range. 0;-0;0;"n/a" replaces any text with n/a, and 0;-0;0;"[ "@" ]" wraps it, which is a cheap way to make an ID column imported from CSV, half numbers and half strings, read consistently. Error and Boolean values sit outside all four sections. A cell holding #DIV/0! displays its error literal whatever the format says, so ;;; does not hide it in Excel, and FormattedText returns the error literal before it looks at the format at all; a Boolean cell likewise comes back as TRUE or FALSE
When is a semicolon not a section separator?
A semicolon separates sections only outside quotes and when it is not escaped. The single-section code "a;b"0 formats 12 as a;b12, and \; is a literal too. The characters _ and * also consume whatever follows them: the first reserves the width of the next character, the second repeats it to fill the cell. Excel's built-in Accounting format for the English (US) locale uses nearly all of this in one string, _("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_). A splitter built on repeated Pos(';', ...) calls cuts "a;b"0 into "a and b"0, two sections with unbalanced quotes. HotXLS walks the code once, tracking quote state and skipping the character after \, _ and *, and the same quote awareness keeps a quoted General as plain text
These rules matter most when Delphi code builds format codes from data, say a unit label from a settings table or a user-typed annotation. Wrap the literal in double quotes and any semicolon, digit or date letter inside it becomes inert. The one character a quoted run cannot hold is the double quote itself, since the format language has no escape inside quotes: close the run, emit \", and reopen. Assign the result to NumberFormat as it would appear in Excel's Format Cells dialog, with plain quote characters; HotXLS escapes the attribute when it writes styles.xml, so pre-escaping it as " yourself leaves that entity visible in the cell
// Turns arbitrary text into a format-code literal: everything goes inside
// double quotes, and an embedded quote closes the run, is emitted as \",
// and the run reopens
function QuoteFormatLiteral(const S: WideString): WideString;
var
I: Integer;
begin
Result := '"';
for I := 1 to Length(S) do
if S[I] = '"' then
Result := Result + '"\""'
else
Result := Result + S[I];
Result := Result + '"';
end;
procedure ApplyUnitFormat(Cell: TXLSXCell; const UnitLabel: WideString);
var
Lit: WideString;
begin
Lit := QuoteFormatLiteral(' ' + UnitLabel);
// positive; negative with explicit sign; zero hidden; text echoed
Cell.NumberFormat := '#,##0' + Lit + ';-#,##0' + Lit + ';;@';
end;
// UnitLabel = 'kg; net' -> #,##0" kg; net";-#,##0" kg; net";;@
// Expected FormattedText for 1250: '1,250 kg; net'
// UnitLabel = '12" pipe' -> the embedded quote is written as "\""
// Expected FormattedText for 3: '3 12" pipe'Conditions choose the section before anything is treated as a date
A bracketed comparison such as [>100] at the start of a section replaces the sign-based rules: the first section whose condition matches is used, and a section without a condition takes the rest. Only then does it make sense to ask whether the format is a date. Under [>=1]0.0" days";h:mm, 2.5 matches the days section and should read 2.5 days, while 0.25 falls through and should read 6:00. A detector that scans the whole code before choosing sees h:mm, decides the entire format is a time format, and prints 2.5 as a clock reading. HotXLS picks the matching section and runs date recognition on that section alone. These bracketed sections are separate from conditional formatting rules, which live outside the number format and change fonts and fills; the conditional format evaluation engine in HotXLS handles those
Quoted text never counts as a date token either. Engineering sheets label lengths with 0.0" mm", and a substring search for mm classifies that as a month or minute format and turns 12.5 into a date fragment instead of 12.5 mm; letters escaped with a backslash are literals in the same way. When the selected section really is a date or time, the remaining question is which serial it receives, and that depends on the workbook's date system, as covered in 1900 and 1904 serial handling in HotXLS
procedure ShowConditionalSections(Sheet: TXLSXWorksheet);
const
Duration = '[>=1]0.0" days";h:mm';
begin
Sheet.Cells[1, 1].Value := 2.5;
Sheet.Cells[1, 1].NumberFormat := Duration;
Sheet.Cells[2, 1].Value := 0.25;
Sheet.Cells[2, 1].NumberFormat := Duration;
Sheet.Cells[3, 1].Value := 12.5;
Sheet.Cells[3, 1].NumberFormat := '0.0" mm"'; // quoted mm is a literal
Writeln(Sheet.Cells[1, 1].FormattedText); // Expected: 2.5 days
Writeln(Sheet.Cells[2, 1].FormattedText); // Expected: 6:00
Writeln(Sheet.Cells[3, 1].FormattedText); // Expected: 12.5 mm
end;Hiding zeros in report templates: spell out all four sections
For a report template that must hide zero values, write the full form, 0;-0;;@, or #,##0;-#,##0;;@ with thousands separators, rather than leaning on whatever an omitted section falls back to. The reasoning is about what the next reader of the file has to get right. With every section written, the only rule a consumer needs is that an empty section displays nothing; each omitted section adds another fallback to reproduce, the shared zero section, the automatic minus sign, the text pass-through, and every one of those is a chance for a hand-rolled parser to disagree with Excel. The shape also extends cleanly: 0;[Red]-0;;@ colors negatives, and GetFormattedTextColor reports the color of the section actually selected, so a hidden zero reports none
Hiding changes only the display text: the stored value stays, and SUM still sees it unless the workbook turns on precision as displayed, which rounds stored values to the digits each format shows. When you generate such templates from Delphi, the HotXLS Delphi Excel Component writes the four-section code you assign to NumberFormat into the styles part unchanged apart from XML escaping