Datavalidering, AutoFilter og regnearktabeller i Delphi med HotXLS

Tre funksjoner i HotXLS deler et regneark, men opererer på helt forskjellige objekter, og problemene starter når du antar at de gjør lignende ting. Datavalidering fester en regel til et område som begrenser hva en bruker kan skrive inn i det. Et AutoFilter fester en lagret kriteriedefinisjon til et område og endrer hvilke rader en betrakter ser. En tabell pakker et område inn i en navngitt, typet struktur med stripete stiling. Én begrenser inndata, én registrerer en visning, én pålegger et skjema. Ingen av dem flytter en eneste celleverdi på egen hånd, og AutoFilter lurer folk spesielt, fordi ordet antyder en handling når det bare lagrer en definisjon. Å vite hvilket objekt hvert kall berører, og når effekten faktisk materialiserer seg, er det som skiller en arbeidsbok som oppfører seg likt i Excel som i testene dine, fra en som stille avviker

AutoFilter lagrer en definisjon, det beskjærer ikke rader

Et AutoFilter i en lagret fil er en kriteriepost. Radskjulingen skjer senere, når Excel åpner arbeidsboken og evaluerer kriteriene mot dataene. HotXLS skriver den posten trofast og beskjærer ingenting: hver rad du filtrerte, er fortsatt fysisk til stede i filen. En pipeline som bruker et filter for å droppe avviste ordrer og deretter leser arbeidsboken tilbake, vil se alle sammen, avviste inkludert, og koden er korrekt etter API-et, men feil etter forfatterens mentale modell. På XLSX-arket erklærer SetAutoFilter det filtrerte området, og AddAutoFilterColumn fester kriterier til én kolonne i det. Når serverkode trenger det faktiske utfallet, for et radantall i et sammendrag eller for å videresende bare matchende rader, evaluerer biblioteket kriteriene for deg i stedet for å late som om filen endret seg:

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');
    // Kolonne-id 3 = fjerde kolonne INNE i filterområdet (0-basert forskyvning)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible samsvarer nå med det Excel vil vise etter at filen er åpnet

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

AutoFilterRowVisible svarer per rad, og PreviewAutoFilterRows går gjennom hele området via en callback når du trenger det matchende settet i én omgang. Det finnes et tilfelle der ingen av dem er det riktige svaret: hvis kravet er at ekskluderte rader ikke skal eksistere i filen i det hele tatt, et personvernkutt snarere enn en visning, slett radene direkte. Et filter er feil verktøy der, fordi enhver mottaker fjerner det med ett klikk, og dataene du mente å holde tilbake, er tilbake på skjermen

Kolonne-id-en er en forskyvning, ikke et kolonnenummer

Kommentaren i utdraget ovenfor flagger fellen som koster mest feilsøkingstid i dette API-et. AddAutoFilterColumn identifiserer målet sitt ved den 0-baserte posisjonen inne i filterområdet, ikke ved regnearkkolonnen. For et filter på A1:E500 skjer det at de to nummereringssystemene skiller seg med én, som er nettopp den typen nesten-glipp som overlever en rask test og brytes i det øyeblikket en kollega filtrerer en annen kolonne. For et filter som starter ved kolonne C, betyr id 0 kolonne C, og misforholdet blir raskt åpenbart. Når filterområdet beregnes ved kjøretid, utled kolonne-id-en fra den samme variabelen som bygget områdestrengen, aldri fra en regnearkkolonnekonstant. Hver kolonne godtar en andre betingelse gjennom overbelastningen som tar to operatorer, to kriterier, og en og/eller-kobling, som speiler Excels egendefinerte filterdialog. XLS-fasaden dekker samme grunn med SetAutoFilter pluss ApplyAutoFilter, hvis kriterie- og operatorparametere følger de eldre COM-stil-konvensjonene og nummererer feltet fra 1. Å bytte fasade betyr å bytte indeksbase, så kallstedet fortjener en kommentar som sier hvilken som gjelder

Valideringsregler er kontrakten brukerne dine redigerer under

Av de tre funksjonene er validering den eneste som aktivt begrenser fremtidig inndata, og den fortjener mest designoppmerksomhet i arbeidsbøker som sendes ut for utfylling og kommer tilbake for behandling. Listevarianten bærer mesteparten av det arbeidet:

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;

  // Antall: hele tall, null eller mer
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Utover lister og hele tall dekker samme familie desimaler, datoer, klokkeslett, tekstlengde og friformsformler gjennom AddCustomValidation, og den generiske AddDataValidation eksponerer hele type-og-operator-matrisen for regelbyggere styrt av konfigurasjon. Feilstilen betyr mer enn navnet antyder. xlsxDvErrStop avviser ugyldig inndata direkte; advarsel- og informasjonsstilene lar verdien slippe gjennom etter ett enkelt klikk. Velg per kolonne basert på om koden som leser arbeidsboken tilbake, tåler en verdi utenfor regelen. To grenser hører hjemme i prompt-teksten eller README-en du sender med filen. Validering i Excel vokter innskriving, men å lime inn en blokk over et validert område glir forbi regelen, så all kode som leser dataene tilbake, må validere på nytt i stedet for å stole på cellene. Og en regel dekker det bokstavelige området du ga den, noe som betyr at å feste validering før du kjenner det endelige radantallet, lar den tilføyde halen stå uten vakt. Skriv dataene først, still deretter reglene inn på den faktiske utstrekningen

Den gamle fasaden tilbyr de samme regelfamiliene med én ergonomisk forskjell. Skaperne på XLS-siden, nemlig AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation og AddCustomValidation, returnerer TDataValidation-objektet direkte i stedet for en indeks, så prompt- og feilkonfigurasjon kjedes fra den returnerte referansen i stedet for et oppslag. Operator-enumereringen (xlsDvBetween, xlsDvGreaterThan, og resten) speiler XLSX-settet, så regelbyggende kode porterer mellom fasadene bortsett fra den returstilforskjellen. Selve prompt-teksten fortjener like mye omtanke som regelen. En nedtrekksliste som avviser inndata med en tom feilboks, lærer brukerne å sende e-post til IT; én som navngir de lovlige tilstandene, lærer dem å rette cellen og gå videre

Ett polaritetsbytte biblioteket absorberer for deg

Alle som har lest OOXML-valideringsXML for hånd, har møtt det inverterte showDropDown-attributtet: i ISO/IEC 29500 betyr en sann verdi «undertrykk nedtrekkspilen», det motsatte av det navnet leses som. HotXLS snur dette internt, så ShowDropDown-egenskapen på en valideringsregel betyr det den sier, med sann som viser nedtrekkslisten. Den eneste måten å brenne seg på, er å blande sannhetsnivåer, sette egenskapen fra kode mens en kollega reviderer den lagrede XML-en og «retter» attributtet som ser baklengs ut for dem. Bestem om egenskapen eller den rå XML-en er autoritativ for revisjonsverktøy, og skriv ned inverteringen der den beslutningen bor

Tabeller gir et område et skjema og et navn

En regnearktabell, ListObject i Excel-termer, pakker et område inn i et navn, typede kolonner, stripete stiling og støtte for strukturerte referanser. Det er funksjonen som gjør at en generert arbeidsbok føles ferdig når brukere begynner å sortere og utvide den. Opprettelse er symmetrisk på tvers av fasadene, med AddTable som tar et navn, et område og en kolonneliste:

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;

På XLSX-siden eksponerer det resulterende tabellobjektet StyleName (den innebygde TableStyleMedium2-familien og søsknene dens), stripevekslere og et totalradflagg, så det å bruke husstiling er en egenskapstildeling i stedet for en manuell formateringsrunde. I gamle .xls-filer skriver samme kall BIFF8-tabellpostene, og fasaden tilbyr også AddPivotTable for sammendragsvisninger bygget fra rad-, kolonne- og datafelt, en påminnelse om at «tabeller» i det eldre formatet rekker lenger enn OOXML-ens ListObject gjør. Navngi tabeller slik du navngir databasevisninger. Nedstrøms kode som leser Orders[Amount] via strukturert referanse, overlever kolonneomorganiseringen som ødelegger posisjonsbasert kode

To konvensjoner sparer opprydding senere. Excel krever at tabellnavn er unike på tvers av hele arbeidsboken, så en generator som lager ett ark per region, trenger et mønster som Orders_EMEA i stedet for å gjenbruke Orders. Et duplikat feiler ikke ved skrivetidspunktet; det dukker opp som en reparasjonsdialog når brukeren åpner filen, som er det verste stedet å oppdage det på. Den andre konvensjonen gjelder totalraden: når den er aktivert, sitter den rett under dataområdet, så all kode som senere tilføyer etter «sist brukte rad pluss én», skriver inn i totalbåndet i stedet for etter det. Spor dataomfanget atskilt fra tabellomfanget, og tilføyelsene lander der du forventer

De tre funksjonene setter seg naturlig sammen i leveranser for datainntasting. En tabell definerer det redigerbare området, validering begrenser kolonnene brukerne skriver inn i, og et forhåndssatt filter sparer mottakeren for de første klikkene. Det finnes et rimelig argument for å levere med et filter allerede påført, slik at arbeidsboken åpnes fokusert på radene som betyr noe, så lenge du husker at de ekskluderte radene fortsatt er i filen, og en nysgjerrig mottaker kan avsløre dem. Å få spørringsresultater effektivt inn i arket, oppstrømshalvdelen av denne pipelinen, dekkes i eksport av databaseresultater til Excel fra Delphi, og arbeidsbøker der formler oppsummerer de validerte dataene, drar nytte av definerte navn for stabile arkkryssende referanser

Validering, filtre og tabeller er forskjellen mellom å levere et rutenett av verdier og å levere en liten applikasjon. Den fullstendige regel-, filter- og tabellreferansen finnes på produktsiden for HotXLS Delphi Component