BIFF8 AutoFilterin DOPER-kriteerit Delphissä HotXLS:llä

HotXLS tallentaa jokaisen BIFF8 AutoFilter -kriteerin AUTOFILTER-tietueeseen, joka kantaa kahta 10-tavuista DOPER-rakennetta, ja DOPERin tyyppi päättää, miten Excel vertailee. Versiosta v2.384.45 alkaen TXLSWorksheet.ApplyAutoFilter kirjoittaa vertailun kuten '>=100' IEEE-numero-DOPERina, joten Excel osuu numeerisiin soluihin eikä vertaile tekstinä. Muutokseen johtanut bugiraportti oli lyhyt ja raastava: yöajon vienti asetti suodatuksen summasarakkeelle, tiedosto avautui valittamatta, alasvetovalikon nuoli näytti kriteerin, ja suodatus osui nollaan riviin. Mikään ei ollut vioittunutta. Tavut olivat kelvollista BIFF8-muotoa, vain vääränlaista kelvollista, ja juuri tätä virheluokkaa tämä artikkeli käy läpi yhdessä kahden vanhemman, versiossa v2.384.18 korjatun tavutason virheen kanssa

Mitä BIFF8 AutoFilter oikeasti tallentaa?

BIFF8 AutoFilter on kolmen tietuetyypin joukko, ei yksi tietue, ja vain kenttää kohden kirjoitettava tietue kantaa kriteerit. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) kirjaa kuinka monta saraketta suodatusalue kattaa. FILTERMODE ($009B) on rungoton merkki, jonka HotXLS kirjoittaa vain silloin kun vähintään yhdellä kentällä on aktiivinen kriteeri. Sen jälkeen jokainen aktiivinen kenttä saa oman AUTOFILTER-tietueensa ($009E, §2.4.6): nollapohjainen kenttäindeksi, grbit-sana jonka kaksi alinta bittiä ovat wJoin, kaksi täsmälleen 10 tavun mittaista DOPERia sekä valinnainen häntä, joka pitää merkkijono-DOPERin merkit. Kenttäindeksi on levillä nollapohjainen vaikka ApplyAutoFilter numeroi kentät ykkösestä, mikä ratkaisee ensimmäisellä kerralla kun etsit tietuetta hexdumpista. Kunkin DOPERin ensimmäinen tavu, vt, kertoo minkä tyyppinen operandi seuraa:

• $04 on IEEE 754 -double jäljellä olevissa 8 tavussa, eli tapa jolla Excel tallentaa numeerisen vertailun
• $06 on merkkijono jonka pituus asuu yhdessä cch-tavussa, ja merkit itsensä työntyvät tietueen häntään
• $08 on Bes-arvo, Boolean tai virhekoodi paketoituna kahteen tavuun
• $0C ja $0E eivät kanna operandia ja tarkoittavat kaikkien tyhjien ja kaikkien ei-tyhjien vastaamista

Toinen tavu, grbitSgn, kantaa vertailua: 1–6 vastaavat merkitsejä <, =, <=, >, <> ja >=. HotXLS pitää molemmat tavut luettavissa jälkikäteen AutoFilterColumns-kokoelman kautta, jonka alkiot paljastavat Criteria1:n ja Criteria2:n TXLSAutofilterDOPER-objekteina, joilla on DataType, grbitSgn ja Value, joten voit assertata kirjoitettavan sisällön sen sijaan että arvaisit

Miksi '>=100'-suodatin ei osunut yhteenkään riviin Excelissä?

Suodatus ei osunut yhteenkään, koska operandi tallennettiin tekstinä, ja Excel vertailee merkkijono-DOPERia solua vasten tekstinä. Ennen v2.384.45:ää CreateFilterDoper tiedostossa lxFilter.pas poisti >=-etuliitteen oikein ja asetti vertailumerkiksi 6:n, ja rakensi sen jälkeen aina vtString-DOPERin joka piti sisällään merkit 100. Numerosolu jonka arvo on 250 ei koskaan läpäise tekstivertailua arvoa "100" vasten, joten joka rivi putosi pois. Ei poikkeusta, ei diagnostiikkaa, ei korjauskehottusta Exceliltä. Sääntö v2.384.45:stä alkaen on tarkoituksella kapea: jos kriteeri alkaa vertailuoperaattorilla ja loppuosa jäsenee lukuna invariant-culture-säännöillä, HotXLS kirjoittaa vtIEEENumber-DOPERin samalla merkillä. Pelkkä arvo ilman operaattoria säilyy merkkijonomuodossa, koska noin Excel itsekin tallentaa alasvetovalikosta poimitun kohdan

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
  Doper: TXLSAutofilterDOPER;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Cells[1, 1].Value := 'Region';
  Sh.Cells[1, 2].Value := 'Amount';
  Sh.Cells[2, 1].Value := 'North';
  Sh.Cells[2, 2].Value := 250;

  // Kenttä 2 = A1:B100:n toinen sarake (API-puolella 1-pohjainen)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE-numero), grbitSgn = 6 (>=)
  // Ennen korjausta: DataType = 6 (merkkijono), joka ei osunut yhteenkään
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;

Jäsentäminen on paikka jossa loput terävät kulmat asuvat. Operandi kulkee TryStrToFloatin läpi pisteellä desimaalierottimena, joten '>=1.5' muuttuu numeroksi ja '>=1,5' jää merkkijono-DOPERiksi ja osuu jälleen hiljaisena yhteenkään, riippumatta siitä mitä Windowsin locale sanoo. Päivämäärät ovat sama ansa toisessa asussa: '>=2026-01-01' ei ole luku, joten se kirjoitetaan tekstinä, vaikka Excel pitää päivämääräsoluja sarjanumeroina. Numeron yhtäsuuruudessa sekä '=100' että numeerinen Variant kuten 100 tuottavat IEEE-DOPERin merkillä 2, kun taas paljas merkkijono '100' tuottaa tekstivertailun. Rakenna numeeriset operandit koodissa sen sijaan että muotoilit ne ihmisille:

var
  Fmt: TFormatSettings;
  Since: TDateTime;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // Kynnys murto-osalla: muotoile aina pisteellä
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Päivämäärät: vertaa sarjanumeroon jonka Excel tallentaa soluun.
  // Delphin TDateTime vastaa 1900-järjestelmän sarjanumeroa maaliskuun 1900 jälkeisille päivämäärille
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Miten AND ja OR yhdistävät kaksi ehtoa?

AUTOFILTER-tietueen grbit-sanassa wJoin-bitit ovat 0 ANDille ja 1 ORille, ja HotXLS piti nämä kaksi vakiota väärinpäin asti v2.384.18:aan. Välityyppinen suodatus, kuten vähintään 100 ja alle 500, tallentui muotoon vähintään 100 tai alle 500, mikä käytännössä osuu jokaiseen numeroon ja näyttää siltä kuin suodatinta ei olisi sovellettu lainkaan. Julkiset operaattorivakiot tuovat mukanaan toisen porttausvaaran. HotXLS:ssä xlAnd on 0 ja xlOr on 1, kun taas Excel automation numeroi ne 1:ksi ja 2:ksi. XlAutoFilterOperator on pelkkä Byte, joten VBA-makrosta literaalinumeroilla käännetty koodi kääntyy siististi, ja COM automationissa ANDia tarkoittanut literaali 1 tarkoittaa täällä ORia. Käytä nimettyjä vakioita, eikä ongelmaa voi syntyä:

// Summa välillä 100 (mukaan lukien) ja 500 (pois lukien)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 levillä
  Assert(Criteria2.grbitSgn = 1);      // 1 = pienempi kuin
end;

Booleanit, tyhjät ja 255 merkin katto

Boolean-kriteeri tallennetaan Bes-arvona ([MS-XLS] §2.5.10), ja Bes laittaa arvotavun bBoolErr ensimmäiseksi ja fError-lipun toiseksi. HotXLS kirjoitti ne käänteisessä järjestyksessä ennen v2.384.18:aa, joten TRUElle asetettu suodatin pani 1:n virhelippuun ja Excel luki kriteerin virhekoodina. Kirjoittaja ja lukija oli vaihdettu yhdessä, minkä vuoksi HotXLS pyöritti omia tiedostojaan läpi valittamatta kun taas Excel oli eri mieltä — muistutus siitä, että itsensä kanssa yhdenmukainen round trip ei todista mitään spektin mukaisuudesta. Tyhjät eivät tarvitse operandia ylipäätään: pelkkä '=' tuottaa kaikkiin tyhjiin osuvan DOPERin ($0C) ja pelkkä '<>' kaikkiin ei-tyhjiin osuvan DOPERin ($0E)

Merkkijonokriteerit törmäävät kovaan rajaan DOPERin asettelussa. cch-pituuskenttä on yksi tavu, joten merkkijonooperandi ei voi ylittää 255 merkkiä, ja CreateFilterDoper typistää pidemmän tekstin operaattorin poistamisen jälkeen sen sijaan että antaisi pituustavun kiertyä ja tahristaa tietueen hännän. Typistys on hiljaista, ja pitkän kuvaussarakkeen suodatin voi osua eri tavalla kuin täysi teksti jonka annoit. BIFF8:ssä häntä tallentaa jokaisen merkkijonon yhden tavun lippuna jota seuraavat UTF-16-koodiyksiköt, ja ilmoitetun tietueen koon on laskettava nuo tavut täsmälleen — sama kirjanpitokuri, jota käsittelee artikkeli BIFF-tietueen pituusilmoitusten ajautumisesta Delphi XLS -kirjoittajassa

Miksi toinen ApplyAutoFilter-kutsu pyyhkii ensimmäisen pois?

Jokainen ApplyAutoFilter-kutsu määrittelee koko suodatusalueen uudelleen, joten vain viimeisen kutsun kriteeri jää henkiin. Sisäisesti se kutsuu SetAutoFilteria, joka tyhjentää jokaisen kentän ennen alueen rakentamista uudelleen, ja se on oikein yhdelle sarakkeelle ja yllättävää kahdelle. Suodattaaksesi useita sarakkeita kutsu ApplyAutoFilteria kerran perustaaksesi alueen ja ensimmäisen kriteerin, ja lisää sitten muut AutoFilterColumns.SetFieldCriteriain kautta, joka jättää alueen ja muut kentät rauhaan. Molemmat polut ohittavat alueen ulkopuolisen kenttänumeron nostamatta poikkeusta, joten varmista lukemalla takaisin, mieluiten avattuasi tallennetun tiedoston uudelleen:

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                     // alue + kenttä 1
Sh.AutoFilterColumns.SetFieldCriteria(3, '>=100', xlAnd, Unassigned);
Sh.AutoFilterColumns.SetFieldCriteria(4, True, xlAnd, Unassigned);
Wb.SaveAs('orders.xls');

Wb := TXLSWorkbook.Create;
Wb.Open('orders.xls');
Assert(Wb.Sheets[1].AutoFilterColumns.Find(1).Active);
Assert(Wb.Sheets[1].AutoFilterColumns.Find(3).Criteria1.DataType = 4);

Pidä mielessä, että AUTOFILTER-tietue on tallennettu määritelmä: HotXLS kirjoittaa kriteerit eikä laske niitä klassisella XLS-laskentataulukolla, joten putki, joka tarvitsee osuvat rivit palvelimella, joutuu laskemaan ne itse siellä, kun taas XLSX-julkisivu tarjoaa rivitason laskennan kuten artikkeli HotXLS-tietojen validointi, AutoFilter ja taulukot Delphissä näyttää. Kun Excel sitten piilottaakin rivejä, kaikki alueen alapuoliset summat riippuvat siitä, miten SUBTOTAL ja AGGREGATE kohtelevat piilotettuja ja suodatettuja rivejä — seuraava paikka, jossa numeerinen suodatin, joka osuu hiljaisena yhteenkään, ilmenee vääränä lukuna

HotXLS lukee ja kirjoittaa BIFF8 XLS- ja XLSX-työkirjoja natiivisti Delphistä ja C++Builderista käsin, mukaan lukien AutoFilter-kriteerit numeerisilla, Boolean- ja AND/OR-DOPEReilla, jotka Excel laskee tarkoitetulla tavalla. Ominaisuudet, versiot ja kokeiluversion lataus löytyvät HotXLS Delphi Excel -komponentin sivulta