Ejecutar AutoFilter de Excel sin Excel en Delphi: HotXLS

HotXLS evalúa los criterios de AutoFilter ya guardados en un fichero 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 fechas relativas y de promedio, filtros de 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 pone «Top 10 por ingresos», «Este mes» sobre la fecha de pedido y un relleno rojo en las filas vencidas, guarda y sube el fichero. Vuestro servicio Delphi necesita ahora el conjunto de filas que Excel mostraría: para contarlo, exportarlo o escribir el fichero de vuelta con las filas correctas ocultas. El fichero solo contiene la definición. Antes de esta versión HotXLS podía evaluar criterios custom ordinarios, pero los tipos avanzados se quedaban en el XML guardado sin nadie que los ejecutara, así que la respuesta honesta era «abridlo en Excel»

Qué ejecuta realmente HotXLS 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 de ECMA-376 Part 1 §18.3.2, y cada uno 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 n elementos, o n por ciento, con val limitado a 1-500 elementos o 1-100 por ciento
• dynamicFilter: today, yesterday, tomorrow, this/last/next week, month, quarter y year, year to date, M1-M12, Q1-Q4, above average y below average
• colorFilter e iconFilter: color efectivo de relleno o de fuente resuelto a través del differential style, e iconos de formato condicional por icon set e id de icono zero-based

Cualquier cosa fuera de esa lista falla con EXLSXAutoFilterUnsupported en lugar de producir un conjunto de filas adivinado. Eso incluye los tipos above y below standard deviation de ST_DynamicFilterType, los valores de 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 silenciosamente filas «suficientemente cercanas» es peor que uno que se niega, porque nadie se entera hasta que un informe sale con los clientes equivocados dentro

Primero preview, después apply

HotXLS divide el trabajo en una preview de solo lectura y un apply transaccional, y ambos toman el mismo registro TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows devuelve el número de filas de datos que casan y nunca toca la visibilidad de filas. ApplyAutoFilter(AResetVisible, Options) prepara primero todas las decisiones 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 1-based

    // Empezad siempre desde los valores por defecto y cambiad campos sueltos
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

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

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

Inicializad el registro con XLSDefaultAutoFilterExecutionOptions cada vez. La función llena el registro con ceros y después 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 la pila y rellenado campo a campo normalmente llevará basura o ceros en los campos que olvidéis. 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 casa, que es lo que hace Excel cuando reaplicáis. False conserva el ocultado manual anterior en las filas que casan y solo añade ocultado 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 de datos caducados

Por qué las listas de valores ignoran asteriscos e interrogaciones

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

La comparación es contra el valor mostrado, lo que plantea la pregunta de qué 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 por encima. Los controles nativos de Excel en la fixture de HotXLS muestran por qué importa el token: el 1 numérico y el texto «1» casan ambos con una selección de 1, un 1 numérico formateado como 0.0 casa en cambio con 1.0 junto al texto «1.0», y el texto «01» se queda distinto de ambos. El texto con pinta de fecha nunca se parsea a fecha, así que una celda de texto que contiene «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 promedio una sola vez sobre toda la columna de origen, no sobre las filas que otro filtro dejó visibles. Solo los números finitos entran en la población, lo que significa que texto con pinta numérica como «100» queda excluido, y la población se recoge antes de tomar cualquier decisión de fila. En el libro de controles nativos un filtro above average mantiene una media de 27 sobre la columna completa incluso después de que una segunda columna oculte el valor 50; no pasa 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 suelo de un elemento, así que un 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 grabados en las fixtures de prueba de HotXLS, no de leer la especificación, que calla en la mayoría de ellos

Las fechas dinámicas necesitan una fecha de referencia congelada

TXLSXAutoFilterExecutionOptions.ReferenceDate congela el significado de «today» para toda una operación. Los filtros relativos como esta semana o el trimestre pasado, y las reglas de fechas de formato condicional que alimentan filtros de color, se evalúan todos contra esa única fecha, así que una preview a las 23:59:59 y un apply un segundo después están de acuerdo. Un valor cero selecciona la fecha actual una vez cuando la operación empieza; 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: this week va del domingo al domingo siguiente, this quarter del primer día del trimestre al primer día del siguiente, y year to date desde el 1 de enero hasta el final del día de referencia. Fijad ReferenceDate explícitamente en cualquier cosa que corra en un lote o una prueba. Un trabajo nocturno que cruza la medianoche, o una prueba unitaria escrita en octubre que tiene que pasar en enero, producirán si no conjuntos de filas que difieren sin motivo visible

Las celdas de fórmula se filtran por sus valores cacheados. El motor de filtros no recalcula silenciosamente el libro, así que un fichero guardado por un writer que no dejó resultados cacheados necesita una pasada de cálculo explícita antes de que el filtro pueda ver números o fechas con sentido

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 de formato condicional que case, y nada de lo que venga solo de un table style. Excel se comporta igual: una fila con bandas pintada azul claro por un table style incorporado no es «azul claro» para el desplegable del filtro. En los controles nativos, una celda pintada de rojo por formato condicional la selecciona 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 path como distintos tanto de los colores sólidos como de la ausencia de relleno, así que un gradiente nunca casa con un criterio de color sólido ni siquiera cuando uno de sus stops tiene ese color exacto. La versión 2.384.140 añadió el filtrado por color de fuente para rich text, donde una celda casa si cualquier run no vacío lleva el RGB pedido, el color automático o el heredado. La versión 2.384.150 completó las decisiones de rich text condicional, que resultó ser la extraña

Excel nativo puede reutilizar una decisión de color entre una celda de rich text y una celda plana con el mismo texto, el mismo estilo base y la misma ruta de regla condicional que casa, así que el conjunto de filas visible puede depender del orden de esas celdas. HotXLS lo reproduce preparando el rango de datos completo en orden ascendente de filas, incluso cuando solo preguntáis a AutoFilterRowVisible por una fila. Inspeccionar la fila 40 cuesta por tanto una pasada sobre el rango, pero la inspección de filas, la preview y el apply devuelven siempre la misma respuesta para la misma fila

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

HotXLS acota cada clase 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 fiable. 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 formateo
  ACancel := Now > Deadline;
end;

El callback de fila de PreviewAutoFilterRows recibe cada fila de datos con su decisión, no solo las que casan, y la función sigue devolviendo el recuento de coincidencias. Poner su parámetro Cancel detiene la visita pronto sin excepción. OnWork es el gancho de cancelación: poner 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 ficheros 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 dejad la clase base la última. En todos los casos la visibilidad de filas de la hoja queda sin cambios, lo que hace de «registrar, saltar, marcar el fichero para revisión manual» una política por defecto segura

Dónde están los bordes

La cobertura es amplia, pero unos cuantos bordes conviene conocerlos 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 empiezan en domingo; los libros que dependen de otro inicio de semana no están modelados
• Los resultados de fórmula vienen solo de la caché guardada, sin recálculo dentro de la llamada de filtro
• Los colores de table style nunca casan con un filtro de color, por diseño y en acuerdo con Excel
• La estructura de rich text 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 escribe HotXLS los criterios DOPER del AutoFilter BIFF8

Si todavía estáis decidiendo cómo se relacionan las definiciones de AutoFilter, la validación de datos y las tablas en un libro que generáis vosotros, empezad por la validación de datos, AutoFilter y tablas de HotXLS en Delphi, que cubre SetAutoFilter, los ids de columna y cuándo un filtro tiene efecto de verdad

FAQ

¿Cambia PreviewAutoFilterRows qué filas están ocultas?

No. PreviewAutoFilterRows solo evalúa los criterios guardados e informa 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 preparar todas las decisiones, así que un fallo deja la visibilidad original intacta

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

Los filtros como today, this week o last month se definen relativos a la fecha actual, exactamente como en Excel. Poned ReferenceDate en TXLSXAutoFilterExecutionOptions a una fecha fija cuando necesitéis resultados repetibles; un valor cero significa la fecha en la que la operación empieza

¿Puede HotXLS filtrar por colores que vienen de un table style?

No, y Excel tampoco puede. Los filtros de color de HotXLS casan solo con formato directo de celda y superposiciones de formato condicional soportadas. Los colores pintados únicamente por un table style incorporado o custom no participan, así que el resultado de un filtro de color concuerda con lo que el desplegable de Excel seleccionaría

La ejecución avanzada de AutoFilter viene con la API de hoja de cálculo XLSX de HotXLS, el componente de hojas de cálculo Delphi para leer y escribir ficheros Excel sin Excel, desde v2.384.136 en adelante, con manejo de color de gradiente, rich text y rich text condicional añadido hasta v2.384.150