A support ticket arrives: "The timesheet workbook you generate is broken - our staff can't type into the hours column anymore." Only one detail changed in the last release: the generator started calling Protect on the sheet. No one touched the hours column, and that is exactly the bug. In the OOXML cell model each cell carries Locked = True by default, the flag remains dormant until sheet protection is enabled, and at that moment the entire grid freezes in one pass. HotXLS, a native Delphi and C++Builder spreadsheet library, exposes the full protection and page-setup surface of both .xls and .xlsx - so it also faithfully reproduces every counterintuitive Excel semantic in that surface. This guide walks through the ones that generate tickets.
UK teams should align this hotxls protection page setup printing workflow with local governance, audit, and data quality requirements before production release
Every cell starts locked
ECMA-376 defines locked as part of a cell's formatting record, not as a property of protection itself, and it defaults to true. Sheet protection is merely the switch that makes the flag enforceable. The correct generation sequence is therefore: build the layout, explicitly unlock the ranges users must edit, and only then protect:
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Timesheet');
// ... header row, name column, and rate formulas written here ...
Sheet.Range['B2:B50'].SetLocked(False); // staff type hours here
Sheet.Range['F2:F50'].SetFormulaHidden(True); // keep the rate math private
Sheet.Protect('review-2026'); // now the lock flags bite
Book.SaveAs('timesheet.xlsx');
finally
Book.Free;
end;SetFormulaHidden is worth noting: whilst protection is active, the cell still displays its computed value but the formula bar shows nothing. That matters where formulas embed billing rates, margins, or scoring weights you do not want to hand to every recipient who clicks a total. On the XLS facade the same intent is expressed per range through IXLSRange.Locked and FormulaHidden, and the worksheet adds fifteen Allow* flags - AllowSort, AllowAutoFilter, AllowFormatCells, and related flags - so a protected sheet can stay usable for sorting and filtering instead of becoming a sealed exhibit.
What the protection password is, and is not
Both formats store the sheet and workbook protection password as a 4-hex-digit legacy hash. Sixteen bits means countless strings collide with any given password, and removal tools are one search away, so treat protection as a seatbelt against accidental edits - never as access control. It suits 'stop reviewers from typing over the formula column' and the wrong tool for anything involving the word confidential.
At workbook level, ProtectWorkbook on the XLSX facade locks workbook structure: no adding, renaming, deleting, or reordering sheets. Set that whenever the sheet list is itself a contract with a downstream parser that indexes sheets by name or position - a renamed sheet breaks the import on the other side as reliably as a deleted column. The XLS facade follows the same layering with TXLSWorkbook.Protect at workbook level and per-sheet Protect calls, with isProtected available for code that needs to inspect inherited files before modifying them.
Where the requirement is actual confidentiality, the mechanism is different: SaveAsEncrypted produces an AES-encrypted package per the ECMA-376 Standard Encryption scheme, covered in depth in the AES-protected XLSX output guide, and the legacy XLS facade writes and reads RC4-encrypted .xls files through EncryptionPassword and the password overload of Open. The distinction is not academic - a protected sheet travels in cleartext and any zip tool can read its cell values, whilst an encrypted package is unreadable without the password. Audit requirements that say 'the payroll file must be protected' almost always mean the second mechanism, whatever vocabulary the requirement uses.
Page setup is part of the document contract
Print behaviour is invisible on screen, which explains why it ships broken so often. The moment a customer prints the workbook - or exports it to PDF for an auditor - margins, scaling, and repeated titles become functional requirements. On the XLSX facade the settings hang directly off the worksheet:
Sheet.PageLandscape := True;
Sheet.PaperSize := xlsxPaperA4;
Sheet.SetPageMargins(0.5, 0.5, 0.75, 0.75, 0.3, 0.3);
Sheet.CenterHeader := 'Monthly Timesheet';
Sheet.RightFooter := 'Page &P of &N';
Sheet.PrintArea := '$A$1:$F$60'; // bare reference: no sheet name here
Sheet.PrintTitleRows := '$1:$1'; // header row repeats on every page
Sheet.FitToWidth := 1;
Sheet.FitToHeight := 0; // grow downward as the data grows
Sheet.PrintGridlines := False;Two of those lines hide traps. The header and footer strings use Excel's formatting codes - &P for the current page, &N for the total count, with &L, &C, and &R available to address the three sections explicitly. And PrintArea takes a bare cell reference on purpose: HotXLS stores it unqualified and prefixes the sheet name when writing the file, so passing 'Timesheet!$A$1:$F$60' yourself produces a malformed reference. Related to that, print areas and print titles are persisted as the built-in defined names _xlnm.Print_Area and _xlnm.Print_Titles - never add _xlnm.* entries through DefinedNames manually, or the two mechanisms will fight over the same slot.
Scaling that survives production data volumes
The combination FitToWidth := 1 with FitToHeight := 0 reads as "always fit the columns across one page, then take as many pages down as the data needs," and it is the correct default for any report whose row count varies. The trap is tuning a fixed percentage or a fit-to-page pair against a thirty-row test file: feed the same settings six hundred production rows and the output either explodes into dozens of clipped pages or shrinks below legibility. Scale width, let length grow, and repeat the header row through PrintTitleRows so page seventeen is still readable on its own.
Manual breaks follow the same regeneration discipline as everything else in generated workbooks. AddRowBreak(BeforeRow) starts a new page before a section boundary, but when the generator reruns and rows shift, stale breaks land mid-table - so call ClearAllPageBreaks first and re-add breaks from the generator's own row counters instead of patching old positions. On the XLS facade the equivalent controls live on Sheet.PageSetup (orientation, paper size, margins, header and footer strings, fit-to-pages), with RepeatRows and RepeatColumns covering print titles.
Checking the result before a customer does
Protection and print bugs share a property: they are trivial to verify manually and almost never verified. Open the generated file in Excel and spend ninety seconds - type into an input cell (must succeed), type into a locked cell (must show the protection prompt), check that hidden formulas show a blank formula bar, and run Print Preview against a production-sized dataset to confirm the page count, the repeating title row, and the footer numbering. The preview step is the one that pays for itself: print geometry depends on settings that have no on-screen rendering, so it is the only place short of a physical printer where a scaling mistake becomes visible.
Two adjacent settings round out the review experience. FreezePane(ACol, ARow) keeps the header block in view whilst a reviewer scrolls - a screen behaviour, not a print one, but reviewers judge the whole deliverable together. And workbooks that start life as designer-maintained layouts get most of this article for free: the template report generation workflow keeps page setup in the template where a human tuned it against a real printer, leaving code to fill data and re-apply protection after the layout settles.
Frequently asked questions
Why is the whole sheet read-only after calling Protect?
Because each cell defaults to Locked = True and protection activates the flag globally. Call SetLocked(False) on the input ranges before Protect, not after.
Is the sheet protection password secure?
No. It is stored as a 16-bit legacy hash that collides and strips trivially. Use it to prevent accidents; use AES file encryption when the content itself must be protected.
How do I repeat the header row on every printed page?
Set PrintTitleRows := '$1:$1' on the XLSX facade, or call RepeatRows(0, 0) on the XLS facade's worksheet - both persist as the _xlnm.Print_Titles defined name Excel reads.
HotXLS is a native Object Pascal spreadsheet library for Delphi and C++Builder; the complete protection and page-setup API reference is on the HotXLS Component product page.