Listas dropdown HotXLS e validação de entrada em Delphi

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

O gatilho habitual para precisar disto é um template que não desenhou. Alguém do lado do negócio distribui uma pasta de trabalho com uma coluna Status trancada em Draft, Review e Final, uma coluna Channel alimentada por um intervalo numa folha oculta, e uma coluna de quantidade que só aceita 1 a 100. O seu trabalho de importação, o seu formulário web ou o seu preenchidor em batch agora têm de respeitar essas regras, e as regras vivem na pasta de trabalho, não no seu código. Codificar os valores permitidos ao lado do template é como os dois divergem na primeira vez que alguém edita a lista no Excel

Porque é que as escritas por código ignoram a validação de dados do Excel?

A validação de dados são metadados anexados a um intervalo, e impô-los é trabalho do editor em que o utilizador digita. Em XLSX a regra é um elemento dataValidation (ECMA-376 Parte 1, §18.3.1.32) trazendo o tipo, o operador, formula1 e formula2, o prompt e o alerta de erro. Num ficheiro Classic XLS a mesma informação assenta no registo DV descrito em [MS-XLS]. Nenhum formato marca a própria célula, por isso nada impede um programa de guardar um valor que quebre a regra. O Excel comporta-se do mesmo modo: verifica entradas digitadas, e um valor fora do intervalo que chegou por outros meios só aparece quando alguém corre o Circle Invalid Data

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

Ler os itens dropdown por trás de uma célula

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

• Uma lista inline entre aspas como "Draft,Review,Final", dividida nas vírgulas
• Uma lista inline cujos itens são separados por caracteres #0, forma encontrada em regras Classic XLS
• Um intervalo na mesma folha ou noutra folha, como =$G$2:$G$10 ou =Lists!$A$2:$A$7, avaliado aos valores atuais das células
• Um nome definido como =ProductList que aponta para um desses intervalos

Células fundidas também são tratadas. Pergunte por qualquer célula dentro de uma fusão e o HotXLS procura a regra na âncora da fusão, que é onde o Excel guarda tanto a regra como o valor

A flag AShowDropDown merece uma nota, porque os formatos de ficheiro guardam-na ao contrário. Em XLSX, showDropDown="1" na realidade esconde a seta na célula, e em BIFF8 o bit equivalente é uma flag "suppress combo". O HotXLS inverte ambos na leitura, por isso AShowDropDown = True significa o que diz: o Excel mostraria a seta. Uma regra de lista com a seta escondida continua a restringir 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, ambos 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 só precisa das strings, o GetCellDropdownItems(ARow, ACol) devolve o array diretamente e produz um array vazio quando não há lista. O TryGetCellValidationPrompt devolve o título e o texto da mensagem de entrada, que dá jeito quando quer mostrar a dica do próprio autor do template ao lado de um campo de formulário web

Escolher um item por índice ou por texto

O SelectCellDropdownItem escreve 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 base 0 no array que o TryGetCellDropdownItems devolveu. O overload de texto procura primeiro um match exato e depois recai num match sem distinguir maiúsculas, por isso 'review' ainda escolhe Review

Ambos os overloads escrevem através do WriteAutoValue, não através de uma atribuição de string crua. Isso importa para listas respaldadas em intervalos de números: os itens voltam como strings como '3', mas o valor guardado é o número 3, que é o que o Excel guarda quando um utilizador escolhe pela seta e o que qualquer fórmula que referencie a célula espera. A demo DataValidation verifica precisamente isto depois de salvar e reabrir em XLS, XLSX e XLSB

// As mesmas chamadas em 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 entrada digitada antes de a escrever?

O ValidateCellInput(ARow, ACol, AText) testa uma string candidata contra a regra que cobre a célula e nunca modifica a célula. Devolve True quando não há regra aplicável ou a candidata passa. A string passa pela mesma conversão de texto para valor que o WriteAutoValue usa, por isso a verificação e a escrita final veem o mesmo valor. No lado XLSX essa conversão é o XlsxAutoTextValue: 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 à maneira do Excel. O motor Classic XLS tem o seu próprio conversor que espelha o seu próprio WriteAutoValue

Dois companheiros completam o conjunto. O ValidateCellValue(ARow, ACol) verifica o valor já guardado na célula e trata células em branco 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 de estilo difere por motor, por isso o ajuste é à classe da folha de cálculo: TXLSDvErrorStyle de lxDataCheck para o TXLSWorksheet, TXLSXDvErrorStyle para o TXLSXWorksheet

Um comportamento que tem de reproduzir você mesmo: o Excel não valida entrada vazia nem nada que comece por =. O HotXLS aplica esse salto no caminho de edição do viewer, não dentro do ValidateCellInput, por isso um portão do lado do servidor deve fazer o mesmo antes de o chamar:

uses
  lxDataCheck, lxHandle;

// Devolve True quando AText pode ser escrito, 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 salta 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 passar tudo
  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): validar, depois guardar com a mesma conversão
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Emparelhar o ValidateCellInput com o WriteAutoValue é o ponto do exemplo. Valide com uma conversão e guarde com outra, e mais cedo ou mais tarde um valor passa a verificação como número e aterra 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 é guardada como string de fórmula, e uma string de fórmula faz escaping de uma " embutida duplicando-a. 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 o dropdown vai com ela. O TXLSWorksheet.AddListValidation e o seu homólogo XLSX duplicam ambos as aspas embutidas, por isso tais itens sobrevivem intactos

O TXLSWorksheet.AddListValidation também tem um overload array of WideString, ao par com o do TXLSXWorksheet. É conveniente, mas leia o que faz: junta os itens com vírgulas e entrega o resultado ao overload de string. Um item que ele próprio contenha uma vírgula transforma-se portanto em dois itens, e no motor Classic uma string fonte contendo ! é tomada como referência qualificada por folha em vez de lista inline. Para legendas dessas, ponha os valores num intervalo 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 salvar e reabrir, o TryGetCellDropdownItems em H2 devolve He said "hi" com as suas aspas intactas, em saída XLS, XLSX, XLSB e ODS por igual

Deixar o TXLSWorkbookViewer impor as regras

Quando os utilizadores editam células no ecrã, o TXLSWorkbookViewer aplica as mesmas verificações por si. Com Editable em True e EnforceDataValidation deixado no seu default True, uma edição digitada que quebre uma regra com alerta de erro é verificada antes de ser escrita: Stop rejeita, Warning pergunta, Information informa. O viewer também desenha a seta dropdown na célula ativa, abre a lista com um clique ou Alt+Down, e mostra o prompt amarelo de entrada por baixo da célula. Os detalhes da própria grelha estão cobertos em o artigo sobre construir uma grelha de folha de cálculo personalizada com o TXLSWorkbookViewer

Pôr CircleInvalidData em True desenha círculos vermelhos em volta de valores guardados que violam a sua regra, como o comando do Excel com o mesmo nome. Só varre a área visível, elimina a maioria das células rapidamente através do 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    // reabrir o editor com o texto rejeitado
  else
    Action := xvaAccept;
  Handled := True;        // suprimir o diálogo incorporado
end;

Deixe o Handled em False e o viewer mostra o seu próprio diálogo de alerta, que funciona tanto sob VCL como LCL. Esse default morde em testes automatizados: um teste que conduza edições sem um gestor OnValidationError a definir Handled vai bloquear num diálogo modal. O enum de estilos do viewer é um terceiro tipo, TXLSViewerValidationErrorStyle com xveStop, xveWarning e xveInformation, distinto dos dois enums das folhas de cálculo

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

As verificações são fiéis para regras de lista, número, data, hora e comprimento de texto, e alguns detalhes específicos de motor valem a pena conhecer:

• Nas folhas Classic XLS, o ValidateCellInput reporta regras de fórmula personalizada como válidas para candidatas digitadas, porque uma candidata não pode ser colocada temporariamente numa célula BIFF sem perturbar a cadeia de dependências. Verifique o valor guardado com o ValidateCellValue, que avalia a fórmula personalizada, ou use o motor XLSX, que avalia regras personalizadas também para candidatas
• No Classic XLS, o matching de listas trata valores numericamente iguais como o mesmo item, por isso 5 e 5.0 ambos casam com um item de 5
• No viewer, conteúdo colado salta a validação, como no Excel. O EditSelectedCell não tem editor para reabrir, por isso um resultado Retry aí comporta-se como Cancel
• Atribuições Cells[...].Value simples nunca são verificadas, por desenho. A validação acontece só onde o seu código a pede

Criar as regras desde o início, incluindo tipos de número inteiro, data, comprimento de texto e personalizado, está coberto em o guia HotXLS de validação de dados, AutoFilter e tabelas. Pastas de trabalho XLSB carregam no TXLSXWorkbook, por isso cada chamada TXLSXWorksheet mostrada aqui funciona nelas sem alterações, como descrito em o artigo sobre ler e escrever pastas de trabalho binárias XLSB em Delphi

A resolução dropdown, a escolha de itens e a validação de entrada viajam no HotXLS Delphi Excel component para Delphi, C++Builder e Lazarus, juntamente com as demos DataValidation e WorkbookViewer que exercem todas as chamadas deste artigo