The support ticket reads: "The timesheet workbook you generate is broken - our staff can't type into the hours column anymore." Only one thing changed in the last release: the generator started calling Protect on the sheet. Nobody touched the hours column, and that is precisely the bug. In the OOXML cell model every cell carries Locked = True by default, the flag stays dormant until sheet protection switches on, and at that moment the entire grid freezes at once. HotXLS, a native Delphi and C++Builder spreadsheet library, exposes the full protection and page-setup surface of both .xls and .xlsx - which means it also faithfully reproduces every counterintuitive Excel semantic in that surface. This article walks through the ones that generate tickets.
Every cell is born 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 order therefore runs: 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 deserves a note: while protection is active, the cell still displays its computed value but the formula bar shows nothing. That matters when formulas embed billing rates, margins, or scoring weights you would rather not 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 friends - 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 is the right tool for "stop reviewers from typing over the formula column" and the wrong tool for anything involving the word confidential.
One level up, ProtectWorkbook on the XLSX facade locks workbook structure: no adding, renaming, deleting, or reordering sheets. That is worth setting 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 just as surely as a deleted column. The XLS facade mirrors the 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.
When the requirement is actual confidentiality, the mechanism changes entirely: 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, while 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 behavior is invisible on screen, which is 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 rather than 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 while a reviewer scrolls - a screen behavior, 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 every 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.