La solicitud de cambio era de una línea: subir la tasa de impuesto de 8% a 8.5%. Sin embargo, el generador del libro tenía la tasa anterior incrustada como el literal 0.08 dentro de catorce cadenas de fórmula armadas por código Pascal, más dos apariciones en una plantilla que nadie recordaba haber editado. Las constantes y direcciones de celda hard-coded dentro de fórmulas generadas son el equivalente de las hojas de cálculo a los números mágicos, excepto que estos números mágicos se envían a clientes y son auditados por sus contadores. Los nombres definidos existen para eliminar exactamente este modo de falla, y HotXLS, la biblioteca nativa Delphi de losLab para archivos XLS y XLSX, expone la tabla de nombres de ambos formatos con acceso completo para crear, buscar y eliminar.
Este artículo muestra cómo funcionan los dos almacenes de nombres, por qué el alcance es la primera decisión y no una ocurrencia tardía, cómo interactúan las fórmulas entre hojas con los nombres y qué nombres integrados deben dejar en paz.
Dos almacenes de nombres, un concepto
HotXLS tiene jerarquías de clases separadas para los dos formatos de archivo, y cada una lleva su propia colección de nombres. Del lado XLS, TXLSWorkbook.GetNames devuelve una colección IXLSNames cuyo overload Add(Name, RefersTo, Visible) crea un nombre en la tabla de nombres BIFF; las entradas individuales vuelven como objetos IXLSName con 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 difieren de una forma que muerde durante un port: la propiedad predeterminada Item de la colección XLS acepta un Variant, así que tanto Names[0] como Names['TaxRate'] resuelven, mientras que la colección XLSX espera una llamada explícita FindByName('TaxRate') que devuelve nil cuando el nombre no existe. El código que asume una convención compila contra la otra fachada solo por accidente.
El alcance decide quién puede ver el nombre
Un nombre definido puede tener alcance de libro, visible para fórmulas en todas las hojas, o alcance de hoja, visible solo para fórmulas en su hoja propietaria. En la API XLSX, la distinción es un único parámetro opcional: DefinedNames.Add(AName, AFormula) crea un nombre de nivel libro, mientras que Add(AName, AFormula, ASheetIndex) lo vincula a una hoja. Al leerlo de vuelta, TXLSXDefinedName.SheetIndex devuelve -1 para alcance de libro y el índice de hoja 0-based en los demás casos.
El alcance también es su política de colisiones. Excel permite un Total local en cada hoja más un Total de nivel libro, y una fórmula en una hoja determinada resuelve primero el local. Los libros generados deberían explotar eso deliberadamente: las suposiciones de negocio que consumen varias hojas (tasas de impuesto, tasas FX, periodo de reporte) pertenecen al alcance de libro, mientras que los rangos auxiliares que solo referencian las fórmulas de una hoja son más seguros con alcance de hoja, donde no pueden ocultar ni ser ocultados por nada más.
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Summary := Book.Sheets.Add('Summary');
// ... fill Data!A2:D100 with detail rows ...
Book.DefinedNames.Add('TaxRate', '0.08'); // workbook scope, a constant
Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100'); // workbook scope, a range
Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1); // scoped to sheet index 1 only
// XLSX formulas take no leading '='
Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
Book.SaveAs('model.xlsx');
finally
Book.Free;
end;
end;Noten que un nombre definido no tiene que apuntar a un rango. TaxRate arriba se refiere a la constante 0.08, que es la forma más limpia de publicar una suposición de negocio: aparece una vez en el Administrador de nombres de Excel, cada fórmula la referencia simbólicamente y el cambio de tasa del próximo trimestre es una edición de una línea en el generador.
El signo igual que no está
El bug de porting más común entre las dos fachadas es el canal de entrada de fórmulas. Las celdas XLS reciben fórmulas mediante Value con un = inicial; las celdas XLSX tienen una propiedad dedicada Formula que recibe la expresión sin el prefijo. Escriban '=SUM(A1:A10)' en TXLSXCell.Formula y el signo igual se convierte en parte del texto de expresión almacenado en lugar de ser un marcador: el archivo no se comportará como se comportaba la misma cadena del lado XLS.
var
Book: IXLSWorkbook; // interface-counted: do not Free
Names: IXLSNames;
begin
Book := TXLSWorkbook.Create;
// assume a sheet named 'Data' already holds the detail rows
Names := Book.GetNames;
Names.Add('TaxRate', '0.08');
Names.Add('Helper', 'Data!$A$2:$A$100', False); // False = hidden from the Name Manager
// XLS formulas go through Value, with the '=' prefix
Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
Book.SaveAs('model.xls');
end;Dos detalles más del lado XLS visibles en ese fragmento: la colección de hojas es 1-based (Sheets[1] es la primera hoja, a diferencia de Sheets[0] 0-based de XLSX), y el tercer parámetro de Add crea un nombre oculto: presente en el archivo y usable por fórmulas, pero invisible en el Administrador de nombres de Excel. Los nombres ocultos son el vehículo correcto para plomería interna del generador que los usuarios finales no deberían editar ni eliminar por accidente.
Referencias entre hojas que sobreviven la edición
Ambos motores de fórmulas aceptan la sintaxis estándar entre hojas: Data!A1 para nombres de hoja simples y 'Sheet With Space'!A1 con comillas simples cuando el nombre contiene espacios o puntuación. Dentro del texto RefersTo de un nombre, las referencias absolutas (Data!$A$2:$D$100) casi siempre son lo que quieren: una referencia relativa dentro de un nombre definido se resuelve relativa a la celda que la usa, lo cual es una función deliberada de Excel y una fuente profunda de confusión cuando ocurre sin intención.
Las operaciones estructurales mantienen consistentes los nombres del lado XLSX: InsertRows y DeleteRows desplazan los rangos de nombres definidos junto con celdas, combinaciones, hipervínculos y anclajes de gráfico, de modo 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 espacio encima. La advertencia documentada en el motor aplica a las fórmulas: la inserción de filas ajusta solo referencias que apuntan a la hoja editada. Una fórmula de Summary que referencia Data!D2:D100 se reescribe cuando se insertan filas en Data, pero de todos modos planifiquen el paso de verificación, porque la forma barata de saberlo es preguntarle al motor:
// the calculation engine resolves names and cross-sheet references in-process
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 natural de aserción para pruebas de generadores: calculen el agregado esperado desde los datos fuente en Pascal, evalúen la fórmula propia del libro y comparen. El artículo del motor de fórmulas cubre qué evalúa el motor, cuándo y cómo extenderlo con funciones personalizadas.
Los nombres _xlnm que no deben redefinir
Abran la tabla de nombres de un archivo generado en un inspector de bajo nivel y encontrarán entradas que nunca crearon: _xlnm.Print_Area, _xlnm.Print_Titles y parientes. Así es como OOXML (ECMA-376 / ISO 29500) representa áreas de impresión y filas de título repetidas: son nombres definidos con identificadores reservados. HotXLS los administra mediante las propiedades dedicadas de la hoja: establecer PrintArea o PrintTitleRows escribe la entrada _xlnm.* correspondiente por ustedes.
La trampa es agregar manualmente una entrada _xlnm.Print_Area mediante DefinedNames.Add mientras también se establece la propiedad PrintArea: el libro termina con definiciones conflictivas para un nombre reservado, y el comportamiento de Excel con esos archivos no es algo sobre lo cual construir un producto. Traten cada identificador que empiece con _xlnm. como propiedad de la capa de propiedades; si necesitan inspeccionar la configuración de impresión, lean las propiedades, no la tabla de nombres. El artículo de protección y configuración de página cubre las propiedades de área de impresión en contexto.
Preguntas de tabla de nombres que aparecen en revisión de código
¿Los nombres definidos sobreviven la conversión de XLS a XLSX?
No mediante el puente de conveniencia. SaveXLSWorkbookAsXLSX copia contenido de celda y formato básico, y la tabla de nombres no está en su lista documentada de copia: recreen los nombres mediante DefinedNames.Add después de la conversión, lo que además les da oportunidad de normalizar sus alcances.
¿Qué ocurre con las fórmulas cuando un usuario cambia el nombre de una hoja en Excel?
Excel reescribe referencias de hoja dentro de fórmulas y nombres durante un cambio interactivo de nombre, así que los archivos editados en Excel permanecen consistentes. El riesgo está en su generador: si el código Pascal arma cadenas de fórmula a partir de una constante de nombre de hoja, renombrar la hoja en un lugar y no en el otro produce una referencia a una hoja que ya no existe. Mantener el nombre de hoja en una sola constante Delphi usada tanto para Sheets.Add como para ensamblar fórmulas elimina esa deriva.
¿Las plantillas de reporte deben usar nombres o direcciones fijas?
Nombres, para todo lo que el código de llenado necesite encontrar. Una plantilla cuya celda de total está nombrada sigue funcionando después de que un diseñador inserta tres filas encima; un generador que escribe en B17 hard-coded no. El artículo de generación de reportes con plantillas se basa exactamente en este patrón.
La API completa de nombres definidos para ambos formatos, junto con la referencia del motor de fórmulas, se incluye con HotXLS Component.