HotXLS: data validation, AutoFilter, and worksheet tables

Drie functies in HotXLS delen een werkblad maar werken op volledig verschillende objecten, en de problemen beginnen wanneer je aanneemt dat ze vergelijkbare dingen doen. Gegevensvalidatie koppelt een regel aan een bereik die beperkt wat een gebruiker erin mag typen. Een AutoFilter koppelt een opgeslagen criteriumdefinitie aan een regio en verandert welke rijen een kijker te zien krijgt. Een tabel wikkelt een bereik in een benoemde, getypeerde structuur met gebande opmaak. De één beperkt invoer, de ander legt een weergave vast, de derde legt een schema op. Geen van drieën verplaatst op zichzelf ook maar één celwaarde, en vooral de AutoFilter misleidt mensen, omdat het woord een actie suggereert terwijl het alleen een definitie opslaat. Weten welk object elke aanroep raakt, en wanneer het effect daadwerkelijk vorm krijgt, is wat een werkmap die zich in Excel hetzelfde gedraagt als in je tests onderscheidt van een die stilletjes afwijkt

AutoFilter slaat een definitie op, het snoeit geen rijen

Een AutoFilter in een opgeslagen bestand is een criteriumrecord. Het verbergen van rijen gebeurt later, wanneer Excel de werkmap opent en de criteria tegen de data evalueert. HotXLS schrijft dat record getrouw en snoeit niets: elke rij die je hebt gefilterd is nog steeds fysiek aanwezig in het bestand. Een pipeline die een filter toepast om afgewezen bestellingen te laten vallen en de werkmap vervolgens weer inleest, zal ze allemaal zien, afgewezen bestellingen inbegrepen, en de code is correct volgens de API maar fout volgens het mentale model van de auteur. Op het XLSX-werkblad declareert SetAutoFilter de gefilterde regio en koppelt AddAutoFilterColumn criteria aan één kolom ervan. Wanneer server-side code de daadwerkelijke uitkomst nodig heeft, voor een rijtelling in een samenvatting of om alleen overeenkomende rijen door te sturen, evalueert de bibliotheek de criteria voor je in plaats van te doen alsof het bestand is veranderd:

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');
    // Kolom-id 3 = vierde kolom BINNEN het filterbereik (0-gebaseerde offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible komt nu overeen met wat Excel toont nadat het bestand is geopend

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

AutoFilterRowVisible antwoordt per rij, en PreviewAutoFilterRows doorloopt de hele regio via een callback wanneer je de overeenkomende set in één keer nodig hebt. Er is een geval waarin geen van beide het juiste antwoord is: als de eis is dat uitgesloten rijen helemaal niet in het bestand mogen bestaan, een privacysnede in plaats van een weergave, verwijder de rijen dan gewoon. Een filter is daar het verkeerde middel, want elke ontvanger wist het met één klik en de data die je wilde achterhouden staat weer op het scherm

De kolom-id is een offset, geen kolomnummer

Het commentaar in het fragment hierboven markeert de valkuil die de meeste debugtijd kost in deze API. AddAutoFilterColumn identificeert zijn doel via de 0-gebaseerde positie binnen het filterbereik, niet via de werkbladkolom. Voor een filter op A1:E500 verschillen de twee nummeringssystemen toevallig maar één, en dat is precies het soort bijna-misser dat een snelle test overleeft en breekt op het moment dat een collega een andere kolom filtert. Voor een filter dat begint bij kolom C betekent id 0 kolom C, en de mismatch wordt snel duidelijk. Wanneer het filterbereik tijdens runtime wordt berekend, leid de kolom-id dan af uit dezelfde variabele die de bereikstring heeft opgebouwd, nooit uit een werkbladkolomconstante. Elke kolom accepteert een tweede voorwaarde via de overload die twee operatoren, twee criteria en een en/of-verbinder neemt, wat Excels aangepaste filterdialoog weerspiegelt. De XLS-facade behandelt hetzelfde terrein met SetAutoFilter plus ApplyAutoFilter, waarvan de criterium- en operatorparameters de oudere COM-stijlconventies volgen en het veld vanaf 1 nummeren. Van facade wisselen betekent van indexbasis wisselen, dus de aanroepplek verdient een commentaar dat aangeeft welke er in het spel is

Validatieregels zijn het contract waaronder je gebruikers bewerken

Van de drie functies is validatie de enige die toekomstige invoer actief beperkt, en het verdient de meeste ontwerpaandacht in werkmappen die de deur uitgaan om ingevuld te worden en terugkomen voor verwerking. De lijstvariant draagt het meeste van dat werk:

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;

  // Aantallen: gehele getallen, nul of meer
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Naast lijsten en gehele getallen dekt dezelfde familie decimalen, datums, tijden, tekstlengte en vrije formules via AddCustomValidation, en de generieke AddDataValidation biedt de volledige type-en-operatormatrix voor regelbouwers die op configuratie draaien. De foutstijl doet meer dan de naam doet vermoeden. xlsxDvErrStop wijst foutieve invoer regelrecht af; de waarschuwings- en informatiestijlen laten de waarde door na één klik. Kies per kolom op basis van of de code die de werkmap teruglees een waarde buiten de regel kan verdragen. Twee grenzen horen thuis in de prompttekst of de README die je bij het bestand levert. Validatie in Excel bewaakt het typen, maar het plakken van een blok over een gevalideerd bereik glipt langs de regel, dus alle code die de data teruglees moet opnieuw valideren in plaats van de cellen te vertrouwen. En een regel dekt het letterlijke bereik dat je haar hebt gegeven, wat betekent dat het koppelen van validatie voordat je het uiteindelijke rijaantal kent de toegevoegde staart onbewaakt laat. Schrijf eerst de data, en pas dan de omvang van de regels aan op de werkelijke omvang

De legacy-facade biedt dezelfde regelfamilies met één ergonomisch verschil. De XLS-kant-makers, namelijk AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation en AddCustomValidation, geven het TDataValidation-object rechtstreeks terug in plaats van een index, zodat prompt- en foutconfiguratie doorschakelt vanaf de teruggegeven referentie in plaats van een lookup. De operatorenumeratie (xlsDvBetween, xlsDvGreaterThan en de rest) weerspiegelt de XLSX-set, zodat regelbouwcode tussen facades overdraagbaar is, afgezien van dat verschil in returnstijl. De prompttekst zelf verdient net zoveel aandacht als de regel. Een dropdown die invoer afwijst met een leeg foutvak leert gebruikers om IT te mailen; een die de geldige statussen benoemt, leert hen de cel te herstellen en verder te gaan

Eén polariteitsomkering die de bibliotheek voor je opvangt

Iedereen die met de hand OOXML-validatie-XML heeft gelezen, is de omgekeerde showDropDown-attribuut tegengekomen: in ISO/IEC 29500 betekent een waarde true "onderdruk de dropdownpijl," het tegenovergestelde van wat de naam doet vermoeden. HotXLS keert dit intern om, zodat de ShowDropDown-eigenschap op een validatieregel betekent wat ze zegt, waarbij true de dropdown toont. De enige manier om je te branden is niveaus van waarheid te mengen, de eigenschap vanuit code instellen terwijl een collega de opgeslagen XML controleert en het attribuut "corrigeert" dat hen achterstevoren lijkt. Bepaal of de eigenschap of de ruwe XML leidend is voor reviewtooling, en schrijf de omkering op waar die beslissing leeft

Tabellen geven een bereik een schema en een naam

Een werkbladtabel, het ListObject in Excel-termen, wikkelt een bereik in een naam, getypeerde kolommen, gebande opmaak en ondersteuning voor gestructureerde verwijzingen. Het is de functie die een gegenereerde werkmap afgewerkt laat aanvoelen zodra gebruikers gaan sorteren en uitbreiden. Het aanmaken is symmetrisch over facades heen, waarbij AddTable een naam, een bereik en een kolomlijst neemt:

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;

Aan de XLSX-kant biedt het resulterende tabelobject StyleName (de ingebouwde TableStyleMedium2-familie en verwanten), streeptoggle's en een totaalrijvlag, zodat het toepassen van huisstijl een eigenschapstoewijzing is in plaats van een handmatige opmaakpas. In legacy .xls-bestanden schrijft dezelfde aanroep de BIFF8-tabelrecords, en de facade biedt ook AddPivotTable voor samenvattende weergaven opgebouwd uit rij-, kolom- en gegevensvelden, een herinnering dat "tabellen" in het oudere formaat verder reiken dan de OOXML-ListObject. Geef tabellen namen zoals je databaseweergaven benoemt. Downstream code die Orders[Amount] leest via gestructureerde verwijzing overleeft de kolomherordening die positionele code breekt

Twee conventies besparen later opruimwerk. Excel vereist dat tabelnamen uniek zijn over de hele werkmap, dus een generator die één blad per regio uitzendt heeft een schema nodig zoals Orders_EMEA in plaats van Orders te hergebruiken. Een duplicaat faalt niet op schrijfmoment; het duikt op als een hersteldialoog wanneer de gebruiker het bestand opent, wat de slechtste plek is om het te ontdekken. De andere conventie betreft de totaalrij: wanneer ingeschakeld, zit die direct onder het databereik, dus alle code die later toevoegt via "laatst gebruikte rij plus één" schrijft in de totaalband in plaats van erna. Houd de data-omvang apart bij van de tabel-omvang en de toevoegingen komen terecht waar je ze verwacht

De drie functies vormen samen een natuurlijk geheel in dataregistratie-opleverstukken. Een tabel definieert de bewerkbare regio, validatie beperkt de kolommen waarin gebruikers typen, en een vooraf ingesteld filter bespaart de ontvanger de eerste paar klikken. Er is een redelijk argument om een filter al toegepast te leveren zodat de werkmap gefocust opent op de rijen die ertoe doen, zolang je onthoudt dat de uitgesloten rijen nog steeds in het bestand zitten en een nieuwsgierige ontvanger ze kan onthullen. Queryresultaten efficiënt in het blad krijgen, de stroomopwaartse helft van deze pipeline, wordt behandeld in databaseresultaten exporteren naar Excel vanuit Delphi, en werkmappen waar formules de gevalideerde data samenvatten profiteren van gedefinieerde namen voor stabiele cross-sheet-verwijzingen

Validatie, filters en tabellen zijn het verschil tussen het leveren van een raster van waarden en het leveren van een kleine applicatie. De complete referentie voor regels, filters en tabellen staat op de productpagina van HotXLS Delphi Component