Listes déroulantes HotXLS et validation d'entrée en Delphi

HotXLS lit les listes déroulantes de validation de données Excel directement depuis du code Delphi. TryGetCellDropdownItems renvoie les chaînes candidates d'une cellule, SelectCellDropdownItem en écrit une par index ou par texte, et ValidateCellInput rapporte si une valeur saisie passerait la règle de la cellule avant que quoi que ce soit ne soit stocké. Les mêmes méthodes existent sur TXLSWorksheet pour le XLS Classic et sur TXLSXWorksheet pour le XLSX, et toutes deux prennent des numéros de ligne et colonne à base 1

Le déclencheur habituel de ce besoin, c'est un modèle que vous n'avez pas conçu. Quelqu'un du côté métier livre un classeur avec une colonne Statut verrouillée sur Draft, Review et Final, une colonne Canal alimentée depuis une plage d'une feuille cachée, et une colonne quantité qui n'accepte que 1 à 100. Votre job d'import, votre formulaire web ou votre remplisseur en lot doit désormais respecter ces règles, et les règles vivent dans le classeur, pas dans votre code. Coder en dur les valeurs admises à côté du modèle, voilà comment les deux finissent par diverger la première fois que quelqu'un édite la liste dans Excel

Pourquoi les écritures par code ignorent-elles la validation de données Excel ?

La validation de données est une métadonnée attachée à une plage, et son application est le travail de l'éditeur dans lequel l'utilisateur saisit. En XLSX, la règle est un élément dataValidation (ECMA-376 Partie 1, §18.3.1.32) portant le type, l'opérateur, formula1 et formula2, l'invite et l'alerte d'erreur. Dans un fichier XLS Classic, la même information se trouve dans l'enregistrement DV décrit dans [MS-XLS]. Aucun des deux formats ne marque la cellule elle-même, donc rien n'empêche un programme de stocker une valeur qui enfreint la règle. Excel se comporte pareil : il contrôle les saisies, et une valeur hors plage arrivée par d'autres moyens ne surgit que quand quelqu'un lance Entourer les données non valides

HotXLS suit ce modèle exprès. Une affectation telle que Sheet.Cells[3, 2].Value := 250 stocke 250 sous une règle 1-à-100 sans broncher, exactement comme le fait la démo DataValidation livrée avec pour prouver le point. Le contrôle est opt-in, et le reste de cet article parle d'opter au bon endroit

Lire les éléments de liste déroulante derrière une cellule

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) renvoie True seulement quand la cellule est couverte par une règle de type liste, et remplit AItems avec un TArray<WideString> de candidates. HotXLS résout les quatre façons dont une source de liste s'écrit en pratique, pour que l'appelant ne parse jamais formula1 à la main :

• Une liste inline quotée telle que "Draft,Review,Final", coupée sur les virgules
• Une liste inline dont les éléments sont séparés par des caractères #0, une forme présente dans les règles Classic XLS
• Une plage sur la même feuille ou sur une autre, telle que =$G$2:$G$10 ou =Lists!$A$2:$A$7, évaluée à ses valeurs de cellules actuelles
• Un nom défini tel que =ProductList qui pointe vers une de ces plages

Les cellules fusionnées sont gérées aussi. Demandez n'importe quelle cellule dans une fusion et HotXLS cherche la règle à l'ancre de la fusion, là où Excel garde à la fois la règle et la valeur

Le drapeau AShowDropDown mérite une note, car les formats de fichier le stockent à l'envers. En XLSX, showDropDown="1" cache en réalité la flèche dans la cellule, et en BIFF8 le bit équivalent est un drapeau « suppress combo ». HotXLS retourne les deux à la lecture, donc AShowDropDown = True veut dire ce qu'il dit : Excel afficherait la flèche. Une règle de liste à flèche cachée restreint quand même la saisie, voilà pourquoi TryGetCellDropdownItems renvoie les éléments dans les deux cas

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2 : ligne 2, colonne 6, toutes deux à base 1
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

Si vous ne voulez que les chaînes, GetCellDropdownItems(ARow, ACol) renvoie le tableau directement et produit un tableau vide quand aucune liste ne s'applique. TryGetCellValidationPrompt renvoie le titre et le texte du message de saisie, ce qui est pratique quand vous voulez montrer l'indice de l'auteur du modèle à côté d'un champ de formulaire web

Sélectionner un élément par index ou par texte

SelectCellDropdownItem écrit un élément de liste dans la cellule et renvoie False quand la cellule n'a pas de règle de liste ou que l'élément demandé n'existe pas. La surcharge entière prend un index à base 0 dans le tableau renvoyé par TryGetCellDropdownItems. La surcharge texte cherche d'abord une correspondance exacte puis retombe sur une correspondance insensible à la casse, donc 'review' sélectionne quand même Review

Les deux surcharges écrivent via WriteAutoValue, pas via une affectation de chaîne brute. Cela compte pour les listes adossées à des plages de nombres : les éléments reviennent en chaînes comme '3', mais la valeur stockée est le nombre 3, ce que stocke Excel quand un utilisateur choisit depuis la flèche et ce qu'attend toute formule référençant la cellule. La démo DataValidation vérifie exactement cela après une sauvegarde et réouverture en XLS, XLSX et XLSB

// Mêmes appels sur TXLSWorksheet (XLS) et TXLSXWorksheet (XLSX)
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 4e élément, base 0
  Writeln('F2: index 3 is out of range');

Comment valider une saisie avant de l'écrire ?

ValidateCellInput(ARow, ACol, AText) teste une chaîne candidate contre la règle couvrant la cellule et ne modifie jamais la cellule. Elle renvoie True quand aucune règle ne s'applique ou que la candidate passe. La chaîne passe par la même conversion texte-vers-valeur qu'utilise WriteAutoValue, donc le contrôle et l'écriture éventuelle voient la même valeur. Côté XLSX, cette conversion est XlsxAutoTextValue : elle reconnaît TRUE/FALSE, les literals d'erreur, les dates ISO, les dates m/d/yyyy et d.m.yyyy et les nombres au format invariant, et elle garde 0123 comme texte à la manière d'Excel. Le moteur Classic XLS a son propre convertisseur qui reflète son propre WriteAutoValue

Deux compagnes complètent l'ensemble. ValidateCellValue(ARow, ACol) contrôle la valeur déjà stockée dans la cellule et traite les cellules vides comme valides. TryGetCellValidationError renvoie le titre, le texte et le style d'alerte d'erreur de la règle, et renvoie False quand la règle n'affiche aucune alerte d'erreur du tout. Le type de style diffère par moteur, donc appariez-le à la classe de feuille : TXLSDvErrorStyle de lxDataCheck pour TXLSWorksheet, TXLSXDvErrorStyle pour TXLSXWorksheet

Un comportement que vous devez reproduire vous-même : Excel ne valide pas une entrée vide ni rien qui commence par =. HotXLS applique ce saut dans le chemin d'édition du viewer, pas dans ValidateCellInput, donc une barrière côté serveur devrait faire pareil avant de l'appeler :

uses
  lxDataCheck, lxHandle;

// Renvoie True quand AText peut être écrit, reflétant les styles d'alerte d'Excel
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel saute la validation pour les cellules vidées et les formules
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Aucune alerte d'erreur configurée : Excel laisse tout passer
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop refuse ; Warning et Information laissent la décision à l'appelant
  Result := Style <> xlsDvErrStop;
end;

// Appelant (Msg: WideString) : valider, puis stocker avec la même conversion
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Accoupler ValidateCellInput à WriteAutoValue est tout le sens de l'exemple. Validez avec une conversion et stockez avec une autre, et tôt ou tard une valeur passe le contrôle en tant que nombre et atterrit dans la cellule en tant que texte

Éléments de liste quotés et piège d'échappement du Classic XLS

Les éléments de liste inline qui contiennent des guillemets doubles sont une façon facile de perdre une règle en silence. Une liste inline est stockée comme une chaîne de formule, et une chaîne de formule échappe un " embarqué en le doublant. Enveloppez les éléments dans des guillemets sans doubler les internes, et une liste comme He said "hi",No produit une formule qui ne compile pas. Pas d'exception : la règle n'est simplement pas créée, et la liste déroulante s'en va avec. TXLSWorksheet.AddListValidation et son pendant XLSX doublent tous les deux les guillemets embarqués, donc de tels éléments survivent intacts

TXLSWorksheet.AddListValidation a aussi une surcharge array of WideString, jumelle de celle de TXLSXWorksheet. C'est pratique, mais lisez ce qu'elle fait : elle joint les éléments avec des virgules et remet le résultat à la surcharge chaîne. Un élément qui contient lui-même une virgule devient donc deux éléments, et sur le moteur Classic une chaîne source contenant ! est prise pour une référence qualifiée par feuille plutôt que pour une liste inline. Pour des libellés de ce genre, mettez les valeurs dans une plage de cellules et pointez la règle vers elle

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Confirmez que la règle existe avant de compter sur elle
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Après une sauvegarde et réouverture, TryGetCellDropdownItems sur H2 renvoie He said "hi" avec ses guillemets intacts, en sortie XLS, XLSX, XLSB et ODS de même

Laisser TXLSWorkbookViewer appliquer les règles

Quand les utilisateurs éditent des cellules à l'écran, TXLSWorkbookViewer applique les mêmes contrôles pour vous. Avec Editable à True et EnforceDataValidation laissé à son défaut True, une édition saisie qui enfreint une règle avec alerte d'erreur est contrôlée avant d'être écrite : Stop la refuse, Warning demande, Information informe. Le viewer dessine aussi la flèche déroulante sur la cellule active, ouvre la liste par un clic ou Alt+Down, et montre l'invite de saisie jaune sous la cellule. Les détails de la grille elle-même sont couverts dans l'article sur la construction d'une grille de tableur personnalisée avec TXLSWorkbookViewer

Mettre CircleInvalidData à True entoure de cercles rouges les valeurs stockées qui enfreignent leur règle, comme la commande Excel du même nom. Elle ne scanne que la zone visible, écarte la plupart des cellules vite fait via FindDataValidationAt, et n'entoure jamais une cellule vide

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // rouvrir l'éditeur avec le texte refusé
  else
    Action := xvaAccept;
  Handled := True;        // supprimer le dialogue intégré
end;

Laissez Handled à False et le viewer montre son propre dialogue d'alerte, qui marche sous VCL comme sous LCL. Ce défaut mord dans les tests automatisés : un test qui pilote des éditions sans un handler OnValidationError posant Handled restera bloqué sur un dialogue modal. L'enum de style du viewer est un troisième type, TXLSViewerValidationErrorStyle avec xveStop, xveWarning et xveInformation, distinct des deux enums de feuille

Que savoir avant de compter sur la validation d'entrée HotXLS ?

Les contrôles sont fidèles pour les règles de liste, nombre, date, heure et longueur de texte, et quelques détails propres aux moteurs valent d'être connus :

• Sur les feuilles Classic XLS, ValidateCellInput rapporte les règles à formule personnalisée comme valides pour les candidates saisies, car une candidate ne peut pas être placée temporairement dans une cellule BIFF sans perturber la chaîne de dépendances. Contrôlez la valeur stockée avec ValidateCellValue, qui évalue la formule personnalisée, ou utilisez le moteur XLSX, qui évalue les règles personnalisées pour les candidates aussi
• Sur Classic XLS, l'appariement de liste traite les valeurs numériquement égales comme le même élément, donc 5 et 5.0 correspondent tous deux à un élément de 5
• Dans le viewer, le contenu collé saute la validation, comme dans Excel. EditSelectedCell n'a pas d'éditeur à rouvrir, donc un résultat Retry s'y comporte comme Cancel
• Les affectations Cells[...].Value brutes ne sont jamais contrôlées, c'est voulu. La validation n'arrive que là où votre code la demande

La création des règles à la base, types entier, date, longueur de texte et personnalisé compris, est couverte dans le guide HotXLS de la validation de données, AutoFilter et tables. Les classeurs XLSB se chargent dans TXLSXWorkbook, donc chaque appel TXLSXWorksheet montré ici marche inchangé sur eux, comme décrit dans l'article sur la lecture et l'écriture de classeurs binaires XLSB en Delphi

La résolution des listes déroulantes, la sélection d'éléments et la validation d'entrée se livrent dans le composant Excel HotXLS pour Delphi pour Delphi, C++Builder et Lazarus, avec les démos DataValidation et WorkbookViewer qui exercent chaque appel de cet article