Nombres definidos y fórmulas entre hojas en Delphi (HotXLS)

Un nombre definido es una etiqueta que sustituye a una constante, un rango de celdas o una expresión de fórmula, almacenada una sola vez en el libro y referenciada simbólicamente en todos los lugares donde se necesita. Escriba TaxRate en una fórmula y el motor lo resuelve a lo que contenga la definición del nombre, ya sea el literal 0.08 o el rango Data!$A$2:$D$100. Una referencia entre hojas es la idea ortogonal: Data!D2 alcanza una celda de otra hoja cualificando la dirección con un nombre de hoja. Junte las dos y una hoja de resumen puede sumar una hoja de detalle mediante un nombre que nunca menciona una dirección literal, que es exactamente lo que quiere en un libro que un generador ensambla y un contable audita después

HotXLS, la librería Delphi nativa de losLab para ficheros XLS y XLSX, expone la tabla de nombres de ambos formatos con acceso de creación, búsqueda y eliminación, más un motor de fórmulas que resuelve nombres y referencias entre hojas dentro del proceso. Los dos formatos mantienen jerarquías de clases separadas, y las diferencias entre sus API de nombres son la parte que hace tropezar al código portado de uno a otro

Dos almacenes de nombres que no comparten interfaz

En el lado XLS, TXLSWorkbook.GetNames devuelve una colección IXLSNames cuya sobrecarga Add(Name, RefersTo, Visible) escribe un nombre en la tabla de nombres BIFF. Las entradas individuales vuelven como objetos IXLSName que llevan Name, RefersTo, un RefersToRange resuelto y un método Delete. En el lado XLSX, TXLSXWorkbook.DefinedNames es una colección TXLSXDefinedNames con Add, FindByName y DeleteByName

Las convenciones de búsqueda divergen de una forma que aflora al portar y no en tiempo de compilación. La propiedad predeterminada Item de la colección XLS acepta un Variant, así que tanto Names[0] como Names['TaxRate'] se resuelven contra ella. La colección XLSX no tiene esa propiedad predeterminada; se llama a FindByName('TaxRate'), que devuelve nil cuando el nombre no existe. El código escrito para una fachada compila contra la otra solo por accidente, y el fallo tiende a aparecer como un acceso a nil en tiempo de ejecución y no como un subrayado rojo en el IDE

El ámbito es la primera decisión, no una marca que se añade después

Un nombre definido tiene ámbito de libro, visible para las fórmulas de todas las hojas, o ámbito de hoja, visible solo para las fórmulas de la hoja a la que pertenece. En la API XLSX la distinción es un único parámetro opcional. DefinedNames.Add(AName, AFormula) crea un nombre a nivel de libro, mientras que Add(AName, AFormula, ASheetIndex) lo vincula a una hoja. Al leerlo de vuelta, TXLSXDefinedName.SheetIndex devuelve -1 para el ámbito de libro y el índice de hoja desde 0 en caso contrario

El ámbito funciona también como su política de colisiones, y esa es la razón para decidirlo antes de escribir el primer nombre. Excel permite un Total local en cada hoja más un Total a nivel de libro, y una fórmula en una hoja dada resuelve primero el local. Los libros generados deberían apoyarse en eso deliberadamente. Los supuestos de negocio que consumen varias hojas, como tipos impositivos, tipos de cambio y el periodo de informe, pertenecen al ámbito de libro. Los rangos auxiliares que solo referencian las fórmulas de una hoja son más seguros con ámbito de hoja, donde nada puede ocultarlos y ellos no pueden ocultar nada

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... rellenar Data!A2:D100 con las filas de detalle ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // ámbito de libro, una constante
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // ámbito de libro, un rango
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // limitado solo a la hoja de índice 1

    // Las fórmulas XLSX no llevan '=' inicial
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Un nombre definido no tiene por qué apuntar a un rango. TaxRate arriba se refiere a la constante desnuda 0.08, y esa es la forma más limpia de publicar un supuesto de negocio. Aparece una vez en el Administrador de nombres de Excel, todas las fórmulas lo referencian simbólicamente, y el cambio de tipo del próximo trimestre es una edición de una línea en el generador en lugar de una búsqueda a través de catorce cadenas de fórmula ensambladas

El signo igual que pertenece a un solo lado

El canal de entrada de fórmulas es donde el código portado se rompe con más frecuencia, porque las dos fachadas discrepan sobre el signo igual. Las celdas XLS reciben las fórmulas mediante Value con un = inicial. Las celdas XLSX tienen una propiedad Formula dedicada que toma la expresión sin el prefijo. Escriba '=SUM(A1:A10)' en TXLSXCell.Formula y el signo igual pasa a formar parte del texto de la expresión almacenada en lugar de ser un marcador, y el fichero no se comportará como lo hacía la misma cadena en el lado XLS

var
  Book: IXLSWorkbook;   // contado por interfaz: no hacer Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // se supone que una hoja llamada 'Data' ya contiene las filas de detalle
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = oculto en el Administrador de nombres

  // Las fórmulas XLS pasan por Value, con el prefijo '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Ese fragmento muestra dos peculiaridades más del lado XLS. La colección de hojas empieza en 1, así que Sheets[1] es la primera hoja, frente al Sheets[0] de XLSX que empieza en 0. Y el tercer parámetro de Add crea un nombre oculto: presente en el fichero y utilizable por las fórmulas, pero invisible en el Administrador de nombres de Excel. Los nombres ocultos son el vehículo adecuado para la fontanería interna del generador que los usuarios finales nunca deberían editar ni borrar por accidente

Referencias entre hojas, y qué pasa cuando las filas se mueven

Ambos motores de fórmulas aceptan la sintaxis estándar entre hojas. Los nombres de hoja simples cualifican directamente como Data!A1; un nombre con espacios o signos de puntuación necesita comillas simples, como en 'Sheet With Space'!A1. Dentro del texto RefersTo de un nombre, recurra a referencias absolutas como Data!$A$2:$D$100 casi siempre. Una referencia relativa dentro de un nombre definido se resuelve en relación con la celda que lo usa, lo cual es una función deliberada de Excel y una fuente fiable de confusión cuando se activa por accidente

Las ediciones estructurales son donde la contabilidad entre hojas se gana el sueldo, y el lado XLSX mantiene los nombres coherentes a través de ellas. InsertRows y DeleteRows desplazan los rangos de los nombres definidos junto con las celdas, las combinaciones, los hipervínculos y los anclajes de gráficos, así que un nombre que apunta a Data!$A$2:$D$100 sigue cubriendo el bloque de datos después de que el generador abra un hueco encima. Las fórmulas vienen con una salvedad documentada: la inserción de filas ajusta solo las referencias que apuntan a la hoja que se está editando. Una fórmula de Summary que referencia Data!D2:D100 se reescribe cuando entran filas en Data, que es el caso que normalmente quiere. Verifíquelo en lugar de suponerlo, porque el motor se lo dirá a bajo coste:

// el motor de cálculo resuelve nombres y referencias entre hojas dentro del proceso
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evalúa una expresión arbitraria contra el estado actual del libro sin guardar nada, lo que la convierte en la primitiva de aserción natural para las pruebas de generadores. Calcule el agregado esperado a partir de los datos de origen en Pascal, evalúe la fórmula propia del libro y compare los dos. El artículo sobre el motor de fórmulas explica qué evalúa el motor, cuándo, y cómo ampliarlo con funciones personalizadas

Los nombres _xlnm que posee la capa de propiedades

Abra la tabla de nombres de un fichero generado en un inspector de bajo nivel y encontrará entradas que nunca escribió: _xlnm.Print_Area, _xlnm.Print_Titles y sus parientes. Así es como OOXML (ECMA-376 / ISO 29500) almacena las áreas de impresión y las filas de título repetidas, como nombres definidos con identificadores reservados. HotXLS los gestiona mediante propiedades de hoja dedicadas, así que fijar PrintArea o PrintTitleRows escribe por usted la entrada _xlnm.* correspondiente

La trampa es meter la mano en ese espacio de nombres reservado. Añada una entrada _xlnm.Print_Area mediante DefinedNames.Add mientras también fija la propiedad PrintArea y el libro lleva dos definiciones en conflicto para un mismo nombre reservado, un estado que Excel resuelve de maneras de las que ningún producto debería depender. Trate todo identificador que empiece por _xlnm. como perteneciente a la capa de propiedades. Para inspeccionar la configuración de impresión, lea las propiedades, no la tabla de nombres. El artículo sobre protección y configuración de página trata las propiedades del área de impresión en contexto

Dos límites que conviene conocer antes de comprometer un diseño

Los nombres definidos no viajan a través del puente de conveniencia de XLS a XLSX. SaveXLSWorkbookAsXLSX copia el contenido de las celdas y el formato básico, y la tabla de nombres no está en su lista de copia documentada, así que un libro que dependía de sus nombres los pierde en el cruce. Vuelva a crear los nombres mediante DefinedNames.Add tras la conversión. Ese paso es menos tedioso de lo que parece, porque le da un momento para normalizar sus ámbitos en lugar de arrastrar lo que el fichero XLS tuviera por casualidad

El otro límite es la deriva entre las cadenas de fórmula y los nombres de hoja. Excel reescribe las referencias a hojas dentro de fórmulas y nombres durante un cambio de nombre interactivo, así que los ficheros que un usuario edita en Excel se mantienen coherentes por sí solos. La exposición está en el lado del generador: cuando el código Pascal ensambla cadenas de fórmula a partir de un literal de nombre de hoja, renombrar la hoja en un sitio y olvidar el otro produce una referencia a una hoja que ya no existe. Mantenga el nombre de la hoja en una única constante Delphi y pásela tanto a Sheets.Add como a su ensamblado de fórmulas, y los dos nunca podrán discrepar. Es el mismo instinto que aboga por nombrar las celdas de salida de un informe en lugar de codificar direcciones: una plantilla cuya celda de total está nombrada sigue funcionando después de que un diseñador inserte tres filas encima, mientras que un generador que escribe en un B17 literal coloca en silencio su número en el lugar equivocado. El artículo sobre generación de informes con plantillas se apoya exactamente en ese patrón

La API completa de nombres definidos para ambos formatos, junto con la referencia del motor de fórmulas, se distribuye con el componente HotXLS para Delphi