Un service batch a appliqué un AutoFilter pour retirer les lignes rejetées d'un classeur de commandes, puis a transmis le fichier à une deuxième étape qui l'a relu et a exporté les données « filtrées ». Toutes les lignes rejetées sont revenues. Le code était correct selon les règles de l'API et faux selon le modèle mental du développeur : un AutoFilter dans un classeur enregistré est une définition stockée, et le masquage des lignes se produit quand Excel ouvre le fichier et évalue les critères. HotXLS écrit fidèlement la définition — il ne découpe, ne masque ni ne supprime rien. Comprendre laquelle de ces trois fonctionnalités change les données, laquelle change la présentation et laquelle enregistre seulement une intention est le cœur de la génération de classeurs modifiables depuis Delphi qui se comportent dans Excel comme lors de vos tests.
AutoFilter : intention stockée, évaluation ailleurs
Sur la feuille XLSX, SetAutoFilter déclare la région filtrée et AddAutoFilterColumn attache des critères à une colonne de cette région. Quand le code serveur doit connaître le résultat — pour un décompte dans un résumé, ou pour transmettre seulement les lignes correspondantes — la bibliothèque fournit l'évaluation explicitement au lieu de prétendre que le fichier a changé :
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 répond ligne par ligne, et PreviewAutoFilterRows parcourt toute la région via un callback lorsque vous avez besoin de l'ensemble correspondant en une seule passe. Si l'exigence métier est que les lignes exclues ne soient pas présentes dans le fichier — suppression pour confidentialité, pas simple vue — supprimez les lignes ; un filtre est le mauvais outil, puisque n'importe quel destinataire peut l'effacer d'un clic.
L'identifiant de colonne est un offset, pas un numéro de colonne
Le commentaire de l'extrait ci-dessus marque la deuxième erreur classique de cette API. AddAutoFilterColumn identifie sa cible par la position 0-based dans la plage filtrée, pas par la colonne de feuille. Pour un filtre sur A1:E500, les deux systèmes ne diffèrent que d'une unité, exactement le genre de quasi-réussite qui survit à un test rapide ; pour un filtre commençant en colonne C, l'id 0 signifie colonne C et l'erreur devient évidente. Lorsque la plage filtrée est calculée à l'exécution, dérivez l'identifiant de colonne de la même variable que celle qui a construit la chaîne de plage, jamais d'une constante de colonne de feuille. Chaque colonne accepte une seconde condition via la surcharge prenant deux opérateurs, deux critères et un connecteur and/or, en miroir de la boîte de dialogue de filtre personnalisé d'Excel. La façade XLS couvre le même terrain avec SetAutoFilter plus ApplyAutoFilter, dont les paramètres de critères et d'opérateur suivent les anciennes conventions de style COM, y compris un numéro de champ 1-based — passer d'une façade à l'autre signifie aussi changer de base d'index, ce qui mérite un commentaire au point d'appel.
Règles de validation : le contrat sous lequel vos utilisateurs saisissent
La validation des données est la seule des trois fonctionnalités qui contraigne activement les futures saisies, et elle mérite le plus d'attention de conception dans les classeurs envoyés pour complétion puis relus pour traitement. La variante liste est l'outil de base :
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;Au-delà des listes et des nombres entiers, la même famille couvre les décimaux, dates, heures, longueurs de texte et formules libres via AddCustomValidation, tandis que le générique AddDataValidation expose toute la matrice type-opérateur pour les constructeurs de règles pilotés par configuration. Le style d'erreur compte plus qu'il n'y paraît : xlsxDvErrStop rejette franchement une mauvaise saisie, tandis que les styles avertissement et information la laissent passer après un clic — choisissez colonne par colonne selon la tolérance du code aval. Deux limites sont à documenter pour vos utilisateurs : la validation dans Excel encadre la saisie au clavier, mais coller un bloc sur une plage validée peut contourner le contrôle, donc le code de lecture doit encore valider ; et une règle couvre la plage littérale fournie, donc appliquer la validation avant de connaître le nombre final de lignes laisse la queue ajoutée sans garde. Écrivez les données, puis attachez des règles dimensionnées à l'étendue réelle.
La façade legacy offre les mêmes familles de règles avec une différence ergonomique : les créateurs côté XLS — AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation, AddCustomValidation — renvoient directement l'objet TDataValidation plutôt qu'un index, donc la configuration des messages et erreurs s'enchaîne depuis cette référence. L'énumération des opérateurs (xlsDvBetween, xlsDvGreaterThan et les autres) reflète l'ensemble XLSX, ce qui garde le code de construction de règles portable entre façades, en dehors de la différence de style de retour. Dans les deux cas, le texte d'invite mérite autant de soin que la règle elle-même : une liste déroulante qui rejette une saisie avec une boîte d'erreur vide apprend aux utilisateurs à appeler l'IT, tandis qu'une liste qui indique exactement quels états sont permis leur apprend à corriger eux-mêmes.
Une inversion de polarité absorbée par la bibliothèque
Quiconque a déjà lu à la main le XML de validation OOXML connaît l'attribut showDropDown inversé — dans ISO/IEC 29500, il signifie « masquer la flèche de liste déroulante ». HotXLS inverse cela en interne afin que la propriété ShowDropDown d'une règle de validation signifie bien ce qu'elle dit : true affiche la liste. Le seul moyen de se brûler est de mélanger les niveaux — définir la propriété depuis le code pendant qu'un collègue audite le XML enregistré et « corrige » l'attribut apparemment faux. Choisissez un seul niveau de vérité pour les outils de revue et notez-y l'inversion.
Tables : donner un schéma et un nom à une plage
Une table de feuille (ListObject) enveloppe une plage avec un nom, des colonnes typées, un style en bandes et le support des références structurées ; c'est ce qui donne à un classeur généré l'aspect d'un livrable fini lorsque les utilisateurs le trient et l'étendent. La création est symétrique entre façades — AddTable prend un nom, une plage et une liste de colonnes :
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;Côté XLSX, l'objet table résultant expose StyleName (la famille intégrée TableStyleMedium2 et ses voisines), les options de bandes et un indicateur de ligne de totaux ; le style maison devient donc une affectation de propriété plutôt qu'une passe de formatage. Dans les fichiers .xls legacy, le même appel écrit les enregistrements de table BIFF8, et la façade propose en plus AddPivotTable pour les vues de synthèse avec champs de ligne, colonne et données — rappel que les « tables » du format legacy sont une mécanique plus riche et plus ancienne que le ListObject OOXML. Nommez les tables comme vous nommez des vues de base de données : le code aval qui lit Orders[Amount] par référence structurée survit à un réordonnancement des colonnes que du code positionnel ne tolère pas.
Deux conventions évitent du nettoyage plus tard. Excel exige que les noms de tables soient uniques dans tout le classeur ; un générateur qui émet une feuille par région a donc besoin d'un schéma comme Orders_EMEA plutôt que de réutiliser Orders — le doublon n'échoue pas à l'écriture, il échoue dans la boîte de réparation d'Excel. Et lorsque la ligne de totaux est activée, souvenez-vous qu'elle se trouve sous la plage de données : tout code qui ajoute ensuite des lignes par « dernière ligne utilisée plus un » écrira dans la bande de totaux ; suivez l'étendue des données séparément de l'étendue de la table.
Les trois fonctionnalités se composent naturellement dans les livrables de saisie : une table définit la région modifiable, la validation contraint les colonnes que les utilisateurs remplissent, et un filtre prédéfini épargne au destinataire les trois premiers clics. La moitié amont de cette chaîne — charger efficacement les résultats de requête dans la feuille — est le sujet de l'export de résultats de base de données vers Excel depuis Delphi, et les classeurs où des formules résument les données validées bénéficient des noms définis pour des références interfeuilles stables.
FAQ : comportement de validation et de filtrage
Pourquoi les lignes filtrées restent-elles dans mes données exportées ? Parce qu'un filtre est une définition évaluée par l'application qui ouvre le fichier. Utilisez AutoFilterRowVisible ou PreviewAutoFilterRows pour appliquer les critères dans votre propre code, ou supprimez les lignes qui ne doivent pas être livrées.
Une validation de liste empêche-t-elle les utilisateurs de coller des valeurs invalides ? Non — le collage d'Excel peut écraser des cellules validées sans déclencher la règle. Traitez la validation comme une aide à la saisie et gardez des contrôles côté serveur sur tout ce que vous relisez.
Puis-je préappliquer un filtre pour que le classeur s'ouvre déjà filtré ? Oui — écrivez les critères avec AddAutoFilterColumn puis enregistrez ; Excel évalue et masque les lignes non correspondantes à l'ouverture. N'oubliez simplement pas que ces lignes restent présentes dans le fichier.
Validation, filtres et tables font la différence entre livrer une grille de valeurs et livrer une petite application. La référence complète des règles, filtres et tables se trouve sur la page produit HotXLS Component.