Datavalidering, AutoFilter og regnearkstabeller i Delphi med HotXLS

Tre funktioner i HotXLS deler et regneark, men opererer på helt forskellige objekter, og problemerne starter, når man antager, at de gør noget lignende. Datavalidering knytter en regel til et område, som begrænser, hvad en bruger må skrive i det. Et AutoFilter knytter en gemt kriteriedefinition til et område og ændrer, hvilke rækker en seer viser. En tabel pakker et område ind i en navngiven, typet struktur med bånderet styling. Én begrænser input, én registrerer en visning, én pålægger et skema. Ingen af dem flytter en eneste celleværdi af sig selv, og AutoFilter narrer især folk, fordi ordet antyder en handling, når det kun gemmer en definition. At vide, hvilket objekt hvert kald rører ved, og hvornår effekten reelt viser sig, er det, der adskiller en projektmappe, der opfører sig ens i Excel og i dine tests, fra én, der i det stille afviger

AutoFilter gemmer en definition, det beskærer ikke rækker

Et AutoFilter i en gemt fil er en kriteriepost. Rækkeskjulningen sker senere, når Excel åbner projektmappen og evaluerer kriterierne mod dataene. HotXLS skriver den post loyalt og beskærer intet: hver eneste række, du filtrerede, er stadig fysisk til stede i filen. En pipeline, der anvender et filter for at frasortere afviste ordrer og derefter læser projektmappen tilbage, vil se dem alle, afviste inklusive, og koden er korrekt ifølge API'et, men forkert ifølge forfatterens mentale model. På XLSX-arket erklærer SetAutoFilter det filtrerede område, og AddAutoFilterColumn knytter kriterier til én kolonne i det. Når server-side-kode har brug for det faktiske resultat, til et rækketal i et sammendrag eller for kun at videresende matchende rækker, evaluerer biblioteket kriterierne for dig i stedet for at foregive, at filen ændrede sig:

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 INDE i filterområdet (0-baseret offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible matcher nu det, Excel vil vise, efter filen er åbnet

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

AutoFilterRowVisible svarer pr. række, og PreviewAutoFilterRows går hele området igennem via et callback, når du har brug for det matchende sæt i én omgang. Der er ét tilfælde, hvor ingen af dem er det rigtige svar: hvis kravet er, at ekskluderede rækker slet ikke må eksistere i filen, et privatlivssnit frem for en visning, så slet rækkerne helt. Et filter er det forkerte værktøj dér, fordi enhver modtager rydder det med ét klik, og de data, du ville tilbageholde, er tilbage på skærmen

Kolonne-id'et er et offset, ikke et kolonnenummer

Kommentaren i udsnittet ovenfor flager den fælde, der koster mest fejlfindingstid i dette API. AddAutoFilterColumn identificerer sit mål ved den 0-baserede position inden for filterområdet, ikke ved regnearkskolonnen. For et filter på A1:E500 viser de to nummereringssystemer sig at afvige med netop én, hvilket er præcis den slags nærmest-fejl, der overlever en hurtig test og går i stykker, i det øjeblik en kollega filtrerer en anden kolonne. For et filter, der starter ved kolonne C, betyder id 0 kolonne C, og uoverensstemmelsen bliver hurtigt tydelig. Når filterområdet beregnes ved kørselstid, skal kolonne-id'et udledes af den samme variabel, der byggede områdestrengen, aldrig af en regnearkskolonne-konstant. Hver kolonne accepterer en anden betingelse gennem overloadet, der tager to operatorer, to kriterier og en og/eller-forbindelse, hvilket spejler Excels dialog til brugerdefineret filter. XLS-facaden dækker det samme terræn med SetAutoFilter plus ApplyAutoFilter, hvis kriterie- og operatorparametre følger de ældre COM-stilkonventioner og nummererer feltet fra 1. At skifte facade betyder at skifte indeksbase, så kaldestedet fortjener en kommentar om, hvilken der er i spil

Valideringsregler er den kontrakt, dine brugere redigerer under

Af de tre funktioner er validering den eneste, der aktivt begrænser fremtidigt input, og den fortjener mest designopmærksomhed i projektmapper, der sendes ud til udfyldning og kommer tilbage til behandling. Listevarianten bærer det meste af det arbejde:

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ængder: hele tal, nul eller derover
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Ud over lister og hele tal dækker samme familie decimaltal, datoer, tidspunkter, tekstlængde og frie formler gennem AddCustomValidation, og den generiske AddDataValidation eksponerer den fulde type-og-operator-matrix til regelbyggere styret af konfiguration. Fejlstilen betyder mere, end navnet antyder. xlsxDvErrStop afviser dårligt input helt; advarsels- og informationsstilene lader værdien slippe igennem efter ét klik. Vælg pr. kolonne ud fra, om koden, der læser projektmappen tilbage, kan tolerere en værdi uden for reglen. To grænsetilfælde hører hjemme i prompt-teksten eller den README, du sender med filen. Validering i Excel vogter indtastning, men at indsætte en blok over et valideret område glider uden om reglen, så al kode, der læser dataene tilbage, må validere igen frem for at stole på cellerne. Og en regel dækker det bogstavelige område, du gav den, hvilket betyder, at at knytte validering til, før du kender det endelige rækketal, efterlader den tilføjede hale uden beskyttelse. Skriv dataene først, og tilpas derefter reglernes størrelse til den faktiske udstrækning

Den gamle facade tilbyder de samme regelfamilier med én ergonomisk forskel. XLS-sidens opretterfunktioner, nemlig AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation og AddCustomValidation, returnerer TDataValidation-objektet direkte frem for et indeks, så prompt- og fejlkonfiguration kæder af den returnerede reference i stedet for et opslag. Operator-enumerationen (xlsDvBetween, xlsDvGreaterThan og resten) spejler XLSX-sættet, så regelbyggende kode kan flyttes mellem facaderne bortset fra den forskel i returstil. Selve prompt-teksten fortjener lige så meget omtanke som reglen. En dropdown, der afviser input med en tom fejlboks, lærer brugerne at maile IT; én, der navngiver de gyldige tilstande, lærer dem at rette cellen og gå videre

Én polaritetsvending, biblioteket klarer for dig

Enhver, der har håndlæst OOXML-valideringens XML, er stødt på det inverterede showDropDown-attribut: i ISO/IEC 29500 betyder en sand værdi "undertryk dropdown-pilen", det modsatte af, hvad navnet lyder som. HotXLS vender dette internt, så ShowDropDown-egenskaben på en valideringsregel betyder det, den siger, med true, der viser dropdownen. Den eneste måde at brænde sig på er at blande niveauer af sandhed, at sætte egenskaben fra kode, mens en kollega reviderer den gemte XML og "retter" det attribut, der ser bagvendt ud for dem. Beslut, om egenskaben eller den rå XML er autoritativ for review-værktøjer, og skriv vendingen ned dér, hvor den beslutning bor

Tabeller giver et område et skema og et navn

En regnearkstabel, ListObject i Excel-termer, pakker et område ind i et navn, typede kolonner, bånderet styling og understøttelse af strukturerede referencer. Det er den funktion, der får en genereret projektmappe til at føles færdig, så snart brugerne begynder at sortere og udvide den. Oprettelse er symmetrisk på tværs af facaderne, med AddTable, der tager 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 tabelobjekt StyleName (den indbyggede TableStyleMedium2-familie og dens søskende), stribe-toggles og et totalrække-flag, så det at anvende husstil er en egenskabstildeling frem for en manuel formateringsgennemgang. I gamle .xls-filer skriver samme kald de samme BIFF8-tabelposter, og facaden tilbyder også AddPivotTable til sammendragsvisninger bygget af række-, kolonne- og datafelter, en påmindelse om, at "tabeller" i det ældre format rækker længere end OOXML's ListObject gør. Navngiv tabeller, som du navngiver database-views. Downstream-kode, der læser Orders[Amount] via struktureret reference, overlever den kolonneombytning, der ødelægger positionsbaseret kode

To konventioner sparer oprydning senere. Excel kræver, at tabelnavne er unikke på tværs af hele projektmappen, så en generator, der udsender ét ark pr. område, har brug for et skema som Orders_EMEA frem for at genbruge Orders. En dublet fejler ikke ved skrivetid; den dukker op som en reparationsdialog, når brugeren åbner filen, hvilket er det værste sted at opdage det. Den anden konvention angår totalrækken: når den er slået til, sidder den lige under dataområdet, så al kode, der senere tilføjer via "sidst brugte række plus én", skriver ind i totalbåndet i stedet for efter det. Hold styr på dataudstrækningen adskilt fra tabeludstrækningen, og tilføjelserne lander, hvor du forventer det

De tre funktioner spiller naturligt sammen i data-indtastningsleverancer. En tabel definerer det redigerbare område, validering begrænser de kolonner, brugerne skriver i, og et forudindstillet filter sparer modtageren de første par klik. Der er et rimeligt argument for at levere med et filter allerede anvendt, så projektmappen åbner fokuseret på de rækker, der betyder noget, så længe man husker, at de ekskluderede rækker stadig er i filen, og en nysgerrig modtager kan afsløre dem. At få forespørgselsresultater effektivt ind i arket, den opstrøms halvdel af denne pipeline, er dækket i at eksportere databaseresultater til Excel fra Delphi, og projektmapper, hvor formler opsummerer de validerede data, drager fordel af definerede navne til stabile referencer på tværs af ark

Validering, filtre og tabeller er forskellen mellem at levere et gitter af værdier og at levere en lille applikation. Den fulde regel-, filter- og tabelreference findes på produktsiden for HotXLS-Delphi-komponenten