2024!A1 échoue : quoter les noms de feuilles Excel

Un classeur de budget a une feuille nommée FY2024, une cellule de synthèse dit =FY2024!B7, et Excel ne peut pas lire la formule. FY2024 est aussi une adresse de cellule, donc le nom demande des apostrophes, comme dans 'FY2024'!B7 ; HotXLS les écrit selon la même règle qu'Excel applique à l'enregistrement, et son classeur XLSX réécrit chaque formule quand une feuille est renommée ou supprimée

Quels noms de feuilles doivent être quotés dans une formule ?

Un nom de feuille demande des apostrophes dès que le parseur de formules pourrait le lire comme autre chose qu'un nom de feuille : un nombre, un booléen, ou une référence de cellule. La grammaire des formules d'ECMA-376 Partie 1 §18.17 couvre les noms de feuilles quotés et non quotés, mais la référence pratique est le texte qu'Excel écrit dans les éléments <f> de sheet1.xml, parce que c'est ce texte auquel tout autre lecteur a été calibré. L'écho COM Range.Formula d'Excel et son XML enregistré sont en désaccord pour certains noms : R1. et A1.x reviennent nus de .Formula mais portent des apostrophes dans le fichier. Une règle tirée de l'écho COM est contredite par les propres fichiers d'Excel. HotXLS suit la forme stockée, qui demande des quotes pour :

• Tout caractère hors A-Z, a-z, 0-9 et le tiret bas : espaces, tirets, points, parenthèses
• Un chiffre en tête : 2024, 2nd_Half et 3D sont tous quotés
• TRUE ou FALSE quelle que soit la casse. TRUE1 et TRUEX restent nus, puisque ni l'un ni l'autre ne se lit comme un booléen
• Un nom qui se lit comme une cellule A1 dans la grille : une à trois lettres formant une colonne au plus tard XFD, suivies d'une ligne de 1 à 1048576, zéros de tête permis
• Un nom qui commence comme une référence R1C1 : R, C ou RC seuls, ou suivis d'un chiffre
• Dans un nom quoté, une apostrophe est doublée — Bob's Data s'écrit 'Bob''s Data'!A1

La règle A1 est celle que les vrais classeurs rencontrent, tant de noms de feuilles d'affaires tombent être des adresses de cellules valides. FY2024 est la colonne FY, ligne 2024. Jan2024 est la colonne JAN, ligne 2024, et JAN se trouve bien dans les 16 384 colonnes qu'une feuille moderne a. Q1, CR1 et Ra1 sont toutes des adresses aussi, et les trois doivent être quotées. Les cas limites vont dans l'autre sens : A0 n'a pas de ligne zéro, XFE1 est une colonne après le bord, XFD1048577 est une ligne après, et ABCD1 a quatre lettres, si bien qu'Excel laisse chacun nu. Les zéros de tête ne sauvent pas un nom, et A01 reçoit quand même des apostrophes parce que le parseur le lit comme A1. Un écrivain qui ne vérifie que la classe de caractères du nom émettra FY2024!B7 pour un rapport d'exercice fiscal, et Excel ne peut pas relire cette formule

Les sosies R1C1 et le bien-fondé du sur-quotage

Pour les noms en forme R1C1, aucune règle propre ne permet de retrouver la sortie d'Excel. Excel quote R, C, RC, R1, C1, R1x, R1C1x, et aussi R1C16385 et C99999, qui nomment des colonnes inexistantes. Pourtant il laisse R0, C0, RC0, R0C1, R1048577, Rx, Cx et R_1 nus. Aucune combinaison de limites de grille et de formes de jetons ne reproduit les deux listes, si bien que HotXLS prend un surensemble : un nom qui est R, C ou RC, ou qui commence par l'un deux suivi d'un chiffre, reçoit des apostrophes. Le surensemble quote quelques noms qu'Excel laisse nus, R0 et R1048577 entre autres. C'est acceptable pour une raison simple : l'orthographe quotée de n'importe quel nom de feuille est une syntaxe de formule valide, donc tout lecteur qui parse des formules l'accepte, tandis qu'une liste qui chercherait à égaler Excel nom par nom reposerait sur une règle que personne ne peut énoncer. Les noms ordinaires comme Chart1, Region1 et Cost2024 tombent hors des deux tests et restent nus. Si vous travaillez aussi avec du texte R1C1, les notes sur la notation de formules R1C1 dans HotXLS décrivent comment ces références se parsent au départ

Les noms avec lettres hors ASCII sont sur-quotés aussi. Excel est plus indulgent ici. Excel écrit Données!A1 et 数据!A1 sans apostrophes, parce que les lettres accentuées et CJK comptent comme caractères de nom pour son parseur. HotXLS lit ces qualificatifs nus quand il ouvre un fichier, puisque Excel les produit, mais écrit de tels noms quotés, comme dans 'Données'!A1. La forme quotée est valide partout, et le test de l'écrivain reste une liste courte et prévisible plutôt qu'une table de lettres Unicode avec laquelle chaque consommateur devrait être d'accord

Construire des qualificatifs de feuille en Delphi

lxStandard.XlsSheetNameNeedsQuotes est la forme publique de cette règle, et c'est la fonction que l'écrivain XLSX et le moteur classique utilisent tous deux quand ils émettent une référence de feuille. Quand votre propre code assemble du texte de formules à partir de noms de feuilles, par exemple une feuille de synthèse qui pointe vers une feuille par région ou par année, passez chaque nom dedans plutôt que de concaténer Name + '!'. La fonction ne répond que oui ou non. Doubler les apostrophes internes relève de l'appelant, comme le fait l'aide ci-dessous, et les formules de cellules XLSX s'assignent sans le = de tête

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Qualificatif pour du texte de formule construit à la main : apostrophes quand le nom
// les demande, avec toute apostrophe dans le nom doublée
function SheetQualifier(const Name: WideString): WideString;
begin
  if XlsSheetNameNeedsQuotes(Name) then
    Result := '''' + StringReplace(Name, '''', '''''', [rfReplaceAll]) + '''!'
  else
    Result := Name + '!';
end;

const
  Candidates: array[0..10] of WideString = (
    'Summary', '2024', 'FY2024', 'Q1', 'TRUE', 'TRUE1',
    'R1C1', 'Region1', 'XFE1', 'Données', 'Bob''s Data');
var
  Book: TXLSXWorkbook;
  Map, Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Map := Book.Sheets.Add('Map');
    for I := Low(Candidates) to High(Candidates) do
    begin
      Sheet := Book.Sheets.Add(Candidates[I]);
      Sheet.Cells[1, 1].Value := (I + 1) * 10;
      // Utilisez le nom que la feuille a réellement reçu, pas celui demandé
      Map.Cells[I + 1, 1].Value := Sheet.Name;
      Map.Cells[I + 1, 2].Formula := SheetQualifier(Sheet.Name) + 'A1';
      Writeln(Format('%-12s %s', [Sheet.Name, Map.Cells[I + 1, 2].Formula]));
    end;
    Book.SaveAs('sheet-map.xlsx');
  finally
    Book.Free;
  end;
end.

// Expected:
// Summary      Summary!A1
// 2024         '2024'!A1
// FY2024       'FY2024'!A1
// Q1           'Q1'!A1
// TRUE         'TRUE'!A1
// TRUE1        TRUE1!A1
// R1C1         'R1C1'!A1
// Region1      Region1!A1
// XFE1         XFE1!A1
// Données      'Données'!A1
// Bob's Data   'Bob''s Data'!A1

Un nom de feuille de calcul dans un fichier XLSX ne peut pas contenir : \ / ? * [ ], ne peut ni commencer ni finir par une apostrophe, est plafonné à 31 caractères, doit être unique sans égard à la casse, et ne peut pas être History, qu'Excel réserve. TXLSXSheets.Add et le setter TXLSXWorksheet.Name imposent tous deux cela : les caractères interdits deviennent des tirets bas, un conflit reçoit un suffixe (2), et un nom réservé ou vide retombe sur Sheet. La valeur que vous assignez et celle que vous relisez peuvent donc différer, et un qualificatif construit depuis le nom demandé pointe vers une feuille qui n'existe pas. Relisez Name après chaque assignation et quotez cela

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' n'est pas permis dans un nom de feuille
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Que stocke Excel après la suppression d'une feuille ?

Quand une feuille est supprimée, Excel remplace la référence entière vers elle par #REF!, qualificatif et cellule ou plage ensemble. SUM(Data!A1:B2)+1 est stocké comme SUM(#REF!)+1, COUNT(Data!C:C) comme COUNT(#REF!), un nom défini qui pointait vers Data!$A$1:$B$2 comme simple #REF!, et une plage de série de graphique comme <c:f>#REF!</c:f>. La vue COM est trompeuse ici aussi : Range.Formula affiche =#REF!A1+1, comme si la partie cellule avait survécu, alors que le XML qu'Excel enregistre n'a aucune partie cellule. Copier l'affichage COM donne du texte de formule qu'Excel n'en écrit jamais lui-même — un mauvais pari pour un fichier que d'autres outils liront. TXLSXSheets.DeleteByName écrit la forme stockée. La même logique d'effondrement vers #REF! gouverne les références dont les lignes ou colonnes disparaissent, ce que le morceau sur la façon dont insérer et supprimer des lignes ajuste les références de formules couvre pour le cas dans la feuille

Les références tridimensionnelles se comportent autrement. Excel rétrécit une étendue 3D au lieu de la détruire. Avec des feuilles ordonnées Data, Mid, Keep, supprimer Data transforme SUM(Data:Keep!A1) en SUM(Mid:Keep!A1) : le bout qui a disparu se déplace d'une feuille vers l'intérieur, mesuré dans l'ordre des onglets tel qu'il était avant la suppression. Avec seulement deux feuilles dans l'étendue, le résultat est la forme à feuille unique SUM(Keep!A1). Supprimer Mid ne change aucun texte de formule, puisque l'étendue nomme toujours deux feuilles vivantes et couvre simplement une de moins entre les deux. Seulement quand les deux bouts nomment la feuille supprimée la référence devient #REF!. Une référence morte change ce que retourne une formule, si bien que HotXLS marque le résultat en cache de chaque cellule de formule qu'il a réécrite pendant une suppression comme périmé ; appelez Recalculate avant de lire des valeurs. Un renommage laisse les valeurs en cache tranquilles, puisque les nombres ne peuvent pas changer

uses
  System.SysUtils, lxHandleX;

procedure ShowFormulas(Book: TXLSXWorkbook; const Caption: string);
var
  Report: TXLSXWorksheet;
  Row: Integer;
begin
  Writeln(Caption);
  Report := Book.Sheets.SheetByName('Report');
  for Row := 1 to 4 do
    Writeln('  B', Row, ': ', Report.Cells[Row, 2].Formula);
  Writeln('  DataBlock: ', Book.DefinedNames.FindByName('DataBlock').Formula);
end;

var
  Book: TXLSXWorkbook;
  Report: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');
    Book.Sheets.Add('Mid');
    Book.Sheets.Add('Keep');
    Report := Book.Sheets.Add('Report');
    Book.DefinedNames.Add('DataBlock', 'Data!$A$1:$B$2');

    Report.Cells[1, 2].Formula := 'SUM(Data:Keep!A1)';
    Report.Cells[2, 2].Formula := 'SUM(Data!A1:B2)+1';
    Report.Cells[3, 2].Formula := 'Keep!A1*2';
    Report.Cells[4, 2].Formula := 'INDIRECT("Data!A1")';

    Book.Sheets.DeleteByName('Data');
    ShowFormulas(Book, 'After deleting Data:');

    Book.Sheets.SheetByName('Keep').Name := '2025';
    ShowFormulas(Book, 'After renaming Keep to 2025:');

    Book.Recalculate;   // les cellules qui ont perdu Data portent des valeurs en cache périmées
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Expected:
// Après suppression de Data :
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Après renommage de Keep en 2025 :
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Renommer une feuille réécrit les références dans tout le classeur

Assigner TXLSXWorksheet.Name dans un classeur XLSX porte le nouveau nom partout où une formule peut vivre, et DeleteByName parcourt les mêmes endroits pour appliquer l'effondrement en #REF!. La liste va bien au-delà des formules de cellules :

• Formules de cellules sur chaque feuille. Une formule partagée est tenue développée sur chaque cellule suiveuse et une formule matricielle vit sur sa cellule racine, si bien que les deux sont couvertes sans traitement spécial
• Formules de mise en forme conditionnelle, y compris les seuils de type formule dans les barres de données, échelles de couleurs et jeux d'icônes
• Formules de validation de données, comme une source de liste sur une autre feuille
• Formules de colonnes calculées et de lignes de totaux de tables
• Noms définis, l'épine dorsale des modèles bâtis sur les noms définis et les formules entre feuilles ; à un renommage, un cache de tableau croisé qui lit sa plage source depuis la feuille renommée suit aussi
• Plages de graphiques : noms de séries, catégories, valeurs, tailles de bulles et plages d'étiquettes de données, plages plus et moins de barres d'erreur, et les références de cellules derrière les titres de graphiques et d'axes

Une étendue 3D est quotée en bloc quand l'un des bouts demande des quotes : renommer Keep en 2025 produit 'Mid:2025'!A1, jamais Mid:'2025'!A1, parce que l'étendue est un seul qualificatif. Chaque qualificatif dont le renommage ne parle pas est rejoué tel qu'écrit. Si une formule ailleurs dit 'Sheet1'!B4, avec des apostrophes techniquement inutiles, elle le dit encore ensuite. Normaliser ces qualificatifs au passage serait sans danger pour Excel, mais un renommage d'une seule feuille toucherait alors chaque formule du classeur, et un diff entre deux versions d'un rapport généré enterrerait le vrai changement sous des centaines de changements cosmétiques

Du texte qui ne fait que ressembler à une référence de feuille

Trois sortes de texte ressemblent à un qualificatif Name! pour une recherche par motif et n'en sont pas. Dans un littéral de chaîne, "Data!A1" est du texte, si bien que INDIRECT("Data!A1") continue de pointer vers l'ancien nom après un renommage ou une suppression, comme dans Excel ; si un rapport construit des noms de feuilles pour INDIRECT, gardez-les dans une cellule que votre code met à jour en même temps que le renommage. Un qualificatif précédé d'un indice de classeur entre crochets, comme dans [1]Data!A1, appartient à un classeur externe qui se trouve avoir une feuille appelée Data, et renommer la feuille Data locale ne doit pas le toucher ; un qualificatif quoté qui contient un [ est traité de la même façon. Et #REF! lui-même a la forme d'un qualificatif nommé REF, voilà pourquoi HotXLS vérifie le # précédent avant de traiter REF! comme un nom de feuille. Sans cette vérification, renommer une feuille appelée REF réécrirait chaque #REF! du classeur

Les noms de feuilles les plus susceptibles de casser une formule générée sont des noms ordinaires comme FY2024, Q1 et 2025, pas des noms avec espaces ou ponctuation. Quotez-les via XlsSheetNameNeedsQuotes partout où votre code Delphi construit du texte de formules. À partir de là, un renommage ou une suppression dans le composant tableur Delphi HotXLS laisse chaque formule sous la forme qu'Excel lui-même aurait enregistrée