Validación de datos, AutoFilter y tablas de HotXLS en Delphi

Tres funciones de HotXLS comparten una hoja de cálculo pero operan sobre objetos completamente distintos, y los problemas empiezan cuando usted asume que hacen cosas parecidas. La validación de datos adjunta a un rango una regla que restringe lo que un usuario puede escribir en él. Un AutoFilter adjunta a una región una definición de criterios almacenada y cambia qué filas muestra un visor. Una tabla envuelve un rango en una estructura con nombre y tipos, con estilo de bandas. Una restringe la entrada, otra registra una vista, otra impone un esquema. Ninguna de ellas mueve un solo valor de celda por sí sola, y el AutoFilter en particular engaña a la gente, porque la palabra sugiere una acción cuando solo almacena una definición. Saber qué objeto toca cada llamada, y cuándo se materializa realmente el efecto, es lo que separa un libro de trabajo que se comporta igual en Excel que en sus pruebas de uno que diverge silenciosamente

AutoFilter almacena una definición, no recorta filas

Un AutoFilter en un archivo guardado es un registro de criterios. La ocultación de filas ocurre después, cuando Excel abre el libro de trabajo y evalúa los criterios contra los datos. HotXLS escribe ese registro fielmente y no recorta nada: cada fila que usted filtró sigue físicamente presente en el archivo. Un pipeline que aplica un filtro para descartar pedidos rechazados y luego vuelve a leer el libro de trabajo los verá todos, rechazados incluidos, y el código es correcto según la API pero incorrecto según el modelo mental del autor. En la hoja de cálculo XLSX, SetAutoFilter declara la región filtrada y AddAutoFilterColumn adjunta criterios a una columna de ella. Cuando el código del lado del servidor necesita el resultado real, para un conteo de filas en un resumen o para reenviar solo las filas coincidentes, la biblioteca evalúa los criterios por usted en lugar de fingir que el archivo cambió:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Id de columna 3 = cuarta columna DENTRO del rango del filtro (desplazamiento base 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible ahora coincide con lo que Excel mostrará al abrir el archivo

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible responde por fila, y PreviewAutoFilterRows recorre toda la región mediante una devolución de llamada cuando necesita el conjunto coincidente en una sola pasada. Hay un caso en el que ninguna de las dos es la respuesta correcta: si el requisito es que las filas excluidas no deben existir en absoluto en el archivo, un recorte por privacidad y no una vista, elimine las filas directamente. Un filtro es la herramienta equivocada ahí, porque cualquier destinatario lo quita con un clic y los datos que pretendía retener vuelven a la pantalla

El id de columna es un desplazamiento, no un número de columna

El comentario del fragmento anterior señala la trampa que más tiempo de depuración cuesta en esta API. AddAutoFilterColumn identifica su destino por la posición base 0 dentro del rango del filtro, no por la columna de la hoja de cálculo. Para un filtro en A1:E500 los dos sistemas de numeración difieren casualmente en uno, que es exactamente el tipo de casi acierto que sobrevive a una prueba rápida y se rompe en el momento en que un colega filtra otra columna. Para un filtro que empieza en la columna C, el id 0 significa la columna C, y el desajuste se vuelve obvio rápido. Cuando el rango del filtro se calcula en tiempo de ejecución, derive el id de columna de la misma variable que construyó la cadena del rango, nunca de una constante de columna de la hoja de cálculo. Cada columna acepta una segunda condición mediante la sobrecarga que recibe dos operadores, dos criterios y un conector and/or, que refleja el diálogo de filtro personalizado de Excel. La fachada XLS cubre el mismo terreno con SetAutoFilter más ApplyAutoFilter, cuyos parámetros de criterios y operador siguen las convenciones más antiguas al estilo COM y numeran el campo desde 1. Cambiar de fachada significa cambiar de base de índice, así que el sitio de la llamada merece un comentario que diga cuál está en juego

Las reglas de validación son el contrato bajo el que editan sus usuarios

De las tres funciones, la validación es la única que restringe activamente la entrada futura, y es la que más atención de diseño merece en libros de trabajo que salen para ser completados y regresan para ser procesados. La variante de lista carga con la mayor parte de ese trabajo:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Cantidades: números enteros, cero o más
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Más allá de listas y números enteros, la misma familia cubre decimales, fechas, horas, longitud de texto y fórmulas libres mediante AddCustomValidation, y el genérico AddDataValidation expone la matriz completa de tipos y operadores para constructores de reglas impulsados por configuración. El estilo de error importa más de lo que sugiere su nombre. xlsxDvErrStop rechaza la entrada incorrecta de plano; los estilos de advertencia e información dejan pasar el valor tras un solo clic. Elija por columna según si el código que vuelve a leer el libro de trabajo puede tolerar un valor fuera de la regla. Dos límites pertenecen al texto del mensaje o al README que distribuya con el archivo. La validación en Excel vigila la escritura, pero pegar un bloque sobre un rango validado se salta la regla, así que cualquier código que lea los datos de vuelta tiene que validar de nuevo en lugar de confiar en las celdas. Y una regla cubre el rango literal que usted le entregó, lo que significa que adjuntar la validación antes de conocer el conteo final de filas deja la cola anexada sin vigilancia. Escriba primero los datos, luego dimensione las reglas a la extensión real

La fachada heredada ofrece las mismas familias de reglas con una diferencia ergonómica. Los creadores del lado XLS, a saber AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation y AddCustomValidation, devuelven el objeto TDataValidation directamente en lugar de un índice, así que la configuración del mensaje y del error se encadena a partir de la referencia devuelta en lugar de una búsqueda. La enumeración de operadores (xlsDvBetween, xlsDvGreaterThan y el resto) refleja el conjunto de XLSX, así que el código de construcción de reglas se porta entre fachadas salvo por esa diferencia en el estilo de retorno. El texto del mensaje en sí merece tanta reflexión como la regla. Una lista desplegable que rechaza la entrada con un cuadro de error en blanco enseña a los usuarios a escribirle a TI; una que nombra los estados válidos les enseña a corregir la celda y seguir adelante

Una inversión de polaridad que la biblioteca absorbe por usted

Cualquiera que haya leído a mano el XML de validación de OOXML se ha topado con el atributo invertido showDropDown: en ISO/IEC 29500 un valor verdadero significa "suprimir la flecha desplegable", lo contrario de lo que el nombre sugiere. HotXLS invierte esto internamente, así que la propiedad ShowDropDown de una regla de validación significa lo que dice, con verdadero mostrando la lista desplegable. La única forma de quemarse es mezclar niveles de verdad, fijando la propiedad desde código mientras un colega audita el XML guardado y "corrige" el atributo que a él le parece al revés. Decida si la propiedad o el XML en bruto es la autoridad para las herramientas de revisión, y deje la inversión por escrito donde viva esa decisión

Las tablas dan a un rango un esquema y un nombre

Una tabla de hoja de cálculo, el ListObject en términos de Excel, envuelve un rango en un nombre, columnas con tipo, estilo de bandas y compatibilidad con referencias estructuradas. Es la función que hace que un libro de trabajo generado se sienta terminado en cuanto los usuarios empiezan a ordenarlo y extenderlo. La creación es simétrica entre fachadas, con AddTable recibiendo un nombre, un rango y una lista de columnas:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Del lado XLSX, el objeto de tabla resultante expone StyleName (la familia integrada TableStyleMedium2 y sus hermanas), interruptores de bandas y una bandera de fila de totales, así que aplicar el estilo de la casa es una asignación de propiedad y no una pasada de formato manual. En archivos .xls heredados la misma llamada escribe los registros de tabla BIFF8, y la fachada también ofrece AddPivotTable para vistas de resumen construidas a partir de campos de fila, columna y datos, un recordatorio de que las "tablas" en el formato antiguo llegan más lejos que el ListObject de OOXML. Nombre las tablas como nombra las vistas de base de datos. El código posterior que lee Orders[Amount] por referencia estructurada sobrevive al reordenamiento de columnas que rompe el código posicional

Dos convenciones ahorran limpieza después. Excel exige que los nombres de tabla sean únicos en todo el libro de trabajo, así que un generador que emite una hoja por región necesita un esquema como Orders_EMEA en lugar de reutilizar Orders. Un duplicado no falla al escribir; aparece como un diálogo de reparación cuando el usuario abre el archivo, que es el peor lugar para descubrirlo. La otra convención concierne a la fila de totales: cuando está habilitada, queda directamente debajo del rango de datos, así que cualquier código que después anexe por "última fila usada más uno" escribe en la banda de totales en lugar de después de ella. Lleve la extensión de los datos por separado de la extensión de la tabla y los anexados aterrizan donde espera

Las tres funciones se componen de forma natural en entregables de captura de datos. Una tabla define la región editable, la validación restringe las columnas en las que escriben los usuarios, y un filtro preestablecido le ahorra al destinatario los primeros clics. Hay un argumento razonable para distribuir un filtro ya aplicado de modo que el libro de trabajo se abra enfocado en las filas que importan, siempre que recuerde que las filas excluidas siguen en el archivo y que un destinatario curioso puede revelarlas. Llevar los resultados de consultas a la hoja de forma eficiente, la mitad anterior de este pipeline, se cubre en exportar resultados de base de datos a Excel desde Delphi, y los libros de trabajo donde las fórmulas resumen los datos validados se benefician de nombres definidos para referencias estables entre hojas

La validación, los filtros y las tablas son la diferencia entre entregar una cuadrícula de valores y entregar una pequeña aplicación. La referencia completa de reglas, filtros y tablas está en la página de producto de HotXLS Delphi Component