Grupna normalizacija tabela predstavlja tri problema u jednom kaputu. Imate arhivu mešovitih formata: .xls iz BIFF ere, moderni .xlsx, nekoliko .ods datoteka iz nekog LibreOffice eksperimenta i šaku datoteka koje niko ne može da otvori jer je lozinka otišla zajedno sa bivšim zaposlenim. Cilj je konvertovati sve u XLSX i CSV. Verzija ovog posla koju većina ljudi napiše je petlja koja otvara svaku datoteku i čuva je pod novom ekstenzijom, i to funkcioniše sve dok neko ne pita koje su datoteke izgubile svoje grafikone, odbacile makroe ili se uopšte nisu otvorile. Petlja nema odgovor jer sama konverzija ne vodi evidenciju. Radno okruženje vodi: prvo vrši popis, zatim konvertuje i na kraju verifikuje, a ove tri faze moraju deliti informacije kako bi bilo šta od toga bilo pouzdano.
Sklapanje tog radnog okruženja u Delphi-ju ili C++Builder-u znači povezivanje četiri mogućnosti HotXLS-a, od kojih nijedna ne zahteva instaliran Excel bilo gde u procesu. Postoje dva nativna mehanizma: BIFF8 fasada za .xls i OOXML fasada za .xlsx i .ods. Postoje brzi pozivi za sondiranje koji čitaju metapodatke bez analiziranja cele datoteke. Postoje brojači revizije po listu koji vam govore šta radna sveska zaista sadrži. I postoji matrica konverzije sa dokumentovanim profilom vernosti za svaku rutu. Posao se sastoji u tome da znate gde svaka od ovih stavki ima oštru ivicu, jer svaka od njih ima, a te ivice su upravo ono što pretvara čist noćni paketni posao u problem u ponedeljak ujutru.
Sondiranje pre učitavanja: nazivi listova i detekcija šifrovanja
Otvaranje radne sveske od 200 MB samo da bi se otkrilo da je šifrovana troši minute po datoteci, što se na velikoj arhivi pretvara u protraćene dane. Obe fasade izlažu GetSheetNames, koji čita metapodatke listova bez popunjavanja radne sveske. BIFF implementacija skenira samo zapise BoundSheet na početku toka; OOXML implementacija čita samo workbook.xml unutar zip arhive. Pored toga, CanReadEncrypted detektuje kontejner šifrovanja bez pokušaja dešifrovanja:
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;Dva operativna detalja čine ovu petlju brzom. GetSheetNames ne resetuje i ne popunjava instancu radne sveske, tako da jedan objekat sonde može klasifikovati hiljade datoteka bez ponovnog kreiranja. Takođe, verzija istog poziva u XLS fasadi razume i .xlsx pakete, što je čini praktičnom jedinstvenom sondom kada se ekstenzijama datoteka ne može verovati, što je čest slučaj u tako staroj arhivi. Trijaža pre učitavanja zaslužuje posebnu pažnju; mehanika lagane inspekcije opisana je u našem članku o listanju listova i laganoj inspekciji radnih svezaka.
Brojanje onoga što radna sveska zaista sadrži
Nakon što datoteka prođe trijažu, prolaz revizije odlučuje o njenoj ruti konverzije. XLSX fasada izlaže brojač za svaku familiju funkcija koja utiče na odluku o vernosti konverzije: spojene ćelije, grafikoni, slike, uslovni formati, validacije podataka, tabele, hiperveze i komentari, uz zastavice na nivou radne sveske za makroe, zaštitu i izvorni format. Ruta konverzije za datoteku zavisi skoro u potpunosti od toga koji od ovih brojača vrate vrednost različitu od nule.
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;Čitajte Cells.Count sa jednom napomenom na umu. Skladište ćelija je retko (sparse), tako da ovaj broj označava kreirane ćelije, a ne pravougaonu oblast korišćenog opsega. List sa jednom vrednošću u A1 i drugom u ZZ9999 prijavljuje dve ćelije, a ne milion i više njih koje leže između njih. Ekvivalentno skeniranje na strani BIFF-a koristi granice UsedRange zajedno sa ForEachCell, i nosi sa sobom grešku „off-by-one“ (grešku za jedan) koja skoro svakoga saplete prvi put: UsedRange.FirstRow i srodna svojstva su indeksirana od 0, dok je Cells.Item[Row, Col] indeksiran od 1. Prolazak koji zaboravi da doda jedinicu svakoj granici vrši reviziju pogrešnog pravougaonika i to nikada ne prijavljuje.
Dve opcije smanjuju opterećenje prolaza koji vrši samo reviziju nad velikim starim datotekama. Postavljanje _DisableGraphics na true pre otvaranja .xls datoteke potpuno preskače analiziranje OfficeArt sloja za crtanje, što štedi značajno vreme kod radnih svezaka koje su guste sa oblicima. Međutim, ovo je striktno optimizacija samo za čitanje: čuvanje iz instance otvorene na taj način bi odbacilo crteže koje nije analizirala, tako da ova zastavica pripada samo putanjama koje nikada neće upisati datoteku nazad. Kada je reviziji potreban sadržaj po ćeliji, a ne samo brojevi, povratni poziv ForEachCell direktno prolazi kroz popunjene ćelije i izbegava režijski trošak Variant tipa po pristupu koji indeksirana svojstva ćelija plaćaju pri svakom čitanju, što se brzo akumulira na milionima ćelija.
Standardizujte neusaglašene kodove grešaka na vreme
HotXLS I/O pozivi prijavljuju greške preko celobrojnih rezultata umesto kroz izuzetke (exceptions), a konvencije nisu ujednačene u celom API-ju. Većina poziva za otvaranje i čuvanje vraća 1 u slučaju uspeha i -1 u slučaju neuspeha. GetSheetNames vraća broj listova, ili -1 sa očišćenom listom. XLSX SaveAsHTML ponovo narušava ovaj šablon i vraća 0 za uspeh, a -1 ako je indeks lista van opsega. Radno okruženje koje svuda testira = 1 tiho će pogrešno klasifikovati pozive koji uspeh izvršenja signaliziraju na neki drugi način, dok će ono koje testira <> -1 progutati one koji ne uspeju sa drugačijim kodom.
Pravilo koje funkcioniše za ceo API je jednostavnije nego što izgleda: tretirajte <= 0 kao neuspeh za pozive koji vraćaju broj, proverite dokumentovanu vrednost uspeha za svaku rutinu čuvanja koju zaista koristite i postavite oba iza jedne male funkcije za proveru rezultata kako bi ta konvencija živela na tačno jednom mestu. Grupni procesi mnogo češće otkazuju zbog sporog nagomilavanja neproverenih kodova grešaka nego zbog bilo kakve neobične greške u parseru, a cena ove greške se plaća četrdeset hiljada datoteka kasnije, kada se niko ne seća koje su konverzije zapravo uspele.
Matrica konverzije i gde koja putanja gubi podatke
Dve fasade dele posao konverzije između sebe. TXLSXWorkbook otvara XLSX, ODS i CSV, a čuva XLSX, ODS, CSV, HTML, RTF i AES-šifrovani XLSX. TXLSWorkbook otvara i čuva BIFF, i izvozi HTML, RTF i CSV. Korisna stvar je što svaka putanja dolazi sa dokumentovanim profilom vernosti, a ne neodređenim obećanjem ispravnosti, tako da unapred možete odlučiti koje su rute bezbedne za koje datoteke.
Izvoz u CSV upisuje UTF-8 sa BOM oznakom, CRLF završecima redova i navodnicima prema RFC 4180. Ono što ne radi jeste izračunavanje formula: ćelija koja sadrži =SUM(...) izvozi se kao doslovni tekst formule, tako da se list sa formulama pretvara u list sa tekstualnim nizovima osim ako prvo ne izračunate vrednosti. Izvoz u HTML generiše jednu tabelu, gde colspan i rowspan zamenjuju spojene ćelije, a osnovni stilovi su ugrađeni (inlined). Izvoz u RTF ima strože ograničenje: ne može da prostire spojene ćelije kroz kolone, pa preostale ćelije spajanja ostaju prazne. Uvoz ODS-a je namerno lagan, prema sopstvenoj dokumentaciji biblioteke. Skalarne vrednosti i keširani rezultati formula se učitavaju; stilovi, aktivni ODF izrazi formula i crteži ne prolaze. To postaje važno onog trenutka kada arhiva sadrži stvarne OpenDocument datoteke koje podležu standardu OASIS ODF 1.3, gde je za bilo šta blisko vizuelno vernoj konverziji potrebno više nego što ova putanja uvoza može da pruži, a prolaz revizije je ono što vam govori da te datoteke postoje pre nego što ih proces tiho spljošti.
SaveXLSWorkbookAsXLSX je most za podatke, a ne za izgled
BIFF fasada ne može direktno upisivati OOXML, pa se prelazak sa .xls na .xlsx odvija preko funkcije SaveXLSWorkbookAsXLSX u jedinici lxXlsxExport. Vernost tog mosta vredi jasno naglasiti, jer naziv sugeriše više nego što zapravo pruža. On kopira vrednosti, formule, formate brojeva, boje popune, osnovne atribute fonta, širine kolona i podešavanja prikaza kao što su linije mreže. Ne kopira ivice, spojene opsege, komentare, grafikone niti uslovne formate. Za normalizaciju na nivou podataka, gde će nizvodni sistemi analizirati rezultat i niko ne gleda formatiranje, to je sasvim dovoljno i ništa se ne gubi što bi nekome trebalo. Za formatirani izveštaj upravnog odbora koji treba da čita osoba, to nije dovoljno, i tu brojači revizije zarađuju svoje mesto: datoteka koju je revizija označila da sadrži grafikone i uslovne formate treba da se usmeri u red za ručnu obradu, a ne kroz most koji će oboje odbaciti bez ikakve poruke.
var
Legacy: IXLSWorkbook; // interface reference: do not 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; // stream sheet XML into the zip
if Modern.Open(FileName) = 1 then
Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Modern.Free;
end;
end;
end;Gornja petlja takođe pokazuje mogućnost povećanja propusnosti na strani OOXML-a. Postavljanje StreamingWrite na true strimuje XML radnog lista direktno u izlazni paket umesto da ga priprema kao jedan ogroman tekstualni niz u memoriji, što čini razliku između stabilnog rada i pada sistema usled nedostatka memorije kada datoteke dostignu stotine hiljada redova. Veličina i ponašanje memorije za ovaj režim opisani su u našem članku o strimovanju upisa za serverske grupne poslove. Još jedno svojstvo je važno za grupne poslove koji žele da koriste svako jezgro: nijedna fasada nije bezbedna za niti (thread-safe), ali nijedna ne deli ni globalno stanje, tako da je podržani šablon za paralelnu konverziju jedna instanca radne sveske po radnoj niti, bez međusobnog zaključavanja.
Zaštićene datoteke i šta raditi sa njima
Zaključane datoteke u arhivi se jasno dele prema formatu, a ta podela određuje gde idu. Staro .xls šifrovanje, bilo RC4, RC4 preko CryptoAPI-ja ili stara XOR opstrukcija, može se pročitati: prosledite lozinku metodi Open i datoteka se konvertuje kao i svaka druga. Šifrovani .xlsx paketi su druga priča. HotXLS ih detektuje pomoću CanReadEncrypted ali ih ne može dešifrovati, tako da je jedini ispravan potez preusmeriti ih u red gde čovek otvara i ponovo čuva svaku datoteku u Excel-u pre nego što se vrati u proces. Ovu asimetriju vredi isplanirati unapred, jer su šifrovane XLSX datoteke najčešće zapisi do kojih je nekome zapravo stalo.
Zatvaranje procesa verifikacijom
Treća faza je ona koja se preskače, a njeno preskakanje pretvara masovnu konverziju u rizik. Nijedna putanja čuvanja u HotXLS-u ne izračunava formule. Excel vrši ponovno izračunavanje kada otvori datoteku, tako da konverzija iz XLSX u XLSX ostaje ispravna, ali CSV cilj dobija doslovni tekst formule osim ako proces prvo ne pokrene Calculate na ćelijama i upiše rezultate nazad. Znati to unapred čini razliku između CSV-a punog brojeva i CSV-a punog tekstualnih nizova poput =SUM(...) koje niko ne primećuje sve dok uvoz u nizvodni sistem ne otkaže.
Sama verifikacija je dovoljno brza da nema izgovora za njeno izostavljanje. Ponovo otvorite svaku konvertovanu datoteku pomoću iste biblioteke, ponovo pokrenite brojače revizije i uporedite ih sa brojevima pre konverzije koje je popis već zabeležio. Smanjen broj listova, broj grafikona koji je spao na nulu tamo gde je izvor imao tri, broj ćelija koji je drastično opao: svaka od ovih stavki predstavlja tihi gubitak uhvaćen po ceni drugog otvaranja. Pored toga, vizuelno proverite uzorak u Excel-u ili LibreOffice-u, i ta kombinacija će otkriti veliku većinu oštećenja pri konverziji pre nego što se podaci pošalju dalje. To je i razlog zašto faza popisa hrani fazu verifikacije. Bez početnih brojeva, krajnji brojevi ne dokazuju ništa.
Radno okruženje zasnovano na reviziji pretvara rizičnu masovnu konverziju u merljiv proces sa trakom za karantin za datoteke koje ne mogu uspešno da prođu. Svi pozivi za sondiranje, brojanje i konverziju prikazani ovde su deo komponente HotXLS Component, koja ih izvršava nativno unutar procesa bez automatizacije Excel-a.