Duomenų tikrinimas, automatinis filtras ir darbalapio lentelės „Delphi“ programoje su „HotXLS“

Šios trys „HotXLS“ funkcijos dalijasi darbalapiu, tačiau veikia su visiškai skirtingais objektais. Problemos prasideda tada, kai darote prielaidą, kad jos atlieka panašius dalykus. Duomenų tikrinimas (angl. data validation) susieja taisyklę su rėžiu ir apriboja tai, ką vartotojas gali jame įrašyti. Automatinis filtras (angl. AutoFilter) prideda išsaugotus filtravimo kriterijus prie regiono ir pakeičia tai, kurias eilutes mato vartotojas. Lentelė apgaubia rėžį pavadinta, struktūrizuota schema su stiliaus apipavidalinimu. Viena funkcija apriboja įvestį, kita išsaugo vaizdą, o trečioji nustato struktūrą. Nė viena iš jų pati savaime neperkelia nė vieno langelio reikšmės, o automatinis filtras ypač klaidina, nes jo pavadinimas rodo veiksmą, nors jis tik išsaugo apibrėžimą. Supratimas, kurį objektą paliečia kiekvienas iškvietimas ir kada poveikis iš tikrųjų pasireiškia, skiria darbalapį, kuris „Excel“ programoje veikia taip pat kaip ir jūsų testuose, nuo to, kuris tyliai elgiasi kitaip.

Automatinis filtras išsaugo apibrėžimą, o ne ištrina eilutes

Išsaugotame faile automatinis filtras yra tik kriterijų įrašas. Eilučių paslėpimas įvyksta vėliau, kai „Excel“ atidaro knygą ir įvertina kriterijus pagal duomenis. „HotXLS“ įrašo šį kriterijų ir nieko neištrina: kiekviena jūsų nufiltruota eilutė vis dar yra faile. Apdorojimo konvejeris, kuris taiko filtrą atmestiems užsakymams pašalinti, o po to nuskaito knygą, matys visus užsakymus, įskaitant ir atmestus. Kodas yra teisingas pagal API, bet klaidingas pagal programuotojo sumanymą. XLSX darbalapyje SetAutoFilter nurodo filtravimo rėžį, o AddAutoFilterColumn prideda kriterijų vienam stulpeliui. Kai serverio kodui reikia faktinio rezultato (pavyzdžiui, bendro eilučių skaičiaus), biblioteka įvertina kriterijus už jus, užuot apsimetusi, kad failas pasikeitė:

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 atsako už kiekvieną eilutę atskirai, o PreviewAutoFilterRows pereina visą regioną per callback funkciją, kai reikia gauti visą atitinkantį rinkinį vienu praėjimu. Yra atvejų, kai nė vienas iš šių variantų netinka: jei reikalavimas yra toks, kad nufiltruotų eilučių faile neturi būti apskritai (pavyzdžiui, konfidencialumo sumetimais), ištrinkite šias eilutes tiesiogiai. Filtras čia yra netinkamas įrankis, nes bet kuris gavėjas gali jį išjungti vienu spustelėjimu ir paslėpti duomenys vėl bus matomi ekrane.

Stulpelio ID yra poslinkis, o ne stulpelio numeris

Komentaras aukščiau esančiame kodo fragmente įspėja apie klaidą, kuri šiame API atima daugiausiai laiko derinant. Funkcija AddAutoFilterColumn nurodo savo taikinį pagal 0 pagrindo poziciją filtravimo diapazone, o ne pagal darbalapio stulpelį. Filtruojant A1:E500, šios dvi sistemos skiriasi vienetu, kas gali praeiti pro akis testavimo metu ir sulūžti, kai bus bandoma filtruoti kitą stulpelį. Jei filtras prasideda stulpelyje C, ID 0 reikš stulpelį C, ir šis skirtumas greitai taps akivaizdus. Kai filtravimo rėžis apskaičiuojamas vykdymo metu, sukurkite stulpelio ID iš to paties kintamojo, kuris sugeneravo rėžio tekstą, o ne iš darbalapio stulpelio konstantos. Kiekvienas stulpelis priima antrąją sąlygą per metodą, kuris priima du operatorius, du kriterijus ir loginį jungtį (and/or), kas atitinka „Excel“ pasirinktinį filtravimo dialogą. XLS sąsaja tą patį atlieka su SetAutoFilter ir ApplyAutoFilter, kurių kriterijų ir operatorių parametrai seka senesnėmis COM stiliaus taisyklėmis ir numeruoja laukus nuo 1. Keičiant sąsajas keičiasi ir indeksavimo pagrindas, todėl kodo eilutėse verta palikti komentarą, nurodantį, kuri sistema naudojama.

Validacijos taisyklės: sutartis, kurios laikosi jūsų vartotojai

Iš trijų funkcijų tik duomenų tikrinimas (validacija) aktyviai apriboja būsimą įvestį, ir jam reikėtų skirti daugiausiai dėmesio kuriant darbalapius, kurie bus pildomi vartotojų ir grąžinami apdorojimui. Sąrašo (angl. list) variantas atlieka didžiąją dalį šio darbo:

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;

Be sąrašų ir sveikųjų skaičių, ta pati taisyklų šeima apima dešimtainius skaičius, datas, laikus, teksto ilgį ir laisvos formos formules per AddCustomValidation, o bendroji funkcija AddDataValidation leidžia kurti taisykles pagal konfigūraciją. Klaidos rodymo stilius yra svarbesnis nei jo pavadinimas. xlsxDvErrStop atmeta neteisingą įvestį visiškai, o perspėjimo ir informavimo stiliai leidžia įrašyti reikšmę po vieno perspėjimo patvirtinimo. Pasirinkite stilių pagal tai, ar jūsų programa gali apdoroti reikšmes už taisyklės ribų. Dvi svarbios taisyklės: „Excel“ validacija apsaugo tik rankinį pildymą, tačiau bloko įklijavimas (angl. paste) apeina šią taisyklę, todėl nuskaitanti programa vis tiek turi atlikti patikrą, o ne aklai pasitikėti langeliais. Be to, taisyklė apima tik tą rėžį, kurį nurodėte, todėl pritaikius validaciją prieš sužinant galutinį eilučių skaičių, vėliau pridėta dalis liks be apsaugos. Pirmiausia įrašykite duomenis, o tik tada pritaikykite taisykles pagal faktinį dydį.

Senoji XLS sąsaja siūlo tas pačias taisyklių šeimas su viena ergonomine ypatybe. XLS pusėje funkcijos, tokios kaip AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation ir AddCustomValidation, grąžina patį TDataValidation objektą tiesiogiai, o ne jo indeksą, todėl pranešimų ir klaidų konfigūracija vykdoma tiesiai su gauta nuoroda, o ne per paiešką. Operatoriai (xlsDvBetween, xlsDvGreaterThan ir kt.) atitinka XLSX rinkinį, todėl kodas lengvai perkeliamas tarp sąsajų. Pats pranešimo tekstas yra toks pat svarbus kaip ir taisyklė. Išplečiamasis sąrašas, kuris atmeta įvestį su tuščiu pranešimo laukeliu, priverčia vartotojus kreiptis į pagalbą, o tas, kuris nurodo teisingas būsenas, leidžia vartotojui pačiam ištaisyti klaidą.

Priešingos prasmės vėliavėlė, kurią biblioteka išsprendžia už jus

Kiekvienas, kuris rankiniu būdu analizavo OOXML validacijos XML kodą, yra susidūręs su priešingos prasmės showDropDown atributu: ISO/IEC 29500 specifikacijoje reikšmė True reiškia „paslėpti išskleidimo rodyklę“ – priešingai nei rodo pavadinimas. „HotXLS“ šią reikšmę apverčia viduje, todėl savybė ShowDropDown validacijos taisyklėje reiškia būtent tai, kas parašyta: True rodo rodyklę. Vienintelė problema gali kilti, jei jūsų komandos narys patikrins sugeneruotą XML ir nuspręs „ištaisyti“ atributą, kuris jam pasirodys klaidingas. Sutarkite, kas yra autoritetingas šaltinis, ir užrašykite šį sprendimą.

Lentelės suteikia rėžiui schemą ir pavadinimą

Darbalapio lentelė (angl. ListObject „Excel“ terminuose) apgaubia rėžį pavadinimu, nustato stulpelių tipus, pritaiko stilių ir leidžia naudoti struktūrizuotas nuorodas. Tai yra funkcija, kuri paverčia sugeneruotą darbalapį pilnaverčiu įrankiu. Sukūrimo yra vienodas abiejose sąsajose, naudojant AddTable, kuri priima pavadinimą, rėžį ir stulpelių sąrašą:

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;

XLSX pusėje gautas lentelės objektas leidžia nustatyti StyleName (pavyzdžiui, TableStyleMedium2 šeimą), juostų stilius ir bendrų sumų eilutę, todėl stiliaus pritaikymas yra savybės priskyrimas, o ne rankinis formatavimas. Senajame XLS formate tas pats iškvietimas įrašo BIFF8 lentelių įrašus, o sąsaja taip pat siūlo AddPivotTable suvestinėms lentelėms kurti, kas parodo, jog senasis formatas turi platesnes galimybes šioje srityje. Pavadinkite lenteles taip pat, kaip vadinate duomenų bazės lenteles ar peržiūras. Programa, kuri nuskaito duomenis pagal nuorodą Orders[Amount], išliks veikianti net ir pakeitus stulpelių tvarką, kas sugadintų pozicinį kodą.

Du patarimai padės išvengti problemų vėliau. „Excel“ reikalauja, kad lentelių pavadinimai būtų unikalūs visoje knygoje, todėl jei generuojate po vieną lapą regionui, naudokite pavadinimus, pavyzdžiui, Orders_EMEA, užuot visur naudoję Orders. Dubliavimas nesukels klaidų rašymo metu, tačiau parodys klaidos pranešimą vartotojui atidarant failą. Kitas patarimas susijęs su sumų eilute: kai ji įjungta, ji yra tiesiai po duomenų diapazonu, todėl bet koks bandymas pridėti eilutes pagal principą „paskutinė naudota eilutė plius viena“ įrašys duomenis į sumų eilutę, o ne po ja. Sekite duomenų dydį atskirai nuo lentelės ribų, ir nauji duomenys bus įrašyti ten, kur reikia.

Šios trys funkcijos puikiai dera kartu. Lentelė apibrėžia pildymo sritį, validacija apriboja stulpelių įvestį, o iš anksto nustatytas filtras padeta vartotojui iškart matyti reikalingus duomenis. Geras sprendimas yra pateikti failą su jau pritaikytu filtru, kad vartotojas iškart matytų svarbiausias eilutes, tačiau prisiminkite – paslėpti duomenys vis dar yra faile. Duomenų pildymas aprašytas straipsnyje Excel ataskaitų generavimas iš duomenų bazės „Delphi“ programoje, o darbalapių formules padeda apsaugoti straipsnis pavadintos sritys stabilioms nuorodoms tarp lapų.

Duomenų tikrinimas, filtrai ir lentelės paverčia paprastą langelių tinklelį nedidele programa. Pilną taisyklių, filtrų ir lentelių aprašymą rasite „HotXLS Component“ produkto puslapyje HotXLS Component.