Une feuille de calcul avec un million de lignes et une douzaine de colonnes est une exportation parfaitement ordinaire d'une tâche de rapport de base de données. Ouvrez-la de la manière habituelle, en chargeant l'intégralité du classeur dans un TXLSWorkbook, et le processus doit matérialiser chacune de ces douze millions de cellules en tant qu'objet actif avant même que votre première ligne de logique métier ne s'exécute. Le fichier sur le disque peut représenter soixante mégaoctets de XML compressé. L'arborescence d'objets dans laquelle il se développe représente plusieurs fois cela, et tout doit être résident en même temps car le modèle est à accès aléatoire de par sa conception. Pour un rapport que vous avez l'intention de lire de haut en bas et de jeter, c'est une grande quantité 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 vers l'avant uniquement, une cellule à la fois, et laissez chaque cellule passer 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 ne détient 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 la suite de cet article explique pourquoi il reste petit et ce qu'il vous offre en échange
Pourquoi le modèle en mémoire ne passe pas à l'échelle
Un fichier XLSX est un package 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 plus tard demander 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 le style pratiques
Le coût est que l'accès aléatoire nécessite que tout soit présent simultanément. Vous ne pouvez pas indexer dans une structure que vous n'avez que partiellement construite. Ainsi, le pic de mémoire d'une charge complète est fonction du nombre de cellules, et sur une feuille contenant des millions de cellules renseignées, cette fonction atterrit quelque part où votre service ne veut pas être, en particulier si plusieurs tâches de ce type s'exécutent en même temps 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 fonctionnalité que vous n'utiliserez pas
Une analyse SAX vers l'avant uniquement qui ne construit aucun arbre
Le lecteur direct ouvre le package ZIP et parcourt chaque partie de feuille de calcul avec un analyseur de type SAX. SAX signifie ici que l'analyseur signale les événements d'analyse au fur et à mesure qu'il les rencontre, un élément de début, une exécution de texte, un élément de fin, puis passe à autre chose. Il ne garde aucun arbre de nœuds derrière lui. Le lecteur suit la ligne et la colonne actuelles à partir des attributs r, rassemble le type de 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
Étant donné que 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 conserver. 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 différence entre elles n'est que la durée de l'analyse. Vous renoncez à l'accès aléatoire, la fonctionnalité phare du modèle, et en retour vous obtenez un plafond sur la mémoire que le nombre de cellules ne peut pas franchir
Ce qui reste résident et pourquoi ces deux parties
L'analyse n'est pas entièrement apatride, et les exceptions sont instructives. Deux petites tables doivent être conservées en mémoire pendant la durée, car une cellule 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 numérique qui est un index dans xl/sharedStrings.xml, une liste unique dédupliquée de chaque chaîne distincte du 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 à l'avance et la garder résidente, car n'importe quelle cellule n'importe où dans n'importe quelle feuille peut faire référence à n'importe quelle entrée dans celle-ci. La taille de la table dépend du nombre de chaînes distinctes, et non du nombre de cellules, elle reste donc modeste même sur d'énormes feuilles
La seconde est le mappage du format de nombre à partir de la partie styles. Une cellule numérique et une cellule de date sont octet pour octet identiques sur le fil : les deux sont un nombre simple, car une date dans SpreadsheetML n'est qu'un nombre de jours de série. 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 de nombre. Pour signaler une date comme une date plutôt que comme le numéro de série brut, le lecteur charge cette table de style à format et la maintient résidente. Tout le reste du fichier, les données réelles de la cellule qui constituent la majeure partie des octets, passe sans être stocké
Chaque cellule signale un type et une valeur
Chaque cellule émise arrive sous forme d'enregistrement TXLSDirectCell. Il contient l'index et le nom de la feuille, la ligne et la colonne basées sur 1, un Kind sémantique, la Value en tant que Variant, le texte de la Formula sans son signe égal de début et le StyleIndex brut. Le type est l'un des suivants : xdkNumber, xdkString, xdkBoolean, xdkDate ou xdkError, de sorte que vous pouvez bifurquer sur la signification de la cellule plutôt que de la redériver à partir 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é apparaît comme 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 de la date mérite d'être examinée de plus près car c'est là que la plupart des scanners naïfs se trompent. Il n'y a pas de type de date sur une cellule numérique. Une cellule contenant la valeur de série 46000 peut être une quantité, un prix ou le 17 février 2025, et le fichier vous indique laquelle uniquement via l'identifiant du format de nombre atteint via le style de la cellule. ECMA-376 réserve un bloc d'identifiants de format intégrés dont la signification est fixée par tous les producteurs conformes, et les identifiants porteurs de date se situent dans deux plages : de 14 à 22 pour les formats de date et d'heure standard, et de 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 tombe 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 les jetons de date et d'heure, mais les plages réservées constituent l'épine dorsale fiable. Désactivez DetectDates et la table des styles n'est même pas chargée, chaque cellule numérique apparaît comme xdkNumber, et l'analyse est légèrement plus allégée
Ignorer des feuilles et annuler prématurément
L'analyse séquentielle présente un avantage silencieux 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 ne sera jamais analysée, c'est ainsi que vous analysez uniquement les feuilles qui vous intéressent dans un classeur à plusieurs 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 vers l'avant uniquement, l'abandon est véritablement gratuit, car le travail que vous sautez 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" ; laisser le reste non lu
SkipSheet := SheetName <> 'Data';
end;Compter les cellules sans gestionnaire
Un raffinement 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 qu'il passe, et il le fait qu'un gestionnaire OnCell soit attaché ou non. Auparavant, sans gestionnaire défini, le nombre de cellules renseignées revenait à zéro, car le comptage était un effet secondaire de l'émission. Désormais, le décompte est indépendant de l'émission. Cela signifie que vous pouvez poser une question, combien de cellules renseignées ce classeur contient-il réellement, et obtenir la réponse pour le prix d'une analyse sans aucun rappel. ReadFile et ReadStream renvoient tous deux ce total sous la forme d'un 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 n'est pas un package OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// Pas de gestionnaire OnCell : un pur recensement de cellules renseignées, mémoire toujours presque constante
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Pour l'analyse complète, vous attachez le gestionnaire et appelez ReadFile exactement de la même manière. Le contraste avec une charge complète est tout l'intérêt : là où le chargement de quarterly_export.xlsx dans un classeur étendrait chaque cellule en un objet résident et conserverait le lot, le lecteur direct ne conserve que les chaînes partagées et la table de styles pendant que les douze millions de cellules transitent par votre OnCell une par une. L'arithmétique qui s'est exécutée par cellule ne laisse rien derrière, de sorte que le pic de mémoire est 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 le travail consiste à lire une seule fois un grand classeur 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 voulez qu'il se comporte bien sur de gros fichiers, le réglage dans nos notes sur les performances des grands classeurs dans Delphi couvre cette voie. Et lorsque la direction est inversée, produisant une grande sortie plutôt que de la consommer, la procédure d'écriture en continu pour les tâches par lots sur le serveur applique la même discipline de mémoire constante à l'écriture. Les trois sont livrés dans le composant HotXLS pour Delphi et C++Builder, aux côtés des API de lecture, d'écriture, de formule et de formatage couvertes ailleurs sur ce blog