Rijblok-celopslag en gestreamde XLSX-opslag in HotXLS

In SpreadsheetML heeft een kolom met een valutaformaat precies één element nodig: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 koppelt een celstijl op maar drie plekken: een kolom (§18.3.1.13), een rij (§18.3.1.73) en een cel (§18.3.1.4). Smal het formaat in tot B2:D500000 en SpreadsheetML heeft geen enkel element voor die vorm: een kolomstijl zou voorbij rij 500000 doorlopen en een rijstijl zou elke kolom dekken, dus een schrijver eindigt met het uitzenden ervan als style-only-cellen, 1.499.997 stuks

Een spreadsheetmodel in het geheugen botst één stap eerder op dezelfde muur. Als opmaak betekent dat je een property zet op elk celobject, wijst één klik op een kolomkop een miljoen objecten toe om één klein geheel getal te dragen, en een Delphi-proces dat alleen een getalnotatie wilde, raakt de adresruimte kwijt op een werkboek dat op schijf enkele kilobytes groot is. De echte ontwerpvraag is waar opmaak woont tijdens het bewerken, en wanneer hij in per-cel data verandert

Hoe HotXLS een bereik opmaakt zonder cellen te maken

HotXLS bewaart bereikopmaak als intervallen. Elke stijlsetter op TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — voegt één record toe aan de lijst StyleOverlays van het werkblad: vier coördinaten, de stijldelen die de aanroep claimt, en de poolindexen voor die delen. Cellen die al binnen de rechthoek bestaan pakken de nieuwe delen meteen op. Lege coördinaten blijven leeg, en er wordt niets voor hen toegewezen

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]);

    // Kolommen B..D over alle 1.048.576 rijen: één interval per aanroep
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Bouw de rand één keer, pas hem daarna toe via index
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1-based
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Verwacht 3.003: drie kopcellen plus 3.000 waarden
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

De twee opmaakaanroepen dekten 3.145.728 coördinaten en voegden niets toe aan die telling. De intervallen beginnen met opzet op rij 1: een interval over de volledige kolom wordt als één <col>-element opgeslagen, en de tekstkoppen in rij 1 worden toch niet geraakt door een getalnotatie. Bereiken die RCRange, Range['A1:C5'], EntireColumn en EntireRow teruggeven, zijn eigendom van het werkblad, dus het voorbeeld geeft ze nooit vrij

TXLSXFormatParts is wat voorkomt dat verschillende intervallen met elkaar vechten. Zijn leden zijn xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection en xfpCellControl, en elke setter op TXLSXRange claimt er precies één. Wanneer een cel wordt geresolved, worden de intervallen die hem dekken toegepast in de volgorde waarin ze zijn toegevoegd, elk schrijvend alleen zijn eigen delen, en expliciete opmaak op de cel zelf wint van allemaal. Een kolominterval dat de getalnotatie zet en een rijinterval dat een vulling zet, bereiken cel B5 allebei ongeschonden

Wanneer één record meerdere delen moet dragen, neemt Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) een TXLSXCellStyleState met daarin gevulde poolindexen. Add klemmt de coördinaten op het raster van 1.048.576 × 16.384, verwisselt omgekeerde hoeken, en geeft nil terug zonder iets vast te leggen wanneer Parts leeg is. Structurele bewerkingen houden de records in stap: InsertRows binnen een opgemaakt blok verbreedt het interval met het ingevoegde aantal, en invoegen erboven schuift het hele record omlaag

Waarom maakt SetBorders cellen als SetBorderIndex dat niet doet?

TXLSXRange.SetBorders(Kind, Style, Color) moet naar elke cel apart kijken, want het antwoord hangt af van de positie. Met xlsxEdgeOutline krijgt de bovenste rij een bovenrand, de linkerkolom een linkerrand, en het binnenste niets; met xlsxEdgeInside is het grofweg omgekeerd. De aanroep voegt ook de nieuwe randen samen met wat de cel al aan rand heeft, dus Outline gevolgd door Inside bouwt een volledig raster. Die per-cel samenstelling loopt via Cells[R, C], dat een cel aanmaakt wanneer er geen bestaat. Op een rapportblok van 10 × 40 is dat precies goed. Op EntireColumn(3) met xlsxEdgeAll zijn het 1.048.576 nieuwe celobjecten

De splitsing is dus simpel. Voor alles dat hoger is dan een scherm vol data, bouw de rand één keer en pas hem toe via index, zoals het eerste voorbeeld doet: TXLSXBorder.SetAll, dan Workbook.Borders.LookupOrAdd voor een 1-based index, dan SetBorderIndex. Houd SetBorders over voor blokken waarvan de omtrek en de binnenlijnen verschillen, zoals het rapportblok van 10 × 40 hierboven

Formaten teruglezen zonder het werk ongedaan te maken

Dezelfde val betreft code die alleen leest. Cells[R, C] is een accessor die bij een mis aanmaakt: vraag hem om de getalnotatie van B500000 en u krijgt het juiste antwoord plus een nieuwe, opgemaakte, lege cel op B500000 die nu wordt bewaard en opgeslagen. Lus dat over een kolom om opmaak te auditen, en u heeft het miljoen objecten herbouwd dat het interval juist moest vermijden, één onschuldig ogende lezing per keer

TXLSXWorksheet.GetCellVisibleFormat resolveert de effectieve stijl van een coördinaat zonder een cel toe te voegen. Hij voegt de werkbladstandaard samen met rij- en kolomintervallen, rechthoekintervallen, tabelstijlen en eventuele expliciete opmaak op de cel. De overload met een out ABuiltinNumberFormat-parameter meldt een ingebouwd Excel-formaat zoals 14 (korte datum) apart van NumberFormatIndex, dat in de custom-NumberFormats-pool van het werkboek wijst. Cells.HasCell(R, C) antwoordt of er überhaupt een fysieke cel bestaat

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets is 1-based
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Effectieve stijl, resolved zonder een cel aan het blad toe te voegen
    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 beantwoordt dezelfde vraag,
    // maar laat één nieuw celobject achter per rij die hij bezoekt
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Beide tellingen horen identiek te zijn voor en na de lus
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Draai zo'n controle één keer tegen uw echte workload. StyledBlankCellCount telt cellen die een stijl hebben en geen waarde, formule of rich text. Klimt hij tijdens een doorgang die read-only hoorde te zijn, dan maakt ergens een helper cellen puur om stijl te dragen, doorgaans door Cells[R, C] aan te roepen om te beslissen of een cel opmaak nodig heeft

Wat de blokken van 256 rijen voor uw code betekenen

Fysieke cellen wonen in blokken van 256 rijen. Binnen een blok houdt elke bezette rij zijn cellen in een lijst gesorteerd op kolom; een rij zonder cellen is alleen een lege plek, en een strekking van 256 rijen zonder cellen heeft helemaal geen blok. Een ijl blad met data in de rijen 3, 7 en 512 houdt er dus twee blokken op. Het opslaan doorloopt dezelfde structuur in rij-major volgorde, de volgorde die SpreadsheetML vereist voor <row>-elementen en hun <c>-kinderen

De TXLSXCell-referentie die u vasthoudt blijft hetzelfde object terwijl er om hem heen cellen worden toegevoegd of verwijderd; de blokken herordenen hun eigen administratie, niet uw handles. Bulkwriters zoals WriteFloatRowValues en WriteStringRowValues nemen op een fris blad een snellere route en voegen rij-major waarden toe in een verpakte opslag. De eerste per-cel toegang via Cells[R, C] zet die opslag één keer om naar celobjecten. GetStorageStatistics laat via PackedStorageActive en PackedCellCount zien in welke toestand u zit, naast RowBlockCount en PopulatedRowCount

Stijlpools worden bij het opslaan bijgesneden. Voordat styles.xml wordt geschreven, herbouwt HotXLS de font-, vulling-, rand-, getalnotatie-, uitlijnings- en beveiligingspools zodat ze alleen nog entries bevatten die in gebruik zijn, en een werkboek dat een gebruiker een uur lang heeft omgestileerd, verscheept niet elke achtergelaten vulling. Cellen die vastzitten aan een absolute FormatIndex houden hun plek onaangetast

Rij-gestreamde opslag en de getallen die het aantonen

TXLSXWorkbook.StreamingWrite staat standaard op True. Met hem aan, genereert SaveAs elke werkblad-<row> en schrijft hem rechtstreeks weg in de deflate-stream van zijn ZIP-entry, dus het grootste stuk werkblad-XML dat op elk moment in het geheugen zit is één enkele rij. Met hem uit, wordt de XML van elk werkblad eerst als één complete tekenreeks gebouwd en daarna gecomprimeerd, en piekgeheugen volgt het grootste blad. De style-only-cellen waar rechthoekintervallen in veranderen, het geval B2:D500000 uit de opening, worden ook in die lus per rij gegenereerd, dus ze voegen toe aan het bestand maar zitten nooit als één XML-blok in het geheugen

Gedeelde tekenreeksen en pivot cache-records volgen hetzelfde patroon. Elke <si>- of <r>-entry wordt op zichzelf gegenereerd en weggeschreven, dus op een breed analytisch werkboek hoeft de tabel met gedeelde tekenreeksen, vaak groter dan elk afzonderlijk werkblad, nooit als één XML-blok te bestaan

Niets hiervan hoeft op vertrouwen. Na elke opslag stelt het werkboek vier alleen-lezen properties bloot: LastSaveUsedStreaming, LastSavePeakRowXmlChars (de langste enkele rij, in tekens), LastSaveBufferedWorksheetXmlChars (de totale werkblad-XML in het geheugen, nul bij streaming) en LastSavePeakAuxiliaryXmlChars (de langste entry voor gedeelde tekenreeksen of pivot). LastSaveUsedStreaming is True voor .xlsx-, .xltx- en .xltm-uitvoer en voor de binaire .xlsb-schrijver; OpenDocument-uitvoer loopt via zijn eigen schrijver en meldt 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;

// Zelfde werkboek, beide modi; bekijk procesgeheugen in uw profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Op uw eigen data toont de buffered-regel een buffered-cijfer in de orde van de XML van het hele blad, terwijl de streaming-regel nul toont en een peakRow niet groter dan uw breedste rij. Laat StreamingWrite aan. De vlag beslist alleen of werkblad-XML volledig wordt geassembleerd vóór compressie, en LastSaveBufferedWorksheetXmlChars rapporteert de grootte van die assemblage in tekens

Waar de rest van het geheugen naartoe gaat

Zijn cellen en stijlen eenmaal compact, dan zijn de grootste verbruikers op een groot werkboek de tabel met gedeelde tekenreeksen en welke satellietdelen het bestand ook meedraagt: pivot caches, tekeningen, en bewaarde XML van delen die het objectmodel niet modelleert. Die hebben hun eigen regelaars, en geen enkele instelling dekt ze allemaal

Is het openen de flessenhals in plaats van het opslaan, dan is selectief laden de plek om te beginnen: de doorloop van metadata-only en selectief werkblad laden dekt het lezen van een werkboek zonder te betalen voor bladen die u niet aanraakt. Voor doorvoer op de leesroute bij zeer grote bestanden, zie de notities over parallelle XLSX-parsing en de geheugenallocator, en voor uitvoer-only taken die nooit een objectmodel nodig hebben, past streamed writes voor serverbatchtaken beter dan welke afstelling hier ook

HotXLS leest en schrijft XLS en XLSX vanuit native Delphi- en C++Builder-code zonder Excel of OLE-automatisering, dus elk getal hierboven komt uit uw eigen proces en kan daar worden gecontroleerd. Ondersteunde formaten en RAD Studio-versies staan vermeld op de HotXLS Delphi-spreadsheetcomponentpagina