HotXLS-dropdownlistor och inmatningsvalidering i Delphi

HotXLS läser Excels datavaliderings-dropdownlistor direkt från Delphi-kod. TryGetCellDropdownItems returnerar kandidatsträngarna för en cell, SelectCellDropdownItem skriver en av dem per index eller per text, och ValidateCellInput rapporterar om ett skrivet värde skulle klara cellens regel innan något lagras. Samma metoder finns på TXLSWorksheet för Classic XLS och på TXLSXWorksheet för XLSX, och båda tar 1-baserade rad- och kolumnnummer

Den vanliga utlösaren för att behöva det här är en mall du inte designade. Någon på affärssidan levererar en arbetsbok med en Status-kolumn låst till Draft, Review och Final, en Channel-kolumn matad från ett område på ett dolt ark, och en kvantitetskolumn som bara accepterar 1 till 100. Ditt importjobb, ditt webbformulär eller din batchfyllare måste nu respektera de reglerna, och reglerna bor i arbetsboken, inte i din kod. Att hårdkoda de tillåtna värdena bredvid mallen är hur de två glider isär första gången någon redigerar listan i Excel

Varför ignorerar kodskrivningar Excels datavalidering?

Datavalidering är metadata fäst vid ett område, och att driva den är jobbet för vilken redigerare användaren än skriver i. I XLSX är regeln ett dataValidation-element (ECMA-376 Part 1, §18.3.1.32) som bär typen, operatorn, formula1 och formula2, prompten och felvarningen. I en Classic XLS-fil sitter samma information i DV-posten som beskrivs i [MS-XLS]. Inget av formaten märker cellen själv, så ingenting hindrar ett program från att lagra ett värde som bryter regeln. Excel beter sig på samma sätt: det kontrollerar inskrivna poster, och ett utanför-området-värde som kommit på andra vägar visar sig bara när någon kör Circle Invalid Data

HotXLS följer den modellen med flit. En tilldelning som Sheet.Cells[3, 2].Value := 250 lagrar 250 under en 1-till-100-regel utan klagan, precis som den medföljande DataValidation-demon gör för att bevisa poängen. Kontroll är opt-in, och resten av den här artikeln handlar om att välja in på rätt ställe

Läsa dropdownposterna bakom en cell

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) returnerar True bara när cellen täcks av en listtypsregel, och fyller AItems med en TArray<WideString> av kandidater. HotXLS löser de fyra sätt en listkälla skrivs i praktiken, så anroparen behöver aldrig parsa formula1 för hand:

• En citerad inline-lista som "Draft,Review,Final", delad på kommatecken
• En inline-lista vars poster skiljs av #0-tecken, en form som förekommer i Classic XLS-regler
• Ett område på samma ark eller på ett annat ark, som =$G$2:$G$10 eller =Lists!$A$2:$A$7, utvärderat till sina aktuella cellvärden
• Ett definierat namn som =ProductList som pekar på ett av de områdena

Sammanfogade celler hanteras också. Frågar du efter vilken cell som helst inuti en sammanslagning slår HotXLS upp regeln vid sammanslagningens ankare, vilket är där Excel behåller både regeln och värdet

AShowDropDown-flaggan förtjänar en notis, för filformaten lagrar den baklänges. I XLSX gömmer showDropDown="1" faktiskt in-cell-pilen, och i BIFF8 är den motsvarande biten en ”suppress combo”-flagga. HotXLS vänder båda vid läsning, så AShowDropDown = True betyder vad det säger: Excel skulle visa pilen. En listregel med pilen gömd begränsar fortfarande inmatning, vilket är varför TryGetCellDropdownItems returnerar posterna ändå

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2: rad 2, kolumn 6, båda 1-baserade
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

Behöver du bara strängarna returnerar GetCellDropdownItems(ARow, ACol) arrayen direkt och ger en tom array när ingen lista gäller. TryGetCellValidationPrompt returnerar inmatningsmeddelandets titel och text, vilket är praktiskt när du vill visa mallförfattarens egen ledtråd bredvid ett webbformulärsfält

Välja en post per index eller per text

SelectCellDropdownItem skriver en listpost i cellen och returnerar False när cellen saknar listregel eller den begärda posten inte finns. Heltalsoverloaden tar ett 0-baserat index i arrayen som TryGetCellDropdownItems returnerade. Textoverloaden letar efter en exakt matchning först och faller sedan tillbaka till en skiftlägesokänslig matchning, så 'review' plockar ändå Review

Båda overloaderna skriver via WriteAutoValue, inte via en rå strängtilldelning. Det spelar roll för områdesbackade listor av tal: posterna kommer tillbaka som strängar som '3', men det lagrade värdet är talet 3, vilket är vad Excel lagrar när en användare plockar från pilen och vad varje formel som refererar cellen förväntar sig. DataValidation-demon kontrollerar exakt detta efter en sparning och återöppning i XLS, XLSX och XLSB

// Samma anrop på TXLSWorksheet (XLS) och TXLSXWorksheet (XLSX)
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 4:e posten, 0-baserad
  Writeln('F2: index 3 is out of range');

Hur validerar du skriven inmatning innan den skrivs?

ValidateCellInput(ARow, ACol, AText) testar en kandidatsträng mot regeln som täcker cellen och ändrar aldrig cellen. Den returnerar True när ingen regel gäller eller kandidaten passerar. Strängen går genom samma text-till-värde-konvertering som WriteAutoValue använder, så kontrollen och den eventuella skrivningen ser samma värde. På XLSX-sidan är den konverteringen XlsxAutoTextValue: den känner igen TRUE/FALSE, felliteral, ISO-datum, m/d/yyyy- och d.m.yyyy-datum och tal i invariant format, och den behåller 0123 som text precis som Excel. Classic XLS-motorn har sin egen konverterare som speglar sin egen WriteAutoValue

Två följeslagare fullbordar uppsättningen. ValidateCellValue(ARow, ACol) kontrollerar värdet redan lagrat i cellen och behandlar tomma celler som giltiga. TryGetCellValidationError returnerar regelns felvarnings-titel, text och stil, och returnerar False när regeln inte visar någon felvarning alls. Stiltypen skiljer sig per motor, så matcha den till kalkylbladsklassen: TXLSDvErrorStyle från lxDataCheck för TXLSWorksheet, TXLSXDvErrorStyle för TXLSXWorksheet

Ett beteende du måste reproducera själv: Excel validerar inte en tom post eller något som börjar med =. HotXLS tillämpar det hoppet i viewerens redigeringsväg, inte inuti ValidateCellInput, så en serverdelsport bör göra detsamma innan den anropar den:

uses
  lxDataCheck, lxHandle;

// Returnerar True när AText får skrivas, speglar Excel:s felvarningsstilar
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel hoppar över validering för tömda celler och formler
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Ingen felvarning konfigurerad: Excel släpper igenom vad som helst
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop avvisar; Warning och Information lämnar beslutet till anroparen
  Result := Style <> xlsDvErrStop;
end;

// Anropare (Msg: WideString): validera, lagra sedan med samma konvertering
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Att para ihop ValidateCellInput med WriteAutoValue är poängen med exemplet. Validera med en konvertering och lagra med en annan, och förr eller senare passerar ett värde kontrollen som tal och landar i cellen som text

Citerade listposter och Classic XLS-escapefällan

Inline-listposter som innehåller dubbla citattecken är ett enkelt sätt att tappa en regel tyst. En inline-lista lagras som en formelsträng, och en formelsträng escapar ett inbäddat " genom att dubbla det. Wrappa posterna i citattecken utan att dubbla de inre, och en lista som He said "hi",No producerar en formel som inte kompilerar. Det kastas inget exception: regeln skapas helt enkelt inte, och dropdownen försvinner med den. TXLSWorksheet.AddListValidation och dess XLSX-motsvarighet dubblar båda inbäddade citattecken, så sådana poster överlever intakta

TXLSWorksheet.AddListValidation har också en array of WideString-overload, matchande den på TXLSXWorksheet. Den är bekväm, men läs vad den gör: den fogar ihop posterna med kommatecken och räcker resultatet till strängoverloaden. En post som själv innehåller ett kommatecken blir därför två poster, och på Classic-motorn tas en källsträng som innehåller ! som en arkkvalificerad referens i stället för en inline-lista. För rubriker som de, lägg värdena i ett cellområde och peka regeln mot det

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Bekräfta att regeln finns innan du bygger på den
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Efter en sparning och återöppning returnerar TryGetCellDropdownItems på H2 He said "hi" med sina citattecken intakta, i XLS-, XLSX-, XLSB- och ODS-utdata likaså

Låta TXLSWorkbookViewer driva reglerna

När användare redigerar celler på skärmen tillämpar TXLSWorkbookViewer samma kontroller åt dig. Med Editable satt till True och EnforceDataValidation kvar på sin standard True kontrolleras en skriven ändring som bryter en regel med felvarning innan den skrivs: Stop avvisar den, Warning frågar, Information informerar. Viewern ritar också dropdown-pilen på den aktiva cellen, öppnar listan med ett klick eller Alt+Down, och visar den gula inmatningsprompten under cellen. Detaljerna kring rutnätet självt tas upp i artikeln om att bygga ett eget kalkylbladsrutnät med TXLSWorkbookViewer

Att sätta CircleInvalidData till True ritar röda cirklar runt lagrade värden som bryter mot sin regel, som Excels kommando med samma namn. Den skannar bara det synliga området, regelar ut de flesta celler snabbt via FindDataValidationAt, och cirkular aldrig en tom cell

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // öppna redigeraren igen med den avvisade texten
  else
    Action := xvaAccept;
  Handled := True;        // undertryck den inbyggda dialogen
end;

Lämna Handled på False och viewern visar sin egen varningsdialog, som funkar under både VCL och LCL. Den standarden biter i automatiserade tester: ett test som driver ändringar utan en OnValidationError-handler som sätter Handled blockerar på en modal dialog. Viewerens stilenum är en tredje typ, TXLSViewerValidationErrorStyle med xveStop, xveWarning och xveInformation, åtskild från de två kalkylbladsenumerna

Vad bör du veta innan du bygger på HotXLS-inmatningsvalidering?

Kontrollerna är trogna för list-, tal-, datum-, tids- och textlängdregler, och några enginespecifika detaljer är värda att känna till:

• På Classic XLS-kalkylblad rapporterar ValidateCellInput custom-formelregler som giltiga för skrivna kandidater, för en kandidat kan inte placeras i en BIFF-cell tillfälligt utan att störa beroendekedjan. Kontrollera det lagrade värdet med ValidateCellValue, som utvärderar den custom-formeln, eller använd XLSX-motorn, som utvärderar custom-regler för kandidater också
• På Classic XLS behandlar listmatchning numeriskt lika värden som samma post, så 5 och 5.0 matchar båda en post på 5
• I viewern hoppar inklistrat innehåll över validering, som i Excel. EditSelectedCell har ingen redigerare att öppna igen, så ett Retry-resultat där beter sig som Cancel
• Ren Cells[...].Value-tilldelning kontrolleras aldrig, av design. Validering sker bara där din kod ber om den

Att skapa reglerna från början, inklusive heltals-, datum-, textlängd- och custom-typer, tas upp i HotXLS-guiden till datavalidering, AutoFilter och tabeller. XLSB-arbetsböcker laddas in i TXLSXWorkbook, så varje TXLSXWorksheet-anrop som visas här funkar oförändrat på dem, enligt artikeln om att läsa och skriva XLSB-binära arbetsböcker i Delphi

Dropdownuppslagning, postval och inmatningsvalidering följer med HotXLS Delphi Excel-komponenten för Delphi, C++Builder och Lazarus, tillsammans med DataValidation- och WorkbookViewer-demoerna som tränar varje anrop i den här artikeln