Tres funciones de HotXLS comparten una hoja de cálculo pero operan sobre objetos completamente distintos, y el problema empieza cuando se asume que hacen cosas parecidas. La validación de datos adjunta una regla a un rango que restringe lo que un usuario puede escribir en él. Un AutoFilter adjunta una definición de criterios almacenada a una región y cambia qué filas muestra un visor. Una tabla envuelve un rango en una estructura con nombre y tipada, con estilo en 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 en realidad solo almacena una definición. Saber a 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 en silencio
AutoFilter almacena una definición, no recorta filas
Un AutoFilter en un archivo guardado es un registro de criterios. El ocultamiento de filas ocurre más tarde, 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 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 aunque erróneo 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 de sus columnas. Cuando el código del lado servidor necesita el resultado real, para un recuento de filas en un resumen o para reenviar solo las filas que coinciden, 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');
// El id de columna 3 = cuarta columna DENTRO del rango de filtro (offset en 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 a través de un callback cuando necesita el conjunto que coincide 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 el archivo en absoluto, un corte de privacidad y no una vista, elimine las filas directamente. Un filtro es la herramienta equivocada ahí, porque cualquier destinatario lo borra con un clic y los datos que pretendía retener vuelven a estar en pantalla
El id de columna es un offset, 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 objetivo por la posición en base 0 dentro del rango de filtro, no por la columna de la hoja. Para un filtro sobre A1:E500 los dos sistemas de numeración resultan diferir en uno, que es exactamente el tipo de casi-error 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 columna C, y el desajuste se hace evidente rápido. Cuando el rango de 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 hoja. Cada columna acepta una segunda condición mediante la sobrecarga que toma dos operadores, dos criterios, y un conector Y/O, 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 criterio y operador siguen las convenciones al estilo COM más antiguas y numeran el campo desde 1. Cambiar de fachada significa cambiar de base de índice, así que el punto de 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 los libros de trabajo que se envían para completar y vuelven para procesamiento. La variante de lista lleva 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 las listas y los números enteros, la misma familia cubre decimales, fechas, horas, longitud de texto, y fórmulas de forma libre mediante AddCustomValidation, y el genérico AddDataValidation expone la matriz completa de tipo y operador para constructores de reglas dirigidos por configuración. El estilo de error importa más de lo que su nombre sugiere. xlsxDvErrStop rechaza la entrada incorrecta sin más; 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 de aviso o al README que distribuye 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 vuelva a leer los datos tiene que validar de nuevo en lugar de confiar en las celdas. Y una regla cubre el rango literal que le entregó, lo que significa que adjuntar la validación antes de conocer el número final de filas deja la cola añadida sin proteger. Escriba primero los datos, y 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 directamente el objeto TDataValidation en lugar de un índice, de modo que la configuración de aviso y 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 traslada entre fachadas aparte de esa diferencia de estilo de retorno. El propio texto de aviso merece tanta reflexión como la regla. Un desplegable que rechaza la entrada con un cuadro de error en blanco enseña a los usuarios a escribir un correo a informática; uno 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 OOXML se ha topado con el atributo invertido showDropDown: en ISO/IEC 29500 un valor true significa "suprimir la flecha desplegable", justo lo contrario de lo que sugiere el nombre. HotXLS invierte esto internamente, de modo que la propiedad ShowDropDown de una regla de validación significa lo que dice, con true mostrando el desplegable. La única forma de salir escaldado es mezclar niveles de verdad, fijando la propiedad desde el 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 fuente autorizada para las herramientas de revisión, y anote la inversión 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 tipadas, estilo en bandas, y soporte de 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 tomando 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;En el lado XLSX, el objeto de tabla resultante expone StyleName (la familia integrada TableStyleMedium2 y sus hermanas), interruptores de rayado, y un indicador de fila de totales, de modo que aplicar el estilo corporativo es una asignación de propiedad en lugar de un paso 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 nombraría vistas de base de datos. El código posterior que lee Orders[Amount] mediante referencia estructurada sobrevive a la reordenación de columnas que rompe el código posicional
Dos convenciones ahorran limpieza más adelante. 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 en el momento de 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á activada, se sitúa justo debajo del rango de datos, así que cualquier código que más tarde añada filas mediante "última fila usada más uno" escribe en la banda de totales en lugar de después de ella. Rastree la extensión de los datos por separado de la extensión de la tabla y las adiciones caerán donde espera
Las tres funciones se combinan de forma natural en entregables de entrada 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 ahorra al destinatario los primeros clics. Hay un argumento razonable para entregar un filtro ya aplicado de modo que el libro de trabajo se abra centrado en las filas que importan, siempre que recuerde que las filas excluidas siguen en el archivo y un destinatario curioso puede revelarlas. Llevar los resultados de una consulta a la hoja de forma eficiente, la mitad previa de este pipeline, se cubre en exportar resultados de base de datos a Excel desde Delphi, y los libros de trabajo donde fórmulas resumen los datos validados se benefician de nombres definidos para referencias estables entre hojas
La validación, los filtros y las tablas marcan 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