Skupna normalizacija tabličnih dokumenata zapravo su tri problema u jednom. Imate arhivu miješanih formata: .xls iz doba BIFF-a, moderni .xlsx, poneku .ods datoteku iz nekog LibreOffice eksperimenta i pregršt datoteka koje nitko ne može otvoriti jer je lozinka otišla s bivšim zaposlenikom. Cilj je pretvoriti sve u XLSX i CSV. Verzija tog zadatka koju većina ljudi napiše je petlja koja otvara svaku datoteku i sprema je pod novim nastavkom, što radi sve dok netko ne pita koje su datoteke izgubile svoje grafikone, odbacile makronaredbe ili se uopće nisu otvorile. Petlja nema odgovor jer sama pretvorba ne vodi evidenciju. Radno okruženje vodi: prvo radi inventuru, zatim pretvorbu i na kraju provjeru, a te tri faze must moraju dijeliti informacije kako bi bilo što od toga bilo pouzdano.
Sastavljanje tog radnog okruženja u Delphiju ili C++Builderu znači povezivanje četiriju mogućnosti HotXLS-a, od kojih nijedna ne zahtijeva instaliran Excel bilo gdje u procesu. Postoje dva izvorna mehanizma, BIFF8 sučelje za .xls i OOXML sučelje za .xlsx i .ods. Postoje brzi pozivi za ispitivanje koji čitaju metapodatke bez analiziranja cijele datoteke. Postoje brojači revizije po listu koji vam govore što radna knjiga zapravo sadrži. I postoji matrica pretvorbe s dokumentiranim profilom vjernosti za svaku rutu. Posao je u tome da znate gdje svaki od njih ima oštre rubove, jer ih svaki ima, a ti su rubovi upravo ono što čist noćni skupni posao pretvara u problem u ponedjeljak ujutro.
Ispitajte prije učitavanja: nazivi listova i otkrivanje šifriranja
Otvaranje radne knjige od 200 MB samo da bi se otkrilo da je šifrirana troši minute po datoteci, a pomnoženo na veliku arhivu troši dane. Oba sučelja izlažu GetSheetNames, koji čita metapodatke lista bez popunjavanja radne knjige. Implementacija BIFF-a skenira samo zapise BoundSheet na početku toka; implementacija OOXML-a čita samo workbook.xml unutar zip datoteke. Uz to, CanReadEncrypted otkriva spremnik šifriranja bez pokušaja dešifriranja:
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 resetira niti popunjava instancu radne knjige, pa jedan objekt sonde može klasificirati tisuće datoteka bez ponovnog stvaranja. Verzija istog poziva u XLS sučelju također razumije .xlsx pakete, što ga čini praktičnom jedinstvenom sondom kada se ekstenzijama datoteka ne može vjerovati, što je rijetko slučaj u tako staroj arhivi. Trijaža prije učitavanja zaslužuje posebnu obradu; mehanika lagane inspekcije opisana je u našem članku o popisu listova i laganoj inspekciji radnih knjiga.
Brojanje onoga što radna knjiga doista sadrži
Nakon što datoteka prođe trijažu, revizijski prolaz odlučuje o njezinoj ruti pretvorbe. Sučelje XLSX izlaže brojač za svaku obitelj značajki koja utječe na odluku o vjernosti pretvorbe: spojene ćelije, grafikoni, slike, uvjetni formati, provjere valjanosti podataka, tablice, hiperveze i komentari, uz zastavice na razini radne knjige za makronaredbe, zaštitu i izvorni format. Ruta pretvorbe za datoteku gotovo u potpunosti ovisi o tome koji od njih vrate vrijednost 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 s jednom ogradom na umu. Spremište ćelija je rijetko popunjeno (sparse), pa broj označava instancirane ćelije, a ne pravokutno područje korištenog raspona. List s jednom vrijednošću u A1 i drugom u ZZ9999 prijavljuje dvije ćelije, a ne milijun i nešto koji leže između njih. Ekvivalentno skeniranje na strani BIFF-a koristi granice UsedRange zajedno s ForEachCell i nosi pogrešku odstupanja za jedan (off-by-one) koja gotovo svakoga prevari prvi put: UsedRange.FirstRow i srodna svojstva temelju se na indeksu 0, dok se Cells.Item[Row, Col] temelji na indeksu 1. Prolazak koji zaboravi dodati jedan svakoj granici revidira pogrešan pravokutnik i nikada to ne signalizira.
Dvije poluge smanjuju trošak prolaza samo za reviziju preko velikih naslijeđenih datoteka. Postavljanje _DisableGraphics na true prije otvaranja .xls datoteke u potpunosti preskače analizu sloja crteža OfficeArt, što štedi značajno vrijeme na radnim knjigama s mnogo oblika. To je isključivo optimizacija samo za čitanje: spremanje iz instance otvorene na taj način odbacilo bi crteže koje nikada nije analizirala, pa ta zastavica pripada samo stazama koje nikada neće ponovno zapisati datoteku. Kada revizija treba sadržaj po ćeliji, a ne samo broj, povratni poziv ForEachCell izravno prolazi kroz popunjene ćelije i izbjegava trošak Variant po svakom pristupu koji indeksirana svojstva ćelija plaćaju pri svakom čitanju, što se brzo nakuplja na milijunima ćelija.
Rano normalizirajte nedosljedne povratne kodove
HotXLS I/O pozivi prijavljuju pogreške putem cjelobrojnih rezultata, a ne iznimkama, a konvencije nisu ujednačene u cijelom API-ju. Većina poziva za otvaranje i spremanje vraća 1 u slučaju uspjeha i -1 u slučaju neuspjeha. GetSheetNames vraća broj listova ili -1 s očišćenim popisom. XLSX SaveAsHTML ponovno krši taj obrazac i vraća 0 za uspjeh, a -1 za indeks lista izvan raspona. Radno okruženje koje svugdje provjerava = 1 potiho će pogrešno klasificirati pozive koji signaliziraju uspjeh na neki drugi način, a ono koje provjerava <> -1 progutat će one koji ne uspiju s drugim kodom.
Pravilo koje preživljava dodir s cijelim API-jem je uže nego što se čini: tretirajte <= 0 kao neuspjeh za pozive koji vraćaju broj, provjerite dokumentiranu vrijednost uspjeha za svaku rutinu spremanja koju stvarno koristite i stavite oboje iza jedne male funkcije za provjeru rezultata kako bi ta konvencija živjela na točno jednom mjestu. Skupni cjevovodi mnogo češće zakazuju zbog sporog nakupljanja neprovjerenih povratnih kodova nego zbog nekog egzotičnog kvara parsera, a cijena krive implementacije naplaćuje se četrdeset tisuća datoteka kasnije, kada se nitko ne sjeća koje su pretvorbe zapravo uspjele.
Matrica pretvorbe i gdje koja ruta gubi podatke
Dva sučelja međusobno dijele posao pretvorbe. TXLSXWorkbook otvara XLSX, ODS i CSV, a sprema XLSX, ODS, CSV, HTML, RTF i AES-šifrirani XLSX. TXLSWorkbook otvara i sprema BIFF te izvozi HTML, RTF i CSV. Korisna stvar je da svaka staza dolazi s dokumentiranim profilom vjernosti, a ne s neodređenim obećanjem ispravnosti, tako da unaprijed možete odlučiti koje su rute sigurne za koje datoteke.
Izvoz u CSV zapisuje UTF-8 s BOM-om, završecima redaka CRLF i navodnicima prema RFC 4180. Ono što ne čini jest izračunavanje formula: ćelija koja sadrži =SUM(...) izvozi se kao doslovni tekst formule, pa se list formula pretvara u list nizova znakova osim ako prvo ne izračunate vrijednosti. Izvoz u HTML stvara jednu tablicu, pri čemu colspan i rowspan zamjenjuju spojene ćelije, a osnovni stilovi su ugrađeni u redak (inline). Izvoz u RTF ima strože ograničenje: ne može premostiti spojene ćelije preko stupaca, pa ćelije nastavka spajanja ostaju prazne. Uvoz ODS-a je namjerno lagan, prema vlastitoj dokumentaciji knjižnice. Skalarne vrijednosti i predmemorirani rezultati formula prolaze; stilovi, aktivni izrazi formula ODF i crteži ne prolaze. To postaje važno onog trenutka kada arhiva sadrži stvarne OpenDocument datoteke uređene standardom OASIS ODF 1.3, gdje za bilo što blizu vizualno vjerne pretvorbe treba više nego što je ova staza uvoza napravljena da podrži, a revizijski prolaz je ono što vam govori da te datoteke postoje prije nego što ih skupni posao tiho izravna.
SaveXLSWorkbookAsXLSX je podatkovni most, a ne most za izgled
Sučelje BIFF ne može izravno pisati OOXML, pa se prijelaz s .xls na .xlsx odvija preko funkcije SaveXLSWorkbookAsXLSX u jedinici lxXlsxExport. Vjernost tog mosta vrijedi jasno navesti, jer naziv sugerira više nego što stvarno radi. Kopira vrijednosti, formule, formate brojeva, boje ispune, osnovne atribute fonta, širine stupaca i postavke prikaza poput mrežnih crta. Ne kopira obrube, spojene raspone, komentare, grafikone niti uvjetne formate. Za normalizaciju na razini podataka, gdje će nizvodni sustavi analizirati rezultat i nitko ne gleda oblikovanje, to je točno dovoljno i ništa što nekome treba nije izgubljeno. Za oblikovano izvješće uprave koje treba čitati osoba, to nije dovoljno, i upravo tu revizijski brojači opravdavaju svoje mjesto: datoteka koju je revizija označila kao datoteku s grafikonima i uvjetnim formatima trebala bi ići u ručni red čekanja, a ne kroz most koji će oboje odbaciti bez ijedne riječi.
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đer prikazuje polugu propusnosti na strani OOXML-a. Postavljanje StreamingWrite na true usmjerava XML radnog lista izravno u izlazni paket umjesto da ga priprema kao jedan golemi niz znakova u memoriji, što čini razliku između ugodnog rada i pada sustava zbog nedostatka memorije kada datoteke dosegnu stotine tisuća redaka. Ponašanje veličine i memorije za taj način rada opisani su u našem članku o strujnom pisanju za poslužiteljske skupne poslove. Još jedno svojstvo je važno za skupne poslove koji žele koristiti svaku jezgru: nijedno sučelje nije sigurno za niti (thread-safe), ali nijedno ne dijeli ni globalno stanje, pa je podržani obrazac za paralelnu pretvorbu jedna instanca radne knjige po radnoj niti, bez međusobnog zaključavanja.
Zaštićene datoteke i što učiniti s njima
Zaključane datoteke u arhivi jasno se dijele prema formatu, a ta podjela odlučuje kamo idu. Naslijeđeno šifriranje .xls, bilo RC4, RC4 preko CryptoAPI-ja ili stara XOR obfuskacija, može se čitati: proslijedite lozinku u Open i datoteka se pretvara kao i svaka druga. Šifrirani .xlsx paketi su druga priča. HotXLS ih otkriva pomoću CanReadEncrypted ali ih ne može dešifrirati, pa je jedini ispravan potez preusmjeriti ih u red čekanja u kojem čovjek otvara i ponovno sprema svaku u Excelu prije nego što se ponovno pridruži cjevovodu. Tu asimetriju vrijedi planirati unaprijed, jer su šifrirane XLSX datoteke najvjerojatnije one zapise do kojih je nekome stvarno stalo.
Zatvaranje kruga s provjerom
Treća faza je ona koja se preskače, a njezino preskakanje je ono što skupnu pretvorbu pretvara u rizik. Nijedna staza spremanja u HotXLS-u ne izračunava formule. Excel ponovno izračunava kada otvori datoteku, pa pretvorba iz XLSX u XLSX ostaje točna, ali odredišni CSV prima doslovni tekst formule osim ako cjevovod prvo ne pokrene Calculate na ćelijama i zapiše rezultate natrag. Znati to unaprijed čini razliku između CSV datoteke pune brojeva i CSV datoteke pune nizova =SUM(...) koje nitko ne primjećuje dok se nizvodni uvoz ne zablokira na njima.
Sama provjera je dovoljno jeftina da nema isprike za njezino izostavljanje. Ponovno otvorite svaku pretvorenu datoteku s istom knjižnicom, ponovno pokrenite revizijske brojače i usporedite ih s brojevima prije pretvorbe koje je inventarni prolaz već zabilježio. Broj listova koji je pao, broj grafikona koji je pao na nulu tamo gdje je izvor imao tri, broj ćelija koji je dramatično pao: svaki je od tih događaja tihi gubitak uhvaćen po cijeni drugog otvaranja. Uz to, vizualno provjerite uzorak u Excelu ili LibreOfficeu i ta kombinacija hvata veliku većinu oštećenja pri pretvorbi prije isporuke. To je cijeli razlog zašto inventarna faza hrani fazu provjere. Bez brojeva 'prije', brojevi 'poslije' ne dokazuju ništa.
Radno okruženje koje se temelji na reviziji pretvara rizičnu skupnu pretvorbu u mjerljiv proces s karantenom za datoteke koje ne mogu proći čisto. Svi pozivi za ispitivanje, brojanje i pretvorbu prikazani ovdje dio su komponente HotXLS, koja ih izvodi izvorno unutar procesa bez automatizacije Excela.