Uma tabela do Excel cuja coluna Amount guarda =[@Qty]*[@Price] não armazena [@Qty] em lugar nenhum do arquivo salvo. Descompacte a pasta de trabalho e o elemento <f> no XML da planilha mostra o que o Excel realmente escreve: a grafia longa e qualificada pela tabela Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], porque a gramática de fórmulas no ECMA-376 Parte 1, §18.17 tem a palavra-chave #This Row e não tem @. Uma célula de resumo mais abaixo guarda SUM(Lines[Amount]). Nenhuma das duas strings contém um único endereço de célula, então um engine que só analisa referências A1 não tem nada para calcular até saber onde a tabela está, qual linha carrega os cabeçalhos e em qual linha a própria fórmula vive
Essa última informação é a desconfortável. Uma referência de coluna pode virar um retângulo uma vez por tabela. Uma referência de linha atual não; ela significa uma célula diferente para cada linha em que é copiada
Por que uma referência estruturada não é apenas um intervalo com um nome amigável?
Um nome definido congela um endereço, e uma referência de tabela não. Aponte o nome DataBlock para Sheet1!$A$2:$D$100 e ele permanece aquele retângulo até algo reescrevê-lo. Sales[Amount] significa "a coluna Amount da tabela Sales", seja qual for a extensão da tabela quando a fórmula é avaliada. Adicione vinte linhas à tabela e a soma as cobre; não há referência para ajustar porque a fórmula nunca guardou um endereço em primeiro lugar
O HotXLS resolve essas referências toda vez que compila uma fórmula para avaliação. O TXLSXWorkbook procura a tabela pelo nome no modelo de tabelas da pasta (TXLSXTable.Name ou DisplayName, comparado sem diferenciar maiúsculas), casa a coluna com o texto de cabeçalho guardado em TXLSXTable.Columns, aplica o especificador de item para escolher a faixa de linhas e entrega ao compilador um intervalo absoluto como 'Sales'!$D$2:$D$25. O texto reescrito existe só para aquela compilação. A célula mantém a fórmula exatamente como foi escrita, e como a busca roda contra o TXLSXTable.Range como está no momento do recálculo, uma fórmula escrita antes de a tabela crescer ainda avalia contra a tabela maior
A gramática de retângulos: colunas, spans e especificadores de item
Toda forma de tabela inteira que o HotXLS avalia produz um bloco contíguo. As grafias aceitas são [Col] e a sua gêmea entre colchetes [[Col]], os especificadores de item puros [#Data], [#All], [#Headers] e [#Totals], a forma combinada [[#Data],[Col]], um span dentro de um especificador de item como [[#Data],[Col1]:[Col2]], e um span puro [[Col1]:[Col2]]. Um span pode ser escrito da direita para a esquerda; [[Q2]:[Q1]] resolve para as mesmas duas colunas que [[Q1]:[Q2]]
A faixa de linhas que cada especificador escolhe segue duas flags da tabela. O TXLSXTable.HasHeaderRow decide se a primeira linha do Range é um cabeçalho, e o TXLSXTable.TotalsRowShown decide se a última é uma linha de totais. Uma referência de coluna pura e o [#Data] cobrem as linhas entre essas duas. O [#All] leva o Range completo, cabeçalho e totais incluídos. Peça [#Headers] em uma tabela sem linha de cabeçalho, [#Totals] em uma tabela sem linha de totais, ou uma coluna de uma tabela cujo corpo não tem linhas, e a referência vira #REF!. Isso acompanha o que o Excel mostra para o mesmo pedido, e impede que uma referência impossível silenciosamente vire um intervalo vazio que soma zero
Nomes de coluna que contêm colchetes, cerquilha, arroba ou apóstrofo seguem a regra de escape do Excel: cada caractere desse tipo é prefixado com um apóstrofo dentro da referência, então um cabeçalho chamado Unit [USD] é escrito Sales[Unit '[USD']]. As formas de coluna única e de linha atual decodificam esses escapes antes de casar. Spans e as formas combinadas [#Data],[Col] resolvem contra tabelas na própria planilha da fórmula, então uma planilha de resumo que precisa de um span sobre a tabela de outra planilha é melhor servida por uma referência de coluna pura por coluna
O exemplo abaixo constrói uma tabela de vendas de 24 linhas e coloca cinco fórmulas de resumo ao lado dela em F2 a F6, na mesma planilha da tabela. A linha 1 é a linha de cabeçalho, as linhas 2 a 25 são o corpo, e não há linha de totais, então SalesTable[#All] abrange 25 linhas enquanto SalesTable[Amount] abrange 24
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Sales');
Cols.Add('Region');
Cols.Add('Q1');
Cols.Add('Q2');
Cols.Add('Amount');
Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
// ... escreva a linha de cabeçalho e 24 linhas de dados ...
Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Valores esperados para esse layout: F5 é 25 e F6 é 4, uma contagem por célula de cabeçalho. F2 a F4 dependem dos dados que você escreve
Como [@Column] sabe qual linha ler?
Uma referência de linha atual toma a sua linha da célula que está sendo avaliada. Quando o Recalculate alcança uma fórmula contendo Lines[@Qty] na linha 7, o HotXLS a resolve para a coluna Qty da linha 7; o mesmo texto de fórmula na linha 8 lê a linha 8. A grafia longa que o Excel grava no disco, Lines[[#This Row],[Qty]], é tratada como a mesma referência, e o Lines[@[Unit Price]] também, para um cabeçalho que contém um espaço
A forma não qualificada [@Qty], sem nome de tabela na frente, precisa de mais um fato: a qual tabela pertence. O HotXLS responde isso por posição. Se a célula em avaliação está dentro das linhas de corpo e do span de colunas de uma tabela na mesma planilha, essa tabela fornece a coluna, e dentro de tal tabela o [@[Q1]:[Q2]] vira um intervalo de uma linha através dessas colunas. Em qualquer outro lugar a referência fica sem resolver. Um nome definido cuja fórmula usa uma referência de linha atual toma a sua linha da célula que se refere ao nome, que é o mesmo comportamento que uma fórmula teria inline
Mantenha fórmulas de linha atual nas próprias linhas de corpo da tabela, e qualifique-as com o nome da tabela quando o código as gerar. Referências qualificadas sobrevivem a mudanças de lugar, elas se leem da mesma forma que o Excel as guarda, e uma fórmula nas próprias linhas da tabela é o único caso em que o Excel e qualquer outro consumidor do arquivo concordam sobre o que o @ significa
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Orders');
Cols.Add('Item');
Cols.Add('Qty');
Cols.Add('Price');
Cols.Add('Amount');
Sheet.Tables.Add('Lines', 'A1:D13', Cols);
// ... escreva a linha de cabeçalho e itens, quantidades, preços nas linhas 2..13 ...
// Um texto de fórmula, doze resultados diferentes: cada célula lê a própria linha
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Checagem cruzada sob a tabela: total da coluna contra um produto recalculado
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Esperado: D15 avalia para 0 quando toda célula Amount casa com Qty * Price
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
A checagem em D15 é barata e vale deixar em um relatório gerado enquanto ele está sendo construído. Uma fórmula por linha que pegasse a linha errada faria os dois totais discordarem, e a diferença aparece como um número diferente de zero em uma célula que qualquer um pode ler
O que acontece quando a tabela muda de forma
Crescer é o caso fácil, contanto que a definição da tabela cresça de fato. Escrever um valor na linha 26 sob uma tabela cujo Range é A1:D25 não faz a linha 26 virar parte da tabela. Auto-expansão é uma feature de edição do Excel disparada por alguém digitando ao lado da tabela, e escrever células via HotXLS não a dispara. Defina o TXLSXTable.Range para a nova extensão depois de uma importação, ou insira as linhas dentro da tabela com o TXLSXWorksheet.InsertRows, que desloca o Tables[i].Range junto com toda outra referência na planilha. De qualquer forma o [#Data] se alarga e nenhuma fórmula muda
Encolher é onde referências podem quebrar, e o HotXLS segue a regra do Excel. Um DeleteCols sobre uma coluna de tabela reescreve toda referência estruturada que nomeia essa coluna para #REF!, em fórmulas de células, formatos condicionais, regras de validação de dados, séries de gráficos e nomes definidos. Um DeleteRows que remove a linha de cabeçalho exclui a tabela e invalida as referências dela da mesma forma, e um que remove a linha de totais limpa o TotalsRowShown, após o qual o [#Totals] não tem mais linha nenhuma para apontar. Referências A1 comuns passam pelo mesmo ajuste, descrito em ajuste de referências de fórmula na inserção e exclusão
O TXLSXTables.DeleteByName e a renomeação via TXLSXTable.Name atuam apenas no modelo de tabelas. Fórmulas que mencionam o nome antigo mantêm o texto, então renomeie essas fórmulas na mesma passada quando o seu código renomear uma tabela
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Table: TXLSXTable;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('sales.xlsx') <> 1 then Exit;
Sheet := Book.Sheets[1]; // planilhas da pasta são 1-based
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... escreva 20 linhas importadas em A26:D45 ...
Table.Range := 'A1:D45'; // o corpo agora vai da linha 2 à linha 45
Book.Recalculate; // SUM(SalesTable[Amount]) em F2 agora cobre 44 linhas
// Busca estilo recordset sobre o corpo da tabela, resultado de linha 1-based, 0 = nada
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log é rotina sua
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
O FindFirst e o FindNext comparam strings sem diferenciar maiúsculas e números por valor, e varrem apenas as linhas de corpo, então o texto de cabeçalho Region nunca é retornado como correspondência
Disciplina de round-trip
Uma pasta carregada com SUM(SalesTable[Amount]) é salva com SUM(SalesTable[Amount]), e uma célula com Lines[[#This Row],[Qty]] sai de volta com esse texto intacto. O endereço resolvido nunca substitui o que o usuário escreveu. Se o HotXLS salvasse SUM($D$2:$D$25) em vez disso, o arquivo abriria no Excel parecendo bom e então pararia de cobrir linhas novas na primeira vez que alguém estendesse a tabela, que é o tipo de quebra mais lento de rastrear até a causa
As definições de tabela em si, incluindo tabelas sem cabeçalho e comentários por tabela, fazem round-trip pelo modelo de tabelas coberto em validação de dados, AutoFilter e tabelas do Excel. Quando muitas células compartilham um padrão de fórmula, o XLSX pode guardá-la uma vez como fórmula compartilhada, e a expansão dessas células está coberta em expansão de fórmula compartilhada si. Uma coluna calculada salva pelo Excel frequentemente chega assim, então uma referência estruturada em um corpo de tabela passa pelos dois caminhos antes de ser avaliada: a fórmula compartilhada se expande para texto por célula primeiro, depois cada célula resolve a própria linha
O modelo de tabelas, o engine Recalculate e as chamadas de inserção e exclusão de planilha usadas aqui fazem parte do HotXLS, que lê e escreve XLS, XLSX e ODS a partir de Delphi e C++Builder sem instalação do Excel. A referência completa da API está na página do componente de planilhas Delphi da HotXLS