Listas desplegables y validación de entrada en HotXLS

HotXLS lee las listas desplegables de validación de datos de Excel directamente desde código Delphi. TryGetCellDropdownItems devuelve las cadenas candidatas de una celda, SelectCellDropdownItem escribe una de ellas por índice o por texto, y ValidateCellInput informa de si un valor tecleado pasaría la regla de la celda antes de almacenar nada. Los mismos métodos existen en TXLSWorksheet para XLS clásico y en TXLSXWorksheet para XLSX, y ambos toman números de fila y columna base-1

El detonante habitual de necesitar esto es una plantilla que no diseñaste tú. Alguien del negocio distribuye un libro con una columna Estado bloqueada a Draft, Review y Final, una columna Canal alimentada desde un rango en una hoja oculta, y una columna de cantidad que solo acepta de 1 a 100. Tu job de importación, tu formulario web o tu rellenador por lotes ahora tiene que respetar esas reglas, y las reglas viven en el libro, no en tu código. Cablear a fuego los valores permitidos junto a la plantilla es cómo ambas cosas se separan la primera vez que alguien edita la lista en Excel

¿Por qué las escrituras desde código ignoran la validación de datos de Excel?

La validación de datos son metadatos adjuntos a un rango, y hacerla cumplir es trabajo del editor en el que el usuario teclee. En XLSX la regla es un elemento dataValidation (ECMA-376 Parte 1, §18.3.1.32) que lleva el tipo, el operador, formula1 y formula2, el prompt y la alerta de error. En un archivo XLS clásico la misma información vive en el registro DV descrito en [MS-XLS]. Ninguno de los dos formatos marca la celda en sí, así que nada impide que un programa almacene un valor que rompe la regla. Excel se comporta igual: comprueba las entradas tecleadas, y un valor fuera de rango que llegó por otras vías solo asoma cuando alguien ejecuta Circle Invalid Data

HotXLS sigue ese modelo a propósito. Una asignación como Sheet.Cells[3, 2].Value := 250 almacena 250 bajo una regla de 1 a 100 sin queja, exactamente como hace la demo DataValidation incluida para demostrar el punto. Comprobar es opt-in, y el resto del artículo va de hacer opt-in en el lugar correcto

Leer los elementos desplegables detrás de una celda

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) devuelve True solo cuando la celda está cubierta por una regla de tipo lista, y rellena AItems con un TArray<WideString> de candidatos. HotXLS resuelve las cuatro formas en que una fuente de lista se escribe en la práctica, así que quien llama jamás parsea formula1 a mano:

• Una lista inline entrecomillada como "Draft,Review,Final", partida por comas
• Una lista inline cuyos elementos se separan con caracteres #0, una forma que se ve en reglas de XLS clásico
• Un rango en la misma hoja o en otra, como =$G$2:$G$10 o =Lists!$A$2:$A$7, evaluado a sus valores de celda actuales
• Un nombre definido como =ProductList que apunta a uno de esos rangos

Las celdas combinadas también se manejan. Pide cualquier celda dentro de una combinación y HotXLS busca la regla en el ancla de la combinación, que es donde Excel guarda tanto la regla como el valor

La bandera AShowDropDown merece una nota, porque los formatos de archivo la guardan al revés. En XLSX, showDropDown="1" de hecho oculta la flecha en la celda, y en BIFF8 el bit equivalente es una bandera de «suprimir combo». HotXLS invierte ambas al leer, así que AShowDropDown = True significa lo que dice: Excel mostraría la flecha. Una regla de lista con la flecha oculta sigue restringiendo la entrada, y por eso TryGetCellDropdownItems devuelve los elementos en ambos casos

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: fila 2, columna 6, ambas 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;

Si solo necesitas las cadenas, GetCellDropdownItems(ARow, ACol) devuelve el array directamente y produce un array vacío cuando no hay lista aplicable. TryGetCellValidationPrompt devuelve el título y el texto del mensaje de entrada, útil cuando quieres mostrar la propia pista del autor de la plantilla junto a un campo de formulario web

Seleccionar un elemento por índice o por texto

SelectCellDropdownItem escribe una entrada de lista en la celda y devuelve False cuando la celda no tiene regla de lista o la entrada pedida no existe. La sobrecarga entera toma un índice base-0 dentro del array que devolvió TryGetCellDropdownItems. La sobrecarga de texto busca primero una coincidencia exacta y luego recurre a una sin distinción de mayúsculas, así que 'review' sigue eligiendo Review

Ambas sobrecargas escriben vía WriteAutoValue, no vía una asignación de cadena cruda. Eso importa para listas respaldadas por rangos de números: los elementos vuelven como cadenas tipo '3', pero el valor almacenado es el número 3, que es lo que Excel guarda cuando un usuario elige desde la flecha y lo que espera cualquier fórmula que referencie la celda. La demo DataValidation comprueba exactamente esto tras un guardado y reapertura en XLS, XLSX y XLSB

// Las mismas llamadas en TXLSWorksheet (XLS) y 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.º elemento, base-0
  Writeln('F2: index 3 is out of range');

¿Cómo validas la entrada tecleada antes de escribirla?

ValidateCellInput(ARow, ACol, AText) prueba una cadena candidata contra la regla que cubre la celda y jamás modifica la celda. Devuelve True cuando no hay regla aplicable o la candidata pasa. La cadena pasa por la misma conversión de texto a valor que usa WriteAutoValue, así que el check y la escritura eventual ven el mismo valor. En el lado XLSX esa conversión es XlsxAutoTextValue: reconoce TRUE/FALSE, literales de error, fechas ISO, fechas m/d/yyyy y d.m.yyyy y números en formato invariable, y conserva 0123 como texto, igual que Excel. El motor XLS clásico tiene su propio conversor que refleja su propio WriteAutoValue

Dos acompañantes completan el set. ValidateCellValue(ARow, ACol) comprueba el valor ya almacenado en la celda y trata las celdas vacías como válidas. TryGetCellValidationError devuelve el título, el texto y el estilo de la alerta de error de la regla, y devuelve False cuando la regla no muestra alerta de error alguna. El tipo de estilo difiere por motor, así que igualalo a la clase de hoja: TXLSDvErrorStyle de lxDataCheck para TXLSWorksheet, TXLSXDvErrorStyle para TXLSXWorksheet

Un comportamiento que tienes que reproducir tú: Excel no valida una entrada vacía ni nada que empiece por =. HotXLS aplica ese salto en la ruta de edición del viewer, no dentro de ValidateCellInput, así que una puerta en el servidor debería hacer lo mismo antes de llamarla:

uses
  lxDataCheck, lxHandle;

// Devuelve True cuando AText puede escribirse, reflejando los estilos de alerta de 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 se salta la validación para celdas vacías y fórmulas
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Sin alerta de error configurada: Excel deja pasar cualquier cosa
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop rechaza; Warning e Information dejan la decisión a quien llama
  Result := Style <> xlsDvErrStop;
end;

// Quien llama (Msg: WideString): valida, y luego almacena con la misma conversión
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Emparejar ValidateCellInput con WriteAutoValue es la gracia del ejemplo. Valida con una conversión y almacena con otra, y tarde o temprano un valor pasa el check como número y aterriza en la celda como texto

Elementos de lista entrecomillados y la trampa de escape del XLS clásico

Los elementos de lista inline que contienen comillas dobles son una forma fácil de perder una regla en silencio. Una lista inline se guarda como cadena de fórmula, y una cadena de fórmula escapa una " incrustada duplicándola. Envuelve los elementos en comillas sin duplicar las interiores, y una lista como He said "hi",No produce una fórmula que no compila. No hay excepción: la regla sencillamente no se crea, y el desplegable se va con ella. TXLSWorksheet.AddListValidation y su homólogo XLSX duplican ambos las comillas incrustadas, así que esos elementos sobreviven intactos

TXLSWorksheet.AddListValidation también tiene una sobrecarga con array of WideString, homóloga a la de TXLSXWorksheet. Es cómoda, pero lee lo que hace: une los elementos con comas y entrega el resultado a la sobrecarga de cadena. Un elemento que a su vez contiene una coma se convierte por tanto en dos elementos, y en el motor clásico una cadena fuente que contiene ! se toma como referencia calificada por hoja en vez de una lista inline. Para leyendas así, pon los valores en un rango de celdas y apunta la regla a él

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

Tras un guardado y reapertura, TryGetCellDropdownItems sobre H2 devuelve He said "hi" con sus comillas intactas, en salida XLS, XLSX, XLSB y ODS por igual

Dejar que TXLSWorkbookViewer haga cumplir las reglas

Cuando los usuarios editan celdas en pantalla, TXLSWorkbookViewer aplica los mismos checks por ti. Con Editable a True y EnforceDataValidation en su valor por defecto True, una edición tecleada que rompe una regla con alerta de error se comprueba antes de escribirse: Stop la rechaza, Warning pregunta, Information informa. El viewer además dibuja la flecha desplegable en la celda activa, abre la lista con un clic o Alt+Down, y muestra el prompt de entrada amarillo bajo la celda. Los detalles de la propia rejilla quedan cubiertos en el artículo sobre construir una rejilla de hoja de cálculo personalizada con TXLSWorkbookViewer

Poner CircleInvalidData a True dibuja círculos rojos alrededor de los valores almacenados que violan su regla, como el comando de Excel del mismo nombre. Solo escanea el área visible, descarta la mayoría de celdas rápido vía FindDataValidationAt, y jamás circunda una celda vacía

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 el editor con el texto rechazado
  else
    Action := xvaAccept;
  Handled := True;        // suprime el diálogo incorporado
end;

Deja Handled en False y el viewer muestra su propio diálogo de alerta, que funciona tanto en VCL como en LCL. Ese valor por defecto muerde en tests automatizados: un test que conduce ediciones sin un manejador OnValidationError que ponga Handled se bloqueará en un diálogo modal. El enum de estilo del viewer es un tercer tipo, TXLSViewerValidationErrorStyle con xveStop, xveWarning y xveInformation, distinto de los dos enums de hoja

Qué conviene saber antes de fiarte de la validación de entrada de HotXLS

Los checks son fieles para reglas de lista, número, fecha, hora y longitud de texto, y unos cuantos detalles específicos de motor conviene conocer:

• En hojas XLS clásicas, ValidateCellInput reporta las reglas de fórmula personalizada como válidas para candidatas tecleadas, porque una candidata no puede colocarse temporalmente en una celda BIFF sin disturbar la cadena de dependencias. Comprueba el valor almacenado con ValidateCellValue, que evalúa la fórmula personalizada, o usa el motor XLSX, que evalúa reglas personalizadas para candidatas también
• En XLS clásico, la coincidencia de listas trata los valores numéricamente iguales como el mismo elemento, así que 5 y 5.0 coinciden ambos con un elemento de 5
• En el viewer, el contenido pegado se salta la validación, como en Excel. EditSelectedCell no tiene editor que reabrir, así que un resultado Retry allí se comporta como Cancel
• Las asignaciones planas Cells[...].Value jamás se comprueban, a propósito. La validación ocurre solo donde tu código la pide

Crear las reglas en primer lugar, incluidos los tipos de número entero, fecha, longitud de texto y personalizado, queda cubierto en la guía de HotXLS de validación de datos, AutoFilter y tablas. Los libros XLSB cargan en TXLSXWorkbook, así que cada llamada TXLSXWorksheet mostrada aquí funciona sin cambios sobre ellos, como se describe en el artículo sobre leer y escribir libros binarios XLSB en Delphi

La resolución de desplegables, la selección de elementos y la validación de entrada se distribuyen en el componente Excel HotXLS para Delphi para Delphi, C++Builder y Lazarus, junto con las demos DataValidation y WorkbookViewer que ejercitan cada llamada de este artículo