Overovanie dát, AutoFilter a tabuľky na hárku v Delphi cez HotXLS

Tri funkcie v HotXLS zdieľajú rovnaký hárok, ale pracujú s úplne odlišnými objektmi, a problémy začínajú vtedy, keď predpokladáte, že robia podobné veci. Overovanie dát pripája k rozsahu pravidlo, ktoré obmedzuje to, čo doň používateľ môže zapísať. AutoFilter pripája k oblasti definíciu uložených kritérií a mení to, ktoré riadky sa zobrazia. Tabuľka zasa obaľuje rozsah do pomenovanej, typovanej štruktúry s pruhovaným štýlom. Jedna funkcia obmedzuje vstup, druhá zaznamenáva zobrazenie a tretia vynucuje schému. Žiadna z nich sama o sebe nepresúva hodnoty v bunkách, a najmä AutoFilter často klame telom, pretože jeho názov naznačuje akciu, hoci ukladá iba definíciu. Znalosť toho, ktorého objektu sa každé volanie dotýka a kedy sa efekt skutočne prejaví, odlišuje zošit, ktorý sa v Exceli správa rovnako ako vo vašich testoch, od zošita, ktorý sa v tichosti správa inak

AutoFilter ukladá definíciu, neorezáva riadky

AutoFilter v uloženom súbore je v podstate záznam kritérií. Skrytie riadkov prebieha až neskôr, keď Excel otvorí zošit a vyhodnotí kritériá voči dátam. HotXLS zapisuje tento záznam verne a nič neodstraňuje: každý riadok, ktorý ste odfiltrovali, je v súbore stále fyzicky prítomný. Proces, ktorý použije filter na odstránenie zamietnutých objednávok a potom spätne načíta zošit, uvidí všetky objednávky vrátane tých zamietnutých. Kód je z hľadiska API správny, ale nezodpovedá mentálnemu modelu autora. Na hárku XLSX definuje SetAutoFilter filtrovanú oblasť a AddAutoFilterColumn pripája kritériá k jednému z jej stĺpcov. Keď serverový kód potrebuje skutočný výsledok (napríklad pre počet riadkov v súhrne alebo na odoslanie iba zodpovedajúcich riadkov), knižnica pre vás vyhodnotí kritériá namiesto predstierania, že sa súbor zmenil:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Column id 3 = fourth column INSIDE the filter range (0-based offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible now matches what Excel will show after opening the file

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Metóda AutoFilterRowVisible odpovedá na úrovni jednotlivých riadkov a PreviewAutoFilterRows prechádza celú oblasť prostredníctvom spätného volania (callback), keď potrebujete získať zhodnú množinu v jednom prechode. Existujú však prípady, kedy ani jedno nie je správnym riešením: ak je požiadavkou, aby vylúčené riadky v súbore vôbec neexistovali (z dôvodu ochrany súkromia a nie iba zobrazenia), vymažte tieto riadky úplne. Filter je v takomto prípade nesprávny nástroj, pretože akýkoľvek príjemca ho môže jedným kliknutím zrušiť a dáta, ktoré ste chceli zatajiť, sú späť na obrazovke

ID stĺpca je posun a nie číslo stĺpca

Komentár v kóde vyššie poukazuje na pascu, ktorá pri tomto API stojí najviac času pri ladení. Metóda AddAutoFilterColumn identifikuje svoj cieľ na základe indexu začínajúceho od 0 v rámci rozsahu filtra a nie podľa reálneho stĺpca na hárku. Pri filtri na rozsahu A1:E500 sa tieto dva systémy číslovania líšia o jeden stĺpec, čo je presne tá drobná chyba, ktorá prežije rýchly test a prejaví sa až v momente, keď kolega začne filtrovať iný stĺpec. Pri filtri, ktorý začína na stĺpci C, znamená ID 0 stĺpec C a nesúlad je rýchlo zrejmý. Keď sa rozsah filtra počíta počas behu programu, odvoďte ID stĺpca od rovnakej premennej, ktorá zostavila reťazec rozsahu, a nikdy nie z konštanty stĺpca hárka. Každý stĺpec prijíma aj druhú podmienku prostredníctvom preťaženej metódy, ktorá berie dva operátory, dve kritériá a logickú spojku a/alebo, čo zodpovedá vlastnému filtru v Exceli. Rozhranie XLS pokrýva rovnakú funkčnosť pomocou metód SetAutoFilter a ApplyAutoFilter, ktorých parametre kritérií a operátorov sa riadia staršími konvenciami typu COM a číslujú polia od 1. Zmena rozhrania znamená zmenu indexového základu, preto si volanie v kóde zaslúži komentár o tom, ktorý systém sa používa

Pravidlá overovania sú zmluvou, podľa ktorej používatelia upravujú dáta

Z týchto troch funkcií je overovanie jedinou, ktorá aktívne obmedzuje budúci vstup, a preto si zaslúži najväčšiu pozornosť pri návrate zošitov na spracovanie. Variant so zoznamom (list validation) pokrýva väčšinu tejto práce:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Quantities: whole numbers, zero or more
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Okrem zoznamov a celých čísiel pokrýva táto rodina pravidiel aj desatinné čísla, dátumy, časy, dĺžku textu a vlastné vzorce cez AddCustomValidation, pričom univerzálna metóda AddDataValidation sprístupňuje kompletnú maticu typov a operátorov pre tvorcov pravidiel riadených konfiguráciou. Štýl chybového hlásenia je dôležitejší, než by sa mohlo zdať. Hodnota xlsxDvErrStop nepovolený vstup úplne odmietne; štýly varovania (warning) a informácie prepustia hodnotu ďalej po jedinom kliknutí. Vyberte štýl pre každý stĺpec podľa toho, či kód, ktorý bude zošit čítať, dokáže tolerovať hodnotu mimo stanoveného pravidla. Do sprievodného textu alebo README súboru patria dve dôležité upozornenia. Overovanie v Exceli chráni pred priamym zápisom, ale prilepenie (paste) bloku dát cez overený rozsah pravidlo obíde, takže akýkoľvek kód čítajúci dáta ich musí znova overiť a nesmie bunky slepo akceptovať. Pravidlo navyše pokrýva presný rozsah, ktorý ste mu zadali, čo znamená, že priradenie overenia pred znalosťou konečného počtu riadkov ponechá pridaný koniec reportu bez ochrany. Najprv zapíšte dáta, až potom prispôsobte veľkosť pravidiel skutočnému rozsahu

Staršie rozhranie ponúka rovnaké rodiny pravidiel s jedným ergonomickým rozdielom. Metódy na strane XLS, konkrétne AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation a AddCustomValidation, vracajú priamo objekt TDataValidation namiesto indexu, takže nastavenie pomocných a chybových správ sa reťazí priamo na vrátenú referenciu bez potreby vyhľadávania. Enumerácia operátorov (xlsDvBetween, xlsDvGreaterThan a ďalšie) kopíruje sadu z XLSX, takže kód na zostavenie pravidiel je ľahko prenosný medzi rozhraniami okrem spomínaného rozdielu v type návratovej hodnoty. Samotný text výzvy (prompt) si zaslúži rovnakú pozornosť ako pravidlo. Rozbaľovací zoznam, ktorý odmietne vstup s prázdnym chybovým oknom, prinúti používateľov písať na IT podporu; ten, ktorý jasne menuje povolené stavy, ich naučí opraviť bunku a pokračovať

Jedno otočenie polarity, ktoré knižnica vyrieši za vás

Každý, kto niekedy ručne čítal XML pre overovanie v OOXML, sa stretol s obráteným atribútom showDropDown: v štandarde ISO/IEC 29500 znamená hodnota true „potlačiť šípku rozbaľovacieho zoznamu“, čo je presný opak toho, ako názov znie. HotXLS toto správanie vnútorne obracia, takže vlastnosť ShowDropDown na pravidle overovania znamená to, čo hovorí, pričom hodnota true zobrazí rozbaľovaciu šípku. Jediným spôsobom, ako sa popáliť, je miešať tieto dve úrovne pravdivosti – nastaviť vlastnosť v kóde, zatiaľ čo kolega skontroluje uložené XML a „opraví“ atribút, ktorý sa mu zdá obrátený. Rozhodnite sa, či je pre vaše kontrolné nástroje smerodajná vlastnosť v kóde alebo čisté XML, a toto rozhodnutie zdokumentujte

Tabuľky dávajú rozsahu schému a názov

Tabuľka na hárku (v terminológii Excelu označovaná ako ListObject) obaľuje rozsah dát do názvu, typovaných stĺpcov, pruhovaného štýlu a prináša podporu pre štruktúrované odkazy. Je to funkcia, vďaka ktorej vyzerá generovaný zošit profesionálne, len čo používatelia začnú dáta triediť a rozširovať. Vytvorenie je symetrické v oboch rozhraniach, pričom metóda AddTable prijíma názov, rozsah a zoznam stĺpcov:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Na strane XLSX vystavuje výsledný objekt tabuľky vlastnosť StyleName (rodinu vstavaných štýlov ako TableStyleMedium2 a ich alternatívy), prepínače pruhov a príznak riadka celkových súčtov, takže aplikovanie firemného dizajnu je otázkou priradenia vlastnosti a nie manuálneho formátovania. V starších súboroch .xls totožné volanie zapisuje tabuľkové záznamy BIFF8, pričom rozhranie ponúka aj metódu AddPivotTable pre súhrnné zobrazenia zostavené z riadkových, stĺpcových a dátových polí, čo pripomína, že „tabuľky“ v staršom formáte majú širší záber než OOXML objekt ListObject. Pomenúvajte tabuľky rovnako, ako pomenúvate databázové pohľady (views). Nadväzujúci kód, ktorý číta Orders[Amount] pomocou štruktúrovaného odkazu, prežije aj zmenu poradia stĺpcov, ktorá by inak rozbila pozične orientovaný kód

Dve pravidlá ušetria neskoršie čistenie kódu. Excel vyžaduje, aby boli názvy tabuliek jedinečné v celom zošite, takže generátor, ktorý vytvára jeden hárok pre každý región, potrebuje schému typu Orders_EMEA namiesto opätovného použitia názvu Orders. Duplicita nespôsobí zlyhanie pri zápise, ale prejaví sa dialógovým oknom o oprave súboru pri jeho otvorení, čo je to najhoršie miesto na objavenie chyby. Druhé pravidlo sa týka riadka celkových súčtov: ak je povolený, nachádza sa priamo pod rozsahom dát, takže akýkoľvek kód, ktorý neskôr pridáva riadky štýlom „posledný použitý riadok plus jeden“, zapíše dáta do oblasti celkových súčtov a nie za ňu. Sledujte rozsah dát nezávisle od rozsahu tabuľky a pridávané riadky skončia tam, kde očakávate

Tieto tri funkcie sa prirodzene dopĺňajú pri návrhoch formulárov na zadávanie dát. Tabuľka definuje oblasť na úpravu, overovanie obmedzuje stĺpce, do ktorých používatelia píšu, a prednastavený filter ušetrí príjemcovi prvých niekoľko kliknutí. Existuje rozumný dôvod na odoslanie súboru s už aplikovaným filtrom, aby sa zošit otvoril zameraný na dôležité riadky, pokiaľ nezabudnete, že vylúčené riadky sú stále v súbore a zvedavý príjemca ich môže zobraziť. Efektívny import výsledkov dopytov na hárok, čo je vstupná časť tohto spracovania, je popísaný v článku o exportovaní databázových výsledkov do Excelu z Delphi, a zošity, kde vzorce sumarizujú overené dáta, môžu využiť výhody definovaných názvov pre stabilné odkazy medzi hárkami

Overovanie, filtre a tabuľky sú rozdielom medzi odoslaním obyčajnej mriežky hodnôt a distribúciou malej funkčnej aplikácie. Kompletná referencia pre pravidlá, filtre a tabuľky sa nachádza na produktovej stránke HotXLS Component