Dropdown list e validazione input in Delphi con HotXLS

HotXLS legge i dropdown di convalida dati di Excel direttamente dal codice Delphi. TryGetCellDropdownItems restituisce le stringhe candidate per una cella, SelectCellDropdownItem ne scrive una per indice o per testo, e ValidateCellInput riporta se un valore digitato passerebbe la regola della cella prima che qualcosa venga memorizzato. Gli stessi metodi esistono su TXLSWorksheet per l'XLS Classic e su TXLSXWorksheet per XLSX, e entrambi prendono numeri di riga e colonna a base 1

Il solito motivo per cui serve questa cosa è un template che non avete progettato voi. Qualcuno dal lato business spedisce un workbook con una colonna Stato bloccata su Draft, Review e Final, una colonna Canale alimentata da un intervallo su un foglio nascosto, e una colonna quantità che accetta solo da 1 a 100. Il vostro job di import, la vostra form web o il vostro riempitore batch ora deve rispettare quelle regole, e le regole stanno nel workbook, non nel vostro codice. Cablare nel codice i valori ammessi accanto al template è il modo in cui le due cose divergono appena qualcuno modifica la lista in Excel

Perché le scritture da codice ignorano la convalida dati di Excel?

La convalida dati è metadati attaccati a un intervallo, e imporla è compito dell'editor in cui l'utente digita. In XLSX la regola è un elemento dataValidation (ECMA-376 Part 1, §18.3.1.32) che porta il tipo, l'operatore, formula1 e formula2, il prompt e l'avviso di errore. In un file XLS Classic la stessa informazione sta nel record DV descritto in [MS-XLS]. Nessuno dei due formati marca la cella stessa, quindi niente impedisce a un programma di memorizzare un valore che rompe la regola. Excel si comporta allo stesso modo: controlla le voci digitate, e un valore fuori intervallo arrivato con altri mezzi affiora solo quando qualcuno lancia Circle Invalid Data

HotXLS segue quel modello di proposito. Un assegnamento come Sheet.Cells[3, 2].Value := 250 memorizza 250 sotto una regola da 1 a 100 senza protestare, esattamente come fa la demo DataValidation in dotazione per dimostrarlo. Il controllo è opt-in, e il resto dell'articolo parla di fare opt-in nel posto giusto

Leggere le voci dropdown dietro una cella

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) restituisce True solo quando la cella è coperta da una regola di tipo lista, e riempie AItems con un TArray<WideString> di candidate. HotXLS risolve i quattro modi in cui una sorgente lista viene scritta in pratica, così chi chiama non fa mai il parsing di formula1 a mano:

• una lista inline quotata come "Draft,Review,Final", spezzata sulle virgole
• una lista inline le cui voci sono separate da caratteri #0, una forma che si trova nelle regole XLS Classic
• un intervallo sullo stesso foglio o su un altro, come =$G$2:$G$10 o =Lists!$A$2:$A$7, valutato ai suoi attuali valori di cella
• un nome definito come =ProductList che punta a uno di quegli intervalli

Anche le celle unite sono gestite. Chiedete una qualunque cella dentro una union e HotXLS cerca la regola all'ancora dell'unione, che è dove Excel tiene sia la regola sia il valore

La flag AShowDropDown merita una nota, perché i formati file la memorizzano al contrario. In XLSX, showDropDown="1" nasconde davvero la freccia in cella, e in BIFF8 il bit equivalente è una flag "suppress combo". HotXLS ribalta entrambi in lettura, così AShowDropDown = True significa quello che dice: Excel mostrerebbe la freccia. Una regola lista con la freccia nascosta restringe comunque l'input, ed è per questo che TryGetCellDropdownItems restituisce le voci comunque

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: riga 2, colonna 6, entrambi a base 1
    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;

Se vi servono solo le stringhe, GetCellDropdownItems(ARow, ACol) restituisce direttamente l'array e produce un array vuoto quando nessuna lista si applica. TryGetCellValidationPrompt restituisce titolo e testo del messaggio di input, comodo quando volete mostrare accanto a un campo di form web l'indizio dello stesso autore del template

Selezionare una voce per indice o per testo

SelectCellDropdownItem scrive una voce della lista nella cella e restituisce False quando la cella non ha regola lista o la voce richiesta non esiste. L'overload intero prende un indice a base 0 nell'array restituito da TryGetCellDropdownItems. L'overload testuale cerca prima una corrispondenza esatta e poi ripiega su una senza distinzione di maiuscole, così 'review' prende comunque Review

Entrambi gli overload scrivono attraverso WriteAutoValue, non attraverso un assegnamento di stringa grezza. Questo conta per le liste sostenute da intervalli di numeri: le voci tornano come stringhe tipo '3', ma il valore memorizzato è il numero 3, che è ciò che Excel memorizza quando un utente sceglie dalla freccia e ciò che ogni formula che riferisce la cella si aspetta. La demo DataValidation verifica esattamente questo dopo un salvataggio e riapertura in XLS, XLSX e XLSB

// Stesse chiamate su TXLSWorksheet (XLS) e 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  // 4a voce, base 0
  Writeln('F2: index 3 is out of range');

Come si valida l'input digitato prima di scriverlo?

ValidateCellInput(ARow, ACol, AText) prova una stringa candidate contro la regola che copre la cella e non modifica mai la cella. Restituisce True quando nessuna regola si applica o la candidate passa. La stringa passa per la stessa conversione testo-a-valore che usa WriteAutoValue, così il controllo e l'eventuale scrittura vedono lo stesso valore. Sul lato XLSX quella conversione è XlsxAutoTextValue: riconosce TRUE/FALSE, letterali di errore, date ISO, date m/d/yyyy e d.m.yyyy e numeri in formato invariant, e tiene 0123 come testo, come fa Excel. Il motore XLS Classic ha il suo convertitore che specchia il suo WriteAutoValue

Due compagne completano il set. ValidateCellValue(ARow, ACol) controlla il valore già memorizzato nella cella e tratta le celle vuote come valide. TryGetCellValidationError restituisce titolo, testo e stile dell'avviso di errore della regola, e restituisce False quando la regola non mostra proprio nessun avviso di errore. Il tipo stile cambia per motore, quindi accoppiatelo alla classe worksheet: TXLSDvErrorStyle da lxDataCheck per TXLSWorksheet, TXLSXDvErrorStyle per TXLSXWorksheet

Un comportamento che dovete riprodurre voi: Excel non valida una voce vuota né nulla che cominci con =. HotXLS applica quel salto nel percorso di modifica del viewer, non dentro ValidateCellInput, quindi un gate lato server dovrebbe fare lo stesso prima di chiamarlo:

uses
  lxDataCheck, lxHandle;

// Restituisce True quando AText può essere scritto, specchiando gli stili di avviso di Excel
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 salta la validazione per celle svuotate e formule
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Nessun avviso di errore configurato: Excel lascia passare tutto
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop rifiuta; Warning e Information lasciano la decisione a chi chiama
  Result := Style <> xlsDvErrStop;
end;

// Chiamante (Msg: WideString): valida, poi memorizza con la stessa conversione
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Accoppiare ValidateCellInput con WriteAutoValue è il punto dell'esempio. Validate con una conversione e memorizzate con un'altra, e prima o poi un valore passa il controllo come numero e atterra nella cella come testo

Voci di lista quotate e la trappola di escaping dell'XLS Classic

Le voci di lista inline che contengono doppi apici sono un modo facile di perdere una regola in silenzio. Una lista inline viene memorizzata come stringa formula, e una stringa formula fa l'escape di un " incorporato raddoppiandolo. Avvolgete le voci tra virgolette senza raddoppiare quelle interne, e una lista come He said "hi",No produce una formula che non compila. Non c'è alcuna eccezione: la regola semplicemente non viene creata, e il dropdown va via con lei. TXLSWorksheet.AddListValidation e la sua controparte XLSX raddoppiano entrambe le virgolette incorporate, quindi voci simili sopravvivono intatte

TXLSWorksheet.AddListValidation ha anche un overload array of WideString, che rispecchia quello su TXLSXWorksheet. È comodo, ma leggete cosa fa: unisce le voci con virgole e passa il risultato all'overload stringa. Una voce che contiene essa stessa una virgola diventa quindi due voci, e sul motore Classic una stringa sorgente contenente ! viene presa come riferimento qualificato dal foglio invece che come lista inline. Per didascalie di quel tipo, mettete i valori in un intervallo di celle e puntate la regola lì

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Confermate che la regola esista prima di fare affidamento su di essa
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Dopo un salvataggio e riapertura, TryGetCellDropdownItems su H2 restituisce He said "hi" con le sue virgolette intatte, in output XLS, XLSX, XLSB e ODS alla stessa stregua

Lasciare che TXLSWorkbookViewer imponga le regole

Quando gli utenti modificano celle sullo schermo, TXLSWorkbookViewer applica gli stessi controlli per voi. Con Editable a True e EnforceDataValidation lasciato al suo default True, una modifica digitata che rompe una regola con avviso di errore viene controllata prima di essere scritta: Stop la rifiuta, Warning chiede, Information informa. Il viewer disegna anche la freccia dropdown sulla cella attiva, apre la lista con un click o Alt+Down, e mostra il prompt di input giallo sotto la cella. I dettagli della griglia stessa sono trattati in articolo su costruire una griglia spreadsheet personalizzata con TXLSWorkbookViewer

Impostare CircleInvalidData a True disegna cerchi rossi attorno ai valori memorizzati che violano la loro regola, come il comando omonimo di Excel. Scansiona solo l'area visibile, esclude in fretta la maggior parte delle celle attraverso FindDataValidationAt, e non cerchia mai una cella vuota

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    // riapre l'editor con il testo rifiutato
  else
    Action := xvaAccept;
  Handled := True;        // sopprime il dialogo integrato
end;

Lasciate Handled a False e il viewer mostra il suo dialogo di avviso, che funziona sia su VCL sia su LCL. Quel default morde nei test automatizzati: un test che guida modifiche senza un gestore OnValidationError che imposti Handled si bloccherà su un dialogo modale. L'enum di stile del viewer è un terzo tipo, TXLSViewerValidationErrorStyle con xveStop, xveWarning e xveInformation, distinto dai due enum dei worksheet

Cosa conviene sapere prima di contare sulla validazione input HotXLS?

I controlli sono fedeli per le regole lista, numero, data, ora e lunghezza testo, e alcuni dettagli specifici per motore meritano di essere saputi:

• Sui worksheet XLS Classic, ValidateCellInput riporta le regole a formula personalizzata come valide per le candidate digitate, perché una candidate non può essere piazzata temporaneamente in una cella BIFF senza disturbare la catena di dipendenze. Controllate il valore memorizzato con ValidateCellValue, che valuta la formula personalizzata, o usate il motore XLSX, che valuta le regole personalizzate anche per le candidate
• Su XLS Classic, l'abbinamento lista tratta come stessa voce i valori numericamente uguali, quindi 5 e 5.0 abbinano entrambi una voce di 5
• Nel viewer, il contenuto incollato salta la validazione, come in Excel. EditSelectedCell non ha un editor da riaprire, quindi un risultato Retry lì si comporta come Cancel
• Gli assegnamenti Cells[...].Value semplici non vengono mai controllati, per progetto. La validazione avviene solo dove il vostro codice la chiede

Creare le regole in primo luogo, inclusi i tipi numero intero, data, lunghezza testo e personalizzato, è trattato nella guida HotXLS a convalida dati, AutoFilter e tabelle. I workbook XLSB si caricano in TXLSXWorkbook, quindi ogni chiamata TXLSXWorksheet mostrata qui funziona invariata su di essi, come descritto in articolo su leggere e scrivere workbook binari XLSB in Delphi

La risoluzione dropdown, la selezione delle voci e la validazione input viaggiano nell'HotXLS Delphi Excel component per Delphi, C++Builder e Lazarus, insieme alle demo DataValidation e WorkbookViewer che esercitano ogni chiamata di questo articolo