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 reporta si un valor tecleado pasaría la regla de la celda antes de guardar algo. Los mismos métodos existen en TXLSWorksheet para Classic XLS y en TXLSXWorksheet para XLSX, y ambos toman números de fila y columna base 1

El detonante habitual para necesitar esto es una plantilla que usted no diseñó. Alguien del lado del negocio distribuye un workbook con una columna Status bloqueada a Draft, Review y Final, una columna Channel alimentada desde un rango en una hoja oculta, y una columna de cantidad que solo acepta de 1 a 100. Su trabajo de importación, su formulario web o su llenador por lotes ahora tiene que respetar esas reglas, y las reglas viven en el workbook, no en su código. Poner en código duro los valores permitidos junto a la plantilla es como se separan ambos la primera vez que alguien edita la lista en Excel

¿Por qué las escrituras por 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 que el usuario teclea. 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 mensaje de entrada y la alerta de error. En un archivo Classic XLS la misma información vive en el registro DV descrito en [MS-XLS]. Ninguno de los dos formatos marca la celda misma, así que nada impide que un programa guarde un valor que rompe la regla. Excel se comporta igual: verifica las entradas tecleadas, y un valor fuera de rango que llegó por otros medios solo aparece cuando alguien ejecuta Circle Invalid Data

HotXLS sigue ese modelo a propósito. Una asignación como Sheet.Cells[3, 2].Value := 250 guarda 250 bajo una regla de 1 a 100 sin quejarse, exactamente como lo hace la demo DataValidation incluida para demostrar el punto. La verificación es opt-in, y el resto de este artículo trata 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 llena 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 nunca parsea formula1 a mano:

• Una lista inline entre comillas como "Draft,Review,Final", partida por comas
• Una lista inline cuyos elementos están separados por caracteres #0, una forma que se encuentra en reglas Classic XLS
• Un rango en la misma hoja o en otra hoja, 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. Pida cualquier celda dentro de una combinación y HotXLS busca la regla en el ancla de la combinación, que es donde Excel conserva 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" realmente oculta la flecha en la celda, y en BIFF8 el bit equivalente es una bandera de "suprimir combo". HotXLS voltea 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 de cualquier 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: 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 necesita las cadenas, GetCellDropdownItems(ARow, ACol) devuelve el arreglo directamente y produce un arreglo vacío cuando no aplica ninguna lista. TryGetCellValidationPrompt devuelve el título y el texto del mensaje de entrada, que es útil cuando quiere 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 solicitada no existe. La sobrecarga entera toma un índice base 0 dentro del arreglo que devolvió TryGetCellDropdownItems. La sobrecarga de texto busca primero una coincidencia exacta y luego recurre a una sin distinguir mayúsculas, así que 'review' igual elige Review

Ambas sobrecargas escriben por WriteAutoValue, no por una asignación de cadena cruda. Eso importa para listas respaldadas por rangos de números: los elementos vuelven como cadenas como '3', pero el valor guardado es el número 3, que es lo que Excel guarda cuando un usuario elige por la flecha y lo que cualquier fórmula que referencie la celda espera. La demo DataValidation verifica 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  // cuarto elemento, base 0
  Writeln('F2: index 3 is out of range');

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

ValidateCellInput(ARow, ACol, AText) prueba una cadena candidata contra la regla que cubre la celda y nunca modifica la celda. Devuelve True cuando no aplica ninguna regla o la candidata pasa. La cadena pasa por la misma conversión de texto a valor que usa WriteAutoValue, así que la verificación y la escritura eventual ven el mismo valor. Del 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 de formato invariante, y conserva 0123 como texto como lo hace Excel. El motor Classic XLS tiene su propio conversor que refleja su propio WriteAutoValue

Dos compañeros completan el conjunto. ValidateCellValue(ARow, ACol) verifica el valor ya guardado 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 del estilo difiere por motor, así que empárelo con la clase de worksheet: TXLSDvErrorStyle de lxDataCheck para TXLSWorksheet, TXLSXDvErrorStyle para TXLSXWorksheet

Un comportamiento que tiene que reproducir usted mismo: Excel no valida una entrada vacía ni nada que empiece con =. HotXLS aplica ese salto en la ruta de edición del visor, no dentro de ValidateCellInput, así que una compuerta del lado 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 salta la validación para celdas limpiadas 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): valide, luego guarde con la misma conversión
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Emparejar ValidateCellInput con WriteAutoValue es el punto del ejemplo. Valide con una conversión y guarde con otra, y tarde o temprano un valor pasa la verificación como número y aterriza en la celda como texto

Elementos de lista entre comillas y la trampa de escape de Classic XLS

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 una cadena de fórmula, y una cadena de fórmula escapa una " incrustada duplicándola. Envuelva los elementos en comillas sin duplicar las internas, y una lista como He said "hi",No produce una fórmula que no compila. No hay excepción: la regla simplemente no se crea, y el desplegable se va con ella. TXLSWorksheet.AddListValidation y su contraparte XLSX duplican ambos las comillas incrustadas, así que tales elementos sobreviven intactos

TXLSWorksheet.AddListValidation también tiene una sobrecarga array of WideString, que corresponde a la de TXLSXWorksheet. Es conveniente, pero lea lo que hace: une los elementos con comas y entrega el resultado a la sobrecarga de cadena. Un elemento que contiene una coma se convierte por tanto en dos elementos, y en el motor Classic una cadena fuente que contiene ! se toma como referencia calificada por hoja en vez de una lista inline. Para leyendas como esas, ponga los valores en un rango de celdas y apunte la regla hacia él

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Confirme que la regla existe antes de depender 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 las mismas verificaciones por usted. Con Editable en True y EnforceDataValidation en su valor por defecto True, una edición tecleada que rompe una regla con alerta de error se verifica antes de escribirse: Stop la rechaza, Warning pregunta, Information informa. El visor además dibuja la flecha desplegable en la celda activa, abre la lista con un clic o Alt+Down, y muestra el mensaje amarillo de entrada debajo de la celda. Los detalles de la cuadrícula misma están cubiertos en el artículo sobre construir una cuadrícula de hojas de cálculo personalizada con TXLSWorkbookViewer

Poner CircleInvalidData en True dibuja círculos rojos alrededor de los valores guardados que violan su regla, como el comando de Excel del mismo nombre. Solo escanea el área visible, descarta la mayoría de las celdas rápido mediante FindDataValidationAt, y nunca 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 integrado
end;

Deje Handled en False y el visor muestra su propio diálogo de alerta, que funciona tanto en VCL como en LCL. Ese valor por defecto muerde en pruebas automatizadas: una prueba que maneja ediciones sin un manejador OnValidationError que ponga Handled se bloqueará en un diálogo modal. El enum de estilo del visor es un tercer tipo, TXLSViewerValidationErrorStyle con xveStop, xveWarning y xveInformation, distinto de los dos enums de worksheet

¿Qué debería saber antes de confiar en la validación de entrada de HotXLS?

Las verificaciones son fieles para reglas de lista, número, fecha, hora y longitud de texto, y unos cuantos detalles por motor valen la pena conocer:

• En worksheets Classic XLS, 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 perturbar la cadena de dependencias. Verifique el valor guardado con ValidateCellValue, que evalúa la fórmula personalizada, o use el motor XLSX, que evalúa reglas personalizadas para candidatas también
• En Classic XLS, la coincidencia de listas trata los valores numéricamente iguales como el mismo elemento, así que 5 y 5.0 ambos coinciden con un elemento de 5
• En el visor, 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 Cells[...].Value simples nunca se verifican, por diseño. La validación ocurre solo donde su código la pide

Crear las reglas en primer lugar, incluidos los tipos de número entero, fecha, longitud de texto y personalizado, está cubierto en la guía de HotXLS sobre validación de datos, AutoFilter y tablas. Los workbooks 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 workbooks 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