HotXLS adatérvényesítés, AutoFilter és táblázatok Delphiben

A HotXLS három képessége osztozik egy munkalapon, mégis teljesen különböző objektumokon dolgozik, és a baj ott kezdődik, amikor azt feltételezi, hogy hasonló dolgokat tesznek. Az adatérvényesítés olyan szabályt csatol egy tartományhoz, amely korlátozza, mit gépelhet oda a felhasználó. Az AutoFilter tárolt feltételdefiníciót csatol egy területhez, és azt változtatja meg, mely sorokat mutatja a néző. A táblázat névvel, típusokkal és sávos stílussal ellátott szerkezetbe burkol egy tartományt. Az egyik a bevitelt korlátozza, a másik egy nézetet rögzít, a harmadik sémát kényszerít. Egyik sem mozdít el magától egyetlen cellaértéket sem, és különösen az AutoFilter vezeti félre az embereket, mert a szó cselekvést sugall, miközben csak definíciót tárol. Annak ismerete, hogy melyik hívás melyik objektumot érinti, és mikor válik valósággá a hatás, választja el azt a munkafüzetet, amely Excelben ugyanúgy viselkedik, mint a tesztjeiben, attól, amelyik csendben elsodródik

Az AutoFilter definíciót tárol, nem vág le sorokat

Egy mentett fájlban az AutoFilter feltételrekord. A sorok elrejtése később történik, amikor az Excel megnyitja a munkafüzetet, és kiértékeli a feltételeket az adatokon. A HotXLS ezt a rekordot hűen kiírja, és semmit nem vág le: minden kiszűrt sor fizikailag jelen van a fájlban. Az a folyamat, amely szűrővel dobja el az elutasított rendeléseket, majd visszaolvassa a munkafüzetet, mindet látni fogja, az elutasítottakkal együtt, a kód pedig az API szerint helyes, a szerző fejében élő modell szerint viszont hibás. Az XLSX munkalapon a SetAutoFilter deklarálja a szűrt területet, az AddAutoFilterColumn pedig feltételeket csatol annak egyik oszlopához. Amikor a kiszolgálóoldali kódnak a tényleges eredményre van szüksége, mondjuk összesítő sorszámhoz vagy csak az egyező sorok továbbításához, a könyvtár kiértékeli Ön helyett a feltételeket, ahelyett hogy megjátszaná a fájl megváltozását:

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');
    // A 3-as oszlopazonosító = a szűrőtartományon BELÜLI negyedik oszlop (0-alapú eltolás)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // A Visible most azzal egyezik, amit az Excel a fájl megnyitása után mutat

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

Az AutoFilterRowVisible soronként válaszol, a PreviewAutoFilterRows pedig visszahíváson át járja be az egész területet, amikor egy menetben kell az egyező halmaz. Van olyan eset, amikor egyik sem a helyes válasz: ha a követelmény az, hogy a kizárt sorok egyáltalán ne létezzenek a fájlban, vagyis adatvédelmi vágásról van szó, nem nézetről, akkor törölje a sorokat véglegesen. Ott a szűrő a rossz eszköz, mert bármely címzett egy kattintással kikapcsolja, és a visszatartani kívánt adat újra a képernyőn van

Az oszlopazonosító eltolás, nem oszlopszám

A fenti részletben szereplő megjegyzés arra a csapdára figyelmeztet, amely ebben az API-ban a legtöbb hibakeresési időt viszi el. Az AddAutoFilterColumn a szűrőtartományon belüli 0-alapú pozícióval azonosítja a célját, nem a munkalap oszlopával. Az A1:E500 tartományra tett szűrőnél a két számozás történetesen épp eggyel tér el, ami pontosan az a fajta hajszálnyi tévesztés, amely túléli a gyors tesztet, és abban a pillanatban törik el, amikor egy kolléga másik oszlopra szűr. Egy C oszlopnál kezdődő szűrőnél a 0-s azonosító a C oszlopot jelenti, és az eltérés gyorsan szembeötlik. Amikor a szűrőtartományt futásidőben számolja ki, ugyanabból a változóból vezesse le az oszlopazonosítót, amely a tartománykarakterláncot felépítette, soha ne munkalap-oszlopkonstansból. Minden oszlop elfogad egy második feltételt is a két operátort, két feltételt és egy és/vagy kapcsolót váró túlterhelésen át, ami az Excel egyéni szűrő párbeszédpanelét tükrözi. Az XLS homlokzat ugyanezt a területet a SetAutoFilter és az ApplyAutoFilter párossal fedi le, amelynek feltétel- és operátorparaméterei a régebbi COM stílusú egyezményeket követik, és 1-től számozzák a mezőt. A homlokzatváltás indexalapváltást is jelent, ezért a hívási hely megérdemel egy megjegyzést arról, melyik van érvényben

Az érvényesítési szabályok az a szerződés, amely alatt a felhasználók szerkesztenek

A három képesség közül az érvényesítés az egyetlen, amely tevékenyen korlátozza a jövőbeli bevitelt, és az kapja a legtöbb tervezői figyelmet azokban a munkafüzetekben, amelyek kitöltésre mennek ki és feldolgozásra jönnek vissza. A lista változat viszi ennek a munkának a nagyját:

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;

  // Mennyiségek: egész számok, nulla vagy annál több
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

A listákon és egész számokon túl ugyanez a család lefedi a tizedes törteket, a dátumokat, az időpontokat, a szöveghosszt és a szabad formájú képleteket az AddCustomValidation révén, az általános AddDataValidation pedig a teljes típus- és operátormátrixot teszi elérhetővé a konfigurációvezérelt szabályépítőknek. A hibastílus többet számít, mint amennyit a neve sejtet. Az xlsxDvErrStop kereken visszautasítja a rossz bevitelt; a figyelmeztető és tájékoztató stílus egyetlen kattintás után átengedi az értéket. Oszloponként válasszon aszerint, hogy a munkafüzetet visszaolvasó kód elvisel-e a szabályon kívüli értéket. Két határ a súgószövegbe vagy a fájllal együtt szállított README-be való. Az Excelben az érvényesítés a gépelést őrzi, egy blokk beillesztése az érvényesített tartományra viszont átcsúszik a szabályon, ezért minden adatot visszaolvasó kódnak újra érvényesítenie kell, nem pedig bíznia a cellákban. A szabály ráadásul pontosan azt a tartományt fedi, amelyet átadott neki, vagyis ha a végleges sorszám ismerete előtt csatolja az érvényesítést, a hozzáfűzött farok őrizetlen marad. Előbb írja ki az adatokat, azután méretezze a szabályokat a tényleges kiterjedéshez

Az örökölt homlokzat ugyanezeket a szabálycsaládokat kínálja egyetlen ergonómiai különbséggel. Az XLS oldali létrehozók, nevezetesen az AddWholeNumberValidation, az AddDecimalValidation, az AddDateValidation, az AddTimeValidation, az AddTextLengthValidation és az AddCustomValidation, index helyett közvetlenül a TDataValidation objektumot adják vissza, így a súgó és a hibaüzenet beállítása keresés helyett a visszakapott hivatkozásról láncolódik. Az operátorfelsorolás (xlsDvBetween, xlsDvGreaterThan és a többi) tükrözi az XLSX készletet, így a szabályépítő kód a visszatérési stílus különbségén kívül átvihető a homlokzatok között. Maga a súgószöveg annyi gondolkodást érdemel, mint a szabály. Az a legördülő, amely üres hibaablakkal utasítja vissza a bevitelt, arra tanítja a felhasználót, hogy írjon az informatikának; az, amelyik megnevezi a megengedett állapotokat, arra tanítja, hogy javítsa a cellát és haladjon tovább

Egy polaritásváltás, amelyet a könyvtár elnyel Ön helyett

Aki már kézzel olvasott OOXML érvényesítési XML-t, találkozott a fordított showDropDown attribútummal: az ISO/IEC 29500 szerint az igaz érték azt jelenti, hogy „a legördülő nyíl elrejtése”, vagyis az ellenkezőjét annak, amit a név sugall. A HotXLS ezt belül megfordítja, így egy érvényesítési szabály ShowDropDown tulajdonsága azt jelenti, amit mond, és az igaz érték mutatja a legördülőt. Csak úgy égetheti meg magát, ha keveri az igazság szintjeit: kódból állítja be a tulajdonságot, miközben egy kolléga a mentett XML-t auditálja, és „kijavítja” azt az attribútumot, amely számára fordítottnak látszik. Döntse el, hogy az ellenőrző eszközök szempontjából a tulajdonság vagy a nyers XML a mérvadó, és írja le a megfordítást ott, ahol ez a döntés él

A táblázatok sémát és nevet adnak egy tartománynak

A munkalaptáblázat, Excel szóhasználatban a ListObject, névbe, típusos oszlopokba, sávos stílusba és strukturált hivatkozások támogatásába burkol egy tartományt. Ez az a képesség, amely befejezetté teszi az előállított munkafüzetet, amint a felhasználók rendezni és bővíteni kezdik. A létrehozás szimmetrikus a homlokzatok között, az AddTable nevet, tartományt és oszloplistát vár:

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;

Az XLSX oldalon a keletkező táblázatobjektum elérhetővé teszi a StyleName tulajdonságot (a beépített TableStyleMedium2 családot és testvéreit), a csíkozáskapcsolókat és az összegsor jelzőjét, így a házon belüli stílus alkalmazása kézi formázás helyett tulajdonság-hozzárendelés. Az örökölt .xls fájlokban ugyanez a hívás a BIFF8 táblázatrekordokat írja, a homlokzat pedig AddPivotTable hívást is kínál a sor-, oszlop- és adatmezőkből épített összegző nézetekhez, ami emlékeztet arra, hogy a „táblázat” a régebbi formátumban messzebbre nyúlik, mint az OOXML ListObject. Úgy nevezze el a táblázatokat, ahogy az adatbázisnézeteket nevezné el. Az a későbbi kód, amely strukturált hivatkozással olvassa az Orders[Amount] értéket, túléli azt az oszlopátrendezést, amely a pozíció alapú kódot eltöri

Két egyezmény takarít meg utólagos munkát. Az Excel megköveteli, hogy a táblázatnevek az egész munkafüzetben egyediek legyenek, ezért annak a generátornak, amely régiónként külön lapot bocsát ki, Orders_EMEA jellegű sémára van szüksége az Orders újrahasználata helyett. A duplikátum nem íráskor bukik el; javítási párbeszédpanelként bukkan fel, amikor a felhasználó megnyitja a fájlt, ami a legrosszabb hely a felfedezésére. A másik egyezmény az összegsort érinti: ha be van kapcsolva, közvetlenül az adattartomány alatt ül, így minden olyan kód, amely később az „utolsó használt sor plusz egy” elv szerint fűz hozzá, az összegsávba ír, nem mögé. Kövesse az adatok kiterjedését a táblázat kiterjedésétől külön, és a hozzáfűzések oda érkeznek, ahová várja

A három képesség természetesen áll össze az adatbeviteli szállítmányokban. A táblázat kijelöli a szerkeszthető területet, az érvényesítés korlátozza azokat az oszlopokat, amelyekbe a felhasználók gépelnek, az előre beállított szűrő pedig megkíméli a címzettet az első néhány kattintástól. Jó érv szól amellett, hogy már alkalmazott szűrővel szállítson, így a munkafüzet a lényeges sorokra fókuszálva nyílik meg, amíg emlékszik arra, hogy a kizárt sorok továbbra is a fájlban vannak, és egy kíváncsi címzett fel tudja fedni őket. A lekérdezési eredmények hatékony bejuttatását a lapra, vagyis e folyamat feljebb eső felét, az adatbázis-eredmények Excelbe exportálása Delphiből tárgyalja, azoknak a munkafüzeteknek pedig, amelyekben képletek összegzik az érvényesített adatokat, jót tesznek a definiált nevek a stabil, lapok közötti hivatkozásokhoz

Az érvényesítés, a szűrők és a táblázatok jelentik a különbséget aközött, hogy értékek rácsát szállítja vagy egy kis alkalmazást. A teljes szabály-, szűrő- és táblázathivatkozás a HotXLS Delphi Component termékoldalán található