Um nome definido é uma etiqueta que representa uma constante, um intervalo de células ou uma fórmula, guardado uma única vez no livro de cálculo e referenciado de forma simbólica onde for necessário. Ao escrever TaxRate numa fórmula, o motor resolve a referência para o valor associado ao nome, seja a constante 0.08 ou o intervalo Data!$A$2:$D$100. Por sua vez, uma referência entre folhas de cálculo constitui a ideia complementar: a expressão Data!D2 acede a uma célula noutra folha qualificando o endereço com o nome desta. Combinando estes dois conceitos, uma folha de resumo consegue totalizar dados de uma folha de detalhes através de um nome que nunca indica coordenadas fixas, que é precisamente a abordagem pretendida num livro gerado automaticamente e posteriormente auditado por um contabilista
O HotXLS, a biblioteca nativa Delphi da losLab para arquivos XLS e XLSX, expõe a tabela de nomes de ambos os formatos com permissões de criação, pesquisa e eliminação, disponibilizando também um motor de cálculo que resolve nomes e referências entre folhas internamente. Os dois formatos utilizam hierarquias de classes independentes, e as diferenças entre as suas APIs de gestão de nomes constituem o principal obstáculo na migração de código
Dois repositórios de nomes que não partilham interface
Na faceta XLS, o método TXLSWorkbook.GetNames devolve a coleção IXLSNames, cuja sobrecarga Add(Name, RefersTo, Visible) grava um nome na tabela de nomes BIFF. Cada registro é retornado como um objeto IXLSName contendo as propriedades Name, RefersTo, o intervalo resolvido RefersToRange e o método Delete. No lado XLSX, a propriedade TXLSXWorkbook.DefinedNames é uma coleção de tipo TXLSXDefinedNames com os métodos Add, FindByName e DeleteByName
As convenções de pesquisa divergem de uma forma que se manifesta em tempo de execução e não na compilação. A propriedade padrão Item na coleção XLS aceita o tipo Variant, permitindo resolver as chamadas Names[0] e Names['TaxRate']. A coleção XLSX, contudo, não possui essa propriedade padrão: deve invocar o método FindByName('TaxRate'), que devolve nil se o nome estiver ausente. O código escrito para uma faceta apenas compilará na outra por mero acaso, e a falha manifestar-se-á como um erro de acesso nulo (nil access) em execução e não como um aviso visual no IDE
O âmbito é a primeira decisão e não um marcador a adicionar depois
Um nome definido pode ter o âmbito do livro de cálculo (workbook-scoped), ficando visível para fórmulas em todas as folhas, ou o âmbito da folha (sheet-scoped), restringindo-se à folha que o aloja. Na API XLSX, a diferença reside num único parâmetro opcional: o método DefinedNames.Add(AName, AFormula) cria um nome ao nível do livro, ao passo que Add(AName, AFormula, ASheetIndex) o vincula a uma folha. Ao ler os dados, a propriedade TXLSXDefinedName.SheetIndex devolve -1 para nomes globais e o índice baseado em 0 da folha nos restantes casos
O âmbito também funciona como a sua política de colisões de nomes, devendo ser definido de início. O Excel permite ter um nome local Total em cada folha em simultâneo com um nome global Total, e as fórmulas de cada folha resolvem a referência local em primeiro lugar. Os livros gerados devem tirar partido deste comportamento: variáveis partilhadas entre várias folhas (como taxas de impostos, câmbios e períodos de relatório) devem ser declaradas ao nível do livro; intervalos auxiliares referenciados por fórmulas de uma única folha devem ter âmbito local, evitando sobreposições de nomes
Um nome definido não tem obrigatoriamente de apontar para um intervalo de células. No exemplo acima, TaxRate refere-se à constante 0.08, sendo a via mais clara para definir uma variável. O nome surge uma única vez no Gestor de Nomes do Excel, todas as fórmulas o referenciam de forma simbólica e futuras alterações na taxa exigem apenas a alteração de uma linha de código no gerador e não a substituição de strings em dezenas de fórmulas
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;O sinal de igual que pertence apenas a uma das interfaces
O fluxo de escrita de fórmulas é onde ocorrem mais falhas na migração de código devido a divergências na utilização do sinal de igual. Na interface XLS, as células recebem fórmulas através de Value iniciando com o caractere =. Por outro lado, as células XLSX utilizam a propriedade Formula, que recebe a expressão sem o sinal inicial. Se escrever '=SUM(A1:A10)' na propriedade TXLSXCell.Formula, o sinal de igual será tratado como parte do texto da fórmula e não como marcador, fazendo com que a célula não seja avaliada corretamente
O exemplo demonstra mais duas particularidades da faceta XLS: a coleção de folhas baseia-se em 1 (pelo que Sheets[1] representa a primeira folha, contrastando com o índice baseado em 0 no XLSX Sheets[0]); o terceiro argumento do método Add cria um nome oculto (presente no arquivo e utilizável por fórmulas, mas invisível no Gestor de Nomes do Excel). Os nomes ocultos são a via indicada para referências internas da aplicação que os usuários finais não devem alterar ou eliminar
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;Referências entre folhas e o comportamento no deslocamento de linhas
Ambos os motores de cálculo suportam a sintaxe padrão de referências entre folhas. Nomes de folhas simples qualificam-se como Data!A1; nomes contendo espaços ou pontuação requerem aspas simples (como em 'Sheet With Space'!A1). Na propriedade RefersTo de um nome, utilize referências absolutas (como Data!$A$2:$D$100) na maioria dos casos. Uma referência relativa num nome definido é resolvida em relação à célula que a invoca, o que constitui uma funcionalidade do Excel mas pode causar comportamentos inesperados se ativada sem intenção
As alterações estruturais são geridas pelo motor de forma consistente no formato XLSX. Os métodos InsertRows e DeleteRows deslocam os intervalos de nomes definidos em conjunto com as células, uniões, hiperligações e âncoras de gráficos; assim, um nome que aponte para Data!$A$2:$D$100 continuará a abranger o bloco de dados mesmo após a abertura de espaço no topo. Contudo, há uma salvaguarda no ajuste de fórmulas: a inserção de linhas apenas atualiza referências direcionadas para a folha em edição (uma fórmula em Summary que aponte para Data!D2:D100 é atualizada quando são inseridas linhas em Data). Valide este comportamento no código:
O método Calculate avalia uma expressão arbitrária contra o estado atual do livro sem efetuar gravações, servindo como rotina de validação em testes de geração de relatórios. Calcule o valor agregado esperado a partir dos dados de origem em Pascal, execute a fórmula do livro e compare ambos. O artigo sobre o motor de fórmulas aborda o que o motor avalia, quando o faz e como estendê-lo com funções personalizadas
// 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));Os nomes _xlnm geridos pela camada de propriedades
Se abrir a tabela de nomes de um arquivo gerado num analisador de baixo nível, encontrará registros não criados pelo seu código (como _xlnm.Print_Area, _xlnm.Print_Titles e afins). Estas são as nomenclaturas reservadas da especificação OOXML (ECMA-376 / ISO 29500) para armazenar áreas de impressão e títulos repetidos. O HotXLS gere estes elementos através de propriedades específicas da folha de cálculo; assim, definir PrintArea or PrintTitleRows grava automaticamente a respetiva entrada _xlnm.*
A armadilha consiste em manipular este espaço de nomes reservado manualmente. Adicionar uma entrada _xlnm.Print_Area através de DefinedNames.Add e, em simultâneo, definir a propriedade PrintArea gera duas definições em conflito para o mesmo nome reservado no livro, um estado que o Excel resolve de forma imprevisível. Considere qualquer identificador iniciado por _xlnm. como pertencente à camada de propriedades. Para inspecionar 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 aborda estas propriedades
Dois limites relevantes antes de desenhar a aplicação
Os nomes definidos não são preservados na conversão rápida de XLS para XLSX. A função SaveXLSWorkbookAsXLSX copia os dados das células e a formatação básica, mas a tabela de nomes não é abrangida; como tal, um livro que dependa de nomes perde-os no processo. Recrie os nomes invocando DefinedNames.Add após a conversão. Este passo permite consolidar e uniformizar o âmbito dos nomes em vez de herdar a estrutura legada do arquivo XLS
Outra salvaguarda diz respeito à sincronização entre strings de fórmulas e nomes de folhas. O Excel atualiza as referências de folhas nas fórmulas e nomes durante uma alteração interativa realizada pelo usuário. Contudo, o risco reside no gerador: se o seu código em Pascal construir strings de fórmulas usando texto fixo para indicar o nome da folha, alterar o nome da folha num local e esquecer a fórmula gerará referências a folhas inexistentes. Declare o nome da folha numa constante única em Delphi e utilize-a tanto no método Sheets.Add quanto na montagem das fórmulas, evitando inconsistências. Esta prática assemelha-se à recomendação de atribuir nomes às células de totais em vez de usar coordenadas fixas: se a célula de total tiver um nome definido no modelo, a fórmula continuará a funcionar mesmo que o designer insira linhas acima dela, ao passo que um gerador que escreva na coordenada fixa B17 colocará os dados no local incorreto. O artigo sobre geração de relatórios com modelos baseia-se neste padrão
A API completa de nomes definidos para ambos os formatos, em conjunto com as especificações do motor de cálculo, é fornecida com o HotXLS Component