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

Un nombre definido es una etiqueta que reemplaza a una constante, un rango de celdas o una expresión de fórmula, almacenada una sola vez en el libro de trabajo 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, 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 calificando 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 usted quiere en un libro de trabajo que un generador ensambla y un contador audita después

HotXLS, la biblioteca nativa de losLab para Delphi para archivos 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 al otro

Dos almacenes de nombres que no comparten interfaz

Del 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 regresan como objetos IXLSName que llevan Name, RefersTo, un RefersToRange resuelto y un método Delete. Del lado XLSX, TXLSXWorkbook.DefinedNames es una colección TXLSXDefinedNames con Add, FindByName y DeleteByName

Las convenciones de búsqueda divergen de una forma que aparece durante el porte 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; usted 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 bandera que se agrega 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 que lo posee. En la API de 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 base 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 de trabajo generados deberían apoyarse en eso deliberadamente. Los supuestos de negocio que consumen varias hojas, como tasas de impuestos, tipos de cambio y el período del 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');
    // ... llene Data!A2:D100 con filas de detalle ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // ámbito libro, una constante
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // ámbito 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 que 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, cada fórmula lo referencia simbólicamente, y el cambio de tasa 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 fórmulas a través de Value con un = inicial. Las celdas XLSX tienen una propiedad Formula dedicada que recibe la expresión sin el prefijo. Escriba '=SUM(A1:A10)' en TXLSXCell.Formula y el signo igual se convierte en parte del texto de la expresión almacenada y no en un marcador, y el archivo no se comportará como lo hizo la misma cadena del lado XLS

var
  Book: IXLSWorkbook;   // contado por interfaz: no llame a Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // se asume 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 van 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 rarezas más del lado XLS. La colección de hojas tiene base 1, así que Sheets[1] es la primera hoja, frente al Sheets[0] base 0 de XLSX. Y el tercer parámetro de Add crea un nombre oculto: presente en el archivo y utilizable por las fórmulas, pero invisible en el Administrador de nombres de Excel. Los nombres ocultos son el vehículo correcto para la fontanería interna del generador que los usuarios finales nunca deberían editar ni eliminar 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 califican directamente como Data!A1; un nombre con espacios o 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 confiable de confusión cuando se dispara por accidente

Las ediciones estructurales son donde la contabilidad entre hojas se gana su lugar, y el lado XLSX mantiene los nombres consistentes 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 las anclas 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 abre 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 asumirlo, porque el motor se lo dirá a bajo costo:

// 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 de trabajo sin guardar nada, lo que lo convierte en la primitiva natural de aserción 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 de trabajo y compare los dos. El artículo sobre el motor de fórmulas cubre qué evalúa el motor, cuándo, y cómo extenderlo con funciones personalizadas

Los nombres _xlnm que posee la capa de propiedades

Abra la tabla de nombres de un archivo 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 a través de propiedades dedicadas de la hoja de cálculo, así que fijar PrintArea o PrintTitleRows escribe la entrada _xlnm.* correspondiente por usted

La trampa es meter la mano en ese espacio de nombres reservado. Agregue una entrada _xlnm.Print_Area mediante DefinedNames.Add mientras también fija la propiedad PrintArea y el libro de trabajo lleva dos definiciones en conflicto para un nombre reservado, un estado que Excel resuelve de maneras de las que ningún producto debería depender. Trate cada identificador que empiece con _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 cubre las propiedades del área de impresión en contexto

Dos límites que vale la pena conocer antes de comprometerse con 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 documentada de copia, así que un libro de trabajo que dependía de sus nombres los pierde en el cruce. Vuelva a crear los nombres mediante DefinedNames.Add después de la conversión. Ese paso es menos tedioso de lo que suena, porque le da un momento para normalizar sus ámbitos en lugar de arrastrar lo que el archivo 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 renombrado interactivo, así que los archivos que un usuario edita en Excel se mantienen consistentes por sí solos. La exposición está del 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 lugar y olvidar el otro produce una referencia a una hoja que ya no existe. Mantenga el nombre de la hoja en una única constante de Delphi y aliméntela tanto a Sheets.Add como a su ensamblado de fórmulas, y los dos nunca podrán discrepar. Este 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 inserta tres filas encima, mientras que un generador que escribe en un B17 literal deposita silenciosamente su número en el lugar equivocado. El artículo sobre generación de informes con plantillas se construye exactamente sobre ese patrón

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