LlosLab Software Development BlogNotas de ingeniería para desarrolladores de PDF, Delphi y software de documentos.

Artículo técnico

Fórmulas de desbordamiento de matriz dinámica en Delphi con HotXLS

HotXLS Component

HotXLS evalúa fórmulas de desbordamiento de matriz dinámica de Excel 365 de forma nativa en Delphi y C++Builder: usted le entrega un libro de trabajo cuyas celdas contienen XLOOKUP o FILTER, y su motor de fórmulas calcula el conjunto de resultados y lo desborda a lo largo del rectángulo de salida anclado, los mismos valores que produciría Excel. El componente de Excel para Delphi HotXLS modela una fórmula de desbordamiento como una celda raíz que posee el cálculo más un bloque de celdas dependientes que leen de ella, lo cual representa todo el panorama que usted requiere antes de escribir una línea de código

Ese modelo importa porque la tarea cotidiana no es abstracta. Un cliente envía un archivo .xlsx creado en Excel 365, sus hojas están llenas de =FILTER(...) y =XLOOKUP(...), y su servicio debe reproducir exactamente los mismos números de forma silenciosa (headless), en un equipo sin Excel instalado, y luego leer los valores desbordados o escribir una nueva región de desbordamiento propia. Las matrices dinámicas cambiaron el contrato de cálculo de "una fórmula, una celda" a "una fórmula, un rectángulo de celdas", y el motor HotXLS sigue ese contrato en lugar de simularlo con una cuadrícula preexpandida

¿Cómo se evalúan XLOOKUP y FILTER en Delphi?

HotXLS procesa cada función de matriz dinámica a través de un único punto de entrada del evaluador, CalcDynArrayFunc en lxCalc.pas, por lo que toda la familia comparte una misma ruta de código de desbordamiento. El conjunto compatible incluye XLOOKUP y FILTER (las dos originales), a las que se unen XMATCH, SORT, UNIQUE y SEQUENCE. Cada una devuelve una matriz variante bidimensional en lugar de un escalar: SEQUENCE(3;2;1;1) produce una cuadrícula de 3 por 2, SORT reorders filas por cualquier columna clave de forma ascendente o descendente, UNIQUE reduce las filas duplicadas conservando la primera ocurrencia, y XMATCH reporta la posición basada en 1 de un valor bajo los mismos modos de coincidencia exacta, comodín, siguiente menor y siguiente mayor que XLOOKUP. A diferencia de las funciones escalares de distribución estadística, que devuelven un número por llamada, estas funciones entregan una forma completa

Para calcular un libro de trabajo que ya contiene estas fórmulas, ábralo, llame a Recalculate una vez y lea las celdas en las que cayó el desbordamiento. TXLSXWorkbook.Recalculate recorre el grafo de dependencias y evalúa cada fórmula desactualizada en orden topológico, por lo que una raíz de desbordamiento se calcula una sola vez y sus elementos se escriben directamente en las celdas miembro

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Si el libro de trabajo necesita una función que el motor no incluye, el mismo evaluador le permite conectar su propia lógica a través del punto de extensión de funciones personalizadas de hoja de cálculo, y una función personalizada es libre de devolver una matriz variante para que se desborde exactamente igual que las integradas

Anclar el rango de desbordamiento con SetArrayFormula

HotXLS nunca adivina qué tan grande debe ser un desbordamiento: usted define el rectángulo de salida y TXLSRange.SetArrayFormula le ancla la fórmula. El método compila la fórmula una vez, almacena el árbol de sintaxis en la celda raíz superior izquierda junto con el rango anclado, y asigna a cada una de las demás celdas del rectángulo una fórmula ligera que hace una referencia débil a la raíz. En el recálculo, la raíz se evalúa una vez y sus elementos de matriz se transmiten directamente a cada celda miembro, que es también la forma en que una raíz de desbordamiento aparece como un nodo en el grafo de dependencias de recálculo incremental. Esta es la ruta a usar cuando la tarea es escribir una región de desbordamiento de vuelta en el archivo en lugar de leer una existente

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

La consecuencia del anclaje explícito es un límite que vale la pena mencionar claramente: HotXLS no cambia automáticamente el tamaño de un desbordamiento como lo hace Excel interactivo. Excel expande y contrae la región desbordada a medida que cambian sus entradas y genera el error #SPILL! cuando las celdas de destino están ocupadas. En HotXLS, el rectángulo que usted pasa a SetArrayFormula es el rectángulo que obtiene, por lo que debe dimensionarlo para el resultado que espera, y si una función produce más filas de las que ancló, el excedente no tendrá dónde colocarse

Transmisión de matrices elemento por elemento en los operadores

HotXLS aplica los operadores aritméticos elemento por elemento cuando cualquiera de los lados de la operación es una matriz. El asistente ApplyArrayBinaryOp cubre +, -, *, / y ^ sobre matrices variantes unidimensionales y bidimensionales: un operando escalar se transmite a cada elemento, dos matrices deben coincidir en forma o la operación se rechaza, y la división por cero se propaga como un error en lugar de fallar. La ruta aritmética escalar no se altera, por lo que esto solo se activa cuando al menos uno de los operandos es verdaderamente una matriz, como un rango desbordado o el resultado de una función de matriz. Eso significa que una fórmula como =D2:D13*1.1 anclada sobre una columna multiplica cada elemento a su vez, y =D2:D13*E2:E13 multiplica dos columnas de igual forma posición por posición

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

¿Qué hace el operador @ frente a una matriz CSE heredada?

HotXLS lee un @ explícito como el operador de intersección de referencia, no como la intersección implícita de selección de fila de Excel interactivo. En el motor, A1:A3 @ B1:B3 devuelve la celda única donde los dos rangos se cruzan, y el operador se asocia con mayor prioridad que ^ y menor que %. Un comentario honesto acompaña a esto: la forma de intersección implícita separada por espacios aún no se analiza (tokeniza), porque el analizador léxico todavía ignora los espacios en blanco, por lo que solo se obtiene la intersección a través del símbolo @ explícito

La distinción más profunda es entre una fórmula de matriz CSE heredada y una matriz dinámica moderna, y en HotXLS ambas fluyen a través del mismo mecanismo de anclaje. Una fórmula de matriz clásica era el bloque {=...} ingresado con Ctrl+Shift+Enter sobre una selección predimensionada, compartiendo cada celda una fórmula compilada. Una matriz dinámica es una fórmula única cuyo resultado determina su propia forma. HotXLS las unifica bajo SetArrayFormula: usted siempre define el rectángulo, la raíz posee el árbol compilado y los miembros hacen referencia a él, ya sea que la fórmula sea una expresión de matriz de estilo antiguo o un nuevo SORT. Lo que nunca obtendrá es el efecto de propagación automática de Excel donde la región vuelve a crecer silenciosamente, y tener presente esa diferencia evita que los dos modelos mentales se confundan

Dónde se detiene la compatibilidad con matrices dinámicas

Conocer los límites le ahorra una tarde de depuración. SORT respeta la dirección por clave, de forma ascendente por defecto y descendente cuando el argumento de orden es negativo; la dirección de comparación es un punto en el que vale la pena probar sus propias columnas clave frente a Excel, ya que una comparación invertida devuelve silenciosamente filas en el orden opuesto en lugar de fallar. UNIQUE conserva la primera ocurrencia de cada fila distinta, lo cual el motor confirma escaneando las filas anteriores en lugar de confiar en un conteo bruto de duplicados. La función TABLE (tablas de datos de análisis de hipótesis / What-If Analysis) se reconoce para que realice el viaje de ida y vuelta a través del archivo, pero se evalúa como un marcador de posición porque los resultados de sustitución reales son los valores almacenados en caché que Excel ya guardó, y HotXLS no vuelve a ejecutar la cuadrícula de hipótesis

LET es el límite más marcado a señalar. HotXLS registra LET y cuenta con una ruta de evaluación para ella, pero el analizador (parser) rechaza un nombre simple que no sea un nombre ya definido, por lo que =LET(x;10;x) falla durante el análisis antes de llegar al evaluador. Considere LET como no compatible hasta que el analizador adquiera conciencia del alcance para sus nombres enlazados. Una nota de portabilidad menor: las cadenas de caracteres de fórmulas en estos ejemplos utilizan el separador de lista del motor, el punto y coma, así que hágalo coincidir con el separador que su compilación espere cuando ensamble texto de fórmulas. Las funciones de matriz dinámica cubiertas aquí, desde XLOOKUP y FILTER hasta SORT, UNIQUE, SEQUENCE y XMATCH, se distribuyen en el HotXLS Delphi Excel Component, cuya referencia de fórmulas lista el catálogo completo de funciones y los modos de argumentos admitidos de cada función