Article technique

HotXLS : références structurées de tableau Excel

Un tableau Excel dont la colonne Amount tient =[@Qty]*[@Price] ne stocke [@Qty] nulle part dans le fichier sauvegardé. Décompressez le classeur et l'élément <f> du XML de feuille montre ce qu'Excel écrit réellement : l'orthographe longue qualifiée par table Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], parce que la grammaire des formules de ECMA-376 Part 1, §18.17 a un mot-clé #This Row et pas de @. Une cellule de synthèse plus bas tient SUM(Lines[Amount]). Aucune de ces chaînes ne contient une seule adresse de cellule, donc un moteur qui n'analyse que des références A1 n'a rien à calculer tant qu'il ignore où siège le tableau, quelle ligne porte les en-têtes et sur quelle ligne vit la formule elle-même

Cette dernière information est la plus épineuse. Une référence de colonne peut être tournée en rectangle une fois par tableau. Une référence de ligne courante ne le peut pas ; elle désigne une cellule différente pour chaque ligne vers laquelle elle est copiée

Pourquoi une référence structurée n'est-elle pas simplement une plage avec un nom convivial ?

Un nom défini gèle une adresse et une référence de tableau non. Pointez le nom DataBlock vers Sheet1!$A$2:$D$100 et il reste ce rectangle jusqu'à ce que quelque chose le réécrive. Sales[Amount] signifie « la colonne Amount du tableau Sales », quelle que soit l'étendue du tableau au moment où la formule est évaluée. Ajoutez vingt lignes au tableau et la somme les couvre ; il n'y a aucune référence à ajuster parce que la formule n'a jamais tenu d'adresse

HotXLS résout ces références à chaque compilation de formule pour évaluation. TXLSXWorkbook cherche le tableau par nom dans le modèle de tableaux du classeur (TXLSXTable.Name ou DisplayName, comparés sans la casse), apparie la colonne contre le texte d'en-tête stocké dans TXLSXTable.Columns, applique le spécificateur d'élément pour choisir la bande de lignes, et remet au compilateur une plage absolue comme 'Sales'!$D$2:$D$25. Le texte réécrit n'existe que pour cette compilation. La cellule garde la formule exactement comme elle a été écrite, et comme la recherche tourne contre TXLSXTable.Range en l'état au moment du recalcul, une formule écrite avant que le tableau ne grandit s'évalue quand même contre le tableau élargi

Diagramme de HotXLS résolvant la référence structurée SalesTable[Amount] en Delphi en trouvant la table par nom, en appariant l'en-tête de colonne Amount et en appliquant le spécificateur #Data pour obtenir une plage concrète
HotXLS compile les références Table[Column] contre le modèle de table au moment de l'analyse et les transforme en la plage concrète courante, si bien que la formule continue de suivre la table à mesure qu'elle grandit

La grammaire des rectangles : colonnes, étendues et spécificateurs d'éléments

Toute forme à l'échelle du tableau que HotXLS évalue produit un bloc contigu unique. Les orthographes acceptées sont [Col] et sa jumelle entre crochets [[Col]], les spécificateurs d'éléments nus [#Data], [#All], [#Headers] et [#Totals], la forme combinée [[#Data],[Col]], une étendue à l'intérieur d'un spécificateur d'élément comme [[#Data],[Col1]:[Col2]], et une étendue simple [[Col1]:[Col2]]. Une étendue peut s'écrire de droite à gauche ; [[Q2]:[Q1]] se résout dans les mêmes deux colonnes que [[Q1]:[Q2]]

La bande de lignes que chaque spécificateur choisit suit deux drapeaux du tableau. TXLSXTable.HasHeaderRow décide si la première ligne de Range est un en-tête, et TXLSXTable.TotalsRowShown décide si la dernière est une ligne de totaux. Une référence de colonne simple et [#Data] couvrent les lignes entre les deux. [#All] prend le Range entier, en-tête et totaux compris. Demandez [#Headers] sur un tableau sans ligne d'en-tête, [#Totals] sur un tableau sans ligne de totaux, ou une colonne d'un tableau dont le corps n'a aucune ligne, et la référence devient #REF!. Cela correspond à ce qu'Excel montre pour la même demande, et cela empêche une référence impossible de se muer en silence en une plage vide dont la somme vaut zéro

Les noms de colonnes qui contiennent un crochet, un dièse, une arobase ou une apostrophe suivent la règle d'échappement d'Excel : chaque tel caractère est préfixé d'une apostrophe à l'intérieur de la référence, donc un en-tête nommé Unit [USD] s'écrit Sales[Unit '[USD']]. Les formes à colonne unique et à ligne courante décodent ces échappements avant l'appariement. Les étendues et les formes combinées [#Data],[Col] se résolvent contre les tableaux de la feuille propre de la formule, donc une feuille de synthèse qui a besoin d'une étendue sur le tableau d'une autre feuille est mieux servie par une référence de colonne simple par colonne

Carte de la grammaire des références structurées qu'HotXLS accepte en Delphi, montrant quelles régions du tableau couvrent les formes #Data, #Headers, #Totals et #All et comment les orthographes entre crochets correspondent aux mêmes rectangles
Toute forme à l'échelle du tableau couvre une bande contiguë du tableau, et les variantes entre crochets se résolvent exactement comme leurs orthographes simples

L'exemple ci-dessous bâtit un tableau de ventes de 24 lignes et place cinq formules de synthèse à côté en F2 à F6, sur la même feuille que le tableau. La ligne 1 est la ligne d'en-tête, les lignes 2 à 25 sont le corps, et il n'y a pas de ligne de totaux, donc SalesTable[#All] couvre 25 lignes tandis que SalesTable[Amount] en couvre 24

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... écrire la ligne d'en-tête et 24 lignes de données ...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

Valeurs attendues pour cet agencement : F5 vaut 25 et F6 vaut 4, un compte par cellule d'en-tête. F2 à F4 dépendent des données que vous écrivez

Comment [@Column] sait-elle quelle ligne lire ?

Une référence de ligne courante prend sa ligne dans la cellule en cours d'évaluation. Quand Recalculate atteint une formule contenant Lines[@Qty] en ligne 7, HotXLS la résout vers la colonne Qty de la ligne 7 ; le même texte de formule en ligne 8 lit la ligne 8. L'orthographe longue qu'Excel écrit sur disque, Lines[[#This Row],[Qty]], est traitée comme la même référence, et il en va de même pour Lines[@[Unit Price]] pour un en-tête contenant une espace

La forme non qualifiée [@Qty], sans nom de tableau devant, a besoin d'un fait de plus : à quel tableau elle appartient. HotXLS répond par la position. Si la cellule en évaluation siège dans les lignes du corps et dans l'étendue de colonnes d'un tableau de la même feuille, ce tableau fournit la colonne, et à l'intérieur d'un tel tableau [@[Q1]:[Q2]] devient une plage d'une seule ligne à travers ces colonnes. Partout ailleurs, la référence reste non résolue. Un nom défini dont la formule emploie une référence de ligne courante prend sa ligne dans la cellule qui se réfère au nom, ce qui est le même comportement qu'une formule aurait en ligne

Gardez les formules à ligne courante sur les lignes du corps du tableau lui-même, et qualifiez-les avec le nom du tableau quand le code les génère. Les références qualifiées survivent à un déplacement, elles se lisent comme Excel les stocke, et une formule sur les lignes propres du tableau est le seul cas où Excel et tout autre consommateur du fichier s'accordent sur ce que signifie @

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ... écrire la ligne d'en-tête et articles, quantités, prix aux lignes 2..13 ...

    // Un texte de formule, douze résultats différents : chaque cellule lit sa propre ligne
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // Contre-vérification sous le tableau : total de colonne contre produit recalculé
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // Attendu : D15 s'évalue à 0 quand chaque cellule Amount correspond à Qty * Price
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

Le contrôle en D15 est bon marché et mérite de rester dans un rapport généré pendant qu'il se construit. Une formule par ligne qui aurait ramassé la mauvaise ligne ferait diverger les deux totaux, et la différence surgit comme un nombre non nul dans une cellule que tout le monde peut lire

Organigramme de HotXLS résolvant la forme à ligne courante [@Amount] en Delphi contre la ligne de la cellule en évaluation tandis que le texte de formule fait un aller-retour inchangé pour qu'Excel lise la même référence après sauvegarde
Les références à ligne courante se résolvent par cellule contre la ligne où siège la formule, et le fichier sauvegardé porte toujours le texte de référence d'origine pour qu'Excel le lise

Ce qui se passe quand la forme de la table change

La croissance est le cas facile, à condition que la définition du tableau grandisse réellement. Écrire une valeur en ligne 26 sous un tableau dont le Range est A1:D25 ne rend pas la ligne 26 partie du tableau. L'auto-expansion est une fonctionnalité d'édition d'Excel déclenchée par quelqu'un qui tape à côté du tableau, et écrire des cellules via HotXLS ne la déclenche pas. Posez TXLSXTable.Range à la nouvelle étendue après un import, ou insérez les lignes à l'intérieur du tableau avec TXLSXWorksheet.InsertRows, qui décale Tables[i].Range avec toute autre référence de la feuille. Dans les deux cas [#Data] s'élargit et pas une formule ne change

La contraction est là où les références peuvent se briser, et HotXLS suit la règle d'Excel. DeleteCols sur une colonne de tableau réécrit en #REF! toute référence structurée nommant cette colonne, dans les formules de cellules, formats conditionnels, règles de validation de données, séries de graphiques et noms définis. Un DeleteRows qui retire la ligne d'en-tête supprime le tableau et invalide ses références de la même manière, et un qui retire la ligne de totaux efface TotalsRowShown, après quoi [#Totals] n'a plus de ligne à pointer. Les références A1 ordinaires passent par le même ajustement, décrit dans l'ajustement des références de formules à l'insertion et à la suppression

TXLSXTables.DeleteByName et le renommage via TXLSXTable.Name travaillent sur le modèle de tableaux seul. Les formules qui mentionnent l'ancien nom gardent leur texte, donc renommez ces formules dans la même passe quand votre code renomme un tableau

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // les feuilles du classeur démarrent à 1

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ... écrire 20 lignes importées dans A26:D45 ...
    Table.Range := 'A1:D45';  // le corps court maintenant de la ligne 2 à la ligne 45

    Book.Recalculate;  // SUM(SalesTable[Amount]) en F2 couvre maintenant 44 lignes

    // Recherche façon recordset sur le corps du tableau, résultat de ligne à base 1, 0 = aucun
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Log est votre propre routine
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

    Book.SaveAs('sales.xlsx');
  finally
    Book.Free;
  end;
end;

FindFirst et FindNext comparent les chaînes sans la casse et les nombres par valeur, et ils ne balayent que les lignes du corps, donc le texte d'en-tête Region n'est jamais renvoyé comme correspondance

Discipline de l'aller-retour

Un classeur chargé avec SUM(SalesTable[Amount]) est sauvegardé avec SUM(SalesTable[Amount]), et une cellule tenant Lines[[#This Row],[Qty]] repart avec ce texte intact. L'adresse résolue ne remplace jamais ce que l'utilisateur a écrit. Si HotXLS sauvegardait SUM($D$2:$D$25) à la place, le fichier s'ouvrirait dans Excel en ayant l'air correct puis cesserait de couvrir les nouvelles lignes dès que quelqu'un étendrait le tableau, ce qui est le genre de casse la plus lente à remonter à sa cause

Les définitions de tableaux elles-mêmes, y compris les tableaux sans en-tête et les commentaires par tableau, font l'aller-retour via le modèle de tableaux couvert dans la validation de données, l'AutoFilter et les tableaux Excel. Quand beaucoup de cellules partagent un motif de formule, XLSX peut le stocker une fois comme formule partagée, et l'expansion de ces cellules est couverte dans l'expansion si des formules partagées. Une colonne calculée sauvegardée par Excel arrive souvent ainsi, donc une référence structurée dans un corps de tableau passe par les deux chemins avant d'être évaluée : la formule partagée s'expand d'abord en texte par cellule, puis chaque cellule résout sa propre ligne

Le modèle de tableaux, le moteur Recalculate et les appels d'insertion et de suppression de feuille employés ici font partie de HotXLS, qui lit et écrit XLS, XLSX et ODS depuis Delphi et C++Builder sans installation d'Excel. La référence complète de l'API vit sur la page du composant tableur HotXLS Delphi