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

Artículo técnico

Fórmulas de desbordamiento de matrices dinámicas en Delphi con HotXLS

HotXLS Component

HotXLS evalúa fórmulas de desbordamiento de matrices dinámicas 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, que es todo el panorama que necesita antes de escribir una línea de código

Ese modelo es importante porque la tarea diaria no es abstracta. Un cliente envía un archivo .xlsx creado en Excel 365, cuyas hojas están llenas de =FILTER(...) y =XLOOKUP(...), y su servicio tiene que reproducir exactamente los mismos números de forma headless (sin interfaz gráfica), en una máquina que no tiene Excel instalado, para 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 de HotXLS sigue ese contrato en lugar de simularlo con una cuadrícula preexpandida

¿Cómo evaluar XLOOKUP y FILTER en Delphi?

HotXLS despacha 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 sola ruta de código de desbordamiento. El conjunto compatible está formado por XLOOKUP y FILTER (las dos originales), a las que se unen XMATCH, SORT, UNIQUE y SEQUENCE. Cada una de ellas devuelve una matriz de variantes bidimensional en lugar de un escalar: SEQUENCE(3;2;1;1) produce una cuadrícula de 3 por 2, SORT reorders las filas por cualquier columna clave de forma ascendente o descendente, UNIQUE contrae las filas duplicadas conservando la primera aparición, y XMATCH informa la posición basada en 1 de un valor bajo los mismos modos de coincidencia exacta, comodín, siguiente más pequeño y siguiente más grande que XLOOKUP. A diferencia de las funciones escalares de distribución estadística, que devuelven un solo número por llamada, estas funciones devuelven una estructura completa

Para calcular un libro que ya contiene estas fórmulas, ábralo, llame a Recalculate una vez y lea las celdas donde aterrizó el desbordamiento. TXLSXWorkbook.Recalculate recorre el grafo de dependencias y evalúa cada fórmula sucia en orden topológico, de modo 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 conexión para funciones de hoja de trabajo personalizadas, y una función personalizada es libre de devolver una matriz de variantes para que se desborde exactamente igual que las integradas

Anclaje del 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 con una referencia débil a la raíz. En la recalculación, la raíz se evalúa una vez y sus elementos de matriz se transmiten directamente a cada celda miembro, que es también como se muestra una raíz de desbordamiento como un nodo en el grafo de dependencias de recalculación incremental. Esta es la ruta que debe utilizar cuando la tarea sea escribir una región de desbordamiento de vuelta en el archivo en lugar de leer una

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 exponer claramente: HotXLS no redimensiona automáticamente 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 pasa a SetArrayFormula es el rectángulo que obtiene, por lo que debe dimensionarlo según el resultado que espera, y si una función produce más filas de las que ancló, el excedente no tendrá dónde aterrizar

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 de variantes de 1D y 2D: 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 provocar un bloqueo. La ruta aritmética escalar no se toca, por lo que esto solo se activa cuando al menos uno de los operandos es genuinamente 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 filas del Excel activo. En el motor, A1:A3 @ B1:B3 devuelve la celda única donde se cruzan los dos rangos, y el operador se asocia con mayor prioridad que ^ y menor que %. Una nota de honestidad acompaña a esto: la forma de intersección implícita separada por espacios aún no está tokenizada, porque el analizador léxico todavía ignora los espacios en blanco, por lo que la intersección solo se obtiene a través del símbolo explícito @

La distinción más profunda se da entre una fórmula de matriz CSE heredada y una matriz dinámica moderna, y en HotXLS ambas fluyen a través de la misma maquinaria de anclaje. Una fórmula de matriz clásica era el bloque {=...} introducido con Ctrl+Shift+Enter sobre una selección de tamaño preestablecido, compartiendo cada celda una única 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 ella, 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 por el cual la región vuelve a crecer silenciosamente, y tener clara esa diferencia evita que los dos modelos mentales se confundan

Dónde se detiene el soporte de matrices dinámicas

Conocer los límites le ahorrará una tarde de depuración. SORT respeta la dirección para cada clave, de forma ascendente de manera predeterminada y descendente cuando el argumento de orden es negativo; la dirección de comparación es un aspecto que vale la pena comprobar comparando sus propias columnas clave con Excel, ya que una comparación invertida devuelve silenciosamente las filas en el orden opuesto en lugar de fallar. UNIQUE conserva la primera aparición de cada fila distinta, lo que el motor confirma escaneando las filas anteriores en lugar de confiar en un recuento bruto de duplicados. La función TABLE (tablas de datos de análisis Y si...) se reconoce de forma que se mantiene en la ida y vuelta a través del archivo, pero se evalúa con un marcador de posición porque los resultados reales de la sustitución son los valores almacenados en caché que Excel ya guardó, y HotXLS no vuelve a ejecutar la cuadrícula de simulación

LET es el límite más estricto que se debe advertir. HotXLS registra LET y dispone de una ruta de evaluación para este, pero el analizador sintáctico 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 que se alcance el evaluador. Considere LET como no compatible hasta que el analizador sintáctico adquiera reconocimiento de ámbito para sus nombres vinculados. Una pequeña nota de portabilidad: las cadenas de fórmula en estos ejemplos utilizan el separador de listas del motor, un punto y coma, por lo que debe coincidir con cualquier separador que espere su compilación cuando arme el texto de la fórmula. Las funciones de matriz dinámica cubiertas aquí, desde XLOOKUP y FILTER hasta SORT, UNIQUE, SEQUENCE y XMATCH, se incluyen en HotXLS Delphi Excel Component, cuya referencia de fórmulas detalla el catálogo completo de funciones y los modos de argumento admitidos para cada una