Tri funkcije v HotXLS si delijo delovni list, vendar delujejo na povsem različnih objektih, težave pa se začnejo, ko predvidevate, da počnejo podobne stvari. Validacija podatkov prine pravilo na obseg celic, kar omejuje, kaj lahko uporabnik vpiše vanje. Samodejni filter (AutoFilter) pripne shranjeno definicijo meril na območje in spremeni, katere vrstice prikazuje bralnik. Tabela pa ovije obseg celic v poimenovano, tipizirano strukturo s progastim oblikovanjem. Ena omejuje vnos, druga beleži pogled, tretja pa predpisuje shemo. Nobena od njih sama po sebi ne premakne niti ene vrednosti celice, samodejni filter pa še posebej zavaja ljudi, saj beseda nakazuje dejanje, čeprav shranjuje le definicijo. Poznavanje tega, katerega objekta se dotakne posamezen klic in kdaj se učinek dejansko izrazi, ločuje delovni zvezek, ki v Excelu deluje enako kot pri vaših testih, od tistega, ki tiho odstopa.
AutoFilter shrani definicijo, ne izbriše pa vrstic
Samodejni filter v shranjeni datoteki je le zapis meril. Skrivanje vrstic se zgodi pozneje, ko Excel odpre delovni zvezek in ovrednoti merila glede na podatke. HotXLS ta zapis zanesljivo zapiše in ne izbriše ničesar: vsaka vrstica, ki ste jo filtrirali, je še vedno fizično prisotna v datoteki. Cevovod, ki uporabi filter za izločitev zavrnjenih naročil in nato prebere delovni zvezek nazaj, bo videl vsa naročila, vključno z zavrnjenimi. Koda je torej pravilna po API-ju, a napačna po avtorjevem miselnem modelu. Na delovnem listu XLSX funkcija SetAutoFilter določi filtrirano območje, AddAutoFilterColumn pa pripne merila na enega od stolpcev. Ko strežniška koda potrebuje dejanski rezultat (npr. za štetje vrstic v povzetku ali posredovanje le ujemajočih se vrstic), knjižnica ovrednoti merila namesto vas, ne da bi se pretvarjala, da se je datoteka spremenila:
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;Metoda AutoFilterRowVisible odgovarja za vsako vrstico posebej, PreviewAutoFilterRows pa pregleda celotno območje prek povratnega klica (callback), ko potrebujete ujemajoči se niz v enem prehodu. V nekaterih primerih pa nobeden od njiju ni pravi odgovor: če je zahteva, da izključene vrstice sploh ne smejo obstajati v datoteki (npr. zaradi varovanja podatkov in ne le zaradi prikaza), vrstice neposredno izbrišite. Filter je v tem primeru napačno orodje, saj ga lahko vsak prejemnik odstrani z enim klikom in podatki, ki ste jih deli prikriti, bodo spet vidni na zaslonu.
ID stolpca je odmik in ne številka stolpca
Komentar v zgornjem izseku opozarja na past, ki v tem API-ju vzame največ časa za odpravljanje napak. Funkcija AddAutoFilterColumn identificira svoj cilj na podlagi položaja, osnovanega na 0 znotraj območja filtra, in ne po stolpcu delovnega lista. Pri filtru na območju A1:E500 se sistema oštevilčevanja razlikujeta le za ena, kar je ravno tista napaka, ki preživi kratek test, nato pa se zalomi, ko sodelavec filtrira drug stolpec. Pri filtru, ki se začne v stolpcu C, ID 0 pomeni stolpec C in neskladje postane hitro očitno. Ko se obseg filtra izračuna med izvajanjem, izpeljite ID stolpca iz iste spremenljivke, ki je zgradila niz obsega, in nikoli iz konstante stolpca delovnega lista. Vsak stolpec sprejme drugi pogoj prek preobremenitve, ki sprejme dva operatorja, dve merili in veznik in/ali, kar zrcali Excelovo pogovorno okno za filtre po meri. Vmesnik XLS pokriva isto področje s SetAutoFilter in ApplyAutoFilter, katerih parametri meril in operatorjev sledijo starejšim konvencijam v slogu COM in polje štejejo od 1 dalje. Preklop vmesnikov pomeni spremembo indeksne osnove, zato si klic zasluži komentar, ki pojasnjuje, katera osnova je trenutno aktivna.
Validacijska pravila so pogodba, pod katero vaši uporabniki urejajo podatke
Od treh funkcij je validacija edina, ki aktivno omejuje prihodnji vnos, zato si zasluži največ oblikovalske pozornosti v delovnih zvezkih, ki se pošiljajo v izpolnjevanje in vračajo v obdelavo. Različica s seznamom opravi večino tega dela:
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;Poleg seznamov in celih števil ista družina pokriva decimalna števila, datume, čase, dolžino besedila in poljubne formule prek AddCustomValidation, splošna funkcija AddDataValidation pa ponuja celotno matriko tipov in operatorjev za graditelje pravil, ki jih vodi konfiguracija. Slog napake je pomembnejši, kot nakazuje njegovo ime. xlsxDvErrStop neposredno zavrne napačen vnos, sloga opozorila in informacij pa po enem kliku dovolita vnos vrednosti. Izberite slog glede na stolpec in glede na to, ali koda, ki bere delovni zvezek nazaj, lahko tolerira vrednost izven pravila. Dve omejitvi sodita v besedilo poziva ali datoteko README, ki jo pošljete z datoteko. Validacija v Excelu varuje ročni vnos, lepljenje bloka čez validirano območje pa se izogne pravilu, zato mora koda, ki podatke bere nazaj, znova opraviti validacijo in ne le zaupati celicam. Pravilo prav tako pokriva natančen obseg, ki ste mu ga določili, kar pomeni, da nastavitev validacije pred poznavanjem končnega števila vrstic pusti dodan konec nezaščiten. Najprej zapišite podatke, nato pa velikost pravil prilagodite dejanskemu obsegu.
Starejši vmesnik ponuja enake družine pravil z eno ergonomsko razliko. Ustvarjalniki na strani XLS (AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation in AddCustomValidation) vrnejo neposredno objekt TDataValidation in ne indeksa, zato se nastavitve pozivov in napak vežejo neposredno na vrnjeni sklic in ne na iskanje. Enumeracija operatorjev (xlsDvBetween, xlsDvGreaterThan ipd.) zrcali nabor XLSX, zato se koda za gradnjo pravil med vmesnikoma prenaša enostavno, razen omenjene razlike v slogu vračanja. Tudi besedilo poziva si zasluži toliko premisleka kot samo pravilo. Spustni meni, ki zavrne vnos s praznim poljem za napako, uporabnike uči, naj pišejo IT podpori; tisti, ki navaja dovoljena stanja, pa jih uči, naj popravijo celico in nadaljujejo.
Eno obračanje polaritete, ki jo knjižnica opravi namesto vas
Vsakdo, ki je ročno bral datoteke XML za validacijo v OOXML, je naletel na obrnjen atribut showDropDown: v standardu ISO/IEC 29500 vrednost true pomeni 'skrij puščico spustnega menija', kar je nasprotno od tega, kar ime pove. HotXLS to interno obrne, tako da lastnost ShowDropDown na validacijskem pravilu pomeni točno to, kar piše – true prikaže spustni meni. Edini način, da gre kaj narobe, je mešanje ravni interpretacije, ko lastnost nastavite v kodi, sodelavec pa pregleda shranjeni XML in 'popravi' atribut, ki je zanj videti obrnjen. Določite, ali je za revizijska orodja merodajna lastnost ali surovi XML, in to zapišite tam, kjer ta odločitev živi.
Tabele dajejo obsegu shemo in ime
Delovna tabela (ListObject v terminologiji Excela) ovije obseg v ime, tipizirane stolpce, progaste sloge in podporo za strukturirane sklice. To je funkcija, zaradi katere je ustvarjeni delovni zvezek videti dokončan, ko uporabniki začnejo razvrščati in razširjati podatke. Ustvarjanje je simetrično med vmesniki, pri čemer AddTable sprejme ime, obseg in seznam stolpcev:
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 strani XLSX ustvarjeni objekt tabele izpostavlja lastnost StyleName (vgrajeni slogi TableStyleMedium2 in sorodni), preklapljanje črt in zastavico za vrstica s skupnimi zneski, tako da je uporaba hišnih slogov le dodelitev lastnosti in ne ročni prehod oblikovanja. V starejših datotekah .xls isti klic zapiše zapise tabel BIFF8, vmesnik pa ponuja tudi AddPivotTable za povzetke pogledov, zgrajene iz vrstic, stolpcev in podatkovnih polj, kar opozarja, da 'tabele' v starejšem formatu segajo dlje kot ListObject v OOXML. Poimenujte tabele tako, kot poimenujete poglede baze podatkov. Koda, ki bere Orders[Amount] prek strukturiranega sklica, preživi prerazporejanje stolpcev, ki bi slikovito pokvarilo pozicijsko kodo.
Dve konvenciji prihranita delo pozneje
Excel zahteva, da so imena tabel edinstvena v celotnem delovnem zvezku, zato generator, ki ustvari en list na regijo, potrebuje shemo, kot je Orders_EMEA, in ne ponovne uporabe imena Orders. Podvojeno ime ne odpove ob zapisovanju, temveč se pojavi kot pogovorno okno za popravilo, ko uporabnik odpre datoteko, kar je najslabše mesto za odkrivanje napak. Druga konvencija se nanaša na vrstica s skupnimi zneski: ko je omogočena, se nahaja neposredno pod obsegom podatkov, zato vsaka koda, ki pozneje dodaja podatke prek 'zadnja uporabljena vrstica plus ena', piše v pas s skupnimi zneski in ne pod njega. Spremljajte obseg podatkov ločeno od obsega tabele in dodajanja bodo pristala tam, kjer jih pričakujete.
Vse tri funkcije se naravno povezujejo pri vnosu podatkov. Tabela določa območje za urejanje, validacija omejuje stolpce, v katere uporabniki vpisujejo podatke, vnaprej nastavljeni filter pa prejemniku prihrani prvih nekaj klikov. Obstaja dober argument za pošiljanje že filtriranega dokumenta, da se delovni zvezek odpre osredotočen na vrstice, ki so pomembne, če le ne pozabite, da so izključene vrstice še vedno v datoteki in jih radoveden prejemnik lahko prikaže. Učinkovit uvoz rezultatov poizvedb v list (zgornja polovica tega cevovoda) je opisan v članku izvoz rezultatov baze podatkov v Excel iz Delphija, delovni zvezki, kjer formule povzemajo validirane podatke, pa imajo korist od poimenovanih območij za stabilne medlistne sklice.
Validacija, filtri in tabele predstavljajo razliko med pošiljanjem preproste mreže vrednosti in pošiljanjem manjše aplikacije. Celotna referenca pravil, filtrov in tabel je na voljo na strani izdelka HotXLS Component.