Artículo técnico

Criterios DOPER de AutoFilter BIFF8 en Delphi con HotXLS

Una columna de montos guarda 250, 730 y 1,040. Un .xls generado aplica un AutoFilter de >=100 sobre esa columna, Excel abre el archivo sin aviso de reparación, el dropdown del encabezado muestra que hay un filtro activo, y todas las filas de datos desaparecen. Los bytes son BIFF8 válido. El problema completo es un byte de tipo: el operando 100 quedó guardado como string, Excel compara un operando de string como texto, y ninguna celda numérica pasa una comparación de texto contra "100". Guarde el operando como un double IEEE y el mismo filtro conserva las tres filas

Dónde guarda sus criterios un AutoFilter de BIFF8

Un AutoFilter de BIFF8 se reparte entre tres tipos de registro, y solo uno de ellos guarda criterios. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) anota cuántas columnas cubre el rango del filtro. FILTERMODE ($009B) es un marcador sin cuerpo, y HotXLS lo emite solo cuando al menos un campo tiene un criterio activo. Cada campo activo recibe entonces su propio registro AUTOFILTER ($009E, §2.4.6): un índice de campo, una palabra grbit cuyos dos bits bajos son wJoin, dos estructuras DOPER de exactamente 10 bytes cada una, y una cola opcional que guarda los caracteres de cualquier operando de string. El índice de campo en disco es base cero, mientras que TXLSWorksheet.ApplyAutoFilter numera los campos desde 1, así que la tercera columna de su rango aparece como 02 00 al inicio del cuerpo del registro en un hex dump

El primer byte de cada DOPER, vt, dice qué clase de operando viene después:

  • $04 es un double IEEE 754 en los 8 bytes restantes, la forma que Excel usa para una comparación numérica
  • $06 es un string cuya longitud vive en un solo byte cch, con los caracteres empujados a la cola del registro
  • $08 es un valor Bes, un Boolean o un código de error empaquetados en dos bytes
  • $0C y $0E no llevan operando alguno y significan match con todos los vacíos y match con todos los no vacíos

El segundo byte, grbitSgn, es la comparación: 1 a 6 representan <, =, <=, >, <> y >=. En HotXLS ambos bytes siguen inspeccionables después de fijar un criterio. TXLSWorksheet.AutoFilterColumns devuelve un TXLSAutofilter, su método Find devuelve el ítem de un campo base 1, y ese ítem expone Criteria1 y Criteria2 como objetos TXLSAutofilterDOPER con propiedades DataType, grbitSgn y Value de solo lectura. Eso es lo que se escribirá, byte a byte, cuando se guarde el libro

Anatomía del registro AUTOFILTER de HotXLS que muestra el índice de campo base cero, la palabra grbit cuyos dos bits bajos guardan wJoin, y dos estructuras DOPER de 10 bytes cuyo byte vt selecciona un operando de número IEEE, string, Boolean Bes, celda en blanco o no en blanco, mientras grbitSgn codifica el operador de comparación que aplica Excel
Cada registro AUTOFILTER lleva dos DOPERs de 10 bytes, y el byte vt decide si Excel compara un criterio como número, texto, Boolean o prueba de vacío — lea ambos de vuelta vía AutoFilterColumns antes de guardar

¿Por qué un filtro '>=100' no matcheó ninguna fila en Excel?

El filtro no matchea nada porque un operando de texto fuerza una comparación de texto. El primer escritor natural quita bien el prefijo >=, fija el signo en 6, y luego emite un DOPER vtString con los caracteres 100, pues todos los criterios llegan como string de todos modos, tanto en el diálogo del propio Excel como en la llamada AutoFilter de VBA. Excel acepta ese registro y lo evalúa exactamente como está escrito. Una celda numérica con 250 nunca es "mayor o igual" que el texto "100" en esa comparación, así que la columna queda vacía

TXLSWorksheet.ApplyAutoFilter y TXLSAutofilter.SetFieldCriteria convierten los criterios de la misma manera. Un string que arranca con uno de los seis operadores, seguido de texto que parsea como número, se convierte en un DOPER vtIEEENumber con ese operador como signo. Un Variant numérico (Integer, Int64, Double, Currency y los demás tipos ordinales y de punto flotante) se convierte en un DOPER de número con signo 2, una prueba de igualdad. Un Variant Boolean se convierte en un DOPER Bes. Un string pelado sin operador se queda como DOPER de string, porque así es como el propio Excel guarda un ítem elegido de la lista del dropdown

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
  Doper: TXLSAutofilterDOPER;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Cells[1, 1].Value := 'Region';
  Sh.Cells[1, 2].Value := 'Amount';
  Sh.Cells[2, 1].Value := 'North';
  Sh.Cells[2, 2].Value := 250;

  // Campo 2 = segunda columna de A1:B100 (los campos son base 1 en el API)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // Esperado: DataType = 4 (número IEEE), grbitSgn = 6 (>=)
  // El Assert es una comprobación que usted agrega, no forma parte de la biblioteca
  Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));

  Wb.SaveAs('orders.xls');
end;
El mismo criterio de AutoFilter >=100 guardado como un DOPER vtString no matchea celdas numéricas, mientras que el DOPER vtIEEENumber con grbitSgn 6 que HotXLS escribe se evalúa numéricamente contra el monto 250
Los bytes son BIFF8 válido en ambos casos — solo cambia el byte del tipo de operando, por eso Excel abre la versión de string, muestra el criterio en el dropdown y aun así matchea cero filas

La regla del string pelado es la que atrapa a la gente. En Delphi, '100' y 100 parecen intercambiables como argumentos Variant, pero el primero produce un match de texto y el segundo una igualdad numérica. Adivinar sería peor. Una columna de números de parte que guarda 00100 como texto recibiría un DOPER de número para 100 y no matchearía ninguna de sus propias celdas. Los strings pelados literales imitan lo que Excel escribe para una elección de lista, y cuando usted quiere un número pasa un número o escribe '=100', que da un DOPER IEEE con signo 2

Separadores decimales, fechas y el sistema de fechas 1904

La prueba numérica después del operador siempre usa punto como separador decimal, digan lo que digan las opciones regionales de Windows. '>=1.5' se vuelve un DOPER de número; '>=1,5' no parsea, se queda como DOPER de string y vacía una columna numérica en una máquina alemana o francesa tan confiablemente como en una estadounidense. Los separadores de agrupamiento fallan igual, así que '>=1,000' es texto. Los espacios entre el operador y los dígitos se recortan, así que '>= 100' está bien. Fijar el separador en un solo lugar evita que un reporte se comporte distinto según qué servidor lo generó, y ese es el valor por defecto correcto para un formato de archivo

Las fechas son la misma trampa con otro disfraz. '>=2026-01-01' no es un número, así que se escribe como texto, mientras que Excel guarda las celdas de fecha como números seriales y las compara numéricamente. Pase el serial en su lugar. Un TDateTime de Delphi tiene la misma parte entera que el serial del sistema 1900 de Excel para cualquier fecha a partir del 1 de marzo de 1900. Un libro cambiado al sistema de fechas 1904 cuenta desde el 1 de enero de 1904, así que sus seriales van 1462 por debajo, y IXLSWorkbook.Date1904 le dice qué sistema usa el archivo. Los detalles de ese desfase están en cómo interactúan los seriales de fecha 1900 y 1904 con los formatos numéricos

var
  Fmt: TFormatSettings;
  Serial: Integer;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // Umbral con fracción: formatéelo con punto, nunca con la configuración regional
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Fechas: compare contra el número serial que Excel guarda en la celda
  Serial := Trunc(EncodeDate(2026, 1, 1));
  if Wb.Date1904 then
    Dec(Serial, 1462);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
    xlAnd, Unassigned);
end;

Un Variant numérico sería más simple que la concatenación de strings, pero un Variant número siempre significa igualdad. Cualquier otra comparación tiene que viajar como operador más texto, así que el paso de formateo no se puede saltar

¿Cómo unen AND y OR dos condiciones?

Los bits wJoin del grbit de AUTOFILTER son 0 para AND y 1 para OR, y invertirlos es fácil y caro. Cámbielos y un filtro de rango como al menos 100 y menor que 500 se guarda como al menos 100 o menor que 500, lo que matchea todos los números y aparenta que el filtro nunca se aplicó. HotXLS escribe 0 para xlAnd y 1 para xlOr, y cuando hay criterios de igualdad involucrados también fija los bits fSimple1 y fSimple2 junto a wJoin, que marcan un DOPER con signo 2

Las constantes públicas traen su propio peligro de porteo. En HotXLS, xlAnd es 0 y xlOr es 1, mientras que la automatización de Excel los numera 1 y 2. XlAutoFilterOperator está declarado como un Byte común, así que una macro VBA traducida con números literales compila sin advertencias. Un literal 1 que significaba AND bajo COM ahora significa OR. Un literal 2 que significaba OR choca con xlBottom10Items, y como HotXLS trata todo valor de operador distinto de xlOr como AND cuando construye los dos DOPERs, ese criterio se une con AND. Ambos literales se voltean. Las constantes nombradas evitan el problema completo:

// Monto desde 100 (inclusive) hasta 500 (exclusive)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  // Comprobaciones que usted agrega: confirme lo que se escribirá
  Assert(Operator = xlAnd);            // wJoin = 0 en disco
  Assert(Criteria2.DataType = 4);      // número IEEE
  Assert(Criteria2.grbitSgn = 1);      // 1 = menor que
end;
Distribución de bits wJoin de HotXLS para criterios de AutoFilter donde 0 une dos DOPERs con AND y 1 con OR, una recta numérica que muestra cómo las constantes intercambiadas ensanchan un filtro between a un OR que matchea todo, y el choque de numeración de xlAnd y xlOr con la automatización de Excel
Las constantes wJoin invertidas convierten un filtro between en uno que matchea todos los números, y el VBA traducido sigue compilando porque XlAutoFilterOperator es un Byte común — un literal 1 que significaba AND bajo automatización COM significa OR aquí

Booleans, celdas en blanco y el techo de 255 caracteres

Un criterio Boolean se guarda como un valor Bes ([MS-XLS] §2.5.10), y Bes pone primero el byte de valor bBoolErr y después la bandera fError. Escríbalos en el orden contrario y un filtro por TRUE pone 1 en la bandera de error, con lo que Excel lee el criterio como un código de error. Lo sucio es que un lector con el mismo intercambio hace round-trip de sus propios archivos a la perfección. Una biblioteca que solo lee lo que ella misma escribió no prueba nada sobre conformidad aquí; abrir el archivo en Excel y revisar el diálogo del filtro sí lo hace. HotXLS escribe bBoolErr primero y fError como 0, así que pasar True produce un DOPER cuyo DataType es 8 y cuyo Value es 1

Las pruebas de vacío no necesitan operando. El string '=' solo produce un DOPER de match con todos los vacíos ($0C), y '<>' solo un DOPER de match con todos los no vacíos ($0E). Ambos son criterios completos, así que el campo cuenta como activo y se escribe FILTERMODE

Los operandos de string chocan con un límite duro en el layout del DOPER. El campo cch es de un byte, así que un operando de string puede guardar a lo sumo 255 caracteres, el mismo techo que el propio diálogo de filtro de Excel impone. HotXLS quita el operador primero y luego trunca a 255 caracteres lo que sea más largo, lo que evita que el byte de longitud dé la vuelta y desencaje la cola del registro. Un filtro sobre una columna de descripciones largas puede, por lo tanto, matchear sobre un prefijo de lo que usted pasó. Las mayúsculas y minúsculas se dejan tranquilas: Excel matchea el texto del filtro sin distinguir mayúsculas cuando aplica el filtro, y reescribir el criterio almacenado solo cambiaría los datos del usuario. En BIFF8 la cola guarda cada string como una bandera de un byte seguida de unidades de código UTF-16, y el tamaño declarado del registro tiene que contar exactamente esos bytes, la misma contabilidad que cubre cómo se desvían las declaraciones de longitud de registro BIFF en un escritor XLS de Delphi

Filtrar varias columnas y verificar el archivo guardado

Cada llamada a ApplyAutoFilter redefine todo el rango del filtro, así que tras dos llamadas solo sobrevive el criterio de la segunda. El método pasa por el TXLSWorksheet.SetAutoFilter público, que limpia cada campo antes de reconstruir el rango. Eso es correcto para una sola columna y sorprende la primera vez que usted filtra dos. Para varias columnas, llame a ApplyAutoFilter una vez para establecer el rango y el primer criterio, y luego agregue el resto con AutoFilterColumns.SetFieldCriteria, que toca un campo y deja el rango y los demás campos tranquilos. Ambos caminos ignoran un número de campo fuera del rango sin lanzar una excepción, así que la comprobación que vale la pena tener lee los criterios de vuelta del archivo reabierto

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                       // rango + campo 1
Sh.AutoFilterColumns.SetFieldCriteria(3, '>=100', xlAnd, Unassigned);
Sh.AutoFilterColumns.SetFieldCriteria(4, True, xlAnd, Unassigned);
Wb.SaveAs('orders.xls');

Wb := TXLSWorkbook.Create;
Wb.Open('orders.xls');
with Wb.Sheets[1].AutoFilterColumns do
begin
  // Comprobaciones que usted agrega tras reabrir el archivo
  Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6));   // texto 'North'
  Assert(Find(3).Criteria1.DataType = 4);                          // número 100
  Assert(Find(4).Criteria1.DataType = 8);                          // Boolean TRUE
end;

El registro AUTOFILTER es una definición almacenada. En una hoja XLS clásica HotXLS escribe los criterios para que Excel los evalúe al abrir el archivo, y la evaluación a nivel de fila del lado del servidor vive en el modelo de objetos XLSX, como se muestra en validación de datos, AutoFilter y tablas de HotXLS en Delphi. Una vez que Excel sí oculta filas, cualquier total debajo del rango depende de cómo tratan SUBTOTAL y AGGREGATE las filas ocultas y filtradas, y un filtro numérico guardado como texto aparece ahí como un total de cero

La conversión de criterios, el layout Bes y los bits de unión descritos arriba son lo que HotXLS escribe en cada libro BIFF8 que guarda, junto con el soporte XLSX nativo para Delphi y C++Builder. Las ediciones, la lista completa de funcionalidades y una descarga de prueba están en la página del componente de hojas de cálculo HotXLS para Delphi