Listas suspensas do HotXLS e validação de entrada no Delphi

O HotXLS lê listas suspensas de validação de dados do Excel direto do código Delphi. O TryGetCellDropdownItems devolve as strings candidatas de uma célula, o SelectCellDropdownItem grava uma delas por índice ou por texto, e o ValidateCellInput informa se um valor digitado passaria pela regra da célula antes de qualquer coisa ser armazenada. Os mesmos métodos existem no TXLSWorksheet para Classic XLS e no TXLSXWorksheet para XLSX, e ambos recebem números de linha e coluna com base 1

O gatilho usual para precisar disso é um template que você não desenhou. Alguém da área de negócio distribui uma pasta de trabalho com uma coluna Status travada em Draft, Review e Final, uma coluna Channel alimentada por um range numa planilha oculta, e uma coluna de quantidade que só aceita 1 a 100. O seu job de importação, o seu formulário web ou o seu preenchedor em lote agora precisa respeitar essas regras, e as regras vivem na pasta de trabalho, não no seu código. Hard-code dos valores permitidos ao lado do template é como os dois se dessincronizam na primeira vez que alguém edita a lista no Excel

Por que gravações por código ignoram a validação de dados do Excel?

Validação de dados são metadados anexados a um range, e impô-los é trabalho do editor em que o usuário digita. No XLSX a regra é um elemento dataValidation (ECMA-376 Part 1, §18.3.1.32) carregando o tipo, o operador, formula1 e formula2, o prompt e o alerta de erro. Numa file Classic XLS a mesma informação fica no record DV descrito no [MS-XLS]. Nenhum formato marca a célula em si, então nada impede um programa de armazenar um valor que quebra a regra. O Excel se comporta igual: ele confere entradas digitadas, e um valor fora do range que chegou por outros meios só aparece quando alguém roda Circle Invalid Data

O HotXLS segue esse modelo de propósito. Uma atribuição como Sheet.Cells[3, 2].Value := 250 armazena 250 sob uma regra de 1 a 100 sem reclamar, exatamente como a demo DataValidation que vem junto faz para provar o ponto. A checagem é opt-in, e o resto deste artigo é sobre fazer opt-in no lugar certo

Lendo os itens suspensos por trás de uma célula

O TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) devolve True só quando a célula é coberta por uma regra de tipo lista, e preenche AItems com um TArray<WideString> de candidatas. O HotXLS resolve as quatro formas como uma fonte de lista é escrita na prática, então o chamador nunca parseia o formula1 à mão:

• Uma lista inline entre aspas como "Draft,Review,Final", separada por vírgulas
• Uma lista inline cujos itens são separados por caracteres #0, forma encontrada em regras do Classic XLS
• Um range na mesma planilha ou noutra, como =$G$2:$G$10 ou =Lists!$A$2:$A$7, avaliado para os valores atuais das células
• Um defined name como =ProductList que aponta para um desses ranges

Células mescladas também são tratadas. Peça qualquer célula dentro de uma mesclagem e o HotXLS procura a regra na âncora da mesclagem, que é onde o Excel guarda tanto a regra quanto o valor

A flag AShowDropDown merece uma nota, porque os formatos de arquivo a armazenam ao contrário. No XLSX, showDropDown="1" na verdade esconde a seta na célula, e no BIFF8 o bit equivalente é uma flag de "suprimir combo". O HotXLS inverte ambos na leitura, então AShowDropDown = True significa o que diz: o Excel mostraria a seta. Uma regra de lista com a seta escondida ainda restringe a entrada, e é por isso que o TryGetCellDropdownItems devolve os itens de qualquer forma

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: linha 2, coluna 6, ambas com 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 você só precisa das strings, o GetCellDropdownItems(ARow, ACol) devolve o array direto e rende um array vazio quando nenhuma lista se aplica. O TryGetCellValidationPrompt devolve o título e o texto da mensagem de entrada, útil quando você quer mostrar a própria dica do autor do template ao lado de um campo de formulário web

Selecionando um item por índice ou por texto

O SelectCellDropdownItem grava uma entrada da lista na célula e devolve False quando a célula não tem regra de lista ou a entrada pedida não existe. O overload inteiro recebe um índice com base 0 no array que o TryGetCellDropdownItems devolveu. O overload de texto procura um match exato primeiro e depois recua para um match case-insensitive, então 'review' ainda escolhe Review

Ambos os overloads gravam via WriteAutoValue, não via atribuição de string bruta. Isso importa para listas alimentadas por ranges de números: os itens voltam como strings como '3', mas o valor armazenado é o número 3, que é o que o Excel armazena quando um usuário escolhe pela seta e o que toda fórmula que referencia a célula espera. A demo DataValidation confere exatamente isso depois de um save e reopen em XLS, XLSX e XLSB

// Mesmas chamadas no 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  // 4º item, base 0
  Writeln('F2: index 3 is out of range');

Como validar a entrada digitada antes de gravá-la?

O ValidateCellInput(ARow, ACol, AText) testa uma string candidata contra a regra que cobre a célula e nunca modifica a célula. Ele devolve True quando nenhuma regra se aplica ou a candidata passa. A string passa pela mesma conversão de texto para valor que o WriteAutoValue usa, então a checagem e a gravação final veem o mesmo valor. No lado XLSX essa conversão é o XlsxAutoTextValue: ele reconhece TRUE/FALSE, literais de erro, datas ISO, datas m/d/yyyy e d.m.yyyy e números em formato invariante, e mantém 0123 como texto do jeito que o Excel faz. O engine Classic XLS tem o próprio conversor que espelha o próprio WriteAutoValue dele

Dois companheiros completam o conjunto. O ValidateCellValue(ARow, ACol) confere o valor já armazenado na célula e trata células vazias como válidas. O TryGetCellValidationError devolve o título, o texto e o estilo do alerta de erro da regra, e devolve False quando a regra não mostra alerta de erro nenhum. O tipo do estilo difere por engine, então faça o match com a classe da planilha: TXLSDvErrorStyle do lxDataCheck para o TXLSWorksheet, TXLSXDvErrorStyle para o TXLSXWorksheet

Um comportamento que você precisa reproduzir por conta própria: o Excel não valida uma entrada vazia nem nada que comece com =. O HotXLS aplica esse pulo no caminho de edição do viewer, não dentro do ValidateCellInput, então um gate do lado servidor deve fazer o mesmo antes de chamá-lo:

uses
  lxDataCheck, lxHandle;

// Devolve True quando AText pode ser gravado, espelhando os estilos de alerta do 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;
  // O Excel pula a validação para células limpas e fórmulas
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Sem alerta de erro configurado: o Excel deixa qualquer coisa passar
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop rejeita; Warning e Information deixam a decisão ao chamador
  Result := Style <> xlsDvErrStop;
end;

// Chamador (Msg: WideString): valide, depois armazene com a mesma conversão
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Parear ValidateCellInput com WriteAutoValue é o ponto do exemplo. Valide com uma conversão e armazene com outra, e mais cedo ou mais tarde um valor passa na checagem como número e aterrissa na célula como texto

Itens de lista entre aspas e a armadilha de escaping do Classic XLS

Itens de lista inline que contêm aspas duplas são uma forma fácil de perder uma regra em silêncio. Uma lista inline é armazenada como uma string de fórmula, e uma string de fórmula escapa um " embutido duplicando-o. Envolva os itens em aspas sem duplicar as internas, e uma lista como He said "hi",No produz uma fórmula que não compila. Não há exceção: a regra simplesmente não é criada, e a lista suspensa vai embora com ela. O TXLSWorksheet.AddListValidation e o seu equivalente XLSX duplicam as aspas embutidas, então tais itens sobrevivem intactos

O TXLSWorksheet.AddListValidation também tem um overload de array of WideString, que acompanha o do TXLSXWorksheet. É conveniente, mas leia o que ele faz: ele junta os itens com vírgulas e entrega o resultado ao overload de string. Um item que contém uma vírgula vira, portanto, dois itens, e no engine Classic uma string de origem contendo ! é tomada como referência qualificada por planilha em vez de lista inline. Para legendas assim, ponha os valores num range de células e aponte a regra para ele

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Confirme que a regra existe antes de confiar nela
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Depois de um save e reopen, o TryGetCellDropdownItems em H2 devolve He said "hi" com as aspas intactas, em saída XLS, XLSX, XLSB e ODS igualmente

Deixando o TXLSWorkbookViewer impor as regras

Quando usuários editam células na tela, o TXLSWorkbookViewer aplica as mesmas checagens por você. Com Editable definido como True e EnforceDataValidation no default dele de True, uma edição digitada que quebra uma regra com alerta de erro é conferida antes de ser gravada: Stop rejeita, Warning pergunta, Information informa. O viewer também desenha a seta da lista suspensa na célula ativa, abre a lista com um clique ou Alt+Down, e mostra o prompt amarelo de entrada abaixo da célula. Os detalhes da grade em si estão cobertos em o artigo sobre construir uma grade de planilha customizada com o TXLSWorkbookViewer

Definir CircleInvalidData como True desenha círculos vermelhos em torno de valores armazenados que violam a própria regra, como o comando de mesmo nome do Excel. Ele varre só a área visível, descarta a maioria das células rapidamente via FindDataValidationAt, e nunca circula uma célula vazia

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    // reabre o editor com o texto rejeitado
  else
    Action := xvaAccept;
  Handled := True;        // suprime o diálogo embutido
end;

Deixe Handled em False e o viewer mostra o próprio diálogo de alerta dele, que funciona tanto sob VCL quanto LCL. Esse default morde em testes automatizados: um teste que conduz edições sem um handler de OnValidationError definindo Handled vai travar num diálogo modal. O enum de estilo do viewer é um terceiro tipo, TXLSViewerValidationErrorStyle com xveStop, xveWarning e xveInformation, distinto dos dois enums de planilha

O que você deve saber antes de confiar na validação de entrada do HotXLS?

As checagens são fiéis para regras de lista, número, data, hora e comprimento de texto, e alguns detalhes específicos por engine valem saber:

• Em planilhas Classic XLS, o ValidateCellInput reporta regras de fórmula customizada como válidas para candidatas digitadas, porque uma candidata não pode ser posta numa célula BIFF temporariamente sem perturbar a cadeia de dependências. Confira o valor armazenado com o ValidateCellValue, que avalia a fórmula customizada, ou use o engine XLSX, que avalia regras customizadas para candidatas também
• No Classic XLS, o match de lista trata valores numericamente iguais como o mesmo item, então 5 e 5.0 ambos batem com um item de 5
• No viewer, conteúdo colado pula a validação, como no Excel. O EditSelectedCell não tem editor para reabrir, então um resultado Retry ali se comporta como Cancel
• Atribuições Cells[...].Value simples nunca são conferidas, por design. A validação acontece só onde o seu código a pede

Criar as regras em primeiro lugar, incluindo tipos de número inteiro, data, comprimento de texto e customizado, está coberto em o guia do HotXLS de validação de dados, AutoFilter e tabelas. Pastas de trabalho XLSB carregam no TXLSXWorkbook, então toda chamada TXLSXWorksheet mostrada aqui funciona nelas sem mudanças, como descrito em o artigo sobre ler e escrever pastas de trabalho binárias XLSB no Delphi

Resolução de listas suspensas, seleção de itens e validação de entrada vêm no HotXLS Delphi Excel component para Delphi, C++Builder e Lazarus, junto com as demos DataValidation e WorkbookViewer que exercitam toda chamada deste artigo