Crearea unui banc de lucru pentru auditul și conversia registrelor în Delphi cu HotXLS

Un job de normalizare în masă a foilor de calcul este trei probleme îmbrăcate într-un singur veșmânt. Aveți o arhivă cu formate mixte: .xls din era BIFF, .xlsx modern, o presărare de .ods dintr-un experiment LibreOffice și o mână de fișiere pe care nimeni nu le poate deschide pentru că parola a plecat odată cu un fost angajat. Obiectivul este să convertiți totul în XLSX și CSV. Versiunea acestui job pe care o scriu majoritatea oamenilor este o buclă care deschide fiecare fișier și îl salvează sub o nouă extensie, și funcționează exact până când cineva întreabă care fișiere și-au pierdut diagramele, și-au aruncat macrocomenzile sau nu s-au deschis deloc. Bucla nu are niciun răspuns, pentru că simpla conversie nu păstrează nicio evidență. O bancă de lucru o face: inventariază mai întâi, convertește al doilea, verifică al treilea, iar cele trei etape trebuie să partajeze informații pentru ca oricare dintre ele să fie de încredere

Asamblarea acelei bănci de lucru în Delphi sau C++Builder înseamnă conectarea a patru capabilități HotXLS, dintre care niciuna nu are nevoie de Excel instalat undeva în pipeline. Există două motoare native, o fațadă BIFF8 pentru .xls și o fațadă OOXML pentru .xlsx și .ods. Există apeluri de sondare ieftine care citesc metadate fără să analizeze întregul fișier. Există contoare de audit per foaie care vă spun ce conține cu adevărat un registru de lucru. Și există o matrice de conversie cu un profil de fidelitate documentat pentru fiecare rută. Munca este să știți unde fiecare dintre acestea are o muchie ascuțită, pentru că fiecare o are, iar muchiile sunt exact lucrurile care transformă un lot curat de peste noapte într-un incident de luni dimineața

Sondați înainte de a încărca: nume de foi și detectarea criptării

Deschiderea unui registru de lucru de 200 MB doar pentru a descoperi că este criptat irosește minute per fișier, iar multiplicat pe o arhivă mare, irosește zile. Ambele fațade expun GetSheetNames, care citește metadatele foilor fără să populeze registrul de lucru. Implementarea BIFF scanează doar înregistrările BoundSheet de la începutul fluxului; implementarea OOXML citește doar workbook.xml din interiorul arhivei zip. Alături de aceasta, CanReadEncrypted detectează un container de criptare fără să încerce decriptarea:

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

Două detalii operaționale fac această buclă ieftină. GetSheetNames nu resetează și nici nu populează instanța registrului de lucru, așa că un singur obiect de sondare poate clasifica mii de fișiere fără să fie recreat. Iar versiunea pentru fațada XLS a aceluiași apel înțelege și pachetele .xlsx, ceea ce îl face o sondă unică și convenabilă atunci când extensiile fișierelor nu pot fi de încredere, așa cum rareori pot fi într-o arhivă atât de veche. Triajul înainte de încărcare merită propriul tratament; mecanismele inspecției ușoare se află în articolul nostru despre listarea foilor și inspecția ușoară a registrelor de lucru

Numărarea a ceea ce chiar conține un registru de lucru

Odată ce un fișier trece de triaj, trecerea de audit decide ruta lui de conversie. Fațada XLSX expune un contor pentru fiecare familie de funcționalități care influențează o decizie de fidelitate: celule îmbinate, diagrame, imagini, formate condiționate, validări de date, tabele, hyperlink-uri și comentarii, plus indicatori la nivel de registru de lucru pentru macrocomenzi, protecție și format sursă. Ruta de conversie pentru un fișier depinde aproape în întregime de care dintre acestea revin diferite de zero

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

Citiți Cells.Count ținând cont de o avertizare. Depozitul de celule este rar, așa că numărul contorizează celulele instanțiate, nu aria dreptunghiulară a intervalului utilizat. O foaie cu o valoare în A1 și alta în ZZ9999 raportează două celule, nu milionul și ceva care se află între ele. Scanarea echivalentă pe partea BIFF folosește limitele UsedRange împreună cu ForEachCell, și poartă eroarea off-by-one care încurcă aproape pe toată lumea prima dată: UsedRange.FirstRow și frații lui sunt bazați pe 0, în timp ce Cells.Item[Row, Col] este bazat pe 1. O parcurgere care uită să adauge unu la fiecare limită auditează dreptunghiul greșit și nu o spune niciodată

Două pârghii reduc costul unei treceri doar de audit peste fișiere legacy mari. Setarea _DisableGraphics pe true înainte de a deschide un .xls omite complet analiza stratului de desen OfficeArt, ceea ce economisește timp real pe registrele de lucru dense în forme. Este strict o optimizare doar-citire, totuși: salvarea dintr-o instanță deschisă în acest fel ar arunca desenele pe care nu le-a analizat niciodată, așa că flag-ul aparține doar căilor care nu vor scrie niciodată fișierul înapoi. Când auditul are nevoie de conținut per celulă, nu de numere, callback-ul ForEachCell parcurge direct celulele populate și ocolește suprasarcina Variant per acces pe care proprietățile de celulă indexate o plătesc la fiecare citire, ceea ce se adună rapid pe milioane de celule

Normalizați devreme codurile de retur inconsistente

Apelurile de I/O din HotXLS raportează erorile prin rezultate întregi, nu prin excepții, iar convențiile nu sunt uniforme în întregul API. Majoritatea apelurilor de deschidere și salvare returnează 1 la succes și -1 la eșec. GetSheetNames returnează numărul de foi, sau -1 cu lista golită. SaveAsHTML din XLSX rupe din nou tiparul și returnează 0 pentru succes, -1 pentru un index de foaie în afara intervalului. O bancă de lucru care testează = 1 peste tot va clasifica greșit, tacit, apelurile care semnalează succesul în alt mod, iar una care testează <> -1 va înghiți pe cele care eșuează cu un cod diferit

Regula care rezistă contactului cu întregul API este mai îngustă decât pare: tratați <= 0 ca eșec pentru apelurile care returnează un număr, verificați valoarea de succes documentată pentru fiecare rutină de salvare pe care chiar o folosiți, și puneți-le pe amândouă în spatele unei singure funcții mici de verificare a rezultatului, astfel încât convenția să trăiască într-un singur loc. Pipeline-urile de lot eșuează mult mai des dintr-o acumulare lentă de coduri de retur neverificate decât din vreo eroare exotică de parser, iar costul de a greși aici apare patruzeci de mii de fișiere mai târziu, când nimeni nu-și mai amintește care conversii chiar au reușit

Matricea de conversie și unde pierde date fiecare drum

Cele două fațade își împart munca de conversie între ele. TXLSXWorkbook deschide XLSX, ODS și CSV și salvează XLSX, ODS, CSV, HTML, RTF și XLSX criptat AES. TXLSWorkbook deschide și salvează BIFF și exportă HTML, RTF și CSV. Lucrul util este că fiecare cale vine cu un profil de fidelitate documentat, nu o promisiune vagă de corectitudine, așa că puteți decide din timp care rute sunt sigure pentru care fișiere

Exportul CSV scrie UTF-8 cu BOM, sfârșituri de linie CRLF și citare RFC 4180. Ce nu face este să evalueze formulele: o celulă ce conține =SUM(...) exportă textul literal al formulei, așa că o foaie de formule devine o foaie de șiruri, cu excepția cazului în care calculați valorile mai întâi. Exportul HTML produce un singur tabel, cu colspan și rowspan reprezentând celulele îmbinate și stilurile de bază inline. Exportul RTF are o limită mai dură: nu poate întinde celule îmbinate peste coloane, așa că celulele de continuare ale unei îmbinări ies goale. Importul ODS este ușor în mod intenționat, chiar conform documentației bibliotecii. Valorile scalare și rezultatele din cache ale formulelor trec; stilurile, expresiile de formule ODF active și desenele nu. Acest lucru contează în momentul în care arhiva conține fișiere OpenDocument reale, guvernate de OASIS ODF 1.3, unde orice se apropie de o conversie fidelă vizual are nevoie de mai mult decât a fost construită să transporte această cale de import, iar trecerea de audit este cea care vă spune că acele fișiere există înainte ca lotul să le aplatizeze tacit

SaveXLSWorkbookAsXLSX este o punte de date, nu o punte de aspect

Fațada BIFF nu poate scrie OOXML direct, așa că trecerea de la .xls la .xlsx se face prin funcția SaveXLSWorkbookAsXLSX din unitatea lxXlsxExport. Fidelitatea acelei punți merită afirmată clar, pentru că numele sugerează mai mult decât face. Copiază valori, formule, formate numerice, culori de umplere, atribute de bază ale fontului, lățimi de coloane și setări de vizualizare precum liniile de grilă. Nu copiază borduri, intervale îmbinate, comentarii, diagrame sau formate condiționate. Pentru normalizare la nivel de date, unde sistemele din aval vor analiza rezultatul și nimeni nu se uită la formatare, asta este exact suficient și nu se pierde nimic de care cineva să aibă nevoie. Pentru un raport de consiliu formatat, destinat citirii de către o persoană, nu este suficient, iar aici exact contoarele de audit își câștigă locul: un fișier pe care auditul l-a marcat drept purtător de diagrame și formate condiționate ar trebui rutat spre o coadă manuală, nu printr-o punte care va arunca ambele fără niciun cuvânt

var
  Legacy: IXLSWorkbook;        // referință interfață: nu apelați Free
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // trimite XML-ul foii în flux în arhiva zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Bucla de mai sus arată și pârghia de throughput pe partea OOXML. Setarea StreamingWrite pe true trimite XML-ul foii de lucru direct în pachetul de ieșire, în loc să îl țină ca un șir gigantic în memorie, ceea ce reprezintă diferența dintre o rulare confortabilă și o prăbușire din lipsă de memorie odată ce fișierele ajung la sute de mii de rânduri. Comportamentul de dimensionare și memorie pentru acel mod primește propriul tratament în articolul nostru despre scrierile în flux pentru job-uri de lot pe server. Mai contează încă o proprietate pentru un lot care vrea să folosească fiecare nucleu: nicio fațadă nu este thread-safe, dar niciuna nu partajează stare globală, așa că modelul acceptat pentru conversia paralelă este o instanță de registru de lucru per thread de worker, fără niciun blocaj între ele

Fișierele cu parolă, și ce să faceți cu ele

Fișierele blocate din arhivă se împart curat pe format, iar această împărțire decide unde ajung. Criptarea .xls veche, fie RC4, RC4 peste CryptoAPI, sau vechea obfuscare XOR, este lizibilă: transmiteți parola la Open și fișierul se convertește ca oricare altul. Pachetele .xlsx criptate sunt o altă poveste. HotXLS le detectează cu CanReadEncrypted, dar nu le poate decripta, așa că singura mișcare onestă este să le rutați către o coadă unde un om deschide și resalvează fiecare fișier în Excel înainte ca acesta să se alăture din nou pipeline-ului. Această asimetrie merită proiectată din start, pentru că fișierele XLSX criptate sunt cele mai probabile a fi înregistrările la care cineva chiar ține

Închiderea buclei cu verificare

A treia etapă este cea care se sare, iar a o sări este ceea ce transformă o conversie în masă într-o vulnerabilitate. Nicio cale de salvare din HotXLS nu evaluează formulele. Excel recalculează atunci când deschide un fișier, așa că o conversie XLSX-la-XLSX rămâne corectă, dar o țintă CSV primește textul formulei cuvânt cu cuvânt, cu excepția cazului în care pipeline-ul rulează mai întâi Calculate pe celule și scrie rezultatele înapoi. A ști asta din timp este diferența dintre un CSV plin de numere și un CSV plin de șiruri =SUM(...) pe care nimeni nu le observă până când un import din aval se sufocă cu ele

Verificarea în sine este suficient de ieftină încât nu există nicio scuză să o omiteți. Redeschideți fiecare fișier convertit cu aceeași bibliotecă, rerulați contoarele de audit și comparați-le cu numerele dinaintea conversiei pe care trecerea de inventariere le-a înregistrat deja. Un număr de foi care a scăzut, un număr de diagrame care a ajuns la zero acolo unde sursa avea trei, un număr de celule care s-a prăbușit: fiecare este o pierdere tăcută prinsă la costul unei a doua deschideri. Verificați prin sondaj cu ochiul liber un eșantion în Excel sau LibreOffice, pe deasupra, iar combinația prinde marea majoritate a daunelor de conversie înainte ca acestea să ajungă la client. Acesta este întregul motiv pentru care etapa de inventariere alimentează etapa de verificare. Fără numerele de dinainte, numerele de după nu dovedesc nimic

O bancă de lucru care pune auditul primul transformă o conversie în masă riscantă într-un proces măsurabil, cu o bandă de carantină pentru fișierele care nu pot trece curat. Toate apelurile de sondare, numărare și conversie prezentate aici fac parte din HotXLS Delphi Component, care le rulează nativ în proces, fără automatizare Excel