Um nome definido é um rótulo que representa uma constante, um intervalo de células ou uma expressão de fórmula, armazenado uma vez no livro e referenciado simbolicamente em todos os locais onde é necessário. Escreva TaxRate numa fórmula e o motor de cálculo resolverá a referência para o conteúdo do nome, quer seja o literal 0.08 ou o intervalo Data!$A$2:$D$100. A referência entre folhas é a ideia complementar: Data!D2 acede a uma célula noutra folha qualificando o endereço com o nome dessa folha. Combine os dois conceitos e uma folha de resumo poderá somar os dados de uma folha de detalhe através de um nome que nunca faz menção a um endereço físico, precisamente o que se pretende num livro de Excel gerado por código e posteriormente auditado por um contabilista.
O HotXLS, a biblioteca Delphi nativa da losLab para ficheiros XLS e XLSX, expõe a tabela de nomes de ambos os formatos com acesso de criação, pesquisa e eliminação, a par de um motor de cálculo que resolve nomes e referências entre folhas em tempo de execução. Os dois formatos mantêm hierarquias de classes distintas, e as diferenças entre as respetivas APIs de nomes constituem o fator que mais afeta a portabilidade do código entre eles.
Dois armazenamentos de nomes que não partilham uma interface
Do lado XLS, a função TXLSWorkbook.GetNames devolve uma coleção IXLSNames, cuja sobrecarga Add(Name, RefersTo, Visible) escreve um nome na tabela de nomes BIFF. As entradas individuais são devolvidas como objetos IXLSName contendo as propriedades Name, RefersTo, o intervalo resolvido RefersToRange e um método Delete. Do lado XLSX, a coleção TXLSXWorkbook.DefinedNames é uma coleção TXLSXDefinedNames com os métodos Add, FindByName e DeleteByName.
As convenções de pesquisa diferem de um modo que se manifesta em tempo de execução e não durante a compilação. A propriedade padrão Item da coleção XLS aceita um Variant, fazendo com que tanto Names[0] como Names['TaxRate'] sejam resolvidos corretamente. A coleção XLSX não possui essa propriedade padrão; é necessário chamar FindByName('TaxRate'), que devolve nil caso o nome não exista. O código escrito para uma das fachadas compila para a outra apenas por mero acaso, e o erro manifesta-se normalmente como um acesso a um apontador nulo (nil access) em execução e não como um alerta vermelho no editor de código da IDE.
O escopo é a decisão inicial, não uma opção adicionada mais tarde
Um nome definido pode ter escopo ao nível do livro, sendo visível em fórmulas de qualquer folha, ou ao nível da folha, restringindo-se à folha proprietária. Na API do XLSX, a distinção reside num único parâmetro opcional. DefinedNames.Add(AName, AFormula) cria um nome ao nível do livro, enquanto Add(AName, AFormula, ASheetIndex) associa-o a uma folha específica. Ao efetuar a leitura, TXLSXDefinedName.SheetIndex devolve -1 para nomes com escopo de livro e o índice baseado em 0 da folha nos restantes casos.
O escopo atua também como a sua política de colisões, sendo importante defini-lo antes de escrever o primeiro nome. O Excel permite configurar um nome local Total em cada folha e um nome global Total ao nível do livro, e a fórmula numa folha resolverá primeiro a referência local. Os livros gerados por código devem tirar partido disto de forma intencional. As regras de negócio que afetam várias folhas, como taxas de impostos, taxas de câmbio (FX rates) e o período de relatório, pertencem ao escopo do livro. Os intervalos auxiliares de uso exclusivo numa folha devem ter escopo local de folha, onde não interferem com outros nomes nem são sobrepostos.
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;Um nome definido não necessita de apontar para um intervalo de células. O nome TaxRate acima aponta para a constante simples 0.08, sendo este o método mais limpo de expor uma regra de negócio. Surge uma vez no Gestor de Nomes do Excel, todas as fórmulas o referenciam simbolicamente, e qualquer alteração de taxa no trimestre seguinte exige a edição de uma linha no gerador de relatórios e não uma pesquisa em catorze strings de fórmulas construídas.
O sinal de igual que pertence a apenas um dos lados
A definição de fórmulas é a área onde a migração de código gera mais quebras, porque as duas fachadas divergem quanto ao uso do sinal de igual. As células XLS recebem fórmulas através de Value antecedido de =. As células XLSX possuem a propriedade dedicada Formula, que aceita a expressão sem o prefixo. Escrever '=SUM(A1:A10)' na propriedade TXLSXCell.Formula faz com que o sinal de igual passe a integrar a expressão e não atue como marcador, pelo que o ficheiro resultante não funcionará da mesma forma que no 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;O trecho de código acima demonstra duas peculiaridades adicionais do lado XLS. A coleção de folhas baseia-se em 1, pelo que Sheets[1] corresponde à primeira folha, contrastando com o índice baseado em 0 do XLSX Sheets[0]. E o terceiro parâmetro da função Add gera um nome oculto: ativo no ficheiro e processável por fórmulas, mas invisível no Gestor de Nomes do Excel. Os nomes ocultos são os indicados para a estrutura lógica interna do gerador de ficheiros, impedindo edições acidentais pelos utilizadores finais.
Referências entre folhas e as consequências da movimentação de linhas
Ambos os motores de cálculo aceitam a sintaxe padronizada de referências entre folhas. Nomes de folhas sem espaços qualificam-se diretamente como Data!A1; nomes contendo espaços ou pontuação exigem aspas simples, como 'Sheet With Space'!A1. Na definição RefersTo de um nome, recorra a referências absolutas como Data!$A$2:$D$100 na maior parte dos casos. Uma referência relativa no interior de um nome definido é resolvida em relação à célula que o utiliza, uma funcionalidade nativa do Excel que constitui fonte frequente de erros quando ativada sem intenção.
A edição estrutural é a área onde a integridade das referências entre folhas se revela valiosa, e o lado XLSX preserva a consistência dos nomes através destas operações. O InsertRows e o DeleteRows deslocam os intervalos de nomes definidos em conjunto com células, uniões, hiperligações e âncoras de gráficos, garantindo que o nome a apontar para Data!$A$2:$D$100 continue a abranger o bloco de dados após a abertura de linhas adicionais. Em relação a fórmulas, aplica-se uma regra documentada: a inserção de linhas só altera as referências que apontem para a folha editada. Uma fórmula em Summary que aponte para Data!D2:D100 é reescrita quando são inseridas linhas em Data, o que costuma ser o comportamento pretendido. Valide os resultados em vez de os presumir, recorrendo ao motor da biblioteca:
// 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));A função Calculate avalia uma expressão arbitrária face ao estado atual do livro sem efetuar qualquer gravação, sendo ideal para asserções em testes do gerador. Calcule o resultado esperado a partir dos dados de origem em Pascal, execute a fórmula do livro e compare os dois valores. O artigo sobre o motor de fórmulas aborda os detalhes de avaliação e a integração de funções personalizadas.
Os nomes _xlnm sob gestão da camada de propriedades
Se analisar a tabela de nomes de um ficheiro gerado com um inspetor de baixo nível, encontrará registos que não criou: _xlnm.Print_Area, _xlnm.Print_Titles e afins. Trata-se da forma como a especificação OOXML (ECMA-376 / ISO 29500) armazena as áreas de impressão e as linhas de títulos repetidas, recorrendo a nomes definidos com identificadores reservados. O HotXLS gere-os através de propriedades de folhas de cálculo dedicadas, pelo que configurar PrintArea ou PrintTitleRows grava o registo _xlnm.* correspondente de forma automática.
O risco reside em tentar interagir com esse namespace reservado manualmente. Se adicionar um registo _xlnm.Print_Area através de DefinedNames.Add enquanto configura a propriedade PrintArea, o livro resultante conterá duas definições em conflito para o mesmo nome reservado, um estado de inconsistência que o Excel resolve sem garantias de consistência. Todos os identificadores que comecem com _xlnm. pertencem à camada de propriedades da biblioteca. Para verificar as definições de impressão, leia as propriedades e não a tabela de nomes. O artigo sobre proteção e configuração de página detalha estas propriedades.
Duas limitações relevantes antes de estruturar o projeto
Os nomes definidos não são migrados através do processo de ponte simplificado de XLS para XLSX. A função SaveXLSWorkbookAsXLSX copia apenas conteúdos de células e formatações básicas, não incluindo a tabela de nomes nas suas especificações de cópia, pelo que um livro que dependa de nomes definidos perderá essa informação no processo. Recrie os nomes através de DefinedNames.Add após a conversão. Trata-se de um processo simples que permite normalizar o escopo dos nomes em vez de herdar o estado do ficheiro XLS.
A outra limitação diz respeito ao desajuste entre as strings de fórmulas e os nomes das folhas de cálculo. O Excel reescreve as referências de folhas nas fórmulas e nomes durante uma redenominação interativa, preservando a consistência dos ficheiros editados visualmente. O risco reside no lado do gerador automático: quando o código Pascal constrói fórmulas a partir de strings literais com nomes de folhas, alterar o nome da folha num local e não no outro gera referências a folhas inexistentes. Mantenha o nome da folha numa constante Delphi única e passe-a tanto a Sheets.Add como à construção de fórmulas, eliminando discrepâncias. O mesmo se aplica à lógica de atribuir nomes a células de saída em vez de usar endereços fixos: um modelo em que a célula de totais tem um nome continua a funcionar mesmo que um designer insira três linhas acima desta, ao passo que um gerador que escreva no endereço literal B17 registará o valor no local errado. O artigo sobre geração de relatórios orientada a modelos baseia-se exatamente neste padrão.
A API de nomes definidos completa para ambos os formatos, em conjunto com a referência do motor de fórmulas, acompanha o HotXLS Component.