Masinis skaičiuoklių normalizavimo darbas yra trys problemos, paslėptos po vienu apsiaustu. Turite mišrių formatų archyvą: BIFF eros .xls, modernų .xlsx, keletą .ods failų iš kažkokio „LibreOffice“ eksperimento ir saują failų, kurių niekas negali atidaryti, nes slaptažodis išėjo kartu su buvusiu darbuotoju. Tikslas yra konvertuoti viską į XLSX ir CSV. Daugelio žmonių rašoma šio darbo versija yra ciklas, kuris atidaro varje failą ir išsaugo jį su nauju plėtiniu, ir tai veikia tol, kol kas nors paklaus, kurie failai prarado diagramas, prarado makrokomandas arba apskritai neatsidarė. Ciklas netui atsakymo, nes pats konvertavimas neišsaugo jokių įrašų. Darbo aplinka (angl. workbench) tai daro: pirmiausia ji inventorizuoja, tada konvertuoja ir galiausiai patikrina, o šie trys etapai turi dalytis informacija, kad rezultatas būtų patikimas.
Tokios darbo aplinkos kūrimas Delphi ar C++Builder aplinkoje reiškia keturių HotXLS funkcijų sujungimą, iš kurių nė vienai nereikia, kad „Excel“ būtų įdiegtas kur nors sistemoje. Yra du natūralūs varikliai: BIFF8 sąsaja, skirta .xls, ir OOXML sąsaja, skirta .xlsx ir .ods. Yra pigūs zondavimo iškvietimai, kurie nuskaito metaduomenis neanalizuodami viso failo. Kiekvienam lapui yra audito skaitikliai, kurie parodo, ką darbaknygė iš tikrųjų turi. Taip pat yra konvertavimo matrica su dokumentuotu kiekvieno kelio tikslumo profiliu. Sunkumas yra žinoti, kur kiekviena iš šių funkcijų turi aštrių kampų, nes kiekviena jų turi, o tie kampai yra būtent tai, kas tvarkingą naktinį paketą paverčia pirmadienio ryto incidentu.
Zondavimas prieš įkeliant: lapų pavadinimai ir šifravimo aptikimas
Atidaryti 200 MB darbaknygę tik tam, kad sužinotumėte, jog ji yra užšifruota, eikvoja minutes kiekvienam failui, o padauginus iš didelio archyvo – prarandamos dienos. Abiejų formatų sąsajos atskleidžia GetSheetNames metodą, kuris nuskaito lapo metaduomenis neužpildydamas darbaknygės. BIFF realizacija nuskaito tik BoundSheet įrašus srauto pradžioje; OOXML realizacija nuskaito tik workbook.xml failą, esantį zip archyve. Šalia to, CanReadEncrypted aptinka šifravimo konteinerį nebandydamas jo iššifruoti:
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;Dvi veiklos detalės daro šį ciklą pigų. GetSheetNames neišvalo ir neužpildo darbaknygės egzemplioriaus, tos pačios savybės leidžia vienam zondavimo objektui klasifikuoti tūkstančius failų be pakartotinio sukūrimo. Be to, XLS sąsajos to paties iškvietimo versija taip pat supranta ir .xlsx paketus, o tai leidžia patogiai naudoti vieną zondą, kai negalima pasitikėti failų plėtiniais (taip dažnai būna senuose archyvuose). Rūšiavimas prieš įkeliant nusipelno atskiro aptarimo; lengvojo tikrinimo mechanika aprašyta mūsų straipsnyje apie lapų sąrašą ir lengvąjį darbaknygės tikrinimą.
Suskaičiavimas, ką darbaknygė iš tikrųjų turi
Kai failas praeina rūšiavimą, audito etapas nusprendžia jo konvertavimo kelią. XLSX sąsaja atskleidžia skaitiklį kiekvienai funkcijų grupei, kuri turi įtakos suderinamumo sprendimui: sujungtiems langeliams, diagramoms, paveikslėliams, sąlyginiam formatavimui, duomenų patvirtinimui, lentelėms, nuorodoms ir komentarams, taip pat darbaknygės lygio žymoms makrokomandoms, apsaugai ir šaltinio formatui. Failo konvertavimo kelias beveik visiškai priklauso nuo to, kurie iš jų grąžina ne nulinę reikšmę.
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;Skaitykite Cells.Count turėdami omenyje vieną įspėjimą. Langelių saugykla yra išsklaidyta, todėl šis skaičius rodo sukurtus langelius, o ne stačiakampį naudojamo diapazono plotą. Lapas su viena reikšme langelyje A1 ir kita – ZZ9999, praneš apie du langelius, o ne apie milijoną su viršumi, esančių tarp jų. Ekvivalentinis nuskaitymas BIFF pusėje naudoja UsedRange ribas kartu su ForEachCell, ir jis turi vieneto paklaidą, kuri beveik visus suklaidina pirmą kartą: UsedRange.FirstRow ir jo kaimynai yra skaičiuojami nuo 0, o Cells.Item[Row, Col] yra skaičiuojamas nuo 1. Ciklas, kuris pamiršta pridėti vienetą prie kiekvienas ribos, atlieka neteisingo stačiakampio auditą ir niekada apie tai nepraneša.
Du svertiniai parametrai sumažina tik audito etapo išlaidas dideliems seniems failams. Nustačius _DisableGraphics į true prieš atidarant .xls failą, visiškai praleidžiamas OfficeArt piešimo sluoksnio analizavimas, o tai sutaupo nemažai laiko darbaknygėse su daug figūrų. Tačiau tai yra griežtai tik skaitymo optimizavimas: išsaugojimas iš tokiu būdu atidaryto objekto pašalintų piešinius, kurie niekada nebuvo išanalizuoti, todėl ši žyma tinka tik tiems keliams, kurie niekada neįrašys failo atgal. Kai auditui reikia konkretaus langelio turinio, o ne tik skaičiaus, ForEachCell atgalinis iškvietimas tiesiogiai pereina užpildytus langelius ir išvengia Variant tipo pridėtinių išlaidų kiekvienai kreipčiai, kurias indeksuotos langelių savybės moka už kiekvieną skaitymą, o tai greitai susideda skaitant milijonus langelių.
Nenuoseklių grąžinimo kodų normalizavimas ankstyvoje stadijoje
HotXLS I/O iškvietimai apie klaidas praneša per sveikųjų skaičių rezultatus, o ne išimtis, ir šios konvencijos nėra vienodos visame API. Dauguma atidarymo ir išsaugojimo iškvietimų sėkmės atveju grąžina 1, o nesėkmės atveju -1. GetSheetNames grąžina lapų skaičių arba -1 su išvalytu sąrašu. XLSX SaveAsHTML vėl pažeidžia dėsningumą ir grąžina 0 sėkmės atveju, o -1, jei lapo indeksas yra už ribų. Darbo aplinka, kuri visur tikrina reikšmę = 1, tyliai klaidingai klasifikuos iškvietimus, kurie praneša apie sėkmę kitu būdu, o ta, kuri tikrina <> -1, praleis tuos, kurie nepavyksta su kitu kodu.
Taisyklė, kuri pasiteisina dirbant su visa API, yra siauresnė nei atrodo: traktuokite <= 0 kaip nesėkmę skaitiklius grąžinantiems iškvietimams, patikrinkite dokumentuotą sėkmės reikšmę kiekvienai faktiškai naudojamai išsaugojimo funkcijai ir įkelkite abu patikrinimus į vieną nedidelę rezultatų tikrinimo funkciją, kad ši konvencija gyventų tik vienoje vietoje. Paketų apdorojimo konvejeriai kur kas dažniau sugenda dėl lėto netikrinamų grąžinimo kodų kaupimosi nei dėl kokių nors egzotinių analizatoriaus klaidų, o klaidos kaina paaiškėja po keturiasdešimties tūkstančių failų, kai niekas neprisimena, kurie konvertavimai iš tikrųjų pavyko.
Konvertavimo matrica ir kur kiekvienas kelias praranda duomenis
Dvi sąsajos pasidalija konvertavimo darbą tarpusavyje. TXLSXWorkbook atidaro XLSX, ODS ir CSV, bei išsaugo XLSX, ODS, CSV, HTML, RTF ir AES šifruotą XLSX. TXLSWorkbook atidaro ir išsaugo BIFF, bei eksportuoja HTML, RTF ir CSV. Patogu tai, kad kiekvienas kelias turi dokumentuotą tikslumo profilį, o ne miglotą teisingumo pažadą, žodžiu, galite iš anksto nuspręsti, kurie keliai yra saugūs kuriems failams.
CSV eksportavimas įrašo UTF-8 su BOM, CRLF eilučių pabaigomis ir RFC 4180 kabutėmis. Ko jis nedaro – tai nevertina formulių: langelis su =SUM(...) eksportuojamas kaip pažodinis formulės tekstas, todėl formulių lapas virsta eilučių lapu, nebent pirmiausia apskaičiuotumėte reikšmes. HTML eksportavimas sugeneruoja vieną lentelę, kurioje colspan ir rowspan pakeičia sujungtus langelius, o pagrindiniai stiliai įterpiami tiesiogiai. RTF eksportavimas turi griežtesnį apribojimą: jis negali išplėsti sujungtų langelių per stulpelius, todėl sujungimo tęsinio langeliai lieka tušti. ODS importas yra lengvas tyčia, pagal pačios bibliotekos dokumentaciją. Skaliarinės reikšmės ir talpykloje esantys formulių rezultatai persikelia; stiliai, gyvos ODF formulės išraiškos ir piešiniai – ne. Tai svarbu, kai archyve yra tikri OpenDocument failai, valdomi OASIS ODF 1.3 standarto, kur vizualiai tiksliam konvertavimui reikia daugiau nei šis importo kelias gali atlikti, ir audito etapas yra tai, kas praneša apie šių failų egzistavimą prieš paketui juos tyliai suplokštinant.
SaveXLSWorkbookAsXLSX yra duomenų tiltas, o ne maketo tiltas
BIFF sąsaja negali tiesiogiai įrašyti OOXML, todėl perėjimas iš .xls į .xlsx vykdomas per SaveXLSWorkbookAsXLSX funkciją, esančią lxXlsxExport modulyje. Šio tilto tikslumą verta įvardyti aiškiai, nes pavadinimas žada daugiau nei jis daro. Jis nukopijuoja reikšmes, formules, skaičių formatus, užpildymo spalvas, pagrindinius šrifto atributus, stulpelių pločius ir vaizdo nustatymus, pavyzdžiui, tinklelio linijas. Jis nenukopijuoja rėmelių, sujungtų diapazonų, komentarų, diagramų ar sąlyginio formatavimo. Duomenų lygio normalizavimui, kai tolesnės sistemos analizuos rezultatą ir niekas nežiūrės į formatavimą, to visiškai pakanka ir neprarandama nieko, ko kam nors reikėtų. Formatuotai valdybos ataskaitai, skirtai skaityti žmogui, to nepakanka, ir būtent čia audito skaitikliai užima savo vietą: failas, kurį auditas pažymėjo kaip turintį diagramų ir sąlyginio formatavimo, turėtų būti nukreiptas į rankinio apdorojimo eilę, o ne per tiltą, kuris abu juos pašalins be jokio įspėjimo.
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;Aukščiau pateiktas ciklas taip pat parodo pralaidumo svertą OOXML pusėje. Nustačius StreamingWrite į true, darbalapio XML srautas siunčiamas tiesiogiai į išvesties paketą, o ne kaupiamas kaip viena milžiniška eilutė atmintyje. Tai yra skirtumas tarp sklandaus veikimo ir atminties trūkumo klaidų, kai failai pasiekia šimtus tūkstančių eilučių. Šio režimo dydžio nustatymas ir elgsena su atmintimi aprašyti mūsų straipsnyje apie srautinį rašymą serverio paketiniams darbams. Dar viena savybė yra svarbi paketams, norintiems išnaudoti visus branduolius: nei viena sąsaja nėra saugi gijoms (angl. thread-safe), tačiau nei viena iš jų nedalijasi ir bendra būsena, todėl palaikomas lygiagretaus konvertavimo modelis yra vienas darbaknygės egzempliorius vienai darbininko gijai be jokių tarpusavio užraktų.
Slaptažodžiu apsaugoti failai ir ką su jais daryti
Užrakinti archyvo failai pasidalija pagal formatą, ir šis pasidalijimas nusprendžia, kur jie keliauja. Senesnis .xls šifravimas (nesvarbu, ar tai būtų RC4, RC4 per CryptoAPI, ar senasis XOR iškraipymas) yra perskaitomas: perduokite slaptažodį funkcijai Open ir failas konvertuojamas kaip bet kuris kitas. Užšifruoti .xlsx paketai yra kita istorija. HotXLS aptinka juos naudodamas CanReadEncrypted, tačiau negali jų iššifruoti, todėl vienintelis teisingas sprendimas yra nukreipti juos į eilę, kurioje žmogus atidaro ir iš naujo išsaugo kiekvieną iš jų programoje „Excel“, prieš jiems vėl patenkant į konvejerį. Šią asimetriją verta numatyti iš anksto, nes užšifruoti XLSX failai dažniausiai yra tie įrašai, kurie iš tikrųjų kam nors rūpi.
Ciklo užbaigimas su patikrinimu
Trečiasis etapas yra tas, kuris dažniausiai praleidžiamas, o jo praleidimas paverčia masinį konvertavimą rizika. Joks HotXLS išsaugojimo kelias nevertina formulių. „Excel“ perskaičiuoja atidarydamas failą, todėl konvertavimas iš XLSX į XLSX išlieka teisingas, tačiau CSV tikslas gauna formulės tekstą pažodžiui, nebent konvejeris pirmiausia paleidžia Calculate funkciją langeliams ir įrašo rezultatus atgal. Žinojimas apie tai iš anksto yra skirtumas tarp CSV failo, pilno skaičių, ir CSV failo, pilno =SUM(...) eilučių, kurių niekas nepastebi, kol tolesnis importas neužsikerta.
Pats patikrinimas yra pakankamai pigus, kad nebūtų pateisinimo jo praleisti. Vėl atidarykite kiekvieną konvertuotą failą su ta pačia biblioteka, iš naujo paleiskite audito skaitiklius ir palyginkite juos su skaičiais prieš konvertavimą, kuriuos inventorizacijos etapas jau užregistravo. Sumažėjęs lapų skaičius, diagramų skaičius, sumažėjęs iki nulio (kai šaltinyje buvo trys), ar drastiškai sumažėjęs langelių skaičius: kiekvienas praradimas pastebimas už antrojo atidarymo kainą. Papildomai vizualiai patikrinkite pavyzdį programoje „Excel“ arba „LibreOffice“, ir šis derinys padės aptikti didžiąją dalį konvertavimo klaidų dar prieš išsiunčiant failus. Tai yra visa priežastis, kodėl inventorizacijos etapas aprūpina patikrinimo etapą duomenimis. Be skaičių „prieš“, skaičiai „po“ nieko neįrodo.
Pirmiausia auditą atliekanti darbo aplinka rizikingą masinį konvertavimą paverčia išmatuojamu procesu su karantino juosta failams, kurie negali būti švariai apdoroti. Visi čia parodyti zondavimo, skaičiavimo ir konvertavimo iškvietimai yra dalis HotXLS Component komponento, kuris juos vykdo natūraliai procese be „Excel“ automatizavimo.