O HotXLS agora avalia referências estruturadas de tabela, então =SUM(Table1[Amount]) produz um número em vez de ser ignorado. O resolvedor lida com Table[Column], Table[[Column]], intervalos de colunas como Table[[Q1]:[Q4]], e os especificadores de item [#Data], [#All], [#Headers] e [#Totals], resolvendo cada um contra o modelo de tabelas da pasta de trabalho no momento do parsing, enquanto o texto original da fórmula faz o round-trip literalmente
Uma forma está deliberadamente ausente, e é a primeira que as pessoas encontram. O atalho de linha atual [@Column] não é suportado, por um motivo estrutural que vale a pena entender, em vez de contornar às cegas
Por que uma referência estruturada não é apenas um intervalo com um nome amigável?
Porque um nome definido congela um endereço, e uma referência de tabela não. Escreva DataBlock como um nome apontando para Sheet1!$A$2:$D$100 e ele permanece aquele retângulo até que algo o reescreva. Escreva Sales[Amount] e isso significa "a coluna Amount da tabela Sales", seja qual for a extensão daquela tabela no momento em que a fórmula é avaliada. Adicione vinte linhas à tabela e a soma vai cobri-las; não há referência a ajustar porque nunca existiu, para começar, um endereço na fórmula
Essa qualidade simbólica é exatamente o motivo pelo qual a referência não pode ser resolvida por substituição de string. O resolvedor precisa encontrar a tabela pelo nome na pasta de trabalho, procurar a coluna pelo texto do seu cabeçalho, decidir quais linhas o especificador de item solicitado cobre, e produzir um retângulo concreto. O HotXLS faz isso durante a compilação da fórmula por meio do modelo de tabelas, e é por isso que uma fórmula escrita antes de a tabela crescer ainda é avaliada contra a extensão atual da tabela
A gramática que o HotXLS resolve
A gramática suportada cobre um único resultado retangular e vale a pena declará-la com precisão, porque a documentação do Excel apresenta uma superfície muito maior do que a maioria dos motores implementa. O HotXLS aceita [Col] e a variante entre colchetes duplos [[Col]], os especificadores de item simples [#Data], [#All], [#Headers] e [#Totals], a forma combinada [[#Data],[Col]], um intervalo dentro de um especificador de item como [[#Data],[Col1]:[Col2]], e um intervalo simples [Col1]:[Col2]
O que esse conjunto oferece é toda forma de referência que produz um único bloco contíguo: uma coluna, uma sequência de colunas adjacentes, uma fatia somente do corpo ou incluindo o cabeçalho de qualquer uma delas. Uniões não adjacentes e resultados de múltiplas áreas ficam fora dele. Quando uma referência não pode ser resolvida, a fórmula mantém o comportamento anterior de pular sem valor em vez de substituir por um palpite, então uma referência não resolvível nunca vira um número errado plausível
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);
// ... grave a linha de cabeçalho e as 24 linhas de dados ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Por que a forma de linha atual é excluída de propósito?
[@Column] e [#This Row] significam "a célula daquela coluna na linha onde esta fórmula está". O valor, portanto, depende da posição da célula que está avaliando, não só da tabela. Esse é um tipo diferente de referência: não um retângulo que o compilador pode resolver uma vez, mas uma resolução por célula que precisa ser refeita para cada linha que a fórmula ocupa
O HotXLS retorna False do resolvedor de intervalo de tabela para essas formas, o que as encaminha para o caminho de pular sem valor. O texto da fórmula é preservado e gravado de volta sem alterações, então uma pasta de trabalho que usa [@Amount] abre corretamente no Excel depois de um round-trip pela sua aplicação; apenas o valor calculado pelo HotXLS está ausente. Diante da escolha entre um valor ausente e um valor calculado contra a linha errada, a ausência é a que você consegue detectar
A solução alternativa prática é mecânica: em uma pasta de trabalho que você gera, escreva a referência relativa equivalente no estilo A1, que é o que o Excel armazena internamente de qualquer forma para boa parte da lógica com escopo de tabela. Em uma pasta de trabalho que você apenas processa, deixe a fórmula em paz e leia o valor em cache que o Excel já armazenou, que é o que um pipeline de carregar-e-relatar normalmente quer
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Busca no estilo recordset sobre o corpo da tabela, resultado de linha baseado em 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;O que acontece quando a tabela muda de forma
Referências estruturadas são invalidadas em vez de silenciosamente redirecionadas quando aquilo que elas nomeiam deixa de existir. Exclua uma coluna e as fórmulas que se referem a ela são invalidadas da mesma forma que o Excel as invalida; exclua ou renomeie a tabela e as referências a ela são tratadas da mesma maneira. Esse é o comportamento correto e espelha o ajuste de referência comum, descrito em ajuste de referência de fórmula em inserção e exclusão, onde o trabalho do motor é manter as fórmulas honestas, e não mantê-las com aparência válida
O crescimento de linhas é o caso oposto e não precisa de nenhum ajuste. Como a referência nomeia a tabela, e não um retângulo, anexar linhas dentro do intervalo da tabela amplia o que [#Data] cobre sem tocar em uma única fórmula. Essa é a propriedade que torna as tabelas valiosas em um template de relatório: a linha de totais continua somando tudo o que a importação produziu, não importa quantas linhas isso tenha resultado
Disciplina de round-trip
O HotXLS mantém o texto original da fórmula. Uma pasta de trabalho carregada com SUM(SalesTable[Amount]) é salva com SUM(SalesTable[Amount]), e não com o SUM(D2:D25) resolvido. Isso importa mais do que pode parecer: um usuário que abre a sua saída no Excel espera ver a fórmula que escreveu, e um endereço resolvido converteria silenciosamente um modelo que se auto-mantém em um modelo frágil que deixa de cobrir linhas novas
Dois recursos relacionados completam o quadro. As próprias definições de tabela, incluindo tabelas sem cabeçalho e comentários por tabela, fazem o round-trip por meio do modelo de tabelas descrito em validação de dados, AutoFilter e tabelas do Excel. E quando muitas células compartilham um padrão, o XLSX as armazena uma vez como uma fórmula compartilhada, que é expandida e reemitida, como abordado em expansão de si de fórmula compartilhada. Referências estruturadas dentro de fórmulas compartilhadas passam pelos dois caminhos, então os dois precisam se comportar bem, e se comportam
O HotXLS lê e grava XLS, XLSX e ODS a partir de Delphi e C++Builder sem instalação do Excel e sem automação do Office, avaliando fórmulas em seu próprio motor. O modelo de tabelas, o motor de fórmulas e a API de recálculo estão documentados na página do componente HotXLS Delphi para planilhas