HotXLS évalue désormais les références structurées de tableau, si bien que =SUM(Table1[Amount]) produit un nombre au lieu d'être ignoré. Le résolveur gère Table[Column], Table[[Column]], les plages de colonnes telles que Table[[Q1]:[Q4]], et les spécificateurs de sélection [#Data], [#All], [#Headers] et [#Totals], en résolvant chacun face au modèle de tableau du classeur au moment de l'analyse tandis que le texte de formule original fait l'aller-retour mot pour mot
Une forme est délibérément absente, et c'est celle que l'on rencontre en premier. Le raccourci de ligne actuelle [@Column] n'est pas pris en charge, pour une raison structurelle qui mérite d'être comprise plutôt que contournée aveuglément
Pourquoi une référence structurée n'est-elle pas simplement une plage avec un nom convivial ?
Parce qu'un nom défini fige une adresse alors qu'une référence de tableau ne le fait pas. Écrivez DataBlock comme un nom pointant vers Sheet1!$A$2:$D$100 et il reste ce rectangle jusqu'à ce que quelque chose le réécrive. Écrivez Sales[Amount] et cela signifie « la colonne Amount de la table Sales », quelle que soit l'étendue de cette table au moment où la formule est évaluée. Ajoutez vingt lignes à la table et la somme les couvre ; il n'y a aucune référence à ajuster car il n'y a jamais eu d'adresse dans la formule au départ
Cette qualité symbolique est précisément la raison pour laquelle la référence ne peut pas être résolue par substitution de chaîne. Le résolveur doit trouver la table par son nom dans le classeur, retrouver la colonne par le texte de son en-tête, décider quelles lignes couvre le spécificateur de sélection demandé, et produire un rectangle concret. HotXLS fait cela pendant la compilation de la formule via le modèle de tableau, ce qui explique pourquoi une formule écrite avant que la table ne grandisse s'évalue quand même par rapport à l'étendue actuelle de la table
La grammaire que HotXLS résout
La grammaire de spécification prise en charge couvre un unique résultat rectangulaire et mérite d'être énoncée précisément, car la documentation d'Excel présente une surface bien plus large que ce que la plupart des moteurs implémentent. HotXLS accepte [Col] et la variante entre crochets [[Col]], les spécificateurs de sélection nus [#Data], [#All], [#Headers] et [#Totals], la forme combinée [[#Data],[Col]], une plage à l'intérieur d'un spécificateur de sélection comme [[#Data],[Col1]:[Col2]], et une plage simple [Col1]:[Col2]
Cet ensemble vous donne toute forme de référence qui produit un bloc contigu unique : une colonne, une suite de colonnes adjacentes, une tranche du corps seul ou incluant l'en-tête de l'une ou l'autre. Les unions non adjacentes et les résultats multi-zones en sont exclus. Quand une référence ne peut pas être résolue, la formule conserve le comportement précédent d'omission sans valeur plutôt que de substituer une estimation, si bien qu'une référence non résoluble ne devient jamais un nombre plausible mais faux
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);
// ... write the header row and 24 data rows ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Pourquoi la forme de ligne actuelle est-elle exclue à dessein ?
[@Column] et [#This Row] signifient « la cellule de cette colonne sur la ligne où réside cette formule ». La valeur dépend donc de la position de la cellule évaluatrice, pas seulement de la table. C'est un type de référence différent : non pas un rectangle que le compilateur peut résoudre une fois, mais une résolution par cellule qui doit être refaite pour chaque ligne qu'occupe la formule
HotXLS renvoie False depuis le résolveur de plage de table pour ces formes, ce qui les dirige vers le chemin d'omission sans valeur. Le texte de formule est préservé et réécrit sans changement, si bien qu'un classeur utilisant [@Amount] s'ouvre correctement dans Excel après un aller-retour à travers votre application ; seule la valeur calculée par HotXLS est absente. Entre une valeur absente et une valeur calculée par rapport à la mauvaise ligne, l'absence est celle que vous pouvez détecter
Le contournement pratique est mécanique : dans un classeur que vous générez, écrivez la référence relative équivalente de style A1, ce qu'Excel stocke de toute façon en interne pour une grande partie de la logique à portée de table. Dans un classeur que vous ne faites que traiter, laissez la formule telle quelle et lisez la valeur mise en cache qu'Excel a déjà stockée, ce qu'un pipeline de chargement-rapport veut généralement
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Recherche de type recordset sur le corps de la table, résultat de ligne indexé à partir de 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Ce qui se passe quand la forme de la table change
Les références structurées sont invalidées plutôt que redirigées silencieusement quand ce qu'elles nomment disparaît. Supprimez une colonne et les formules qui font référence à cette colonne sont invalidées de la même façon qu'Excel les invalide ; supprimez ou renommez la table et les références vers elle sont traitées de la même manière. C'est le comportement correct et il reflète l'ajustement ordinaire des références, décrit dans l'ajustement des références de formule à l'insertion et à la suppression, où le rôle du moteur est de garder les formules honnêtes plutôt que de les garder d'apparence valide
La croissance en lignes est le cas opposé et ne nécessite aucun ajustement. Comme la référence nomme la table plutôt qu'un rectangle, ajouter des lignes à l'intérieur de la plage de la table élargit ce que couvre [#Data] sans toucher à une seule formule. C'est cette propriété qui rend les tables utiles dans un modèle de rapport : la ligne de totaux continue de sommer tout ce que l'import a produit, quel que soit le nombre de lignes obtenu
Discipline de l'aller-retour
HotXLS conserve le texte de formule original. Un classeur chargé avec SUM(SalesTable[Amount]) est enregistré avec SUM(SalesTable[Amount]), pas avec l'adresse résolue SUM(D2:D25). Cela compte plus qu'il n'y paraît : un utilisateur qui ouvre votre sortie dans Excel s'attend à voir la formule qu'il a écrite, et une adresse résolue transformerait discrètement un modèle auto-entretenu en un modèle fragile qui cesse de couvrir les nouvelles lignes
Deux capacités connexes complètent le tableau. Les définitions de table elles-mêmes, y compris les tables sans en-tête et les commentaires par table, font l'aller-retour via le modèle de tableau décrit dans la validation de données, l'AutoFilter et les tables Excel. Et quand de nombreuses cellules partagent un même motif, XLSX les stocke une seule fois comme formule partagée, laquelle est développée et réémise comme couvert dans l'expansion si des formules partagées. Les références structurées à l'intérieur de formules partagées passent par les deux chemins, il faut donc que les deux se comportent bien, et c'est le cas
HotXLS lit et écrit XLS, XLSX et ODS depuis Delphi et C++Builder sans aucune installation d'Excel et sans automatisation Office, en évaluant les formules dans son propre moteur. Le modèle de tableau, le moteur de formules et l'API de recalcul sont documentés sur la page HotXLS Delphi spreadsheet component