Lire en flux (Stream) d'énormes fichiers XLSX dans Delphi sans les charger

Une feuille de calcul avec un million de lignes et une douzaine de colonnes est un export tout à fait ordinaire à partir d'une tâche de rapport de base de données. Ouvrez-la de la manière habituelle, en chargeant tout le classeur dans un TXLSWorkbook, et le processus doit matérialiser chacune de ces douze millions de cellules sous forme d'objet actif (live object) avant l'exécution de votre première ligne de logique métier. Le fichier sur le disque peut représenter soixante mégaoctets de XML compressé. L'arborescence d'objets dans laquelle il se déploie fait plusieurs fois cette taille, et tout doit être résident en même temps car le modèle est conçu pour un accès aléatoire (random-access). Pour un rapport que vous avez l'intention de lire de haut en bas et de jeter, cela représente beaucoup de mémoire dépensée pour une structure dont vous n'avez jamais eu besoin

Il existe un deuxième chemin à travers le même fichier. Au lieu de construire un modèle, vous analysez le XML de la feuille de calcul uniquement vers l'avant (forward only), une cellule à la fois, et laissez chaque cellule s'écouler après l'avoir examinée. Rien ne s'accumule. La mémoire reste presque constante, que la feuille compte mille lignes ou dix millions, car le lecteur (reader) ne conserve jamais plus que la partie qu'il est en train d'analyser plus quelques petites tables de recherche. C'est ce que fait le lecteur direct HotXLS, et le reste de cet article explique pourquoi il reste de petite taille et ce qu'il vous offre en échange

Pourquoi le modèle en mémoire n'évolue pas (does not scale)

Un fichier XLSX est un paquet ZIP de parties XML décrites par ECMA-376. Chaque feuille de calcul est sa propre partie, xl/worksheets/sheetN.xml, et à l'intérieur, chaque ligne est un élément <row> contenant des éléments de cellule <c>. Le chemin de chargement normal lit cette partie et construit un objet adressable pour chaque cellule afin que vous puissiez demander plus tard Cells[12345, 7] et obtenir une réponse en temps constant. L'accès aléatoire est tout l'intérêt d'un modèle de classeur, et c'est exactement ce qui rend l'édition, l'évaluation des formules et la mise en forme pratiques

Le coût est que l'accès aléatoire exige que tout soit présent simultanément. Vous ne pouvez pas faire d'indexation dans une structure que vous n'avez que partiellement construite. Ainsi, le pic de mémoire d'un chargement complet est fonction du nombre de cellules, et sur une feuille contenant des millions de cellules renseignées, cette fonction atterrit à un endroit où votre service ne veut pas se trouver, surtout si plusieurs tâches de ce type s'exécutent simultanément sur une machine partagée. Lorsque le modèle d'accès dont vous avez réellement besoin est séquentiel, payer pour un accès aléatoire, c'est payer pour une capacité que vous n'utiliserez pas

Une analyse SAX à sens unique (forward-only) qui ne construit aucune arborescence

Le lecteur direct ouvre le paquet ZIP et parcourt chaque partie de la feuille de calcul avec un analyseur syntaxique de type "pull" (pull parser) de style SAX. Ici, SAX signifie que l'analyseur (parser) signale les événements d'analyse au fur et à mesure qu'il les rencontre : un élément de début, une séquence de texte (text run), un élément de fin, puis il passe à autre chose. Il ne conserve aucune arborescence de nœuds derrière lui. Le lecteur suit la ligne et la colonne actuelles à partir des attributs r, rassemble le type de la cellule, l'index de style, la valeur et le texte de la formule au fur et à mesure que les événements arrivent, et lorsque la balise de fermeture </c> est vue, il émet une cellule et l'oublie. La cellule suivante réutilise la même poignée de variables locales

Comme rien n'est conservé entre les cellules, l'empreinte mémoire n'augmente pas avec le nombre de cellules. C'est la propriété qu'il vaut la peine de retenir. Une feuille de deux cents lignes et une feuille de vingt millions de lignes coûtent au lecteur la même mémoire résidente, et la seule différence entre elles est la durée de l'analyse (scan). Vous renoncez à l'accès aléatoire, la fonctionnalité phare du modèle, et en retour vous obtenez un plafond de mémoire que le nombre de cellules ne peut pas dépasser

Ce qui reste résident, et pourquoi ces deux parties

L'analyse n'est pas entièrement sans état (stateless), et les exceptions sont instructives. Deux petites tables doivent être conservées en mémoire pour toute la durée, car une cellule à elle seule ne contient pas suffisamment d'informations pour être interprétée sans elles

La première est la table des chaînes partagées. Dans SpreadsheetML, une cellule de texte ne stocke pas son propre texte. Elle porte t="s" et une charge utile (payload) numérique qui est un index dans xl/sharedStrings.xml, une liste unique dédupliquée de chaque chaîne distincte dans le classeur. Il s'agit d'un bon compromis d'espace pour les fichiers où les mêmes étiquettes se répètent sur des milliers de lignes, mais cela signifie que le lecteur doit charger cette table de chaînes au préalable et la garder résidente, car n'importe quelle cellule n'importe où dans n'importe quelle feuille peut référencer n'importe quelle entrée à l'intérieur. La taille de la table est fonction du nombre de chaînes distinctes, et non du nombre de cellules, de sorte qu'elle reste modeste même sur des feuilles immenses

La seconde est le mappage du format numérique à partir de la partie styles. Une cellule numérique et une cellule de date sont identiques octet par octet sur le réseau (on the wire) : les deux sont un simple nombre, car une date dans SpreadsheetML n'est qu'un numéro de série représentant un nombre de jours. La seule chose qui les distingue est le style de la cellule, qui pointe via cellXfs dans xl/styles.xml vers un identifiant de format numérique. Pour signaler une date en tant que date plutôt qu'en tant que numéro de série brut, le lecteur charge cette table style-vers-format et la garde résidente. Tout le reste dans le fichier, les données réelles de la cellule qui constituent l'essentiel des octets, passe en flux (streams) sans être stocké

Chaque cellule signale un type (kind) et une valeur

Chaque cellule émise arrive sous la forme d'un enregistrement TXLSDirectCell. Elle contient l'index et le nom de la feuille, la ligne et la colonne de base 1, un Kind (type) sémantique, la Value (valeur) en tant que Variant, le texte de la Formula (formule) sans son signe égal de début, et le StyleIndex brut. Le type (kind) est l'un des suivants : xdkNumber, xdkString, xdkBoolean, xdkDate ou xdkError, ce qui vous permet d'orienter le traitement en fonction de la signification de la cellule plutôt que de la déduire à nouveau des attributs. Une cellule de formule signale le type de son résultat mis en cache, avec le texte de la formule à côté, de sorte qu'un total calculé arrive sous la forme d'un nombre qui vous indique également comment il a été produit

type
  TReportScan = class
    procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
      var Abort: Boolean);
  end;

procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
  var Abort: Boolean);
begin
  case Cell.Kind of
    xdkString:  AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
    xdkNumber:  AddToTotals(Cell.Col, Double(Cell.Value));
    xdkDate:    NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
    xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
    xdkError:   LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
  end;
end;

Distinguer une date d'un nombre

La question des dates mérite qu'on s'y attarde, car c'est là que la plupart des scanneurs naïfs se trompent. Il n'y a pas de type "date" sur une cellule numérique. Une cellule contenant la valeur de série 46000 pourrait être une quantité, un prix ou le 17 février 2025, et le fichier ne vous l'indique que via l'identifiant de format numérique accessible via le style de la cellule. ECMA-376 réserve un bloc d'identifiants de format intégrés dont la signification est fixe pour tous les producteurs conformes, et les identifiants porteurs de date se situent dans deux plages : 14 à 22 pour les formats de date et d'heure standard, et 45 à 47 pour les formats de temps écoulé tels que [h]:mm:ss. Lorsque DetectDates est activé, ce qui est le cas par défaut, le lecteur résout le style de chaque cellule numérique vers son identifiant de format, et une cellule dont l'identifiant se trouve dans ces plages réservées est signalée comme xdkDate avec sa Value déjà convertie en un TDateTime Delphi. Les formats personnalisés sont également vérifiés, en inspectant le code de format pour y trouver des jetons de date et d'heure, mais les plages réservées constituent la colonne vertébrale fiable. Désactivez DetectDates et la table des styles n'est même pas chargée, chaque cellule numérique arrive sous la forme de xdkNumber, et l'analyse (scan) est un peu plus légère

Ignorer (skip) des feuilles et interrompre (abort) prématurément

L'analyse séquentielle présente un avantage discret que l'accès aléatoire ne peut égaler : vous pouvez vous arrêter. L'événement OnSheet se déclenche avant l'ouverture de chaque feuille de calcul, et il vous donne deux commutateurs. Définissez SkipSheet et cette partie entière n'est jamais analysée, ce qui vous permet de n'analyser que les feuilles qui vous intéressent dans un classeur multi-feuilles sans payer pour lire le reste. Définissez Abort et l'analyse entière se termine immédiatement. L'événement OnCell porte son propre Abort, vous pouvez donc vous arrêter au moment où vous avez trouvé ce que vous cherchiez (une ligne particulière, une valeur sentinelle, la fin d'un bloc d'en-tête) sans lire les millions de cellules restantes. Sur une analyse à sens unique (forward-only), l'interruption (abort) est véritablement gratuite, car le travail que vous ignorez est un travail qui ne s'était pas encore produit

procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
  const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
  // Analyser uniquement la feuille "Data" ; ne pas lire le reste
  SkipSheet := SheetName <> 'Data';
end;

Compter les cellules sans gestionnaire (handler)

Un perfectionnement récent mérite d'être souligné, car il transforme une question courante en un seul appel peu coûteux. Le lecteur compte chaque cellule renseignée devant laquelle il passe, et il le fait qu'un gestionnaire (handler) OnCell soit attaché ou non. Auparavant, sans gestionnaire défini, le nombre de cellules renseignées renvoyé était de zéro, car le comptage était un effet secondaire de l'émission. Désormais, le comptage est indépendant de l'émission. Cela signifie que vous pouvez poser une seule question, "combien de cellules renseignées ce classeur contient-il réellement", et obtenir la réponse pour le prix d'une analyse (scan) sans aucun rappel (callbacks). ReadFile et ReadStream renvoient tous deux ce total sous forme de Int64, et le même nombre est disponible par la suite en tant que propriété CellCount. Un retour de -1 signale que le fichier n'a pas pu être ouvert ou qu'il ne s'agit pas d'un package OOXML

var
  Reader: TXLSDirectReader;
  Populated: Int64;
begin
  Reader := TXLSDirectReader.Create;
  try
    // Pas de gestionnaire OnCell : un recensement pur de cellules renseignées, mémoire toujours quasi constante
    Populated := Reader.ReadFile('quarterly_export.xlsx');
    if Populated < 0 then
      raise Exception.Create('Not a readable XLSX package')
    else
      Writeln(Format('%d cellules renseignees (CellCount = %d)',
        [Populated, Reader.CellCount]));
  finally
    Reader.Free;
  end;
end;

Pour l'analyse (scan) complète, vous attachez le gestionnaire et appelez ReadFile exactement de la même manière. Le contraste avec un chargement complet est tout l'intérêt : là où le chargement de quarterly_export.xlsx dans un classeur développerait chaque cellule en un objet résident et conserverait le tout, le lecteur direct ne conserve que les chaînes partagées et la table de styles pendant que les douze millions de cellules circulent dans votre OnCell une à la fois. L'arithmétique qui s'est exécutée par cellule ne laisse rien derrière elle, le pic de mémoire est donc défini par le nombre de chaînes distinctes du classeur, et non par son nombre de lignes

Le lecteur direct est le bon outil lorsque la tâche consiste à lire une seule fois un classeur volumineux et à l'extraire ou à le résumer. Lorsque vous avez plutôt besoin de l'accès aléatoire du modèle complet mais que vous souhaitez qu'il se comporte bien sur de gros fichiers, l'optimisation abordée dans nos notes sur les performances des classeurs volumineux dans Delphi couvre ce chemin. Et lorsque la direction est inversée, produisant une grande quantité de sortie plutôt que de la consommer, la présentation détaillée de l'écriture en flux (streaming-write) pour les tâches par lots (batch jobs) de serveur applique la même discipline de mémoire constante à l'écriture. Les trois sont livrés dans le cadre du Composant HotXLS pour Delphi et C++Builder, aux côtés des API de lecture, d'écriture, de formule et de mise en forme abordées ailleurs sur ce blog