Ejecute AutoFilters de Excel sin Excel en Delphi: HotXLS

HotXLS evalúa los criterios de AutoFilter ya guardados en un archivo XLSX sin arrancar Excel. Desde v2.384.136, TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter y AutoFilterRowVisible ejecutan listas de valores, grupos de fechas, filtros Top/Bottom-n y porcentuales, filtros de fecha relativa y de promedio, filtros por color de relleno y de fuente, y filtros de iconos de formato condicional, bajo una fecha de referencia congelada y límites de recursos explícitos

El disparador habitual es un libro que una persona filtró en Excel y un servidor que tiene que actuar sobre él. Alguien fija "Top 10 por ingresos", "Este mes" sobre la fecha de pedido y un relleno rojo en las filas vencidas, guarda, y sube el archivo. Su servicio Delphi ahora necesita el conjunto de filas que Excel mostraría: para contarlo, exportarlo, o escribir el archivo de vuelta con las filas correctas ocultas. El archivo solo guarda la definición. Antes de esta versión HotXLS podía evaluar criterios personalizados ordinarios, pero los tipos avanzados quedaban en el XML guardado sin nada que los ejecutara, así que la respuesta honesta era "ábralo en Excel"

¿Qué ejecuta HotXLS realmente de un AutoFilter guardado?

HotXLS ejecuta un tipo de filtro por columna, tomado del elemento filterColumn que Excel escribió, y combina columnas con AND, exactamente como los desplegables de Excel. Los elementos vienen del grupo AutoFilter en ECMA-376 Part 1 §18.3.2, y cada uno se mapea a una ruta de evaluación distinta:

• filters con hijos filter y dateGroupItem: una lista de valores marcada, una entrada de vacío opcional y grupos de fechas de calendario desde año hasta segundo, unidos con OR
• customFilters: uno o dos criterios de comparación unidos por AND u OR, la ruta que ya existía
• top10: Top o Bottom de n elementos, o n por ciento, con val limitado a 1-500 elementos o 1-100 por ciento
• dynamicFilter: hoy, ayer, mañana, esta/pasada/próxima semana, mes, trimestre y año, año hasta la fecha, M1-M12, Q1-Q4, sobre el promedio y bajo el promedio
• colorFilter y iconFilter: color efectivo de relleno o fuente resuelto a través del estilo diferencial, y iconos de formato condicional por conjunto de iconos e id de icono base 0

Cualquier cosa fuera de esa lista falla con EXLSXAutoFilterUnsupported en lugar de producir un conjunto de filas adivinado. Eso incluye los tipos por encima y por debajo de la desviación estándar de ST_DynamicFilterType, valores calendarType no gregorianos, elementos de extensión desconocidos, referencias dxfId sin resolver y una columna que de alguna manera lleva dos tipos de filtro a la vez. Un motor de filtros que devuelve en silencio filas "suficientemente cercanas" es peor que uno que se rehúsa, porque nadie se entera hasta que un reporte sale con los clientes equivocados dentro

Primero previsualizar, después aplicar

HotXLS divide el trabajo en una previsualización de solo lectura y una aplicación transaccional, y ambos toman el mismo registro TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows devuelve el número de filas de datos que coinciden y nunca toca la visibilidad de filas. ApplyAutoFilter(AResetVisible, Options) prepara primero toda decisión de fila y solo entonces publica el nuevo conjunto de filas ocultas en un paso, así que una excepción durante la preparación deja la hoja exactamente como estaba

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Options: TXLSXAutoFilterExecutionOptions;
  Matching: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders-filtered-by-sales.xlsx');
    Sheet := Book.Sheets[1];   // el indexador de Sheets es base 1

    // Parta siempre de los valores por defecto, luego cambie campos sueltos
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // solo conteo
    WriteLn('Rows Excel would show: ', Matching);

    // True: las filas que coinciden se vuelven visibles aunque estuvieran ocultas antes
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Inicialice el registro con XLSDefaultAutoFilterExecutionOptions cada vez. La función llena el registro de ceros y luego fija cada límite, y el contexto de ejecución rechaza un registro cuyos límites no sean todos positivos con EXLSXAutoFilterLimit. Un registro declarado en el stack y llenado campo por campo normalmente llevará basura o ceros en los campos que olvidó. Las sobrecargas sin parámetros (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) usan los mismos valores por defecto, con la fecha actual como referencia

El flag AResetVisible decide qué pasa con las filas que pasan el filtro pero ya estaban ocultas. True hace visible cada fila que coincide, que es lo que hace Excel cuando reaplica. False conserva el ocultamiento manual anterior en las filas que coinciden y solo agrega ocultamiento para las filas que fallan. Las filas fuera del rango del AutoFilter conservan su estado de cualquier forma. Si el libro cambió entre la preparación y la publicación, ApplyAutoFilter lanza en lugar de publicar decisiones calculadas con datos obsoletos

¿Por qué las listas de valores ignoran asteriscos y signos de interrogación?

Las entradas de lista de valores en un elemento filters son tokens de visualización literales, así que HotXLS compara * y ? como caracteres ordinarios. Una entrada marcada A* en el desplegable significa que la celda muestra exactamente A*, no "empieza con A". Los wildcards pertenecen a customFilters, donde "empieza con" y "contiene" sí son patrones, y esos conservan las reglas separadas cubiertas en cómo HotXLS compara criterios wildcard de Excel y escapes con tilde. Mezclar las dos semánticas es la forma más rápida de filtrar una columna de números de parte hacia el sinsentido

La comparación es contra el valor mostrado, lo que plantea la pregunta de cuál visualización. HotXLS formatea la celda con su formato numérico base, incluidas las superposiciones de estilo guardadas, y deliberadamente ignora un formato numérico que el formato condicional aplica encima. Los controles nativos de Excel en el fixture de HotXLS muestran por qué importa el token: el numérico 1 y el texto "1" coinciden ambos con una selección de 1, un numérico 1 formateado como 0.0 en cambio coincide con 1.0 junto con el texto "1.0", y el texto "01" se mantiene distinto de ambos. El texto con apariencia de fecha nunca se parsea a fecha, así que una celda de texto que contenga "2026-10-19" no caerá en un grupo de fechas

¿Cómo deciden los filtros Top 10, promedio y de fechas dinámicas?

HotXLS calcula los cortes de ranking y de promedio una sola vez sobre la columna de origen completa, no sobre las filas que otro filtro dejó visibles. Solo los números finitos entran a la población, lo que significa que el texto con apariencia numérica como "100" queda excluido, y la población se recolecta antes de tomar cualquier decisión de fila. En el libro de control nativo un filtro de sobre el promedio mantiene una media de 27 sobre la columna completa incluso después de que una segunda columna oculte el valor 50; no cambia a la media de 21.25 de las filas restantes

Los empates se incluyen en el corte, como en Excel. Un filtro Top 3 sobre valores con dos entradas empatadas en 20 selecciona cuatro filas. El modo porcentual toma la parte truncada de la población numérica, con un piso de un elemento, así que el Top 50 por ciento de cinco valores selecciona dos valores con un umbral de 30. Las variantes Bottom espejan la comparación. Estos números vienen de los conjuntos de filas del propio Excel registrados en los fixtures de prueba de HotXLS, no de leer la especificación, que calla sobre la mayoría de ellos

Las fechas dinámicas necesitan una fecha de referencia congelada

TXLSXAutoFilterExecutionOptions.ReferenceDate congela el significado de "hoy" para una operación completa. Los filtros relativos como esta semana o el trimestre pasado, y las reglas de fecha del formato condicional que alimentan los filtros de color, se evalúan todos contra esa única fecha, así que una previsualización a las 23:59:59 y una aplicación un segundo después coinciden. Un valor cero selecciona la fecha actual una vez cuando la operación arranca; los valores seriales respetan el sistema de fechas 1900 o 1904 del libro, incluido el serial ficticio 60 de Excel en el sistema 1900 para los filtros M2 y Q1

Las ventanas son intervalos de calendario semiabiertos: esta semana corre del domingo al domingo siguiente, este trimestre del primer día del trimestre al primer día del siguiente, y el año hasta la fecha desde el 1 de enero hasta el final del día de referencia. Fije ReferenceDate explícitamente en cualquier cosa que corra en un lote o en una prueba. Un trabajo nocturno que cruza la medianoche, o una prueba unitaria escrita en octubre que tiene que pasar en enero, de otro modo producirá conjuntos de filas que difieren sin razón visible

Las celdas de fórmula se filtran por sus valores en caché. El motor de filtros no recalcula el libro en silencio, así que un archivo guardado por un escritor que no dejó resultados en caché necesita una pasada de cálculo explícita antes de que el filtro pueda ver números o fechas con significado

¿Qué colores ve un filtro de color?

Un filtro de color de HotXLS ve el color efectivo del formato directo de celda más cualquier superposición coincidente de formato condicional, y nada de lo que viene solo de un estilo de tabla. Excel se comporta igual: una fila con bandas pintada celeste por un estilo de tabla incorporado no es "celeste" para el desplegable del filtro. En los controles nativos, una celda pintada de rojo por formato condicional es seleccionada por un filtro de relleno rojo, mientras que una celda formateada roja directamente pero superada por una regla amarilla activa queda excluida

Tres refinamientos llegaron en versiones de seguimiento. La versión 2.384.138 trata los rellenos de gradiente lineal y de trayectoria como distintos tanto de los colores sólidos como de la ausencia de relleno, así que un gradiente nunca coincide con un criterio de color sólido aunque uno de sus stops tenga ese color exacto. La versión 2.384.140 agregó el filtrado por color de fuente para texto enriquecido, donde una celda coincide si cualquier run no vacío lleva el RGB, automático o heredado solicitado. La versión 2.384.150 completó las decisiones condicionales de texto enriquecido, que resultaron ser la extraña

El Excel nativo puede reutilizar una decisión de color entre una celda de texto enriquecido y una celda simple con el mismo texto, el mismo estilo base y la misma ruta de regla condicional coincidente, así que el conjunto de filas visibles puede depender del orden de esas celdas. HotXLS lo reproduce preparando el rango de datos completo en orden ascendente de filas, incluso cuando usted solo le pregunta a AutoFilterRowVisible por una fila. Inspeccionar la fila 40 cuesta por tanto una pasada sobre el rango, pero la inspección de filas, la previsualización y la aplicación siempre devuelven la misma respuesta para la misma fila

Cada recurso tiene un límite, y qué atrapar

HotXLS acota cada tipo de trabajo en una operación de AutoFilter con su propio campo en TXLSXAutoFilterExecutionOptions, porque un libro filtrado que viene de un formulario de subida es entrada no confiable. Los valores por defecto son 1048576 para MaxXmlCharacters, 16384 para MaxXmlNodes, 4096 para MaxFilterItems, 4000000 para MaxExaminedCells, 1000000 cada uno para MaxEffectiveFormatCells y MaxPopulationValues, 67108864 bytes para MaxContextBytes y 16000000 para MaxConditionalSteps. MaxContextBytes mide estado lógico retenido y envoltorios temporales reservados, no memoria del proceso

type
  TFilterJob = class
  public
    Deadline: TDateTime;
    VisibleRows: TList<Integer>;
    procedure HandleRow(Sender: TObject; SheetIndex, Row: Integer;
      Visible: Boolean; var Cancel: Boolean);
    procedure HandleWork(Sender: TObject; ACompleted: Int64;
      var ACancel: Boolean);
  end;

procedure TFilterJob.HandleRow(Sender: TObject; SheetIndex, Row: Integer;
  Visible: Boolean; var Cancel: Boolean);
begin
  // Se llama por cada fila de datos; Visible es la decisión del filtro
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted cuenta celdas examinadas y puede quedarse plano durante el formato
  ACancel := Now > Deadline;
end;

El callback de filas de PreviewAutoFilterRows recibe cada fila de datos con su decisión, no solo las que coinciden, y la función sigue devolviendo el conteo de coincidencias. Fijar su parámetro Cancel detiene la visita temprano sin excepción. OnWork es el gancho de cancelación: fijar ACancel a True lanza EXLSXAutoFilterCancelled. Mientras la operación prepara decisiones sostiene un lease de lectura sobre el libro, así que ninguno de los dos callbacks puede modificar ese libro

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // tope más estricto para archivos subidos
Options.OnWork := Job.HandleWork;
try
  Sheet.PreviewAutoFilterRows(Job.HandleRow, Options);
  Sheet.ApplyAutoFilter(True, Options);
except
  on E: EXLSXAutoFilterUnsupported do
    Log('Filter needs Excel: ' + E.Message);
  on E: EXLSXAutoFilterLimit do
    Log('Filter exceeded a budget: ' + E.Message);
  on E: EXLSXAutoFilterCancelled do
    Log('Filter cancelled at deadline');
  on E: EXLSXAutoFilterExecution do
    Log('Filter state invalid: ' + E.Message);
end;

Las tres excepciones específicas derivan de EXLSXAutoFilterExecution, que a su vez cubre estado inválido como una fecha de referencia fuera del calendario soportado o un libro que cambió antes de la publicación, así que deje la clase base al final. En cada caso la visibilidad de filas de la hoja queda sin cambios, lo que hace de "registrar, saltar, marcar el archivo para revisión manual" una política por defecto segura

Dónde están los límites

La cobertura es amplia, pero unas pocas aristas vale la pena conocer antes de prometer paridad a un cliente:

• Los filtros dinámicos de desviación estándar se rechazan hasta que exista una política verificada, aunque la enumeración tenga valores para ellos
• Las ventanas de semana arrancan el domingo; los libros que dependen de un inicio de semana distinto no se modelan
• Los resultados de fórmulas vienen solo de la caché guardada, sin recálculo dentro de la llamada del filtro
• Los colores de estilo de tabla nunca coinciden con un filtro de color, por diseño y en acuerdo con Excel
• La estructura de texto enriquecido desconocida o los metadatos de color desconocidos fallan explícitamente, mientras que las propiedades conocidas que no son de color, como la familia de fuente, no bloquean una coincidencia de color
• La fachada XLS y su almacenamiento BIFF8 son una ruta separada, cubierta en cómo HotXLS escribe criterios DOPER de AutoFilter BIFF8

Si todavía está decidiendo cómo se relacionan las definiciones de AutoFilter, la validación de datos y las tablas en un libro que usted genera, empiece con validación de datos, AutoFilter y tablas en HotXLS para Delphi, que cubre SetAutoFilter, los ids de columna y cuándo un filtro realmente toma efecto

FAQ

¿PreviewAutoFilterRows cambia qué filas están ocultas?

No. PreviewAutoFilterRows solo evalúa los criterios guardados y reporta decisiones a través de su valor de retorno y un callback opcional. Solo ApplyAutoFilter publica un nuevo conjunto de filas ocultas, y lo hace después de que todas las decisiones están preparadas, así que una falla deja la visibilidad original intacta

¿Por qué un filtro de fecha relativa da filas distintas en días distintos?

Los filtros como hoy, esta semana o el mes pasado se definen relativos a la fecha actual, exactamente como en Excel. Fije ReferenceDate en TXLSXAutoFilterExecutionOptions a una fecha fija cuando necesite resultados repetibles; un valor cero significa la fecha en que la operación arranca

¿Puede HotXLS filtrar por colores que vienen de un estilo de tabla?

No, y Excel tampoco puede. Los filtros de color de HotXLS coinciden solo con el formato directo de celda y las superposiciones de formato condicional soportadas. Los colores pintados únicamente por un estilo de tabla incorporado o personalizado no participan, así que el resultado de un filtro de color coincide con lo que el desplegable de Excel seleccionaría

La ejecución avanzada de AutoFilter viene con la API de hojas XLSX de HotXLS, el componente de hojas de cálculo Delphi para leer y escribir archivos Excel sin Excel, desde v2.384.136 en adelante, con manejo de color de gradientes, texto enriquecido y texto enriquecido condicional agregado hasta v2.384.150