Performanse velikih Excel radnih knjiga u Delphiju uz HotXLS

Kada izvoz od 300.000 redaka premaši proračun memorije, obično se krivi broj redaka. Međutim, broj redaka je obično nevin. Skupi dijelovi velike radne knjige su oni koji se stvaraju kao nuspojava: skup stilova (style pool) koji raste za jednu stavku po ćeliji jer je oblikovanje dodano unutar petlje, XML radnog lista sastavljen kao jedan golemi niz u trenutku spremanja, te milijun identičnih tijela formula pohranjenih jedno po jedno. HotXLS, nativna losLab biblioteka za Delphi za XLS i XLSX datoteke, daje vam specifičnu polugu za svaki od ovih troškova. Nijedna od njih nije omogućena prema zadanim postavkama jer svaka mijenja kompromise, pa je poznavanje koje poluge odgovaraju kojem simptomu stvarna vještina optimizacije performansi

Gdje velika radna knjiga troši memoriju

Postoje dva različita memorijska režima o kojima treba razmišljati. Tijekom generiranja, model ćelije u memoriji raste sa svakom ćelijom koju dotaknete: vrijednosti, formati i formule postaju objekti ili unosi u skupu. Tijekom spremanja, zadana XLSX staza dodatno renderira XML svakog radnog lista u široki niz znakova (wide string) prije nego što ga komprimira u zip spremnik, pa je vršna upotreba model plus serijalizirani oblik najvećeg lista. Zadatak koji preživi petlju izgradnje, a zatim se sruši unutar SaveAs, pogađa drugi režim, a ne prvi, i popravak za jedan ne pomaže kod drugog

Veličina datoteke slijedi slično pravilo: ćelije su samo jedan od čimbenika, uz stilove, dijeljene nizove znakova (shared strings), formule, slike i komentare. Revizija pomoću ForEachCell i broja zbirki po listu govori vam koji resurs zapravo dominira problematičnom datotekom prije nego što optimizirate krivi dio. Jedna suptilnost mjerenja: Sheet.Cells.Count na strani XLSX-a prijavljuje broj instanciranih ćelija u rijetkoj pohrani, a ne područje korištenog raspona. List čiji podaci zauzimaju pravokutnik 1000-sa-50 s polovicom praznih ćelija broji otprilike 25.000, a ne 50.000. Ta je razlika važna kada uspoređujete klijentovu "ogromnu" datoteku sa svojim testnim datotekama, jer se područje korištenog raspona i stvarna populacija ćelija mogu razlikovati za red veličine u rijetkim financijskim izgledima

StreamingWrite popravlja stazu spremanja, a ne stazu izgradnje

Postavljanje TXLSXWorkbook.StreamingWrite := True prebacuje SaveAs na strujni (streaming) serijalizator koji zapisuje XML radnog lista izravno u zip tok podataka, eliminirajući međukorak niza po listu. Zadani je parametar False radi kompatibilnosti ponašanja, a njegovo uključivanje je promjena u jednom retku:

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;   // XML radnog lista struji u zip spremnik
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Budite precizni oko toga što time dobivate: model ćelije izgrađen petljom zauzima potpuno jednako memorije kao i prije. StreamingWrite izravnava vršnu točku vremena spremanja, što čini razliku između grupnog posla koji se dovrši i onog koji ne uspije na 95% izvršenja. Ako sama petlja izgradnje iscrpi memoriju, poluge koje trebate su sljedeće dvije

Skupovi stilova: dodajte jednom, ponovno koristite indeks

Oblikovanje XLSX-a u HotXLS-u temelji se na skupu: Book.Fonts.Add(...), Fills.AddSolid(...) i Borders.Add(...) vraćaju 0-bazirani indeks skupa na koji se ćelije referiraju. Pozivanje Fonts.Add s identičnim parametrima unutar petlje se deduplicira, pa to gubi vrijeme, a ne prostor. Alignments.Add se ponaša drugačije: vraća novi objekt po pozivu, pa stvaranje poravnanja po ćeliji linearno povećava skup s brojem redaka. Jedna navika pokriva oba slučaja. Razriješite svaki indeks skupa jednom, izvan petlje, i dodijelite indekse unutar nje

// izvuci pretragu skupa izvan kritične petlje
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // indeks skupa temeljen na 0
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // ćelije pohranjuju vrijednost temeljenu na 1; 0 = zadano

Ovaj + 1 nije tipfeler i zaboravljanje istog je klasična greška koja ovdje stvara probleme: skupovi predaju indekse temeljene na 0, dok svojstva na strani ćelije tretiraju 0 kao "zadano", pa se svaki indeks skupa mora pomaknuti za jedan prilikom dodjele. Ako to propustite učiniti, vaša zaglavlja tiho se renderiraju u zadanoj font-obitelji radne knjige, što je nedostatak koji nitko ne primjećuje do revizije vizualnog identiteta

Zamijenite promet Variant tipa po ćeliji s povratnim pozivima redaka

Svaki Sheet.Cells[R, C].Value := X uključuje pretragu ili stvaranje ćelije plus dodjelu tipa Variant. Na nekoliko stotina tisuća ćelija, taj režijski trošak pristupa postaje mjerljiv u profiliranju performansi. HotXLS nudi API-je za skupne povratne pozive na oba sučelja (ForEachCell i ForEachRow za čitanje, WriteCells and WriteRows za pisanje) koji pomiču iteraciju unutar samog mehanizma i vašem kodu predaju cijele retke odjednom:

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;     // prekini cijelo pisanje
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// jedan poziv mehanizma umjesto stotina tisuća pristupa svojstvima
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Zastavica povratnog poziva Skip ostavlja redak netaknutim bez prekidanja, a Cancel prijevremeno završava operaciju, što je korisno kada je izvor čitač čiju duljinu otkrivate u hodu. Uparite WriteRows za izgradnju s StreamingWrite za spremanje i put generiranja više nema preostalih kritičnih točaka po ćeliji

Poluge na strani čitanja na XLS sučelju

Velike naslijeđene .xls datoteke imaju vlastiti alatni skup. _DisableGraphics := True prije Open u potpunosti preskače analizu sloja za crtanje, što ubrzava učitavanje radnih knjiga koje nose godine nakupljenih oblika i ugrađenih slika. Ograničenje je strogo: sloj za crtanje je tada odsutan iz modela, pa spremanje takve radne knjige stvara datoteku bez njezinih crteža. Rezervirajte ovu zastavicu za poslove analize koji su samo za čitanje. SetTempDir preusmjerava privremene datoteke BIFF pisca, što je važno na poslužiteljima gdje zadano privremeno mjesto ima ograničenje kvote ili se nalazi na sporoj pohrani. UseSharedFormulas grupiranje ponovljenih tijela formula u zapise dijeljenih formula, smanjujući datoteke u kojima se stupac formule ponavlja niz šezdeset tisuća redaka

Petlje čitanja preko XLS podataka imaju zamku indeksiranja koju vrijedi istaknuti jer udvostručuje rad kada se njome rukuje obrambeno, a kvari rezultate kada se propusti: UsedRange izvješćuje o svojim granicama FirstRow, LastRow, FirstCol i LastCol na temelju 0, dok je Cells.Item[Row, Col] na temelju 1. Skeniranje koje prolazi kroz korišteni raspon mora dodati jedan svakoj koordinati pri pristupu ćeliji, kao u Cells.Item[Row + 1, Col + 1], inače čita mrežu pomaknutu dijagonalno za jednu ćeliju, tiho odbacujući zadnji redak i stupac i uključujući fantomski prvi. Povratni poziv ForEachCell u potpunosti zaobilazi ovo nepodudaranje, što je još jedan razlog više da mu date prednost pri skeniranju cijelog lista

Ispitajte datoteke prije učitavanja

Najjeftinija operacija s velikom radnom knjigom je ona koju izbjegnete. GetSheetNames na oba sučelja prikazuje radne listove datoteke bez učitavanja podataka o ćelijama. Implementacija XLSX čita samo manifest radne knjige unutar zip-a i eksplicitno ostavlja instancu radne knjige nepopunjenom, a XLS sučelje zaustavlja skeniranje na prvoj granici podtoka. To ga čini ispravnom provjerom prije pokretanja za odluku "koji list bi trebao biti cilj ovog posla uvoza", a CanReadEncrypted odgovara na pitanje "je li ovo šifrirani spremnik" prije osuđenog pokušaja Open

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // neuspjeh prazni listu
  // odaberi ciljni list pa odluči isplati li se potpuni Open
finally
  Book.Free;
  Names.Free;
end;

Obratite pozornost na konvenciju povratnog koda: ove funkcije ispitivanja signaliziraju neuspjeh s vrijednostima na ili ispod nule i prazne izlaznu listu, stoga testirajte <= 0 umjesto uspoređivanja s jednom određenom vrijednošću uspjeha

Prilagodba pristupa vrsti posla

Za automatizirane cjevovode koji generiraju mnogo velikih datoteka u nizu, još dvije navike upotpunjuju sliku. Objekti radne knjige nisu sigurni za višenitno dijeljenje (thread-safe), ali ništa ne sprječava postojanje jedne neovisne radne knjige po radnoj niti, što čisto paralelizira skupnu pretvorbu. A kada izlaz ide na HTTP umjesto na disk, preopterećenja spremanja u TStream kombiniraju se s StreamingWrite tako da se veliki odgovor nikada ne materijalizira kao privremena datoteka. Vrijedi jedna operativna fusnota: spremanje u tok piše s trenutne pozicije bez premotavanja, pa postavite Position := 0 prije predaje toka frameworku za odgovore. Članak o strujnom pisanju i grupnim poslovima razvija taj uzorak na strani poslužitelja, a članak o izvozu baze podataka pokazuje gdje se te poluge uklapaju u izvješće vođeno skupom podataka

Na kraju, zadržite jednu testnu datoteku (fixture) najgoreg slučaja po obitelji izvješća i mjerite njezino vrijeme u CI cjevovodu. Regresije performansi u generiranju dokumenata rijetko se same najavljuju. Stil dodan unutar petlje ili ispitivanje zamijenjeno potpunim Open ne mijenja ništa funkcionalno, a noćna serija jednostavno traje četrdeset minuta dulje. Vremenski ograničeno testiranje na reprezentativnom uzorku od pola milijuna ćelija pretvara to odstupanje u crvenu izgradnju umjesto u operativni incident

Evaluacijske verzije, demo projekti s primjerom skupnog generiranja i cjelovita API referenca dostupni su na stranici HotXLS Delphi komponente