Stockage Blocs de Lignes et Sauvegarde Streaming en HotXLS

Dans SpreadsheetML, une colonne qui porte un format monétaire n'exige qu'exactement un élément : <col min="2" max="2" style="3"/>. ECMA-376 Part 1 attache un style de cellule en trois endroits seulement : une colonne (§18.3.1.13), une ligne (§18.3.1.73) et une cellule (§18.3.1.4). Restreignez le format à B2:D500000 et SpreadsheetML n'a aucun élément unique pour cette forme : un style de colonne courrait au-delà de la ligne 500000 et un style de ligne couvrirait chaque colonne, si bien qu'un rédacteur finit par émettre des cellules de style seul, 1 499 997 d'elles

Un modèle de feuille de calcul en mémoire bute sur le même mur une étape plus tôt. Si formater signifie poser une propriété sur chaque objet cellule, un clic sur un en-tête de colonne alloue un million d'objets pour porter un petit entier, et un processus Delphi qui ne voulait qu'un format de nombre manque d'espace d'adressage sur un classeur qui fait quelques kilooctets sur disque. La vraie question de design est de savoir où vit le formatage pendant l'édition, et quand il devient des données par cellule

Comment HotXLS formate une plage sans créer de cellules

HotXLS garde le formatage de plage sous forme d'intervalles. Chaque poseur de style sur TXLSXRange (SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex) ajoute un enregistrement à la liste StyleOverlays de la feuille : quatre coordonnées, les parties de style que l'appel possède, et les index de pool pour ces parties. Les cellules qui existent déjà dans le rectangle ramassent les nouvelles parties sur-le-champ. Les coordonnées vides restent vides, et rien n'est alloué pour elles

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // Colonnes B..D sur les 1 048 576 lignes : un intervalle par appel
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Bâtir la bordure une fois, puis l'appliquer par index
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // démarre à 1
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Attendre 3 003 : trois cellules d'en-tête plus 3 000 valeurs
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Les deux appels de formatage ont couvert 3 145 728 coordonnées et n'ont rien ajouté à ce compte. Les intervalles démarrent à la ligne 1 à dessein : un intervalle pleine colonne se sauve en un seul élément <col>, et les en-têtes de texte de la ligne 1 ne sont pas affectés par un format de nombre de toute façon. Les plages renvoyées par RCRange, Range['A1:C5'], EntireColumn et EntireRow sont possédées par la feuille, donc l'exemple ne les libère jamais

TXLSXFormatParts est ce qui empêche plusieurs intervalles de se battre. Ses membres sont xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection et xfpCellControl, et chaque poseur de TXLSXRange revendique exactement l'un d'entre eux. Quand une cellule est résolue, les intervalles qui la couvrent sont appliqués dans l'ordre où ils ont été ajoutés, chacun n'écrivant que ses propres parties, et le formatage explicite de la cellule elle-même l'emporte sur tous. Un intervalle de colonne qui pose le format de nombre et un intervalle de ligne qui pose un remplissage atteignent tous deux la cellule B5 intacts

Quand un enregistrement doit porter plusieurs parties, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) prend un TXLSXCellStyleState avec les index de pool remplis. Add borne les coordonnées à la grille de 1 048 576 × 16 384, échange les coins inversés, et renvoie nil sans rien enregistrer quand Parts est vide. Les éditions structurelles gardent les enregistrements en phase : InsertRows à l'intérieur d'un bloc formaté élargit l'intervalle du compte inséré, et insérer au-dessus décale tout l'enregistrement vers le bas

Pourquoi SetBorders crée-t-il des cellules quand SetBorderIndex n'en crée pas ?

TXLSXRange.SetBorders(Kind, Style, Color) doit regarder chaque cellule pour son compte, car la réponse dépend de la position. Avec xlsxEdgeOutline, la ligne du haut reçoit un bord haut, la colonne de gauche un bord gauche, et l'intérieur rien ; avec xlsxEdgeInside c'est à peu près l'inverse. L'appel fusionne aussi les nouveaux bords dans la bordure que la cellule a déjà, donc Outline suivi de Inside bâtit une grille complète. Cette composition par cellule passe par Cells[R, C], qui crée une cellule quand il n'en existe pas. Sur un bloc de rapport de 10 × 40, c'est exactement ce qu'il faut. Sur EntireColumn(3) avec xlsxEdgeAll, ce sont 1 048 576 nouveaux objets cellule

La scission est donc simple. Pour tout ce qui dépasse un écran de données, bâtissez la bordure une fois et appliquez-la par index, comme le fait le premier exemple : TXLSXBorder.SetAll, puis Workbook.Borders.LookupOrAdd pour un index à base 1, puis SetBorderIndex. Gardez SetBorders pour les blocs dont le contour et les lignes internes diffèrent, comme le bloc de rapport de 10 × 40 ci-dessus

Relire les formats sans défaire le travail

Le même piège attrape le code qui ne fait que lire. Cells[R, C] est un accesseur à création au taux manqué : demandez-lui le format de nombre de B500000 et vous obtenez la bonne réponse plus une nouvelle cellule vide stylée en B500000 qui sera désormais stockée et sauvegardée. Bouclez cela sur une colonne pour auditer le formatage et vous avez rebâti le million d'objets que l'intervalle voulait éviter, une lecture innocente à la fois

TXLSXWorksheet.GetCellVisibleFormat résout le style effectif d'une coordonnée sans ajouter de cellule. Il fusionne le défaut de la feuille, les intervalles de ligne et colonne, les intervalles de rectangle, les styles de tableaux et tout formatage explicite de la cellule. La surcharge avec un paramètre out ABuiltinNumberFormat rapporte un format Excel intégré comme 14 (date courte) séparément de NumberFormatIndex, qui pointe dans le pool personnalisé NumberFormats du classeur. Cells.HasCell(R, C) répond à la question de savoir si une cellule physique existe du tout

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets démarre à 1
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Style effectif, résolu sans ajouter de cellule à la feuille
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndex répond à la même question,
    // mais laisse un nouvel objet cellule derrière pour chaque ligne visitée
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Les deux comptes devraient être identiques avant et après la boucle
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Lancez un contrôle de ce genre une fois contre votre vraie charge. StyledBlankCellCount compte les cellules qui ont un style et ni valeur, ni formule, ni texte riche. S'il grimpe pendant une passe censée être en lecture seule, quelque aideur crée des cellules uniquement pour porter du style, typiquement en appelant Cells[R, C] pour décider si une cellule a besoin de formatage

Ce que les blocs de 256 lignes signifient pour votre code

Les cellules physiques vivent en blocs de 256 lignes. À l'intérieur d'un bloc, chaque ligne peuplée garde ses cellules dans une liste triée par colonne ; une ligne sans cellules n'est qu'un emplacement vide, et une étendue de 256 lignes sans cellules n'a aucun bloc du tout. Une feuille creuse avec des données aux lignes 3, 7 et 512 tient donc deux blocs. La sauvegarde parcourt la même structure en ordre ligne-majeure, l'ordre que SpreadsheetML exige pour les éléments <row> et leurs enfants <c>

La référence TXLSXCell que vous tenez reste le même objet tandis que d'autres cellules sont ajoutées ou retirées autour ; les blocs réorganisent leur propre comptabilité, pas vos handles. Les rédacteurs en masse comme WriteFloatRowValues et WriteStringRowValues empruntent une voie plus rapide sur une feuille neuve et annexent des valeurs ligne-majeures dans un magasin compacté. Le premier accès par cellule via Cells[R, C] convertit ce magasin en objets cellule une seule fois. GetStorageStatistics montre l'état dans lequel vous êtes via PackedStorageActive et PackedCellCount, à côté de RowBlockCount et PopulatedRowCount

Les pools de styles sont rognés à la sauvegarde. Avant que styles.xml ne soit écrit, HotXLS rebâtit les pools de polices, remplissages, bordures, formats de nombre, alignements et protections pour ne tenir que les entrées encore en usage, et un classeur qu'un utilisateur a restylé pendant une heure ne livre pas chaque remplissage abandonné. Les cellules épinglées à un FormatIndex absolu gardent leur emplacement intact

Sauvegardes en flux par lignes et chiffres qui le montrent

TXLSXWorkbook.StreamingWrite vaut True par défaut. Avec lui actif, SaveAs génère chaque <row> de feuille et l'écrit droit dans le flux deflate de son entrée ZIP, si bien que le plus grand morceau de XML de feuille en mémoire à tout instant est une seule ligne. Avec lui inactif, le XML de chaque feuille est d'abord bâti en une chaîne complète puis compressé, et la mémoire de pointe suit la plus grande feuille. Les cellules de style seul que les intervalles de rectangle deviennent, le cas B2:D500000 du début, sont générées dans cette boucle par ligne aussi, donc elles ajoutent au fichier sans jamais siéger en mémoire comme un bloc de XML

Les chaînes partagées et les enregistrements de cache de tableaux croisés suivent le même motif. Chaque entrée <si> ou <r> est générée et écrite pour son compte, donc sur un classeur analytique large, la table de chaînes partagées, souvent plus grosse que toute feuille, ne doit jamais exister comme un bloc de XML

Rien de tout cela ne se prend sur parole. Après chaque sauvegarde, le classeur expose quatre propriétés en lecture seule : LastSaveUsedStreaming, LastSavePeakRowXmlChars (la plus longue ligne unique, en caractères), LastSaveBufferedWorksheetXmlChars (le total de XML de feuille tenu en mémoire, zéro en streaming) et LastSavePeakAuxiliaryXmlChars (la plus longue entrée de chaîne partagée ou de tableau croisé). LastSaveUsedStreaming est True pour les sorties .xlsx, .xltx et .xltm et pour le rédacteur binaire .xlsb ; la sortie OpenDocument passe par son propre rédacteur et rapporte False

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// Même classeur, les deux modes ; observez la mémoire du processus dans votre profileur
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Sur vos propres données, la ligne bufferisée montre un chiffre buffered de l'ordre du XML de toute la feuille, tandis que la ligne streaming montre zéro et un peakRow pas plus gros que votre ligne la plus large. Laissez StreamingWrite actif. Le drapeau décide seulement de savoir si le XML de feuille est assemblé en entier avant compression, et LastSaveBufferedWorksheetXmlChars rapporte la taille de cet assemblage en caractères

Où va la mémoire restante

Une fois cellules et styles compactés, les plus gros consommateurs suivants sur un gros classeur sont la table de chaînes partagées et les parties satellites que le fichier porte : caches de tableaux croisés, dessins, et XML préservé de parties que le modèle objet ne modélise pas. Ceux-là ont leurs propres leviers, et aucun réglage unique ne les couvre tous

Si l'ouverture est le goulot plutôt que la sauvegarde, le chargement sélectif est l'endroit où commencer : la visite guidée du chargement de feuilles en métadonnées seules et sélectif couvre la lecture d'un classeur sans payer pour les feuilles que vous ne toucherez pas. Pour le débit en lecture sur de très gros fichiers, voyez les notes sur l'analyse XLSX parallèle et l'allocateur mémoire, et pour des travaux en sortie seule qui n'ont jamais besoin d'un modèle objet, les écritures en streaming pour travaux par lots serveur convient mieux que n'importe quel réglage ici

HotXLS lit et écrit XLS et XLSX depuis du code Delphi et C++Builder natif sans Excel ni automation OLE, donc chaque chiffre ci-dessus vient de votre propre processus et peut y être vérifié. Formats pris en charge et versions de RAD Studio sont listés sur la page du composant tableur HotXLS Delphi