Validation de liste HotXLS : limite 255 caractères et x14

Excel refuse d'ouvrir un classeur XLSX dont la validation de données de liste contient une liste inline de plus de 255 caractères, et cela vaut même pour un fichier qu'Excel a lui-même sauvegardé. HotXLS, le composant Excel natif pour Delphi et C++Builder, charge ces listes et modélise les règles de listes entre feuilles comme extensions x14, mais la réparation durable est la même partout : gardez les longues listes dans une plage de feuille ou un nom défini

Le rapport de bug arrive en général d'un client, pas de vos tests. Un modèle voit sa colonne Région ou Produit grossir, dont la liste déroulante a démarré à huit entrées et en compte désormais soixante. Tout marche encore dans la copie XLSB que quelqu'un garde sur un partage. Puis un job d'export écrit la même règle dans un XLSX, et Excel répond par une invite de réparation ou un refus sec. Rien n'a changé dans votre code sauf la longueur d'une chaîne, et c'est exactement pourquoi cette frontière vaut d'être comprise avant qu'elle ne vous trouve

Où la limite de liste de 255 caractères s'applique-t-elle réellement ?

La limite s'applique à la forme inline d'une validation de liste en XLSX, la chaîne quotée et séparée par virgules stockée dans formula1, et Excel l'applique à l'ouverture du fichier, pas seulement quand vous tapez la liste dans le dialogue Validation de données. Le test direct contre Excel a donné un résultat net : une liste de 253 caractères s'est ouverte, tandis que des listes de 260 et 421 caractères étaient refusées. Le cas 421 caractères est celui qui instruit, car il venait d'un classeur qu'Excel avait lui-même produit. Excel écrira un fichier qu'Excel ne relira pas

Le schéma ne vous sauve pas ici. ECMA-376 Partie 1, §18.3.1.32 définit l'élément dataValidation et ses formules sans énoncer de plafond de 255 caractères, donc un fichier peut être valide au sens du schéma et être rejeté quand même. C'est une contrainte applicative, et la seule référence fiable à son sujet est le comportement d'Excel lui-même

HotXLS n'applique pas ce plafond pour vous. TXLSXWorksheet.AddListValidation enveloppe les éléments dans des guillemets et les stocke tels quels ; il ne tronque ni ne refuse une longue liste, donc une liste de 400 caractères entre dans le XLSX sauvegardé exactement comme demandé et Excel refuse ensuite le résultat. C'est un choix délibéré, puisque rogner une liste en silence corromprait des données sans que personne ne le remarque, mais il met le contrôle de longueur de votre côté. Un petit helper le garde honnête :

const
  // Excel a ouvert une liste de 253 caractères et refusé 260 et 421 caractères.
  // Gardez une marge au lieu de viser le bord exact.
  MaxInlineListChars = 250;

function InlineListLength(const AItems: array of WideString): Integer;
var
  I: Integer;
begin
  Result := 0;
  for I := 0 to High(AItems) do
    Inc(Result, Length(AItems[I]));
  if Length(AItems) > 1 then
    Inc(Result, High(AItems));  // virgules séparatrices
end;

Traitez les guillemets doubles embarqués comme coûtant deux caractères, car AddListValidation les double pour garder la chaîne de formule valide

Pourquoi le XLSB peut porter des listes que le XLSX ne peut pas

Le XLSB stocke une liste inline comme un unique token de chaîne PtgStr dont la longueur est un champ 16 bits, donc le format binaire n'a pas de mur des 255 caractères. Un échantillon natif de 60 éléments et 419 caractères tient dans un seul token et fait des allers-retours dans Excel sans broncher. La même liste en XLSX est le cas 421 caractères ci-dessus

Un loader qui appliquerait la limite de 255 caractères du dialogue au XLSB rejetterait ce fichier. Le contrôle de token de validation XLSB de HotXLS borne un token de chaîne à 32 767 caractères à la place, le même plafond qu'une chaîne de cellule Excel. Deux réserves valent d'être dites clairement. Le loader plafonne aussi une formule de validation complète à 16 384 octets, donc le plafond pratique d'une liste inline est plus bas que ce que le contrôle de token seul suggère. Et les listes séparées par des caractères NUL dans un PtgStr sortent du profil supporté, donc elles sont rapportées comme non supportées au lieu d'être devinées

Le piège se cache dans la conversion. Une longue liste parfaitement saine en XLSB devient un fichier refusé dès que quoi que ce soit, HotXLS compris, sauvegarde ce classeur en XLSX. Si un pipeline convertit des fichiers XLSB de clients, faites tourner le contrôle de longueur sur chaque règle de liste avant la sauvegarde XLSX, pas après qu'un client l'a signalé

Que devient une liste qui pointe vers une autre feuille ?

Excel stocke une liste dont la source se trouve sur une autre feuille dans l'extension x14:dataValidations, pas dans l'élément dataValidations ordinaire. La règle vit dans l'extLst de la feuille sous l'URI d'extension {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, défini dans [MS-XLSX], et sa référence source est un élément xm:f tel que Lists!$A$2:$A$61. Le XLSB garde la même règle dans ses propres enregistrements d'extension

HotXLS parse ces règles d'extension XLSX dans la collection typée DataValidations et marque chacune avec TXLSXDataValidation.IsExtension. Une liste entre feuilles apparaît donc aux côtés des règles ordinaires avec sa plage, son type et sa formule lisibles, et elle survit aux éditions, sauvegardes et réouvertures. À la sauvegarde, le writer garde les règles d'extension hors de l'élément ordinaire et reconstruit l'extension x14 à sa position de schéma, en queue de feuille. Les deux formules sont gardées, donc les règles entier, décimal, longueur de texte, date et heure utilisant between ou notBetween gardent leurs deux bornes

Le profil typé d'extension couvre la règle entière. Outre type, operator, allowBlank, les deux drapeaux show, la plage et les deux formules, une règle d'extension garde son titre et texte d'invite de saisie, son titre et texte d'alerte d'erreur, le style d'alerte, le mode IME et le réglage de cache de la liste déroulante à travers un chargement et une resauvegarde, avec les mêmes règles d'échappement qu'une validation ordinaire

uses
  SysUtils, lxHandleX, lxDiagnostics;

procedure AuditListRules(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Dv: TXLSXDataValidation;
  S, I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    for S := 1 to Book.Sheets.Count do        // Sheets[] est à base 1
    begin
      Sheet := Book.Sheets[S];
      for I := 0 to Sheet.DataValidations.Count - 1 do
      begin
        Dv := Sheet.DataValidations[I];
        if Dv.ValidationType <> xlsxDvList then
          Continue;
        if Dv.IsExtension then
          Writeln(Sheet.Name, ' ', Dv.Range, ': x14 list from ', Dv.Formula1)
        else if (Dv.Formula1 <> '') and (Dv.Formula1[1] = '"') and
                (Length(Dv.Formula1) - 2 > MaxInlineListChars) then
          Writeln(Sheet.Name, ' ', Dv.Range, ': inline list of ',
            Length(Dv.Formula1) - 2, ' chars, refused by Excel as XLSX');
      end;
    end;
  finally
    Book.Free;
  end;
end;

Pour lire les éléments derrière une liste déroulante, sélectionner des entrées et contrôler une saisie face à ces règles, voir la lecture des éléments de liste déroulante et la validation d'entrée avec HotXLS ; cet article reste sur les formats de stockage

Comment Excel et HotXLS traitent les types de validation inconnus

Excel ouvre un dataValidation dont l'attribut type porte un token non reconnu comme une règle de type none, garde sa formule, et à la sauvegarde la réécrit comme un élément dataValidation nu sans attribut type, en jetant l'attribut inconnu. De tels tokens viennent de XML édité à la main, de générateurs tiers et de fonctionnalités Office futures, et la réponse d'Excel est la tolérance, pas le rejet

HotXLS suit la même tolérance. Plutôt que de jeter la règle, ce qui la perdrait à la resauvegarde sans trace, le reader la garde comme xlsxDvNone avec ses formules et sa plage, et enregistre un diagnostic d'avertissement par règle normalisée avec le code 1307, exposé comme la constante xlsDiagnosticValidationTypeNormalized dans lxDiagnostics. À la sauvegarde, une règle none est écrite comme l'élément nu, la même forme que produit Excel. Une règle none ne valide rien, donc le diagnostic est votre signal qu'une règle que l'auteur croyait active ne contraint plus la saisie

for I := 0 to Book.Diagnostics.Count - 1 do
  if Book.Diagnostics[I].Code = xlsDiagnosticValidationTypeNormalized then
    Writeln('Untyped rule kept on sheet ', Book.Diagnostics[I].SheetName,
      ': ', Book.Diagnostics[I].Message);

Pourquoi Excel peut-il refuser un classeur après un aller-retour ?

Excel refuse un classeur resauvegardé quand un fragment d'extension rejoué utilise un préfixe d'espace de noms que le nouveau fichier ne déclare jamais, car le résultat n'est pas du XML bien formé. Les fragments d'extension portent couramment des attributs préfixés comme xr:uid, dont la déclaration xmlns:xr se trouve sur la racine de la feuille d'origine, pas dans le fragment. Copiez le fragment tel quel dans une nouvelle racine de feuille qui manque de cette déclaration et les règles d'espaces de noms XML sont cassées avant qu'Excel n'ait regardé une seule cellule

HotXLS gère ça à l'étape de capture. À la lecture, il scanne chaque fragment rejoué pour les préfixes d'éléments et d'attributs que le fragment ne déclare pas lui-même, résout chaque préfixe vers son URI via le reader, et écrit ces déclarations sur la racine de la feuille au moment de la sauvegarde. Le chemin de sauvegarde en streaming construit la racine par la même routine, donc les deux modes de sauvegarde produisent une sortie bien formée, et un fichier resauvegardé se rouvre dans Excel avec chaque règle de validation présente, y compris une liste x14 entre feuilles rejouée. La vue d'ensemble de la façon dont HotXLS transporte du contenu extLst qu'il ne modélise pas est couverte dans les allers-retours XLSX sans perte pour thèmes, extLst et calcChain, et les règles générales derrière l'invite de réparation d'Excel sont dans pourquoi Excel montre une invite de réparation

Les longues listes appartiennent à une plage ou un nom défini

Le design robuste pour toute liste susceptible de grossir est de stocker les éléments dans des cellules de feuille et de pointer la validation vers elles via un nom défini. Une source de plage n'a pas de limite de 255 caractères dans les deux formats, les éléments peuvent être édités sans toucher la règle, et un nom défini garde la règle dans l'élément dataValidation ordinaire même quand les cellules sont sur une feuille séparée, ce que tout consommateur du fichier peut lire

// Lists est une feuille dédiée, par ex. Book.Sheets.Add('Lists')
function AddListRule(Book: TXLSXWorkbook; Target, Lists: TXLSXWorksheet;
  ACol: Integer; const ARange, AName: WideString;
  const AItems: array of WideString): Integer;
var
  I: Integer;
  ColRef: WideString;
begin
  if InlineListLength(AItems) <= MaxInlineListChars then
  begin
    Result := Target.AddListValidation(ARange, AItems);
    Exit;
  end;
  for I := 0 to High(AItems) do
    Lists.WriteAutoValue(I + 1, ACol, AItems[I]);   // ligne, colonne à base 1
  ColRef := Chr(Ord('A') + ACol - 1);               // colonnes A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Le '=' initial marque une référence ; sans lui le nom est quoté comme texte
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Ce signe égal initial compte. AddListValidation traite un argument commençant par = comme une référence et retire le signe, laisse un argument contenant ! non quoté comme plage, et quote tout le reste comme liste inline. Passez 'StatusList' sans le signe et vous obtenez une liste déroulante à un élément qui lit littéralement StatusList. Deux points plus petits : WriteAutoValue convertit le texte comme la saisie de cellule d'Excel, donc un élément tel que TRUE devient un booléen, et un nom de feuille contenant des espaces exige des quotes simples dans la référence

Pour une règle qui doit pointer directement vers une autre feuille sans nom, posez IsExtension := True sur la règle à l'index renvoyé par AddListValidation, et HotXLS l'écrit comme l'extension x14, la même forme qu'utilise Excel lui-même. La route du nom défini est plus simple et continue de marcher pour les outils qui ne parsent jamais les extensions, voilà pourquoi c'est la recommandation par défaut ici

Tout le comportement décrit ici se livre dans le composant Excel HotXLS pour Delphi pour Delphi, C++Builder et Lazarus, où la page produit liste les types de validation supportés, les formats de fichiers et la référence complète des diagnostics