A batch service applied an AutoFilter to drop rejected rows from an order workbook, then handed the file to a second stage that read it back and exported the "filtered" data. Every rejected row came through. The code was correct by the API's rules and wrong by the developer's mental model: an AutoFilter in a saved workbook is a stored definition, and the row hiding happens when Excel opens the file and evaluates the criteria. HotXLS writes the definition faithfully — it does not crop, hide, or delete anything. Understanding which of these three features changes data, which changes presentation, and which merely records intent is the core of generating editable workbooks from Delphi that behave the same way in Excel as they did in your tests.
AutoFilter: stored intent, evaluated elsewhere
On the XLSX worksheet, SetAutoFilter declares the filtered region and AddAutoFilterColumn attaches criteria to one column of it. When server-side code needs to know the outcome — for a row count in a summary, or to feed only matching rows onward — the library provides the evaluation explicitly instead of pretending the file changed:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R, Visible: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
// Column id 3 = fourth column INSIDE the filter range (0-based offset)
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
Visible := 0;
for R := 2 to 500 do
if Sheet.AutoFilterRowVisible(R) then
Inc(Visible);
// Visible now matches what Excel will show after opening the file
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;AutoFilterRowVisible answers per row, and PreviewAutoFilterRows walks the whole region through a callback when you need the matching set in one pass. If the business requirement is that excluded rows must not be in the file at all — a privacy cut, not a view — delete the rows; a filter is the wrong tool, since any recipient can clear it with one click.
The column id is an offset, not a column number
The comment in the snippet above marks the second classic mistake in this API. AddAutoFilterColumn identifies its target by the 0-based position within the filter range, not by worksheet column. For a filter on A1:E500 the two systems happen to differ by one, which is exactly the kind of near-miss that survives a quick test; for a filter starting at column C, id 0 means column C and the error becomes obvious. When the filter range is computed at runtime, derive the column id from the same variable that built the range string, never from a worksheet column constant. Each column accepts a second condition through the overload taking two operators, two criteria, and an and/or connector, mirroring Excel's custom filter dialog. The XLS facade covers the same ground with SetAutoFilter plus ApplyAutoFilter, whose criteria and operator parameters follow the older COM-style conventions, including a 1-based field number — switching between facades means switching index bases, which is worth a comment at the call site.
Validation rules: the contract your users edit under
Data validation is the one feature of the three that actively constrains future input, and it earns the most design attention in workbooks that go out for completion and come back for processing. The list variant is the workhorse:
var
Idx: Integer;
begin
Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
Sheet.DataValidations[Idx].SetPrompt('Status',
'Pick one of the listed states');
Sheet.DataValidations[Idx].SetError('Invalid status',
'Type or paste only listed values', xlsxDvErrStop);
Sheet.DataValidations[Idx].AllowBlank := False;
// Quantities: whole numbers, zero or more
Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;Beyond lists and whole numbers, the same family covers decimals, dates, times, text length, and free-form formulas via AddCustomValidation, with the generic AddDataValidation exposing the full type-and-operator matrix for rule builders driven by configuration. The error style matters more than it appears: xlsxDvErrStop rejects bad input outright, while the warning and information styles let the value through after a click — choose per column based on whether downstream code can tolerate surprises. Two boundaries to document for your users: validation in Excel guards typing, but pasting a block over a validated range can bypass the check, so reading code must still validate; and a rule covers the literal range you gave it, so applying validation before you know the final row count leaves the appended tail unguarded. Write the data, then attach rules sized to the actual extent.
The legacy facade offers the same rule families with one ergonomic difference: the XLS-side creators — AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation, AddCustomValidation — return the TDataValidation object directly rather than an index, so prompt and error configuration chains off the returned reference. The operator enumeration (xlsDvBetween, xlsDvGreaterThan and the rest) mirrors the XLSX set, which keeps rule-building code portable between facades apart from the return-style difference. Either way, the prompt text deserves as much care as the rule itself: a dropdown that rejects input with a blank error box trains users to ask IT, while one that says exactly which states are legal trains them to fix it themselves.
One polarity flip the library absorbs for you
Anyone who has hand-read OOXML validation XML knows the showDropDown attribute is inverted — in ISO/IEC 29500 it means "suppress the dropdown arrow". HotXLS flips this internally so that the ShowDropDown property on a validation rule means what it says: true shows the dropdown. The only way to get burned is to mix levels — setting the property from code while a colleague audits the saved XML and "fixes" the seemingly wrong attribute. Pick one level of truth for review tooling and note the inversion there.
Tables: giving a range a schema and a name
A worksheet table (ListObject) wraps a range with a name, typed columns, banded styling, and structured-reference support, and it is what makes a generated workbook feel finished when users sort and extend it. Creation is symmetrical across facades — AddTable takes a name, a range, and a column list:
var
Cols: TStringList;
begin
Cols := TStringList.Create;
try
Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
Sheet.AddTable('Orders', 'A1:E500', Cols);
finally
Cols.Free;
end;
end;On the XLSX side the resulting table object exposes StyleName (the built-in TableStyleMedium2 family and its siblings), stripe toggles, and a totals-row flag, so house styling is a property assignment rather than a formatting pass. In legacy .xls files the same call writes the BIFF8 table records, and the facade additionally offers AddPivotTable for summary views with row, column, and data fields — a reminder that "tables" in the legacy format are a richer, older machinery than the OOXML ListObject. Name tables the way you name database views: downstream code that reads Orders[Amount] by structured reference survives column reordering that positional code does not.
Two conventions save cleanup work later. Excel requires table names to be unique across the whole workbook, so a generator that emits one sheet per region needs a naming scheme like Orders_EMEA rather than reusing Orders — the duplicate does not fail at write time, it fails in Excel's repair dialog. And when the totals row is enabled, remember that it sits below the data range, so any code that later appends rows by "last used row plus one" will write into the totals band; track the data extent separately from the table extent.
The three features compose naturally in data-entry deliverables: a table defines the editable region, validation constrains the columns users type into, and a pre-set filter spares the recipient the first three clicks. The upstream half of that pipeline — getting query results into the sheet efficiently — is the subject of exporting database results to Excel from Delphi, and workbooks where formulas summarize the validated data benefit from defined names for stable cross-sheet references.
FAQ: validation and filtering behavior
Why are filtered-out rows still in my exported data? Because a filter is a definition evaluated by the opening application. Use AutoFilterRowVisible or PreviewAutoFilterRows to apply the criteria in your own code, or delete rows that must not ship.
Does a list validation stop users pasting invalid values? No — Excel's paste can overwrite validated cells without triggering the rule. Treat validation as guidance for typists and keep server-side checks on anything you read back.
Can I pre-apply a filter so the workbook opens already filtered? Yes — write the criteria with AddAutoFilterColumn and save; Excel evaluates and hides the non-matching rows on open. Just remember the rows remain present in the file.
Validation, filters, and tables are the difference between shipping a grid of values and shipping a small application. The complete rule, filter, and table reference is on the HotXLS Component product page.