Validacija podataka, AutoFilter i tabele radnog lista u Delphi-ju pomoću HotXLS

Tri funkcije u HotXLS-u dele radni list ali operišu nad potpuno različitim objektima, a problemi nastaju kada pretpostavite da rade slične stvari. Validacija podataka (data validation) vezuje pravilo za opseg koje ograničava šta korisnik može da upiše u njega. AutoFilter vezuje sačuvanu definiciju kriterijuma za regiju i menja koje redove pregledač prikazuje. Tabela obavija opseg u imenovanu, tipiziranu strukturu sa prugastim stilizovanjem. Jedna funkcija ograničava unos, jedna beleži prikaz, a jedna nameće šemu. Nijedna od njih sama po sebi ne pomera vrednost nijedne ćelije, a AutoFilter posebno zbunjuje ljude, jer sama reč sugeriše akciju, a zapravo samo čuva definiciju. Znati koji objekat svaki poziv dotiče, i kada se efekat zapravo materijalizuje, jeste ono što razlikuje radnu svesku koja se u Excel-u ponaša isto kao u vašim testovima od one koja tiho odstupa.

AutoFilter čuva definiciju, on ne skraćuje redove

AutoFilter u sačuvanom fajlu je zapis kriterijuma. Sakrivanje redova se dešava kasnije, kada Excel otvori radnu svesku i proceni kriterijume u odnosu na podatke. HotXLS verno zapisuje taj rekord i ne briše ništa: svaki red koji ste filtrirali i dalje je fizički prisutan u fajlu. Sistem koji primenjuje filter da odbaci odbijene porudžbine i zatim ponovo čita radnu svesku videće sve njih, uključujući i odbijene, što je kod koji je ispravan prema API-ju, ali pogrešan prema mentalnom modelu autora. Na XLSX radnom listu, SetAutoFilter deklariše filtriranu regiju, a AddAutoFilterColumn pridružuje kriterijume jednoj njenoj koloni. Kada je kodu na serveru potreban stvarni rezultat, recimo za broj redova u rezimeu ili za prosleđivanje samo onih redova koji se poklapaju, biblioteka procenjuje kriterijume umesto vas umesto da se pretvara da se fajl promenio:

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;

Funkcija AutoFilterRowVisible daje odgovor po redu, a PreviewAutoFilterRows prolazi kroz celu regiju preko povratnog poziva (callback) kada vam je potreban podudarni skup u jednom prolazu. Postoji slučaj gde nijedno od ovoga nije pravo rešenje: ako je zahtev da isključeni redovi uopšte ne smeju postojati u fajlu (bezbednosno uklanjanje, a ne samo promena prikaza), obrišite redove u potpunosti. Filter je pogrešan alat za to, jer svaki primalac može da ga očisti jednim klikom i podaci koje ste hteli da sakrijete ponovo su na ekranu.

ID kolone je pomeraj, a ne broj kolone

Komentar u gornjem isečku koda ukazuje na zamku koja oduzima najviše vremena za otklanjanje grešaka u ovom API-ju. AddAutoFilterColumn identifikuje svoj cilj preko pozicije zasnovane na nuli (0-based) unutar opsega filtera, a ne preko kolone radnog lista. Za filter na A1:E500 ova dva sistema numerisanja se razlikuju za jedan, što je upravo vrsta propusta koji preživljava brzi test i puca čim kolega filtrira drugu kolonu. Za filter koji počinje na koloni C, ID 0 označava kolonu C, i neslaganje brzo postaje očigledno. Kada se opseg filtera računa tokom izvršavanja, izvedite ID kolone iz iste varijable koja je izgradila string opsega, nikada iz konstante kolone radnog lista. Svaka kolona prihvata drugi uslov kroz preopterećenje koje prima dva operatora, dva kriterijuma i and/or vezu, što odražava Excel-ov dijalog za prilagođeni filter. XLS interfejs pokriva istu oblast sa SetAutoFilter i ApplyAutoFilter, čiji parametri kriterijuma i operatora prate starije COM konvencije i numerišu polje od 1. Promena interfejsa znači promenu osnove indeksa, pa mesto poziva zaslužuje komentar koji govori o tome koji je interfejs aktivan.

Pravila validacije su ugovor pod kojim vaši korisnici vrše izmene

Od ove tri funkcije, validacija je jedina koja aktivno ograničava budući unos, i ona zaslužuje najviše pažnje pri dizajniranju radnih svezaka koje se šalju korisnicima na popunjavanje i vraćaju na obradu. Varijanta liste obavlja najveći deo tog posla:

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;

Pored listi i celih brojeva, ista porodica pokriva decimale, datume, vremena, dužinu teksta i formule slobodnog oblika kroz AddCustomValidation, dok generički AddDataValidation izlaže kompletnu matricu tipova i operatora za sisteme koji grade pravila na osnovu konfiguracije. Stil prikaza greške je važniji nego što njegovo ime sugeriše. xlsxDvErrStop u potpunosti odbacuje neispravan unos; stilovi upozorenja (warning) i informacija (information) propuštaju vrednost nakon jednog klika. Birajte po koloni na osnovu toga da li kod koji čita radnu svesku nazad može tolerisati vrednost van pravila. Dve granice treba navesti u tekstu poruke ili u README fajlu koji isporučujete: validacija u Excel-u štiti samo od kucanja, ali lepljenje (paste) bloka preko validiranog opsega zaobilazi pravilo, tako da svaki kod koji čita podatke nazad mora ponovo da izvrši validaciju umesto da veruje ćelijama. Takođe, pravilo pokriva tačan opseg koji ste mu prosledili, što znači da postavljanje validacije pre nego što saznate konačan broj redova ostavlja dodati kraj tabele nezaštićenim. Prvo upišite podatke, a zatim prilagodite veličinu pravila stvarnom obimu.

Nasleđeni interfejs nudi iste porodice pravila uz jednu ergonomsku razliku. XLS funkcije za kreiranje, konkretno AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation i AddCustomValidation, vraćaju objekat TDataValidation direktno umesto indeksa, pa se konfigurisanje poruka i grešaka vrši direktno na vraćenoj referenci umesto preko pretrage. Nabrajanje operatora (xlsDvBetween, xlsDvGreaterThan i ostali) odražava XLSX set, pa se kod za izgradnju pravila lako prenosi između interfejsa, osim te razlike u stilu povratne vrednosti. Sam tekst poruke zaslužuje podjednako pažnje kao i samo pravilo. Padajući meni koji odbija unos sa praznim prozorom za grešku uči korisnike da pišu IT podršci; onaj koji navodi dozvoljena stanja uči ih da isprave ćeliju i nastave dalje.

Obrt polariteta koji biblioteka rešava za vas

Svako ko je ručno čitao OOXML XML za validaciju susreo se sa obrnutim showDropDown atributom: u standardu ISO/IEC 29500 vrednost true znači "sakrij strelicu padajućeg menija", što je suprotno od onoga kako ime zvuči. HotXLS ovo interno obrće, pa svojstvo ShowDropDown na pravilu validacije znači upravo ono što kaže – vrednost true prikazuje padajući meni. Jedini način da pogrešite jeste mešanje nivoa istine, gde svojstvo postavljate iz koda dok kolega proverava sačuvani XML i "ispravlja" atribut koji mu deluje obrnuto. Odlučite da li je svojstvo ili sirovi XML autoritativan za alate za reviziju, i zapišite to tamo gde se ta odluka donosi.

Tabele daju opsegu šemu i ime

Tabela radnog lista, ili ListObject u terminologiji Excel-a, obavija opseg u ime, tipizirane kolone, prugasto stilizovanje i podršku za strukturirane reference. To je funkcija koja čini da generisana radna sveska deluje kompletno čim korisnici počnu da sortiraju i proširuju podatke. Kreiranje je simetrično na oba interfejsa, pri čemu AddTable prihvata ime, opseg i listu kolona:

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 XLSX strani, dobijeni objekat tabele izlaže StyleName (ugrađena TableStyleMedium2 porodica i slični stilovi), prekidače za pruge i zastavicu za red sa ukupnim vrednostima (totals row), pa je primena korporativnog stila obična dodela svojstva umesto ručnog formatiranja. U nasleđenim .xls fajlovima, isti poziv upisuje BIFF8 zapise tabela, a interfejs nudi i AddPivotTable za zbirne prikaze izgrađene od polja redova, kolona i podataka, što je podsetnik da "tabele" u starijem formatu dosežu dalje nego OOXML ListObject. Imenujte tabele onako kako imenujete poglede (views) u bazi podataka. Kod u kasnijoj fazi koji čita Orders[Amount] preko strukturirane reference preživljava promenu redosleda kolona koja bi inače polomila pozicioni kod.

Dve konvencije štede vreme na naknadno čišćenje. Excel zahteva da imena tabela budu jedinstvena u celoj radnoj svesci, pa generator koji emituje jedan list po regionu treba šemu poput Orders_EMEA umesto ponovnog korišćenja Orders. Duplikat ne javlja grešku pri upisu; on se pojavljuje kao prozor za popravku (repair dialog) kada korisnik otvori fajl, što je najgore mesto za takvo otkriće. Druga konvencija se odnosi na red sa ukupnim vrednostima: kada je uključen, on se nalazi direktno ispod opsega podataka, pa bilo koji kod koji kasnije dodaje podatke na poziciju "poslednji korišćeni red plus jedan" upisuje u liniju ukupnih vrednosti umesto iza nje. Pratite obim podataka odvojeno od obima tabele i dodavanja će završiti tamo gde očekujete.

Ove tri funkcije se prirodno kombinuju u dokumentima za unos podataka. Tabela definiše oblast za uređivanje, validacija ograničava kolone u koje korisnici kucaju, a unapred postavljeni filter štedi primaocu nekoliko prvih klikova. Postoji dobar argument za isporuku već primenjenog filtera kako bi se radna sveska otvorila fokusirana na redove koji su važni, sve dok imate na umu da su isključeni redovi i dalje u fajlu i da ih radoznali primalac može otkriti. Efikasan uvoz rezultata upita na radni list, što je prva polovina ovog procesa, pokriven je u članku o izvozu rezultata baze podataka u Excel iz Delphi-ju, a radne sveske gde formule sumiraju validirane podatke imaju korist od definisanih imena za stabilne reference između listova.

Validacija, filteri i tabele čine razliku između isporuke mreže vrednosti i isporuke male aplikacije. Kompletna referenca o pravilima, filterima i tabelama nalazi se na stranici proizvoda HotXLS Component.