O HotXLS avalia agora referências de tabelas estruturadas, pelo que =SUM(Table1[Amount]) produz um número em vez de ser ignorado. O resolvedor trata 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 da análise, enquanto o texto original da fórmula é preservado literalmente na ida e volta
Uma forma está deliberadamente ausente, e é a primeira com que as pessoas se deparam. A abreviatura da linha atual [@Column] não é suportada, por uma razão estrutural que vale a pena compreender em vez de contornar às cegas
Porque não é uma referência estruturada apenas um intervalo com um nome simpático?
Porque um nome definido fixa um endereço e uma referência de tabela não. Escreva DataBlock como um nome que aponta para Sheet1!$A$2:$D$100 e mantém-se esse retângulo até algo o reescrever. Escreva Sales[Amount] e significa "a coluna Amount da tabela Sales", seja qual for a extensão dessa tabela no momento em que a fórmula é avaliada. Acrescente vinte linhas à tabela e a soma passa a cobri-las; não há referência a ajustar porque nunca houve um endereço na fórmula para começar
Essa qualidade simbólica é precisamente o motivo pelo qual a referência não pode ser resolvida por substituição de strings. O resolvedor tem de encontrar a tabela pelo nome na pasta de trabalho, procurar a coluna pelo texto do seu cabeçalho, decidir que linhas o especificador de item pedido cobre, e produzir um retângulo concreto. O HotXLS faz isto durante a compilação da fórmula através do modelo de tabelas, o que explica por que motivo uma fórmula escrita antes de a tabela crescer continua a avaliar-se contra a extensão atual da tabela
A gramática que o HotXLS resolve
A gramática de especificadores 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 parênteses [[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 lhe dá é toda a forma de referência que produz um único bloco contíguo: uma coluna, uma sequência de colunas adjacentes, uma fatia apenas do corpo ou incluindo o cabeçalho de qualquer uma delas. Uniões não adjacentes e resultados multiárea ficam fora dele. Quando uma referência não pode ser resolvida, a fórmula mantém o comportamento anterior de ignorar sem valor em vez de substituir por uma suposição, pelo que uma referência não resolúvel nunca se torna um número errado mas 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);
// ... escrever a linha de cabeçalho e 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;Porque é a forma current-row excluída de propósito?
[@Column] e [#This Row] significam "a célula dessa coluna na linha onde esta fórmula reside". O valor depende, portanto, da posição da célula avaliadora, e não apenas da tabela. Isso é um tipo de referência diferente: não um retângulo que o compilador possa resolver uma vez, mas uma resolução por célula que tem de ser refeita para cada linha que a fórmula ocupa
O HotXLS devolve False a partir do resolvedor de intervalos de tabela para essas formas, o que as encaminha para o caminho de ignorar sem valor. O texto da fórmula é preservado e gravado sem alterações, pelo que uma pasta de trabalho que utilize [@Amount] abre corretamente no Excel após um ciclo de ida e volta pela sua aplicação; apenas o valor calculado pelo HotXLS está ausente. Perante a escolha entre um valor ausente e um valor calculado contra a linha errada, a ausência é aquela que se consegue detetar
O contorno prático é mecânico: numa pasta de trabalho que gere, escreva a referência relativa equivalente em estilo A1, que é o que o Excel armazena internamente para grande parte da lógica com âmbito de tabela. Numa pasta de trabalho que apenas processa, deixe a fórmula intacta e leia o valor em cache que o Excel já armazenou, o que é geralmente o que um pipeline de carregamento e relatório pretende
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
// Pesquisa ao estilo de conjunto de registos sobre o corpo da tabela, resultado de linha em base 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
As referências estruturadas são invalidadas em vez de silenciosamente reapontadas quando aquilo que designam desaparece. Apague uma coluna e as fórmulas que a referenciam são invalidadas da mesma forma que o Excel as invalida; apague ou renomeie a tabela e as referências a ela são tratadas da mesma maneira. Este é o comportamento correto e reflete o ajuste normal de referências, descrito em ajuste de referências de fórmulas ao inserir e apagar, onde a função do motor é manter as fórmulas honestas em vez de as manter apenas com aparência válida
O crescimento de linhas é o caso oposto e não precisa de qualquer ajuste. Como a referência designa a tabela e não um retângulo, acrescentar linhas dentro do intervalo da tabela alarga o que [#Data] cobre sem tocar numa única fórmula. Essa é a propriedade que torna as tabelas úteis num modelo de relatório: a linha de totais continua a somar tudo o que a importação produziu, qualquer que seja o número de linhas resultante
Disciplina de ida e volta
O HotXLS mantém o texto original da fórmula. Uma pasta de trabalho carregada com SUM(SalesTable[Amount]) é gravada com SUM(SalesTable[Amount]), e não com o endereço resolvido SUM(D2:D25). Isto importa mais do que possa parecer: um utilizador que abre a sua saída no Excel espera ver a fórmula que escreveu, e um endereço resolvido converteria silenciosamente um modelo autossuficiente num modelo frágil que deixa de cobrir novas linhas
Duas capacidades relacionadas completam o quadro. As próprias definições de tabela, incluindo tabelas sem cabeçalho e comentários por tabela, sobrevivem à ida e volta através do modelo de tabelas descrito em validação de dados, AutoFilter e tabelas Excel. E quando muitas células partilham um padrão, o XLSX armazena-as uma única vez como uma fórmula partilhada, que é expandida e reemitida conforme abordado em expansão si de fórmulas partilhadas. As referências estruturadas dentro de fórmulas partilhadas passam por ambos os caminhos, pelo que ambos têm de se comportar corretamente, e comportam-se
O HotXLS lê e escreve XLS, XLSX e ODS a partir do Delphi e do C++Builder sem instalação do Excel nem automação do Office, avaliando fórmulas no 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 para Delphi