Artículo técnico

Criterios DOPER de AutoFilter BIFF8 en Delphi con HotXLS

Una columna de importes contiene 250, 730 y 1,040. Un .xls generado aplica a esa columna un AutoFilter de >=100, Excel abre el archivo sin aviso de reparación, el desplegable de la cabecera muestra que hay un filtro activo, y desaparecen todas las filas de datos. Los bytes son BIFF8 válidos. Todo el problema es un byte de tipo: el operando 100 se guardó como cadena, Excel compara un operando de cadena como texto, y ninguna celda numérica pasa una comparación de texto contra "100". Guardad el operando como un double IEEE y el mismo filtro conserva las tres filas

Dónde guarda sus criterios un AutoFilter BIFF8

Un AutoFilter BIFF8 se reparte entre tres tipos de record, y solo uno de ellos lleva criterios. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) anota cuántas columnas cubre el rango del filtro. FILTERMODE ($009B) es un marcador sin cuerpo que HotXLS emite solo cuando al menos un campo tiene un criterio activo. Cada campo activo recibe entonces su propio record 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 cadena. El índice de campo en disco es base cero, mientras que TXLSWorksheet.ApplyAutoFilter numera los campos desde 1, de modo que la tercera columna de vuestro rango aparece como 02 00 al principio del cuerpo del record en un volcado hexadecimal

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

  • $04 es un double IEEE 754 almacenado en los 8 bytes restantes, que es como Excel guarda una comparación numérica
  • $06 es una cadena cuya longitud vive en un único byte cch, con los caracteres propiamente dichos empujados a la cola del record
  • $08 es un valor Bes, un Boolean o código de error empaquetado en dos bytes
  • $0C y $0E no llevan operando alguno y significan coincidir con todos los vacíos y coincidir con todos los no vacíos

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

Anatomía del record AUTOFILTER de HotXLS mostrando 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, cadena, Boolean Bes, vacío o no vacío mientras grbitSgn codifica el operador de comparación que aplica Excel
Cada record AUTOFILTER lleva dos DOPERs de 10 bytes, y el byte vt decide si Excel compara un criterio como número, texto, Boolean o test de vacío — lee ambos de vuelta vía AutoFilterColumns antes de guardar

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

El filtro no coincide con nada porque un operando de texto fuerza una comparación de texto. El primer escritor natural recorta bien el prefijo >=, fija el signo a 6, y luego emite un DOPER vtString con los caracteres 100, porque al fin y al cabo todo criterio llega como cadena, tanto en el diálogo del propio Excel como en la llamada AutoFilter de VBA. Excel acepta ese record 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 se vacía

TXLSWorksheet.ApplyAutoFilter y TXLSAutofilter.SetFieldCriteria convierten los criterios de la misma manera. Una cadena que empieza por 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 coma flotante) se convierte en un DOPER de número con signo 2, un test de igualdad. Un Variant Boolean se convierte en un DOPER Bes. Una cadena desnuda sin operador se queda como DOPER de cadena, porque así es como el propio Excel guarda un elemento escogido de la lista desplegable

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 (campos base 1 en la 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 añadís vosotros, 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 coincide con celdas numéricas, mientras que el DOPER vtIEEENumber con grbitSgn 6 que escribe HotXLS se evalúa numéricamente contra el importe 250
Los bytes son BIFF8 válidos por ambos caminos — solo difiere el byte del tipo de operando, razón por la que Excel abre la versión de cadena, muestra el criterio en el desplegable y aun así coincide con cero filas

La regla de la cadena desnuda es la que caza a la gente. En Delphi, '100' y 100 parecen intercambiables como argumentos Variant, pero el primero produce una coincidencia de texto y el segundo una igualdad numérica. Adivinar sería peor. Una columna de referencias de pieza que guarda 00100 como texto recibiría un DOPER de número para 100 y no coincidiría con ninguna de sus propias celdas. Las cadenas desnudas literales imitan lo que Excel escribe para una elección de lista, y cuando queréis un número pasáis un número o escribís '=100', que da un DOPER IEEE con signo 2

Separadores decimales, fechas y el sistema de fechas 1904

El test numérico tras el operador usa siempre punto como separador decimal, digan lo que digan las opciones regionales de Windows. '>=1.5' se convierte en un DOPER de número; '>=1,5' no parsea, se queda como DOPER de cadena y vacía una columna numérica en una máquina alemana o francesa con la misma fiabilidad que en una americana. 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' va bien. Fijar el separador en un único sitio evita que un informe se comporte distinto según qué servidor lo generó, y ese es el valor por defecto correcto para un formato de fichero

Las fechas son la misma trampa con otro disfraz. '>=2026-01-01' no es un número, así que se escribe como texto, mientras Excel guarda las celdas de fecha como números de serie y las compara numéricamente. Pasad el serial. Un TDateTime de Delphi tiene la misma parte entera que el serial del sistema 1900 de Excel para cualquier fecha desde el 1 de marzo de 1900 en adelante. Un workbook 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 os dice qué sistema usa el fichero. 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 decimales: formatéalo con punto, nunca la configuración regional
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Fechas: compara contra el número de serie 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 cadenas, pero un Variant número significa siempre 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 valen 0 para AND y 1 para OR, e invertirlos es fácil y caro. Cambiadlos y un filtro de rango como al menos 100 y por debajo de 500 se guarda como al menos 100 o por debajo de 500, lo que coincide con 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 de por medio 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 portado. 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 corriente, así que una macro VBA traducida con números literales compila sin aviso. Un 1 literal que significaba AND bajo COM ahora significa OR. Un 2 literal que significaba OR colisiona 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. Los dos literales se voltean. Las constantes con nombre evitan el problema entero:

// Importe desde 100 (incluido) hasta 500 (excluido)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  // Comprobaciones que añadís vosotros: confirmad lo que se va a escribir
  Assert(Operator = xlAnd);            // wJoin = 0 en disco
  Assert(Criteria2.DataType = 4);      // número IEEE
  Assert(Criteria2.grbitSgn = 1);      // 1 = menor que
end;
Disposició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 mostrando cómo las constantes intercambiadas ensanchan un filtro between en un OR que coincide con todo, y el choque de numeración xlAnd xlOr con la automatización de Excel
Las constantes wJoin invertidas convierten un filtro between en uno que coincide con todos los números, y el VBA traducido sigue compilando porque XlAutoFilterOperator es un Byte corriente — un 1 literal que significaba AND bajo automatización COM significa OR aquí

Booleanos, vacíos 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 el flag fError. Escribidlos en el orden contrario y un filtro para TRUE mete un 1 en el flag de error, así 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 la conformidad aquí; abrir el archivo en Excel y mirar el diálogo del filtro sí. HotXLS escribe bBoolErr primero y fError a 0, así que pasar True produce un DOPER cuyo DataType es 8 y cuyo Value es 1

Los tests de vacío no necesitan operando. La cadena '=' por sí sola produce un DOPER de coincidir con todos los vacíos ($0C), y '<>' por sí sola un DOPER de coincidir 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 cadena chocan con un límite duro en la disposición del DOPER. El campo cch es un byte, así que un operando de cadena admite como máximo 255 caracteres, el mismo techo que aplica el diálogo de filtros del propio Excel. HotXLS quita primero el operador 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 record. Un filtro sobre una columna de descripciones largas puede, por tanto, coincidir con un prefijo de lo que pasasteis. La caja se deja en paz: Excel coincide con 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 cadena como un flag de un byte seguido de unidades de código UTF-16, y el tamaño de record declarado tiene que contar exactamente esos bytes, la misma contabilidad que cubre cómo se desvían las declaraciones de longitud de record BIFF en un escritor XLS en Delphi

Filtrar varias columnas y comprobar el fichero guardado

Cada llamada a ApplyAutoFilter redefine el rango de filtro entero, así que tras dos llamadas solo sobrevive el criterio de la segunda. El método pasa por el TXLSWorksheet.SetAutoFilter público, que limpia todos los campos antes de reconstruir el rango. Es lo correcto para una columna y sorprende la primera vez que filtráis dos. Para varias columnas, llamad a ApplyAutoFilter una vez para establecer el rango y el primer criterio, y añadid los demás con AutoFilterColumns.SetFieldCriteria, que toca un campo y deja en paz el rango y los otros campos. Ambos caminos ignoran un número de campo fuera del rango sin lanzar una excepción, así que la comprobación que merece la pena tener lee los criterios de vuelta del fichero 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 añadís vosotros tras reabrir el fichero
  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 record 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 fichero, y la evaluación a nivel de fila en el lado del servidor vive en el modelo de objetos XLSX, como se muestra en validación de datos, AutoFilter y tablas en Delphi con HotXLS. Cuando Excel oculta filas, cualquier total bajo el rango depende de cómo tratan SUBTOTAL y AGGREGATE las filas ocultas y filtradas, y un filtro numérico guardado como texto se aparece ahí como un total de cero

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