Un servicio de reportes escribe SUM(B2:B501) en una celda de totales, guarda el libro y luego exporta la misma hoja a CSV para un importador aguas abajo. El .xlsx abre perfectamente en Excel, con totales y todo; aun así, el CSV contiene el texto literal =SUM(B2:B501) donde debería haber un número. Nada está roto. HotXLS, igual que los propios formatos de archivo, trata una fórmula como texto almacenado más un resultado en caché opcional: Excel evalúa fórmulas cuando abre el archivo, pero un exportador CSV reproduce el contenido de celda tal como está, y nadie en un pipeline solo servidor ejecutó el cálculo. La solución es la parte de HotXLS de la que trata este artículo: un motor de fórmulas integrado que ustedes invocan explícitamente y extienden con sus propias funciones.
HotXLS es una biblioteca nativa de Object Pascal para leer y escribir archivos XLS y XLSX desde Delphi y C++Builder sin automatización de Excel, y su motor de cálculo funciona de la misma forma en ambas fachadas.
Las fórmulas se almacenan, no se evalúan de inmediato
Escribir una fórmula en una celda no calcula nada. Al guardar, el libro registra el texto de fórmula y, del lado XLS, banderas gobernadas por RecalcOnSave, que por defecto es True y le dice a Excel que recalcule al abrir. Ese modelo es correcto para archivos destinados a Excel, e incorrecto para pipelines que consumen valores de celda directamente: exportación CSV, exportación HTML o su propio código leyendo celdas de vuelta. Para esos casos, evalúen explícitamente con Calculate, que existe en cuatro puntos de entrada: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook y TXLSXWorksheet exponen todos function Calculate(const Formula: WideString): Variant.
// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ','); // the CSV now carries the numberLa expresión entregada a Calculate es texto normal de fórmula de Excel: referencias entre hojas, nombres definidos y funciones anidadas se resuelven contra el libro actual en memoria. Eso la hace útil mucho más allá de parchar exportaciones CSV: es un mecanismo de aserción. Un generador que acaba de escribir quinientas filas de detalle puede pedir al libro su propio gran total y compararlo contra la cifra calculada de forma independiente en Pascal, detectando errores off-by-one en rangos antes de que lo haga el auditor de un cliente.
También enmarca la estrategia correcta de pruebas para salidas cargadas de 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, mantengan un archivo fixture aprobado cuyos valores esperados hayan sido producidos por Excel mismo, y hagan que el pipeline de build evalúe las fórmulas del libro generado con Calculate contra esos fixtures. Las diferencias aparecen entonces como pruebas fallidas en Delphi y no como discrepancias descubiertas por un cliente comparando dos reportes.
Agregar funciones de negocio con OnUserFunction
Cuando el motor encuentra un nombre de función que no reconoce, dispara un evento en lugar de fallar directamente. Asignen OnUserFunction en cualquiera de las clases de libro y podrán resolver la llamada ustedes mismos:
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 arrives as a Variant array
Handled := True;
end;
end;
// wiring and use
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, establezcan Handled := True solo cuando realmente reconocieron el nombre; dejarlo en False permite que el motor continúe su manejo normal de función desconocida, así un handler puede servir a varios libros sin reclamar todo. Segundo, comparen nombres sin distinguir mayúsculas y minúsculas (SameText), porque quienes escriben fórmulas usan discount( y DISCOUNT( indistintamente. Tercero, los argumentos llegan ya evaluados: DISCOUNT(A1) les entrega el valor de A1, no la referencia, así que una función no puede saber de dónde vinieron sus entradas, lo que lleva directamente a la siguiente sección.
Traten el cuerpo del handler con la misma defensiva que cualquier punto de entrada externo. El arreglo Args refleja lo que haya escrito el autor de la fórmula, así que validen el conteo y los tipos de argumento antes de indexarlo, y decidan qué devuelve una llamada inválida: un valor Variant de error o una excepción lanzada. Una excepción dentro del handler se propaga por la llamada Calculate que disparó la evaluación, lo cual es aceptable en un generador estrictamente controlado pero grosero en un servicio que evalúa libros escritos por usuarios; en ese entorno, capturen dentro del handler y devuelvan un centinela que el workflow circundante pueda reconocer y registrar.
Las funciones sensibles a posición necesitan la variante Ex
Algunas funciones dependen legítimamente del lugar donde se evalúan: una tasa que varía por hoja, una búsqueda relativa a fila, un multiplicador por región en hojas regionales. 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
// the same formula yields a different rate on each regional sheet
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 siquiera un poco de su ubicación, conecten el evento Ex desde el inicio; añadir contexto después a un handler que treinta fórmulas ya llaman es mucho más desordenado que elegir la firma correcta el primer día.
Las funciones personalizadas no viajan a Excel
Esta es la decisión de diseño que separa una demo de un producto: una fórmula como DISCOUNT(A1) solo tiene significado mientras estén vivos su proceso Delphi y su handler de evento. Abran el archivo guardado en Excel y DISCOUNT es un nombre desconocido: la celda muestra #NAME? a menos que exista una función VBA o add-in coincidente en la máquina del usuario.
Por eso decidan, por celda, cuál de dos contratos están enviando. Las celdas que el usuario debe ver recalcular en Excel deben limitarse al vocabulario de funciones integradas de Excel. Las celdas cuya lógica es propietaria deben evaluarse en proceso mediante Calculate y persistirse como valores simples; la función personalizada actúa entonces como regla interna de cálculo, no como contenido del archivo. Mezclar ambos contratos persistiendo fórmulas con funciones personalizadas es la opción que genera tickets de soporte de forma confiable.
Hay una ventaja discreta en el contrato de solo valores: protege propiedad intelectual. Una regla de precios evaluada en su proceso Delphi y enviada como número no puede hacerse ingeniería inversa desde el libro como una fórmula visible, y no puede romperse si un usuario edita una celda intermedia. Los generadores de facturas, estados de comisiones y tarjetas de tarifas casi siempre pertenecen a este grupo; los modelos interactivos de what-if, donde se espera que el cliente cambie entradas y vea moverse los totales, son el caso que sí necesita fórmulas vivas, construidas con el vocabulario propio de Excel más nombres definidos.
Modos de cálculo, iteración y R1C1: los diales de la fachada XLS
La fachada XLS expone la configuración de cálculo de nivel BIFF que Excel lee desde el archivo. CalculationMode acepta xlCalcManual, xlCalcAutomatic (el valor predeterminado) o xlCalcAutomaticExceptTables, y determina cómo se comporta Excel después de abrir: un libro modelo con miles de fórmulas suele ser más amable si se entrega en modo manual. EnableIteration (por defecto False) con MaxIterations (por defecto 100) y MaxIterationChange (por defecto 0.001) habilita referencias circulares deliberadas del tipo de convergencia iterativa usado en algunos modelos financieros. ReferenceStyle cambia entre visualización A1 y R1C1, y UseFullPrecision refleja la opción de precisión como se muestra de Excel.
Estas propiedades viven en la fachada XLS porque se mapean a registros BIFF; al generar .xlsx, planifiquen fórmulas que no dependan de configuración iterativa, o calculen los valores convergidos en Delphi y escriban resultados.
Fórmulas de matriz: el punto de entrada público es XLSX
Las fórmulas de matriz estilo CSE heredadas se crean mediante TXLSXRange.SetArrayFormula:
// one array formula spanning 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 se encuentra en una sección private, así que no hay una forma compatible de crear nuevas fórmulas de matriz en archivos .xls: las existentes en archivos abiertos sobreviven intactas a un round-trip, pero los objetivos de generación deberían ser .xlsx siempre que la semántica de matriz forme parte del requisito. Si un entregable .xls heredado realmente necesita comportamiento de matriz, la ruta pragmática es calcular el resultado de matriz en Delphi y escribir los valores individuales.
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 de 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 soportadas, se incluye con HotXLS Component.