Validace dat, AutoFilter a tabulky v listu v Delphi s HotXLS

Tři funkce v HotXLS sdílejí list, ale pracují s naprosto odlišnými objekty, a potíže začínají ve chvíli, kdy předpokládáte, že dělají podobné věci. Validace dat připojí k rozsahu pravidlo, které omezuje, co do něj uživatel smí zadat. AutoFilter připojí k oblasti uloženou definici kritérií a mění, které řádky se divákovi zobrazí. Tabulka obalí rozsah do pojmenované, typované struktury s pruhovaným stylem. Jedna funkce omezuje vstup, druhá zaznamenává pohled, třetí vynucuje schéma. Žádná z nich sama o sobě nepohne jedinou hodnotou buňky, a AutoFilter obzvlášť lidi mate, protože to slovo naznačuje akci, zatímco ve skutečnosti ukládá jen definici. Vědět, kterého objektu se to které volání dotýká a kdy se efekt skutečně projeví, je to, co odlišuje sešit, který se v Excelu chová stejně jako ve vašich testech, od sešitu, který se tiše rozchází

AutoFilter ukládá definici, řádky neořezává

AutoFilter v uloženém souboru je záznam kritérií. Skrytí řádků se odehraje později, když Excel sešit otevře a kritéria vyhodnotí proti datům. HotXLS tento záznam věrně zapíše a neořeže nic: každý řádek, který jste vyfiltrovali, je stále fyzicky přítomný v souboru. Pipeline, která aplikuje filtr, aby odstranila zamítnuté objednávky, a poté sešit znovu načte, uvidí všechny, zamítnuté objednávky nevyjímaje, a kód je podle API správný, zatímco podle mentálního modelu autora chybný. Na listu XLSX SetAutoFilter deklaruje filtrovanou oblast a AddAutoFilterColumn k jednomu jejímu sloupci připojí kritéria. Když serverový kód potřebuje skutečný výsledek, ať už pro počet řádků v souhrnu, nebo aby dál poslal jen vyhovující řádky, knihovna kritéria vyhodnotí za vás, místo aby předstírala, že se soubor změnil:

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');
    // Id sloupce 3 = čtvrtý sloupec UVNITŘ rozsahu filtru (0-based offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible teď odpovídá tomu, co Excel zobrazí po otevření souboru

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

AutoFilterRowVisible odpovídá po řádcích a PreviewAutoFilterRows projde celou oblast přes callback, když potřebujete vyhovující množinu na jeden průchod. Existuje případ, kdy ani jedno není správná odpověď: pokud je požadavkem, aby vyloučené řádky v souboru vůbec neexistovaly, jde o řez z důvodu ochrany soukromí, ne o pohled, smažte řádky rovnou. Filtr je tam nesprávný nástroj, protože kterýkoli příjemce jej jedním kliknutím zruší a data, která jste chtěli zadržet, jsou zpátky na obrazovce

Id sloupce je offset, ne číslo sloupce

Komentář ve výřezu výše upozorňuje na past, která v tomto API stojí nejvíc času při ladění. AddAutoFilterColumn identifikuje svůj cíl podle 0-based pozice uvnitř rozsahu filtru, ne podle sloupce listu. U filtru na A1:E500 se oba číselné systémy náhodou liší jen o jedničku, což je přesně ten druh téměř-nehody, který přežije rychlý test a rozbije se ve chvíli, kdy kolega filtruje jiný sloupec. U filtru, který začíná sloupcem C, znamená id 0 sloupec C, a nesoulad se rychle stane zjevným. Když se rozsah filtru počítá za běhu, odvoďte id sloupce ze stejné proměnné, která sestavila řetězec rozsahu, nikdy z konstanty sloupce listu. Každý sloupec přijímá druhou podmínku přes přetížení, které bere dva operátory, dvě kritéria a spojku and/or, což zrcadlí dialog vlastního filtru v Excelu. Fasáda XLS pokrývá totéž pomocí SetAutoFilter a ApplyAutoFilter, jejichž parametry kritérií a operátoru se řídí staršími konvencemi ve stylu COM a číslují pole od 1. Přepnutí fasády znamená přepnutí základny indexování, takže si místo volání zaslouží komentář, který říká, která z nich je v provozu

Validační pravidla jsou smlouva, podle které vaši uživatelé editují

Ze tří funkcí je validace jediná, která aktivně omezuje budoucí vstup, a v sešitech, které odcházejí k vyplnění a vrací se ke zpracování, si zaslouží nejvíc pozornosti při návrhu. Variantu se seznamem má na starosti většina této 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;

  // Množství: celá čísla, nula nebo více
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Kromě seznamů a celých čísel pokrývá stejná rodina desetinná čísla, data, časy, délku textu a volné vzorce přes AddCustomValidation, a obecná AddDataValidation zpřístupňuje celou matici typů a operátorů pro nástroje na sestavování pravidel řízené konfigurací. Styl chyby má větší význam, než naznačuje jeho název. xlsxDvErrStop odmítne špatný vstup rovnou; styly varování a informace pustí hodnotu dál po jediném kliknutí. Vybírejte podle sloupce na základě toho, jestli kód, který sešit později čte, dokáže tolerovat hodnotu mimo pravidlo. Dvě hranice patří do textu výzvy nebo do README, které se souborem dodáváte. Validace v Excelu hlídá psaní, ale vložení bloku přes validovaný rozsah pravidlo obejde, takže kód, který data později čte, je musí validovat znovu místo toho, aby buňkám důvěřoval. A pravidlo pokrývá doslovný rozsah, který jste mu předali, což znamená, že připojení validace dřív, než znáte konečný počet řádků, nechává připojený ocas nechráněný. Zapište data nejdřív, pak pravidla nastavte podle skutečného rozsahu

Starší fasáda nabízí stejné rodiny pravidel s jedním ergonomickým rozdílem. Tvůrci na straně XLS, konkrétně AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation a AddCustomValidation, vracejí přímo objekt TDataValidation místo indexu, takže se konfigurace výzvy a chyby řetězí za vráceným odkazem místo za vyhledáváním. Výčet operátorů (xlsDvBetween, xlsDvGreaterThan a zbytek) zrcadlí sadu XLSX, takže kód pro sestavování pravidel se mezi fasádami přenáší až na tento rozdíl ve stylu návratové hodnoty. Samotný text výzvy si zaslouží stejně tolik přemýšlení jako pravidlo. Rozevírací seznam, který odmítne vstup prázdným chybovým okénkem, naučí uživatele posílat e-maily na IT; ten, který pojmenuje povolené stavy, je naučí buňku opravit a jít dál

Jedno otočení polarity, které knihovna vyřeší za vás

Kdokoli si někdy ručně přečetl validační XML v OOXML, narazil na obrácený atribut showDropDown: v ISO/IEC 29500 hodnota true znamená „potlač šipku rozevíracího seznamu", přesný opak toho, co by název naznačoval. HotXLS toto interně otočí, takže vlastnost ShowDropDown na validačním pravidle znamená přesně to, co říká, přičemž true rozevírací seznam zobrazí. Jediný způsob, jak se spálit, je smíchat úrovně pravdy, nastavit vlastnost z kódu, zatímco kolega audituje uložené XML a „opraví" atribut, který jemu připadá obrácený. Rozhodněte, jestli je pro revizní nástroje autoritativní vlastnost, nebo syrové XML, a toto rozhodnutí si zapište tam, kde žije

Tabulky dávají rozsahu schéma a jméno

Tabulka listu, v terminologii Excelu ListObject, obalí rozsah jménem, typovanými sloupci, pruhovaným stylem a podporou strukturovaných odkazů. Je to funkce, díky které vygenerovaný sešit působí hotově ve chvíli, kdy jej uživatelé začnou třídit a rozšiřovat. Vytváření je napříč fasádami symetrické, přičemž AddTable přebírá jméno, rozsah a seznam sloupců:

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 straně XLSX výsledný objekt tabulky zpřístupňuje StyleName (vestavěnou rodinu TableStyleMedium2 a její sourozence), přepínače pruhování a příznak řádku se souhrny, takže aplikace firemního stylu je přiřazení vlastnosti místo ručního průchodu formátováním. Ve starších souborech .xls stejné volání zapíše záznamy tabulky BIFF8 a fasáda navíc nabízí AddPivotTable pro souhrnné pohledy sestavené z polí řádků, sloupců a dat, což připomíná, že „tabulky" ve starším formátu sahají dál, než sahá OOXML ListObject. Tabulky pojmenovávejte tak, jako pojmenováváte databázové pohledy. Navazující kód, který čte Orders[Amount] přes strukturovaný odkaz, přežije přeuspořádání sloupců, které rozbije poziční kód

Dvě konvence ušetří pozdější úklid. Excel vyžaduje, aby byly názvy tabulek jedinečné napříč celým sešitem, takže generátor, který vydává jeden list na region, potřebuje schéma jako Orders_EMEA místo opakovaného použití Orders. Duplicita neselže při zápisu; projeví se jako dialog opravy ve chvíli, kdy uživatel soubor otevře, což je ten nejhorší okamžik na její objevení. Druhá konvence se týká řádku se souhrny: je-li zapnutý, sedí přímo pod rozsahem dat, takže jakýkoli kód, který později připojuje řádky metodou „poslední použitý řádek plus jedna", zapíše do pásma souhrnů místo za něj. Sledujte rozsah dat odděleně od rozsahu tabulky a připojení pak přistanou tam, kde je čekáte

Tyto tři funkce se v dodávkách pro zadávání dat přirozeně skládají dohromady. Tabulka definuje editovatelnou oblast, validace omezuje sloupce, do kterých uživatelé zadávají, a předem nastavený filtr ušetří příjemci prvních pár kliknutí. Existuje rozumný argument pro dodání s už aplikovaným filtrem, aby se sešit otevřel zaostřený na řádky, na kterých záleží, pokud si ovšem pamatujete, že vyloučené řádky jsou stále v souboru a zvídavý příjemce je může odhalit. Efektivní dostání výsledků dotazu do listu, což je horní polovina této pipeline, popisuje článek o exportu databázových výsledků do Excelu z Delphi, a sešity, kde vzorce shrnují validovaná data, těží z definovaných názvů pro stabilní odkazy napříč listy

Validace, filtry a tabulky jsou rozdíl mezi dodáním mřížky hodnot a dodáním malé aplikace. Kompletní referenci pravidel, filtrů a tabulek najdete na stránce produktu HotXLS Delphi Component