Validation, AutoFilter et tableaux HotXLS en Delphi

Trois fonctionnalités de HotXLS partagent une feuille de calcul mais opèrent sur des objets entièrement différents, et les ennuis commencent quand vous supposez qu'elles font des choses semblables. La validation des données attache à une plage une règle qui contraint ce qu'un utilisateur peut y saisir. Un AutoFilter attache à une région une définition de critères enregistrée et change les lignes qu'une visionneuse affiche. Un tableau enveloppe une plage dans une structure nommée et typée avec un style à bandes. L'un contraint la saisie, l'autre enregistre une vue, le troisième impose un schéma. Aucun ne déplace à lui seul la moindre valeur de cellule, et l'AutoFilter en particulier trompe son monde, car le mot suggère une action alors qu'il ne stocke qu'une définition. Savoir quel objet chaque appel touche, et quand l'effet se matérialise réellement, voilà ce qui sépare un classeur qui se comporte dans Excel comme dans vos tests d'un classeur qui diverge en silence

AutoFilter stocke une définition, il ne rogne pas les lignes

Un AutoFilter dans un fichier enregistré est un enregistrement de critères. Le masquage des lignes se produit plus tard, quand Excel ouvre le classeur et évalue les critères sur les données. HotXLS écrit fidèlement cet enregistrement et ne rogne rien : chaque ligne que vous avez filtrée est toujours physiquement présente dans le fichier. Un pipeline qui applique un filtre pour écarter les commandes rejetées puis relit le classeur les verra toutes, rejetées comprises, et le code est correct au regard de l'API tout en étant faux au regard du modèle mental de son auteur. Sur la feuille XLSX, SetAutoFilter déclare la région filtrée et AddAutoFilterColumn attache des critères à l'une de ses colonnes. Quand du code côté serveur a besoin du résultat réel, pour un décompte de lignes dans une synthèse ou pour ne transmettre que les lignes correspondantes, la bibliothèque évalue les critères à votre place au lieu de faire croire 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');
    // Identifiant 3 = quatrième colonne À L'INTÉRIEUR de la plage filtrée (décalage en base zéro)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible correspond désormais à ce qu'Excel affichera à l'ouverture du fichier

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible répond ligne par ligne, et PreviewAutoFilterRows parcourt toute la région au travers d'un callback quand vous avez besoin de l'ensemble correspondant en une seule passe. Il existe un cas où ni l'un ni l'autre n'est la bonne réponse : si l'exigence est que les lignes exclues ne doivent pas exister du tout dans le fichier, une coupe pour raisons de confidentialité et non une vue, supprimez purement et simplement les lignes. Un filtre est le mauvais outil là, car n'importe quel destinataire l'efface d'un clic et les données que vous vouliez retenir sont de retour à l'écran

L'identifiant de colonne est un décalage, pas un numéro de colonne

Le commentaire de l'extrait ci-dessus signale le piège qui coûte le plus de temps de débogage dans cette API. AddAutoFilterColumn identifie sa cible par la position en base zéro à l'intérieur de la plage filtrée, pas par la colonne de la feuille. Pour un filtre sur A1:E500, les deux systèmes de numérotation se trouvent différer d'une unité, ce qui est exactement le genre de quasi-coïncidence qui survit à un test rapide et casse dès qu'un collègue filtre une autre colonne. Pour un filtre qui commence à la colonne C, l'identifiant 0 désigne la colonne C, et le décalage devient vite évident. Quand la plage filtrée est calculée à l'exécution, dérivez l'identifiant de colonne de la même variable 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 qui prend deux opérateurs, deux critères et un connecteur et/ou, ce qui reflète 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 et numérotent le champ à partir de 1. Changer de façade signifie changer de base d'indexation, donc le site d'appel mérite un commentaire indiquant laquelle est en jeu

Les règles de validation sont le contrat sous lequel vos utilisateurs saisissent

Des trois fonctionnalités, la validation est la seule qui contraigne activement les saisies à venir, et c'est elle qui mérite le plus d'attention de conception dans les classeurs qui partent pour être complétés et reviennent pour être traités. La variante liste porte l'essentiel de ce travail :

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;

  // Quantités : nombres entiers, zéro ou plus
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Au-delà des listes et des nombres entiers, la même famille couvre les décimaux, les dates, les heures, la longueur de texte et les formules libres via AddCustomValidation, et la fonction 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 davantage que son nom ne le laisse croire. xlsxDvErrStop rejette purement et simplement une saisie invalide ; les styles avertissement et information laissent passer la valeur après un simple clic. Choisissez colonne par colonne selon que le code qui relit le classeur peut tolérer ou non une valeur hors règle. Deux limites méritent de figurer dans le texte d'invite ou dans le fichier README que vous livrez avec le fichier. La validation dans Excel garde la saisie, mais coller un bloc par-dessus une plage validée passe outre la règle, si bien que tout code qui relit les données doit valider de nouveau plutôt que de faire confiance aux cellules. Et une règle couvre la plage littérale que vous lui avez donnée, ce qui veut dire qu'attacher la validation avant de connaître le nombre final de lignes laisse la queue ajoutée sans protection. Écrivez d'abord les données, puis dimensionnez les règles sur l'étendue réelle

La façade héritée offre les mêmes familles de règles avec une différence d'ergonomie. Les créateurs côté XLS, à savoir AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation et AddCustomValidation, renvoient directement l'objet TDataValidation plutôt qu'un index, si bien que la configuration de l'invite et de l'erreur se chaîne sur la référence renvoyée au lieu de passer par une recherche. L'énumération des opérateurs (xlsDvBetween, xlsDvGreaterThan et les autres) reflète l'ensemble XLSX, de sorte que le code de construction des règles se porte d'une façade à l'autre à cette différence de style de retour près. Le texte d'invite lui-même mérite autant de réflexion que la règle. Une liste déroulante qui rejette une saisie avec une boîte d'erreur vide apprend aux utilisateurs à écrire au service informatique ; une liste qui nomme les états autorisés leur apprend à corriger la cellule et à passer à la suite

Une inversion de polarité que la bibliothèque absorbe pour vous

Quiconque a lu à la main du XML de validation OOXML a rencontré l'attribut showDropDown inversé : dans la norme ISO/IEC 29500, une valeur vraie signifie « supprimer la flèche de la liste déroulante », l'inverse de ce que le nom laisse lire. HotXLS opère l'inversion en interne, de sorte que la propriété ShowDropDown d'une règle de validation veut dire ce qu'elle dit, la valeur vraie affichant la liste déroulante. La seule façon de se brûler est de mélanger les niveaux de vérité, en posant la propriété depuis le code pendant qu'un collègue audite le XML enregistré et « corrige » l'attribut qui lui paraît à l'envers. Décidez si la propriété ou le XML brut fait autorité pour l'outillage de revue, et consignez l'inversion là où cette décision est écrite

Les tableaux donnent à une plage un schéma et un nom

Un tableau de feuille de calcul, le ListObject dans le vocabulaire d'Excel, enveloppe une plage dans un nom, des colonnes typées, un style à bandes et la prise en charge des références structurées. C'est la fonctionnalité qui donne à un classeur généré un air fini dès que les utilisateurs commencent à le trier et à l'étendre. La création est symétrique entre les façades, AddTable prenant 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;

Du côté XLSX, l'objet tableau obtenu expose StyleName (la famille intégrée TableStyleMedium2 et ses proches), des bascules de rayures et un indicateur de ligne de totaux, si bien qu'appliquer le style maison relève de l'affectation de propriété plutôt que d'une passe de mise en forme manuelle. Dans les fichiers .xls hérités, le même appel écrit les enregistrements de tableau BIFF8, et la façade offre aussi AddPivotTable pour des vues de synthèse construites à partir de champs de ligne, de colonne et de données, rappel que les « tableaux » dans l'ancien format vont plus loin que le ListObject OOXML. Nommez les tableaux comme vous nommez les vues de base de données. Le code en aval qui lit Orders[Amount] par référence structurée survit au réordonnancement des colonnes qui casse le code positionnel

Deux conventions épargnent du nettoyage plus tard. Excel exige que les noms de tableaux soient uniques dans tout le classeur, donc un générateur qui émet une feuille par région a besoin d'un schéma du genre Orders_EMEA plutôt que de réutiliser Orders. Un doublon n'échoue pas à l'écriture ; il ressort sous forme de boîte de dialogue de réparation quand l'utilisateur ouvre le fichier, soit le pire endroit pour le découvrir. L'autre convention concerne la ligne de totaux : lorsqu'elle est activée, elle se place juste sous la plage de données, si bien que tout code qui ajoute ensuite selon la règle « dernière ligne utilisée plus un » écrit dans la bande des totaux au lieu d'écrire après elle. Suivez l'étendue des données séparément de l'étendue du tableau et les ajouts atterriront là où vous l'attendez

Les trois fonctionnalités se combinent naturellement dans les livrables de saisie de données. Un tableau définit la région modifiable, la validation contraint les colonnes que les utilisateurs remplissent, et un filtre pré-réglé épargne au destinataire les premiers clics. Il y a un argument recevable en faveur de la livraison d'un filtre déjà appliqué pour que le classeur s'ouvre centré sur les lignes qui comptent, tant que vous vous souvenez que les lignes exclues sont toujours dans le fichier et qu'un destinataire curieux peut les révéler. Faire entrer efficacement des résultats de requête dans la feuille, la moitié amont de ce pipeline, est traité dans 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 gagnent à s'appuyer sur des noms définis pour des références stables entre feuilles

La validation, les filtres et les tableaux font la différence entre livrer une grille de valeurs et livrer une petite application. La référence complète des règles, des filtres et des tableaux se trouve sur la page produit du composant Delphi HotXLS