Motor de fórmulas HotXLS: funciones personalizadas en Delphi

Una librería de hojas de cálculo que solo almacena cadenas de fórmula y una librería con un motor de fórmulas operativo son dos productos distintos que parecen idénticos hasta el momento en que le pide a uno de ellos un número. La mayor parte del código Delphi para hojas de cálculo nunca nota la diferencia, porque Excel la disimula: escriba SUM(B2:B501) en una celda, guarde, y Excel recalcula el total en el instante en que una persona abre el fichero. Saque a la persona del circuito, pase el mismo libro por una canalización de servidor que exporta directamente a CSV, y la diferencia deja de ser académica. El CSV lleva el texto literal =SUM(B2:B501) donde correspondía un número, porque en ningún momento nada evaluó realmente la fórmula

Esa es la línea en cuyo lado correcto se sitúa HotXLS. Trata una fórmula como lo hacen los formatos de fichero, como texto almacenado más un resultado en caché opcional, de modo que una exportación CSV sin más reproduce la receta y no el plato. Pero también incorpora un motor de cálculo que puede invocar directamente, el mismo motor en las fachadas XLS y XLSX, más un gancho para resolver nombres de función que el motor nunca ha oído. HotXLS es una librería nativa en Object Pascal que lee y escribe XLS y XLSX desde Delphi y C++Builder sin automatización de Excel, y su mitad de cálculo es lo que convierte de nuevo las fórmulas almacenadas en valores bajo demanda

Las fórmulas se almacenan, no se evalúan de forma anticipada

Escribir una fórmula en una celda no calcula nada. Al guardar, el libro registra el texto de la fórmula. En el lado XLS también registra marcas gobernadas por RecalcOnSave, que por defecto es True e indica a Excel que recalcule al abrir. Ese modelo es correcto para ficheros destinados a Excel e incorrecto para canalizaciones que consumen valores de celda directamente, ya sea una exportación CSV, una exportación HTML o su propio código leyendo celdas. Para esos casos, evalúe explícitamente con Calculate. Existe en cuatro puntos de entrada: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook y TXLSXWorksheet exponen todos function Calculate(const Formula: WideString): Variant

// evaluar dentro del proceso y entregar el valor en lugar de la receta
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // el CSV ahora lleva el número

La expresión que se pasa a Calculate es texto de fórmula Excel ordinario. Las referencias entre hojas, los nombres definidos y las funciones anidadas se resuelven contra el libro actual en memoria, lo que hace útil la llamada mucho más allá de parchear exportaciones CSV. Trátela como un mecanismo de aserción. Un generador que acaba de escribir quinientas filas de detalle puede pedirle al libro su propio total general y compararlo con la cifra que calculó de forma independiente en Pascal, atrapando un error de rango de uno antes de que lo haga el auditor de un cliente

También enmarca la estrategia de pruebas correcta para salidas con muchas fórmulas. Excel sigue siendo la implementación de referencia del lenguaje de fórmulas, así que para el puñado de fórmulas con consecuencias de negocio, mantenga un fichero de referencia aprobado cuyos valores esperados hayan sido producidos por el propio Excel, y haga que la canalización de compilación evalúe las fórmulas del libro generado con Calculate contra esas referencias. Las diferencias afloran entonces como pruebas fallidas en Delphi y no como discrepancias descubiertas por un cliente comparando dos informes

Añadir funciones de negocio con OnUserFunction

Cuando el motor encuentra un nombre de función que no reconoce, lanza un evento en lugar de fallar directamente. Asigne OnUserFunction en cualquiera de las dos clases de libro y podrá resolver la llamada usted mismo:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args llega como un array Variant
    Handled := True;
  end;
end;

// conexión y uso
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Tres detalles merecen atención. Primero, ponga Handled := True solo cuando haya reconocido realmente el nombre. Dejarlo en False permite que el motor continúe con su manejo normal de funciones desconocidas, así que un único manejador puede servir a varios libros sin reclamar todo lo que pasa por él. Segundo, compare los nombres sin distinguir mayúsculas con SameText, ya que los autores de fórmulas escriben discount( y DISCOUNT( indistintamente. Tercero, los argumentos llegan ya evaluados: DISCOUNT(A1) le entrega el valor de A1, no la referencia, así que una función no puede saber de dónde vinieron sus entradas. Este último punto prepara la limitación de la que trata la siguiente sección

Trate el cuerpo del manejador con la misma actitud defensiva que cualquier punto de entrada externo. El array Args refleja lo que el autor de la fórmula haya escrito, así que valide el número y los tipos de los argumentos antes de indexarlo, y decida de antemano qué devuelve una llamada inválida: un valor de error Variant o una excepción lanzada. La elección importa porque una excepción lanzada dentro del manejador se propaga hacia fuera a través de la llamada a Calculate que desencadenó la evaluación. Eso es aceptable en un generador estrictamente controlado y de mala educación en un servicio que evalúa libros escritos por usuarios, donde una sola fórmula errónea tumbaría la petición. En ese entorno, capture dentro del manejador y devuelva un valor centinela que el flujo de trabajo circundante pueda reconocer y registrar

Las funciones que dependen de la posición necesitan la variante Ex

Algunas funciones dependen legítimamente de dónde se evalúan. Un tipo que difiere por hoja, una búsqueda relativa a la fila, un multiplicador por región que solo se aplica en las hojas regionales: ninguna de ellas puede responderse solo con los valores de los argumentos. El evento simple no puede expresar eso, así que el motor ofrece OnUserFunctionEx, idéntico salvo por un parámetro adicional:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // la misma fórmula produce un tipo distinto en cada hoja regional
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext lleva SheetIndex, Row y Col de la celda que se evalúa. Si el resultado de una función depende de su ubicación aunque sea mínimamente, conecte el evento Ex desde el principio. Incorporar el contexto después en un manejador al que ya llaman treinta fórmulas es mucho más engorroso que elegir la firma correcta el primer día, y por lo demás los dos eventos son tan parecidos que hay pocas razones para empezar por el más estrecho

Las funciones personalizadas no viajan a Excel

Una función personalizada vive por completo dentro de su proceso. El nombre DISCOUNT significa algo solo mientras su código Delphi y su manejador de eventos están en ejecución. Abra el fichero guardado en Excel y DISCOUNT no es más que un nombre no reconocido; la celda muestra #NAME? a menos que en la máquina del usuario exista por casualidad una función VBA o un complemento coincidente. Este es el hecho de diseño que separa una demostración de un producto entregable, y le obliga a tomar una decisión deliberada en lugar de descubrirla más tarde

Decida, celda por celda, cuál de los dos contratos está entregando. Las celdas que el usuario debe ver recalcularse dentro de Excel tienen que construirse con el vocabulario de funciones propio de Excel y nada más. Las celdas cuya lógica es propietaria deben evaluarse dentro del proceso con Calculate y persistirse como valores simples, de modo que la función personalizada se comporte como una regla de cálculo interna y no como contenido del fichero. El modo de fallo que genera tickets de soporte con fiabilidad es el término medio: persistir una fórmula con función personalizada y esperar que Excel la respete

El contrato de solo valores tiene una ventaja discreta: protege la propiedad intelectual. Una regla de precios evaluada en su proceso Delphi y entregada como número no puede aplicarse ingeniería inversa desde el libro como sí puede una fórmula visible, y un usuario no puede romperla editando una celda intermedia. Los generadores de facturas, los extractos de comisiones y las tarifas pertenecen casi siempre a este grupo. El caso que necesita de verdad fórmulas vivas es el modelo interactivo de hipótesis, donde se espera que el cliente cambie las entradas y vea moverse los totales, y esos tienen que construirse con el vocabulario propio de Excel más nombres definidos

Modos de cálculo, iteración y R1C1: los mandos de la fachada XLS

La fachada XLS expone los ajustes de cálculo a nivel BIFF que Excel lee del fichero. CalculationMode acepta xlCalcManual, xlCalcAutomatic (el valor predeterminado) o xlCalcAutomaticExceptTables, y determina cómo se comporta Excel una vez abierto el fichero. Un libro de modelo con miles de fórmulas suele ser más amable entregado en modo manual, para que el destinatario decida cuándo se produce la tormenta de recálculo. EnableIteration (por defecto False), junto con MaxIterations (por defecto 100) y MaxIterationChange (por defecto 0.001), desbloquea las referencias circulares deliberadas del tipo de convergencia iterativa que aparecen en algunos modelos financieros. ReferenceStyle alterna entre la visualización A1 y R1C1, y UseFullPrecision refleja la opción de precisión según pantalla de Excel

Estas propiedades viven en la fachada XLS porque corresponden a registros BIFF; al generar .xlsx, planifique las fórmulas de modo que no dependan de ajustes iterativos, o calcule los valores convergidos en Delphi y escriba los resultados

Fórmulas matriciales: el punto de entrada público es XLSX

Las fórmulas matriciales heredadas de estilo CSE se crean mediante TXLSXRange.SetArrayFormula:

// una fórmula matricial que abarca A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

El método equivalente existe en la jerarquía de clases XLS pero está en una sección privada, así que no hay forma admitida de crear fórmulas matriciales nuevas en ficheros .xls. Las existentes en ficheros abiertos sobreviven intactas al viaje de ida y vuelta; lo que no puede hacer es crearlas. La regla que se deriva es bastante simple: cuando la semántica matricial forma parte del requisito, apunte a .xlsx. Si un entregable .xls heredado necesita de verdad comportamiento matricial, la vía pragmática es calcular el resultado de la matriz en Delphi y escribir los valores individuales en las celdas

Dos lecturas relacionadas en este sitio: nombres definidos y fórmulas entre hojas cubre la resolución de nombres que realiza el motor, y el artículo sobre exportación CSV y TSV detalla el comportamiento de exportación que hace necesario el cálculo explícito. La referencia completa del motor, incluido el conjunto de funciones admitidas, se distribuye con el componente HotXLS para Delphi