Performance des grands classeurs Excel dans Delphi avec HotXLS

Quand un export de 300 000 lignes dépasse son budget mémoire, le nombre de lignes est généralement accusé — et il est généralement innocent. Les parties coûteuses d'un grand classeur sont celles créées par effet de bord : un pool de styles qui grossit d'une entrée par cellule parce que le formatage a été ajouté dans la boucle, le XML de feuille assemblé comme une seule chaîne géante au moment de l'enregistrement, un million de corps de formules identiques stockés un par un. HotXLS, la bibliothèque native Delphi de losLab pour les fichiers XLS et XLSX, vous donne un levier précis pour chacun de ces coûts. Aucun n'est activé par défaut, car chacun change un compromis ; savoir quel levier correspond à quel symptôme est donc la vraie compétence de performance.

Où un grand classeur dépense sa mémoire

Il y a deux régimes mémoire distincts à raisonner. Pendant la génération, le modèle de cellules en mémoire grandit avec chaque cellule touchée — valeurs, formats et formules deviennent tous des objets ou des entrées de pool. Pendant l'enregistrement, le chemin XLSX par défaut rend en plus le XML de chaque feuille dans une chaîne large avant de le compresser dans le conteneur zip ; le pic d'utilisation est donc le modèle plus la forme sérialisée de la plus grande feuille. Un job qui survit à la boucle de construction puis meurt dans SaveAs frappe le deuxième régime, pas le premier — et la correction de l'un ne change rien à l'autre.

La taille de fichier suit une règle proche : les cellules ne sont qu'un contributeur, à côté des styles, chaînes partagées, formules, images et commentaires. Une passe d'audit avec ForEachCell et les compteurs de collections par feuille vous indique quelle ressource domine réellement un fichier problématique avant d'optimiser la mauvaise. Une subtilité de mesure : Sheet.Cells.Count côté XLSX indique le nombre de cellules instanciées dans le stockage creux, pas l'aire de la plage utilisée — une feuille dont les données occupent un rectangle 1000 par 50 avec la moitié des cellules vides compte environ 25 000, pas 50 000. Cette distinction compte lorsque vous comparez le fichier « énorme » d'un client à vos fixtures, car l'aire utilisée et la population réelle de cellules peuvent différer d'un ordre de grandeur dans les mises en page financières creuses.

StreamingWrite corrige le chemin d'enregistrement, pas la construction

Définir TXLSXWorkbook.StreamingWrite := True bascule SaveAs vers un sérialiseur streaming qui écrit le XML de feuille directement dans le flux zip, en supprimant l'intermédiaire chaîne par feuille. La valeur par défaut est False pour compatibilité comportementale, et l'activer tient en une ligne :

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // sheet XML streams into the zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Soyez précis sur ce que cela apporte : le modèle de cellules construit par la boucle occupe exactement autant de mémoire qu'avant. StreamingWrite aplatit le pic au moment de l'enregistrement, ce qui fait la différence entre un batch qui se termine et un autre qui échoue à 95 % — mais si la boucle de construction elle-même épuise la mémoire, les leviers utiles sont les deux suivants.

Pools de styles : ajouter une fois, réutiliser l'index

Le formatage XLSX dans HotXLS repose sur des pools : Book.Fonts.Add(...), Fills.AddSolid(...) et Borders.Add(...) renvoient un index de pool 0-based que les cellules référencent. Appeler Fonts.Add avec des paramètres identiques dans une boucle est dédupliqué, donc cela gaspille du temps plutôt que de l'espace — mais Alignments.Add renvoie un nouvel objet à chaque appel, si bien que créer l'alignement par cellule fait grossir le pool linéairement avec le nombre de lignes. L'habitude robuste couvre les deux cas : résolvez chaque index de pool une fois, hors de la boucle, et affectez les indices dedans.

// hoist pool lookups out of the hot loop
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-based pool index
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cells store 1-based; 0 = default

Le + 1 n'est pas une faute de frappe, et l'oublier est le bug classique qui génère les symptômes ici : les pools distribuent des indices 0-based, tandis que les propriétés côté cellule traitent 0 comme « défaut », donc chaque index de pool doit être décalé d'une unité à l'affectation. Omettez-le et vos en-têtes se rendent silencieusement avec la police par défaut du classeur — défaut que personne ne remarque avant une revue de charte graphique.

Remplacer le trafic Variant par cellule par des callbacks de lignes

Chaque Sheet.Cells[R, C].Value := X implique une recherche ou création de cellule plus une affectation Variant. À quelques centaines de milliers de cellules, ce coût par accès devient mesurable dans les profils. HotXLS fournit des API de callbacks en lot sur les deux façades — ForEachCell et ForEachRow pour la lecture, WriteCells et WriteRows pour l'écriture — qui déplacent l'itération dans le moteur et remettent à votre code des lignes entières :

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // stop the whole write
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// one engine call instead of hundreds of thousands of property hits
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

L'indicateur Skip du callback laisse une ligne intacte sans interrompre, et Cancel termine l'opération plus tôt — utile lorsque la source est un reader dont vous découvrez la longueur en avançant. Associez WriteRows pour la construction à StreamingWrite pour l'enregistrement et le chemin de génération n'a plus de point chaud par cellule.

Leviers côté lecture sur la façade XLS

Les gros fichiers .xls legacy ont leur propre boîte à outils. _DisableGraphics := True avant Open saute complètement l'analyse de la couche dessin, ce qui accélère le chargement des classeurs qui transportent des années de formes et d'images intégrées accumulées — avec une restriction dure : la couche dessin est alors absente du modèle, donc enregistrer un tel classeur écrit un fichier sans ses dessins. Réservez cet indicateur aux jobs d'analyse en lecture seule. SetTempDir redirige les fichiers temporaires du writer BIFF, ce qui compte sur les serveurs où l'emplacement temp par défaut a un quota ou repose sur un stockage lent. UseSharedFormulas regroupe les corps de formules répétés en enregistrements de formules partagées, réduisant les fichiers où une colonne de formules se répète sur soixante mille lignes.

Les boucles de lecture sur des données XLS ont un piège d'indexation à signaler, car le traiter défensivement double le travail et l'ignorer corrompt les résultats : UsedRange rapporte ses limites FirstRow, LastRow, FirstCol et LastCol en 0-based, tandis que Cells.Item[Row, Col] est 1-based. Un scan qui parcourt la plage utilisée doit ajouter un à chaque coordonnée lors de l'accès cellule — Cells.Item[Row + 1, Col + 1] — sinon il lit une grille décalée diagonalement d'une cellule, en supprimant silencieusement la dernière ligne et colonne et en ajoutant une première fantôme. Le callback ForEachCell contourne entièrement ce décalage, raison de plus pour le préférer aux scans de feuille complète.

Sonder les fichiers avant de les charger

L'opération la moins chère sur un grand classeur est celle que vous évitez. GetSheetNames sur les deux façades liste les feuilles d'un fichier sans charger les données de cellules — l'implémentation XLSX lit seulement le manifeste de classeur dans le zip et laisse explicitement l'instance de classeur non peuplée, tandis que la façade XLS arrête le scan à la première frontière de sous-flux. C'est donc le bon contrôle de prévol pour « quelle feuille ce job d'import doit-il cibler », et CanReadEncrypted répond à « est-ce un conteneur chiffré » avant une tentative Open vouée à l'échec.

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // failure clears the list
  // pick the target sheet, then decide whether a full Open is worth it
finally
  Book.Free;
  Names.Free;
end;

Notez la convention de code retour : ces fonctions de sondage signalent l'échec par des valeurs inférieures ou égales à zéro et vident la liste de sortie ; testez donc <= 0 plutôt que de comparer à une valeur positive précise.

Dimensionner l'approche au job

Pour les chaînes sans surveillance qui génèrent beaucoup de grands fichiers à la suite, deux habitudes complètent le tableau. Les objets classeur ne sont pas thread-safe en partage, mais rien n'empêche un classeur indépendant par worker thread, ce qui parallélise proprement la conversion batch. Et lorsque la sortie part vers HTTP plutôt que vers le disque, les surcharges d'enregistrement TStream se combinent à StreamingWrite afin qu'une grosse réponse ne se matérialise jamais en fichier temporaire — avec une note opérationnelle : l'enregistrement dans un stream écrit depuis la position courante sans rembobiner, donc définissez Position := 0 avant de remettre le stream au framework de réponse. L'article sur l'écriture streaming et les batchs développe ce modèle serveur, et l'article sur l'export de base de données montre où insérer ces leviers dans un rapport alimenté par dataset.

Enfin, gardez une fixture pire cas par famille de rapports et chronométrez-la en CI. Les régressions de performance en génération documentaire ne s'annoncent presque jamais — un style ajouté dans une boucle ou une sonde remplacée par un Open complet ne change rien fonctionnellement, et le batch nocturne prend simplement quarante minutes de plus. Un test chronométré sur une fixture représentative d'un demi-million de cellules transforme cette dérive en build rouge au lieu d'un incident d'exploitation.

Les builds d'évaluation, les projets de démonstration avec un exemple de génération en masse et la référence complète de l'API sont disponibles sur la page HotXLS Component.