Article technique

Champs de tableau croisé XLSX conformes au schéma en Delphi

L'attribut axis="rowAxis" sur un élément pivotField se lit comme du XML parfaitement sensé, et un lecteur qui le mappe en « champ de ligne » chargera le tableau croisé avec chaque champ sur le bon axe. Passez la même partie dans un validateur bâti depuis le schéma ECMA-376 Partie 1 §18.10 et il échoue au premier attribut : ST_Axis a exactement quatre jetons, axisRow, axisCol, axisPage et axisValues, et rowAxis n'en fait pas partie. Un rédacteur de tableaux croisés doit satisfaire le schéma plutôt que son propre lecteur, car les gens qui ouvrent le fichier utilisent Excel et leurs propres analyseurs

Les définitions de champs pivot regorgent de règles comme celle-là. Trois ont tendance à passer au travers d'un test d'aller-retour : les jetons d'énumération, l'exigence qu'un élément items ne soit jamais vide, et les entrées de sous-total finales qu'Excel liste après les items de données. Deux autres se trouvent côté lecture, dans des attributs qu'Excel omet parce qu'ils portent leur défaut de schéma. Le reste de cet article parcourt chaque règle, montre à quoi le XML doit ressembler, et montre les appels Delphi qui le produisent

Pourquoi un aller-retour ne peut pas attraper un mauvais jeton d'axe

Un aller-retour prouve que le générateur et le lecteur sont d'accord entre eux, et rien de plus. Si le lecteur accepte axisRow et rowAxis, un générateur qui émet le mauvais passe tous les contrôles sauvegarde-puis-charge que vous pouvez écrire, car le lecteur répare la faute au retour. La faute n'apparaît que quand un consommateur plus strict ouvre la partie : un validateur de schéma, un analyseur généré depuis le XSD, ou Excel décidant que la partie demande réparation

HotXLS sépare les deux côtés exprès. Le rédacteur XLSX n'émet que les quatre jetons ST_Axis, tandis que le lecteur de tableaux croisés mappe aussi les graphies d'apparence anglaise rowAxis, colAxis, pageAxis et dataAxis vers l'axe correspondant, si bien que les fichiers de producteurs qui les utilisent se chargent quand même avec leur disposition intacte. La sortie est tenue au schéma ; l'entrée n'a qu'à être reconnaissable. L'inverse, un lecteur strict accouplé à un générateur négligent, échouerait au moins bruyamment dans vos propres tests ; un lecteur indulgent accouplé à un générateur négligent n'échoue nulle part de visible

<!-- invalide : rowAxis n'est pas une valeur ST_Axis, et CT_Items ne peut pas être vide -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- valide : jeton ST_Axis, vrais items, sous-total default final -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
XML pivotField invalide contre valide : la valeur d'axe inventée rowAxis et un élément items vide violent CT_PivotField, tandis que HotXLS émet des jetons ST_Axis comme axisRow avec de vraies entrées item, un drapeau hidden conservé et un sous-total default final que le schéma accepte
Un lecteur tolérant qui accepte les deux graphies laisse chaque aller-retour passer tandis que le fichier casse toute vérification stricte du schéma — n'écrivez que les quatre jetons ST_Axis et laissez CT_Items porter au moins un item

Ce que CT_PivotField attend au-delà de l'axe

CT_PivotField a trois autres exigences faciles à abandonner. Un champ agrégé dans la zone de valeurs doit le dire sur sa propre définition avec dataField="1", même si la liste <dataFields> le référence déjà. HotXLS pose ce drapeau sur chaque champ pivot vers lequel une entrée de DataFields pointe, si bien qu'un champ ajouté avec AddDataFieldByName est marqué aux deux endroits

La seconde règle est structurelle. CT_Items exige au moins un enfant item, ce qui rend <items count="0"></items> invalide même si c'est du XML bien formé qui s'analyse partout. Quand un champ n'a pas d'items, HotXLS laisse de côté tout l'élément items. La troisième règle concerne l'état de chaque item : un membre caché porte h="1" et un membre aux détails repliés porte sd="0". Les deux viennent de TXLSPivotItem.IsHidden et TXLSPivotItem.IsDetailHidden et sont écrits à chaque sauvegarde, si bien qu'un filtre posé en code survit jusqu'à Excel

Items de sous-total finaux et le piège du compte

Quand un champ a des items, Excel fait suivre les items de données d'un item supplémentaire par fonction de sous-total, typés avec ST_ItemType. Le sous-total automatique est <item t="default"/> ; les fonctions explicites emploient sum, countA, avg, max, min, product, count, stdDev, stdDevP, var et varP. Ces entrées ne portent aucun index x vers le cache, et elles sont incluses dans l'attribut count de items. Un rédacteur qui les omet produit une liste d'items qui ne correspond plus aux drapeaux de sous-total du même élément

HotXLS dérive la traîne de TXLSPivotField.Subtotals à la sauvegarde : un item t="…" par fonction de l'ensemble, en ordre d'énumération, ajouté au compte, plus l'attribut du genre sumSubtotal="1" sur le champ. Les champs créés par AddPivotTable démarrent avec un ensemble vide, ce qui écrit defaultSubtotal="0" et aucun item final du tout. Pour des rapports générés, laissez l'ensemble vide : les lignes de sous-total changent la hauteur du tableau croisé et décalent tout ce qui est placé dessous. N'ajoutez des fonctions à Subtotals que sur les champs où un lecteur a besoin des lignes supplémentaires

Le nommage mérite un second regard avant de choisir une fonction. xlpsCount mappe vers countA, qui compte toutes les entrées non vides, et xlpsCountNums mappe vers count, qui ne compte que les nombres. Quiconque lit « count » dans le XML et attrape xlpsCount obtient l'autre fonction

Anatomie de la liste d'items pivot de HotXLS où les entrées item de données sont suivies d'items de sous-total finaux dérivés de TXLSPivotField.Subtotals comme t=default et t=avg et comptés dans le items count, avec le piège de nommage xlpsCount vers countA et xlpsCountNums vers count explicité
Les champs issus d'AddPivotTable démarrent avec un ensemble Subtotals vide, ce qui écrit defaultSubtotal=0 et aucun item final — demandez les fonctions voulues et le générateur dérive un item par fonction dans le compte

Le programme ci-dessous construit un tableau croisé depuis une feuille de données existante avec un champ de ligne, un champ de colonne et un champ de valeur. Le champ Region obtient le sous-total automatique plus une moyenne, ce qui produit deux items finaux après ses items de données et dataField="1" sur Revenue

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets est en base 1
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil si aucun champ de ce nom
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // Revenue reçoit dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Lire ce qu'Excel omet

Le lecteur a le problème en miroir. Un champ pivot fabriqué par Excel liste ses entrées de sous-total dans items, et les charger comme membres ordinaires donnerait au champ des items fantômes qui ne pointent aucune valeur de cache. HotXLS saute tout item dont l'attribut t est présent et différent de data, ce qui couvre les entrées de sous-total, de total général et de vide. Les fonctions de sous-total arrivent par les propres drapeaux du champ et atterrissent dans Subtotals, si bien que le code qui parcourt Items ne voit que de vrais membres et que chaque CacheItemIndex réfère à une valeur de cache

Les attributs manquants sont l'autre moitié. Dans le schéma, defaultSubtotal sur CT_PivotField et containsString sur CT_SharedItems valent tous deux true par défaut, et Excel les omet quand ils portent cette valeur. HotXLS lit un defaultSubtotal absent comme true et ajoute xlpsDefault à Subtotals quand le champ ne porte aucun drapeau de sous-total explicite, ce qui est la façon dont Excel lui-même affiche un tel champ. Un containsString absent se lit aussi comme true, si bien qu'un champ de cache texte pur est classé comme données de type chaîne. Lisez l'un ou l'autre attribut comme false et chaque tableau croisé sauvegardé par Excel perd ses sous-totaux automatiques au chargement, pendant que les champs texte se transforment en un autre type. Un rédacteur qui épelle toujours chaque attribut ne touche jamais ce chemin ; seuls les fichiers d'un autre producteur l'exercent

Un classeur Excel chargé est la bonne entrée de test ici, car c'est le seul genre de fichier qui omet ces attributs. Le contrôle ci-dessous en ouvre un et parcourt un champ de ligne :

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // Un champ Excel sans attributs de sous-total rapporte quand même le défaut
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // Les entrées de sous-total ont été sautées, donc chaque item mappe une valeur de cache
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // contrôle suggéré pour vos propres tests
    end;
  finally
    Book.Free;
  end;
end;

Pourquoi numFmtId="General" est invalide sur les champs de cache

numFmtId="General" est invalide parce que ST_NumFmtId est un entier non signé, aussi naturel qu'il soit d'emprunter le nom que les utilisateurs voient dans la boîte de dialogue Format de cellule. Un analyseur qui type les attributs depuis le schéma rejette carrément le nom, et cette classe de défaillance est exactement celle qui devient une boîte de réparation dans Excel ; l'article sur les règles OPC et de balisage derrière l'invite de réparation d'Excel couvre la façon dont ces boîtes se déclenchent. HotXLS écrit chaque cacheField avec un numFmtId numérique tiré du NumberFormat du champ de cache, qui vaut 0, le format General intégré, sauf si quelque chose l'a réglé

Les tableaux croisés sous la ligne 65535 ont besoin de positions 32 bits

Un modèle de pivot partagé par les moteurs XLS et XLSX est tenté d'emprunter ses largeurs de champs à l'enregistrement BIFF8 SxView, où 16 bits couvrent chaque ligne qu'une feuille classique peut tenir. Une feuille XLSX court jusqu'à 1 048 576 lignes. Stockez FirstRow, LastRow, FirstHeaderRow, FirstDataRow et leurs équivalents en colonnes comme des Word, bornez-les avec Min(Value, High(Word)), et un tableau croisé ancré à la ligne 70001 se fait couper à 65535

Sur TXLSPivotTable, ces propriétés sont des Integer, et seul le rédacteur BIFF8 les resserre à la frontière d'enregistrement. TXLSXWorksheet.AddPivotTable renvoie nil pour une ancre hors de 1..1048576 par 1..16384, et AddPivotTableCopy renvoie nil quand l'étendue de la copie déborderait de la grille

Ancre de pivot à la ligne 70001 contre le plafond 16 bits : FirstRow et LastRow stockés comme Word et bornés avec Min contre High(Word) à 65535 coupent les pivots à la ligne ou au-delà, tandis que HotXLS les garde comme champs Integer avec un retour nil hors grille
Des champs de ligne larges comme Word tiennent dans les enregistrements SxView du BIFF8 mais pas dans une feuille qui court jusqu'à 1048576 lignes — avec eux, une ancre au-delà de la ligne 65535 est bornée à 65535 et le tableau croisé ne se trouve plus là où il a été placé
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // La ligne 70001 est au-delà de la plage 16 bits
    Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
    if Pivot = nil then
      Exit;  // ancre hors feuille ou plage source irrésoluble
    Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);
    Book.SaveAs('late.xlsx');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert est un contrôle suggéré pour vos propres tests
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Le moteur XLS classique suit la même convention en base 1. Les enregistrements SxView et DConRef stockent des valeurs en base 0, tandis que le modèle, comme le moteur XLSX et Cells[Row, Col], garde des positions en base 1 : le lecteur BIFF8 ajoute 1 et le rédacteur retranche 1 à la frontière d'enregistrement. Le AddPivotTable classique renvoie nil pour une ancre hors de 1..65536 par 1..256, donc ancrez les tableaux croisés en (1, 1) plutôt qu'en (0, 0). La disposition des enregistrements elle-même est décrite dans les enregistrements SX du BIFF8 derrière les tableaux croisés .xls classiques

Tester contre le schéma plutôt que contre votre propre lecteur

Chaque piège ci-dessus survit à un aller-retour quand le côté indulgent et le côté fautif vivent dans la même bibliothèque. Trois genres de contrôle les attrapent. Une validation de schéma des parties pivotTableN.xml et pivotCacheDefinitionN.xml générées attrape les jetons inventés, les éléments items vides et les ids de format nommés. Des fichiers sauvegardés par Excel, passés dans votre lecteur, exercent les attributs qu'Excel omet à leurs défauts. Des assertions qui épinglent le jeton exact dans le XML écrit, comme axis="axisRow", attrapent un rédacteur qui dérive vers une graphie que seul votre lecteur comprend ; affirmer sur l'axe analysé ne le peut pas

Les tableaux croisés construits via l'API, y compris les champs calculés, les items calculés et les dispositions pourcentage-du-total montrés dans la construction et le rafraîchissement de tableaux croisés XLSX avec champs calculés, sortent avec ces règles appliquées et aucun code supplémentaire de votre côté. Le travail qui vous reste est de choisir quels champs obtiennent des sous-totaux et quelle fonction de compte vous visez

Le composant tableur HotXLS pour Delphi construit, lit et sauvegarde des tableaux croisés XLS et XLSX depuis du code Delphi et C++Builder, sans installation d'Excel ni automatisation COM sur la machine qui l'exécute