HotXLS: data validation, AutoFilter, and worksheet tables

Tre funktioner i HotXLS delar ett kalkylblad men verkar på helt olika objekt, och problemen börjar när du antar att de gör liknande saker. Datavalidering fäster en regel vid ett område som begränsar vad en användare får skriva i det. Ett AutoFilter fäster en lagrad kriteriedefinition vid ett område och ändrar vilka rader en betraktare visar. En tabell omsluter ett område i en namngiven, typad struktur med bandad styling. En begränsar indata, en registrerar en vy, en påtvingar ett schema. Ingen av dem flyttar ett enda cellvärde på egen hand, och AutoFilter i synnerhet lurar folk, eftersom ordet antyder en handling när det bara lagrar en definition. Att veta vilket objekt varje anrop rör, och när effekten faktiskt materialiseras, är det som skiljer en arbetsbok som beter sig likadant i Excel som i dina tester från en som tyst avviker

AutoFilter lagrar en definition, det beskär inte rader

Ett AutoFilter i en sparad fil är en kriteriepost. Radgömningen sker senare, när Excel öppnar arbetsboken och utvärderar kriterierna mot datan. HotXLS skriver den posten troget och beskär ingenting: varje rad du filtrerade finns fortfarande fysiskt kvar i filen. En pipeline som applicerar ett filter för att slänga refuserade order och sedan läser tillbaka arbetsboken kommer att se alla, inklusive de refuserade, och koden är korrekt enligt API:et men fel enligt författarens mentala modell. På XLSX-kalkylbladet deklarerar SetAutoFilter det filtrerade området och AddAutoFilterColumn fäster kriterier vid en kolumn i det. När serverkod behöver det faktiska utfallet, för ett radantal i en sammanfattning eller för att vidarebefordra bara matchande rader, utvärderar biblioteket kriterierna åt dig i stället för att låtsas att filen ändrades:

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');
    // Kolumn-id 3 = fjärde kolumnen INUTI filterområdet (0-baserad offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible matchar nu vad Excel kommer att visa efter att filen öppnats

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

AutoFilterRowVisible svarar per rad, och PreviewAutoFilterRows går igenom hela området via en callback när du behöver den matchande mängden i ett svep. Det finns ett fall där ingen av dem är rätt svar: om kravet är att uteslutna rader inte alls får finnas i filen, ett integritetssnitt snarare än en vy, ta bort raderna helt och hållet. Ett filter är fel verktyg där, eftersom vilken mottagare som helst kan rensa det med ett klick och datan du menade att undanhålla är tillbaka på skärmen

Kolumn-id:t är en offset, inte ett kolumnnummer

Kommentaren i utdraget ovan flaggar fällan som kostar mest felsökningstid i det här API:et. AddAutoFilterColumn identifierar sitt mål via den 0-baserade positionen inom filterområdet, inte via kalkylbladskolumnen. För ett filter på A1:E500 råkar de två numreringssystemen skilja sig åt med ett, vilket är precis den sortens nära-miss som överlever ett snabbt test och går sönder i samma stund en kollega filtrerar en annan kolumn. För ett filter som börjar vid kolumn C betyder id 0 kolumn C, och missmatchningen blir snabbt uppenbar. När filterområdet beräknas vid körning, härled kolumn-id:t från samma variabel som byggde områdessträngen, aldrig från en kolumnkonstant på kalkylbladet. Varje kolumn accepterar ett andra villkor via överlagringen som tar två operatorer, två kriterier och en och/eller-koppling, vilket speglar Excels anpassade filterdialog. XLS-fasaden täcker samma mark med SetAutoFilter plus ApplyAutoFilter, vars kriterie- och operatorparametrar följer de äldre COM-stilkonventionerna och numrerar fältet från 1. Att byta fasad betyder att byta indexbas, så anropsstället förtjänar en kommentar om vilken som är i bruk

Valideringsregler är kontraktet dina användare redigerar under

Av de tre funktionerna är validering den enda som aktivt begränsar framtida indata, och den förtjänar mest designuppmärksamhet i arbetsböcker som skickas ut för ifyllning och kommer tillbaka för bearbetning. Listvarianten bär det mesta av det arbetet:

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;

  // Antal: heltal, noll eller mer
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Utöver listor och heltal täcker samma familj decimaler, datum, tider, textlängd och fritt formulerade formler via AddCustomValidation, och den generiska AddDataValidation exponerar hela typ-och-operator-matrisen för regelbyggare som styrs av konfiguration. Felstilen betyder mer än namnet antyder. xlsxDvErrStop avvisar felaktig indata helt; varnings- och informationsstilarna släpper igenom värdet efter ett enda klick. Välj per kolumn baserat på om koden som läser tillbaka arbetsboken kan tolerera ett värde utanför regeln. Två gränsfall hör hemma i prompttexten eller README-filen du levererar med filen. Validering i Excel vaktar skrivning, men att klistra in ett block över ett validerat område smiter förbi regeln, så all kod som läser tillbaka datan måste validera igen i stället för att lita på cellerna. Och en regel täcker det bokstavliga området du gav den, vilket betyder att fästa validering innan du känner till det slutliga radantalet lämnar den tillagda svansen oskyddad. Skriv datan först, storleksanpassa sedan reglerna efter den faktiska omfattningen

Den äldre fasaden erbjuder samma regelfamiljer med en ergonomisk skillnad. XLS-sidans skapare, nämligen AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation och AddCustomValidation, returnerar TDataValidation-objektet direkt i stället för ett index, så att prompt- och felkonfiguration kedjas från den returnerade referensen i stället för en uppslagning. Operatoruppräkningen (xlsDvBetween, xlsDvGreaterThan och resten) speglar XLSX-uppsättningen, så regelbyggande kod portas mellan fasaderna bortsett från den returstilsskillnaden. Själva prompttexten förtjänar lika mycket eftertanke som regeln. En rullgardinsmeny som avvisar indata med en tom felruta lär användare att mejla IT; en som namnger de tillåtna tillstånden lär dem att fixa cellen och gå vidare

En polaritetsomvändning biblioteket absorberar åt dig

Alla som handläst OOXML-valideringens XML har stött på det inverterade attributet showDropDown: i ISO/IEC 29500 betyder ett sant värde "dölj rullgardinspilen," motsatsen till vad namnet antyder. HotXLS vänder på det här internt, så egenskapen ShowDropDown på en valideringsregel betyder vad den säger, med true som visar rullgardinsmenyn. Det enda sättet att bränna sig är att blanda sanningsnivåer, sätta egenskapen från kod medan en kollega granskar den sparade XML:en och "rättar" attributet som ser bakvänt ut för dem. Bestäm om egenskapen eller den råa XML:en är auktoritativ för granskningsverktyg, och skriv ner inverteringen där det beslutet bor

Tabeller ger ett område ett schema och ett namn

En kalkylbladstabell, ListObject i Excels termer, omsluter ett område i ett namn, typade kolumner, bandad styling och stöd för strukturerade referenser. Det är funktionen som får en genererad arbetsbok att kännas färdig så fort användare börjar sortera och utöka den. Skapandet är symmetriskt mellan fasaderna, med AddTable som tar ett namn, ett område och en kolumnlista:

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-sidan exponerar det resulterande tabellobjektet StyleName (den inbyggda familjen TableStyleMedium2 och dess syskon), randväxlare och en summeringsradsflagga, så att applicera husstil är en egenskapstilldelning i stället för en manuell formateringsomgång. I äldre .xls-filer skriver samma anrop BIFF8-tabellposterna, och fasaden erbjuder också AddPivotTable för sammanfattningsvyer byggda från rad-, kolumn- och datafält, en påminnelse om att "tabeller" i det äldre formatet sträcker sig längre än OOXML:s ListObject gör. Namnge tabeller på samma sätt du namnger databasvyer. Nedströmskod som läser Orders[Amount] via strukturerad referens överlever den kolumnomordning som slår sönder positionsbaserad kod

Två konventioner sparar uppstädning senare. Excel kräver att tabellnamn är unika över hela arbetsboken, så en generator som skriver ut ett blad per region behöver ett schema som Orders_EMEA i stället för att återanvända Orders. En dublett misslyckas inte vid skrivtillfället; den dyker upp som en reparationsdialog när användaren öppnar filen, vilket är den värsta platsen att upptäcka den på. Den andra konventionen gäller summeringsraden: när den är aktiverad sitter den direkt under dataområdet, så all kod som senare lägger till via "sista använda rad plus ett" skriver in i summeringsbandet i stället för efter det. Håll reda på dataomfattningen separat från tabellomfattningen så hamnar tilläggen där du förväntar dig

De tre funktionerna kombineras naturligt i leveranser för datainmatning. En tabell definierar det redigerbara området, validering begränsar kolumnerna användare skriver i, och ett förinställt filter besparar mottagaren de första klicken. Det finns ett rimligt argument för att leverera med ett filter redan applicerat så att arbetsboken öppnas fokuserad på de rader som spelar roll, så länge du kommer ihåg att de uteslutna raderna fortfarande finns i filen och en nyfiken mottagare kan avslöja dem. Att få frågeresultat in i bladet effektivt, den uppströms halvan av den här pipelinen, täcks i artikeln om att exportera databasresultat till Excel från Delphi, och arbetsböcker där formler sammanfattar den validerade datan drar nytta av definierade namn för stabila korsbladsreferenser

Validering, filter och tabeller är skillnaden mellan att leverera ett rutnät av värden och att leverera en liten applikation. Den fullständiga referensen för regler, filter och tabeller finns på produktsidan för HotXLS Delphi Component