Tietojen kelpoisuustarkistus, AutoFilter ja laskentataulukon taulukot Delphissä HotXLS:llä

HotXLS:n kolme ominaisuutta jakavat laskentataulukon, mutta toimivat täysin eri objekteilla. Ongelmat alkavat, kun niiden oletetaan tekevän samankaltaisia asioita. Tietojen kelpoisuustarkistus liittää alueeseen säännön, joka rajoittaa siihen kirjoitettavaa sisältöä. AutoFilter liittää alueeseen tallennetun ehtomääritelmän ja muuttaa katsojalle näytettäviä rivejä. Taulukko kietoo alueen nimetyn, tyypitetyn rakenteen ja raidallisen tyylin sisään. Yksi rajoittaa syötettä, yksi tallentaa näkymän ja yksi määrää rakenteen. Mikään niistä ei itsessään siirrä yhtä ainutta soluarvoa, ja erityisesti AutoFilter hämää, koska sana viittaa toimintaan, vaikka se tallentaa vain määritelmän. Kun tiedät, mihin objektiin kukin kutsu koskee ja milloin vaikutus todella toteutuu, työkirja käyttäytyy Excelissä samalla tavoin kuin testeissäsi eikä poikkea hiljaa

AutoFilter tallentaa määritelmän, se ei leikkaa rivejä

Tallennetun tiedoston AutoFilter on ehtotietue. Rivien piilottaminen tapahtuu myöhemmin, kun Excel avaa työkirjan ja arvioi ehdot tietoja vasten. HotXLS kirjoittaa tietueen uskollisesti eikä leikkaa mitään: jokainen suodatettu rivi on yhä fyysisesti tiedostossa. Työnkulku, joka käyttää suodatinta hylättyjen tilausten poistamiseen ja lukee sitten työkirjan takaisin, näkee ne kaikki hylätyt mukaan lukien. Koodi on API:n mukaan oikein, mutta väärin tekijän ajatusmallin mukaan. XLSX-laskentataulukossa SetAutoFilter määrittää suodatettavan alueen ja AddAutoFilterColumn liittää ehdot sen yhteen sarakkeeseen. Kun palvelinpuolen koodi tarvitsee todellisen tuloksen, esimerkiksi rivimäärän yhteenvetoon tai vain vastaavien rivien edelleenlähettämiseen, kirjasto arvioi ehdot puolestasi sen sijaan, että teeskentelisi tiedoston muuttuneen:

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');
    // Sarake-id 3 = neljäs sarake suodatinALUEEN SISÄLLÄ (0-pohjainen siirtymä)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible vastaa nyt sitä, mitä Excel näyttää tiedoston avaamisen jälkeen

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

AutoFilterRowVisible vastaa rivi kerrallaan, ja PreviewAutoFilterRows käy koko alueen läpi takaisinsoittokutsulla, kun tarvitset vastaavat rivit yhdellä kertaa. On tilanne, jossa kumpikaan ei ole oikea vastaus: jos vaatimuksena on, ettei pois suljettuja rivejä saa olla tiedostossa lainkaan, kyse on tietosuojarajauksesta eikä näkymästä, joten poista rivit suoraan. Suodatin on siihen väärä työkalu, sillä vastaanottaja voi poistaa sen yhdellä napsautuksella ja piilotettavaksi tarkoitettu tieto on taas näytöllä

Saraketunniste on siirtymä, ei sarakenumero

Yllä olevan esimerkin kommentti osoittaa ansan, joka vie eniten virheenkorjausaikaa tässä API:ssa. AddAutoFilterColumn tunnistaa kohteensa suodatusalueen sisältä lasketulla 0-pohjaisella sijainnilla, ei laskentataulukon sarakkeella. Suodattimessa A1:E500 nämä kaksi numerointitapaa eroavat sattumalta yhdellä, mikä on juuri sellainen läheltä piti -tilanne, joka selviää nopeasta testistä ja rikkoutuu, kun kollega suodattaa toisen sarakkeen. Kun suodatusalue alkaa sarakkeesta C, tunniste 0 tarkoittaa saraketta C ja ero käy nopeasti ilmeiseksi. Kun suodatusalue lasketaan ajonaikana, johda saraketunniste samasta muuttujasta, josta alueen merkkijono rakennettiin, älä koskaan laskentataulukon sarakevakiosta. Jokainen sarake hyväksyy toisen ehdon ylikuormituksella, joka vastaanottaa kaksi operaattoria, kaksi ehtoa ja ja/tai-yhdistimen; se vastaa Excelin mukautettua suodatinvalintaikkunaa. XLS-rajapinta kattaa saman SetAutoFilter- ja ApplyAutoFilter-kutsuilla, joiden ehdot ja operaattoriparametrit noudattavat vanhempia COM-tyylisiä käytäntöjä ja numeroivat kentän alkaen yhdestä. Rajapinnan vaihtaminen tarkoittaa indeksikannan vaihtamista, joten kutsukohtaan kannattaa kirjoittaa kommentti käytössä olevasta kannasta

Kelpoisuustarkistussäännöt ovat sopimus, jonka puitteissa käyttäjä muokkaa

Näistä kolmesta ominaisuudesta vain kelpoisuustarkistus rajoittaa aktiivisesti tulevaa syötettä, ja siihen kannattaa käyttää eniten suunnittelua työkirjoissa, jotka lähetetään täytettäväksi ja palautetaan käsittelyyn. Luetteloversio hoitaa suurimman osan työstä:

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;

  // Määrät: kokonaislukuja, nolla tai enemmän
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Luetteloiden ja kokonaislukujen lisäksi sama perhe kattaa desimaalit, päivämäärät, ajat, tekstin pituuden ja vapaamuotoiset kaavat AddCustomValidation-kutsulla, ja yleinen AddDataValidation paljastaa koko tyyppi- ja operaattorimatriisin määritysperusteisille sääntörakentajille. Virhetyylillä on enemmän merkitystä kuin nimi antaa ymmärtää. xlsxDvErrStop hylkää virheellisen syötteen kokonaan; varoitus- ja tietotyylit päästävät arvon läpi yhden napsautuksen jälkeen. Valitse tyyli sarakekohtaisesti sen mukaan, sietääkö työkirjan takaisin lukeva koodi säännön ulkopuolista arvoa. Kaksi rajaa kuuluu kehotetekstiin tai tiedoston mukana toimitettavaan README-tiedostoon. Excelin kelpoisuustarkistus suojaa kirjoittamista, mutta lohkon liittäminen tarkistetun alueen päälle ohittaa säännön, joten tiedot takaisin lukeva koodi joutuu tarkistamaan ne uudelleen eikä saa luottaa soluihin. Sääntö kattaa kirjaimellisen sille annetun alueen, joten tarkistuksen liittäminen ennen lopullisen rivimäärän tietämistä jättää lisätyn hännän suojaamatta. Kirjoita tiedot ensin ja mitoita säännöt sitten todelliseen laajuuteen

Vanha rajapinta tarjoaa samat sääntöperheet yhdellä ergonomisella erolla. XLS-puolen luojat, eli AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation ja AddCustomValidation, palauttavat suoraan TDataValidation-objektin indeksin sijaan, joten kehotteen ja virheen asetukset ketjutetaan palautetusta viitteestä haun sijaan. Operaattoriluettelo (xlsDvBetween, xlsDvGreaterThan ja muut) vastaa XLSX-joukkoa, joten säännönrakennuskoodi siirtyy rajapintojen välillä palautustavan eroa lukuun ottamatta. Itse kehoteteksti ansaitsee yhtä paljon harkintaa kuin sääntö. Pudotusvalikko, joka hylkää syötteen tyhjällä virheruudulla, opettaa käyttäjät lähettämään sähköpostia IT:lle; valikko, joka kertoo sallitut tilat, opettaa korjaamaan solun ja jatkamaan

Yhden napaisuuden käännöksen kirjasto hoitaa puolestasi

Jokainen OOXML:n kelpoisuustarkistus-XML:ää käsin lukenut on kohdannut käänteisen showDropDown-attribuutin: ISO/IEC 29500 -standardissa arvo true tarkoittaa "piilota pudotusvalikkonuoli", vastoin sitä, miltä nimi vaikuttaa. HotXLS kääntää tämän sisäisesti, joten kelpoisuustarkistussäännön ShowDropDown-ominaisuus tarkoittaa sitä mitä sanoo: true näyttää pudotusvalikon. Ainoa tapa polttaa näppinsä on sekoittaa totuustasoja, asettaa ominaisuus koodista samalla kun kollega tarkastaa tallennetun XML:n ja "korjaa" hänestä nurinkuriselta näyttävän attribuutin. Päätä, onko ominaisuus vai raaka XML tarkistustyökalujen auktoriteetti, ja kirjaa käännös siihen paikkaan, jossa päätös elää

Taulukot antavat alueelle rakenteen ja nimen

Laskentataulukon taulukko, Excelin termein ListObject, kietoo alueen nimen, tyypitettyjen sarakkeiden, raidallisen tyylin ja rakenteisten viittausten tuen sisään. Se saa luodun työkirjan tuntumaan valmiilta, kun käyttäjät alkavat lajitella ja laajentaa sitä. Luonti on rajapinnoissa symmetrinen: AddTable vastaanottaa nimen, alueen ja sarakeluettelon:

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-puolella tuloksena syntyvä taulukko-objekti paljastaa StyleName-ominaisuuden (sisäänrakennettu TableStyleMedium2-perhe sisaruksineen), raitakytkimet ja loppusummarivin lipun, joten organisaation tyylin käyttö on ominaisuuden asetus eikä käsin tehtävä muotoiluvaihe. Vanhassa .xls-tiedostossa sama kutsu kirjoittaa BIFF8-taulukkotietueet, ja rajapinta tarjoaa myös AddPivotTable-kutsun rivi-, sarake- ja tietokentistä rakennettaville yhteenvetonäkymille. Se muistuttaa, että vanhemman muodon "taulukot" ulottuvat OOXML:n ListObjectia pidemmälle. Nimeä taulukot kuten tietokantanäkymät. Jatkokoodi, joka lukee rakenteisella viittauksella Orders[Amount], kestää sarakeuudelleenjärjestelyn, joka rikkoo sijaintiin perustuvan koodin

Kaksi käytäntöä säästää myöhemmin siivoustyötä. Excel vaatii taulukkonimien olevan yksilöllisiä koko työkirjassa, joten yksi arkki aluetta kohden luova generaattori tarvitsee mallin kuten Orders_EMEA sen sijaan, että se käyttäisi Orders-nimeä uudelleen. Kaksoiskappale ei epäonnistu kirjoitushetkellä; se ilmestyy korjausvalintaikkunana, kun käyttäjä avaa tiedoston, mikä on pahin mahdollinen hetki havaita se. Toinen käytäntö koskee loppusummariviä: käytössä ollessaan se sijaitsee suoraan tietoalueen alla, joten myöhemmin "viimeinen käytetty rivi plus yksi" -periaatteella lisäävä koodi kirjoittaa summariville eikä sen jälkeen. Seuraa tietoaluetta erillään taulukkoalueesta, niin lisäykset päätyvät odotettuun paikkaan

Nämä kolme ominaisuutta yhdistyvät luontevasti tietojen syöttöön tarkoitetuissa toimituksissa. Taulukko määrittää muokattavan alueen, kelpoisuustarkistus rajoittaa sarakkeita, joihin käyttäjät kirjoittavat, ja ennalta asetettu suodatin säästää vastaanottajan ensimmäisiltä napsautuksilta. On perusteltua toimittaa suodatin jo käytössä, jotta työkirja avautuu olennaisiin riveihin kohdistettuna, kunhan muistat, että pois suljetut rivit ovat silti tiedostossa ja utelias vastaanottaja voi paljastaa ne. Kyselytulosten tehokasta siirtämistä arkille, tämän työnkulun alkupäätä, käsitellään Delphin tietokantatulosten Excel-vientiä käsittelevässä artikkelissa, ja työkirjat, joissa kaavat tekevät yhteenvedon tarkistetuista tiedoista, hyötyvät määritettyjä nimiä vakaille arkkien välisille viittauksille käsittelevästä artikkelista

Kelpoisuustarkistukset, suodattimet ja taulukot ovat ero arvoruudukon ja pienen sovelluksen toimittamisen välillä. Täydellinen sääntö-, suodatin- ja taulukkomääritys on HotXLS Delphi Component -tuotesivulla