O HotXLS avalia fórmulas de matrizes dinâmicas com transbordo (spill) do Excel 365 nativamente no Delphi e C++Builder: entrega-lhe um livro de trabalho (workbook) cujas células contêm XLOOKUP ou FILTER, e o seu motor de fórmulas calcula o conjunto de resultados e transborda-o ao longo do retângulo de saída fixado, os mesmos valores que o Excel produziria. O componente HotXLS para Delphi e Excel modela uma fórmula de transbordo como uma célula raiz que detém o cálculo mais um bloco de células dependentes que leem a partir dela, que é a perspetiva completa de que necessita antes de escrever uma linha de código
Esse modelo é importante porque a tarefa do dia a dia não é abstrata. Um cliente envia um ficheiro .xlsx criado no Excel 365, cujas folhas estão cheias de =FILTER(...) e =XLOOKUP(...), e o seu serviço tem de reproduzir exatamente os mesmos números em modo headless, numa máquina sem Excel instalado, e depois ler os valores transbordados ou gravar uma nova região de transbordo própria. As matrizes dinâmicas mudaram o contrato de cálculo de "uma fórmula, uma célula" para "uma fórmula, um retângulo de células", e o motor do HotXLS segue esse contrato em vez de o simular com uma grelha pré-expandida
Como avaliar o XLOOKUP e o FILTER no Delphi?
O HotXLS processa cada função de matriz dinâmica através de um único ponto de entrada do avaliador, o CalcDynArrayFunc em lxCalc.pas, pelo que toda a família partilha o mesmo caminho de código de transbordo. O conjunto suportado é constituído por XLOOKUP e FILTER (los dois originais), aos quais se juntam o XMATCH, SORT, UNIQUE e SEQUENCE. Cada um devolve uma matriz variante bidimensional (2-D) em vez de um escalar: SEQUENCE(3;2;1;1) gera uma grelha de 3 por 2, o SORT reordenadas as linhas por qualquer coluna-chave de forma ascendente ou descendente, o UNIQUE colapsa linhas duplicadas mantendo a primeira ocorrência, e o XMATCH reporta a posição baseada em 1 de um valor sob os mesmos modos de correspondência exata, com caracteres curinga (wildcards), valor imediatamente menor e valor imediatamente maior do que o XLOOKUP. Ao contrário das funções escalares de distribuição estatística, que devolvem um único número por chamada, estas funções devolvem uma estrutura completa
Para calcular um livro de trabalho que já contém estas fórmulas, abra-o, chame o Recalculate uma vez e leia as células onde o transbordo ocorreu. O TXLSXWorkbook.Recalculate percorre o gráfico de dependências e avalia cada fórmula modificada (dirty) por ordem topológica, pelo que uma raiz de transbordo é calculada uma única vez e os seus elementos são gravados diretamente nas células membros
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
r, c: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('from-customer.xlsx');
Book.Recalculate; // avalia cada fórmula, incluindo transbordos
Sheet := Book.Sheets[1]; // Sheets é baseado em 1
// Lê de volta o bloco onde o XLOOKUP / FILTER transbordou, por exemplo, D2:F9.
for r := 2 to 9 do
for c := 4 to 6 do
Writeln(Sheet.Cells[r, c].Text);
finally
Book.Free;
end;
end;Se o livro de trabalho precisar de uma função que o motor não fornece, o mesmo avaliador permite-lhe ligar a sua própria lógica através do gancho de funções personalizadas de folha de cálculo, e uma função personalizada é livre de devolver uma matriz variante para que transborde exatamente como as predefinidas
Fixar o intervalo de transbordo com o SetArrayFormula
O HotXLS nunca adivinha o tamanho que um transbordo deve ter: o utilizador indica o retângulo de saída, e o TXLSRange.SetArrayFormula fixa a fórmula a esse retângulo. O método compila a fórmula uma vez, armazena a árvore de sintaxe na célula raiz do canto superior esquerdo juntamente com o intervalo fixado, e atribui a cada uma das outras células no retângulo uma fórmula leve que faz uma referência fraca (weak-reference) à raiz. No recálculo, a raiz é avaliada uma vez e os seus elementos de matriz são transmitidos diretamente para cada célula membro, que é também a forma como uma raiz de transbordo aparece como um nó no gráfico de dependências de recálculo incremental. Este é o caminho a utilizar quando a tarefa consiste em gravar uma região de transbordo de volta no ficheiro, em vez de ler uma
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('report.xlsx');
Sheet := Book.Sheets[1];
// Redimensiona o retângulo para o resultado: uma grelha de 3 linhas por 2 colunas.
if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
Book.Recalculate; // preenche A1:B3 com 1..6 ao longo das linhas
Book.SaveAs('report-out.xlsx');
finally
Book.Free;
end;
end;A consequência da fixação explícita é um limite que vale a pena expor claramente: o HotXLS não redimensiona automaticamente um transbordo da forma como o Excel interativo faz. O Excel aumenta e encolhe a região transbordada à medida que as suas entradas mudam e apresenta o erro #SPILL! quando as células de destino estão ocupadas. No HotXLS, o retângulo que passa para o SetArrayFormula é o retângulo que obtém, pelo que deve dimensioná-lo para o resultado que espera e, se uma função produzir mais linhas do que as fixadas, o excedente não terá onde ficar
Transmissão (broadcasting) de elementos da matriz através dos operadores
O HotXLS aplica os operadores aritméticos elemento a elemento quando qualquer um dos lados da operação é uma matriz. O auxiliar ApplyArrayBinaryOp abrange +, -, *, / e ^ sobre matrizes variantes unidimensionais (1-D) e bidimensionais (2-D): um operando escalar é transmitido a cada elemento, duas matrizes devem corresponder em forma ou a operação é rejeitada, e a divisão por zero propaga-se como um erro em vez de causar uma falha. O caminho aritmético escalar permanece intocado, pelo que isto apenas se ativa quando pelo menos um operando é genuinamente uma matriz, tal como um intervalo transbordado ou o resultado de uma função de matriz. Isso significa que uma fórmula como =D2:D13*1.1 fixada sobre uma coluna multiplica cada elemento sucessivamente, e =D2:D13*E2:E13 multiplica duas colunas de forma idêntica posição por posição
// Transmissão escalar: cada célula da fixação obtém D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Duas matrizes de forma idêntica multiplicam elemento a elemento.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;O que faz o operador @ contra uma matriz CSE legada?
O HotXLS lê um @ explícito como o operador de referência-interseção, e não como a interseção implícita de seleção de linha do Excel em tempo real. No motor, A1:A3 @ B1:B3 devolve a célula única onde os dois intervalos se cruzam, e o operador vincula-se com maior prioridade do que o ^ e com menos prioridade do que o %. Uma nota de honestidade acompanha isto: a forma de interseção implícita separada por espaços ainda não é processada em tokens, porque o analisador léxico (lexer) ainda ignora os espaços em branco, pelo que só obtém a interseção através do símbolo @ explícito
A distinção mais profunda é entre uma fórmula de matriz CSE legada e uma matriz dinâmica moderna, e no HotXLS ambas fluem através do mesmo mecanismo de fixação. Uma fórmula de matriz clássica era o bloco {=...} introduzido com Ctrl+Shift+Enter sobre uma seleção pré-dimensionada, com cada célula a partilhar uma fórmula compilada. Uma matriz dinâmica é uma única fórmula cujo resultado determina a sua própria forma. O HotXLS unifica-as sob o SetArrayFormula: o utilizador nomeia sempre o retângulo, a raiz detém a árvore compilada e os membros fazem referência a ela, quer a fórmula seja uma expressão de matriz ao estilo antigo ou um novo SORT. O que nunca obtém é a propagação automática do Excel, onde a região cresce silenciosamente de novo, e manter esta distinção em mente impede que os dois modelos mentais se misturem
Onde o suporte a matrizes dinâmicas termina
Conhecer os limites poupa uma tarde de depuração. O SORT honra uma direção por chave, ascendente por padrão e descendente quando o argumento de ordenação é negativo; a direção da comparação é um ponto em que vale a pena testar as suas próprias colunas-chave contra o Excel, uma vez que uma comparação invertida devolve silenciosamente as linhas na ordem oposta em vez de falhar. O UNIQUE mantém a primeira ocorrência de cada linha distinta, o que o motor confirma através do scan das linhas anteriores em vez de confiar numa contagem simples de duplicados em bruto. A função TABLE (tabelas de dados de análise de cenários / What-If Analysis) é reconhecida de modo a completar a viagem através do ficheiro, mas é avaliada como um marcador de posição (placeholder) porque os resultados reais de substituição são os valores em cache que o Excel já armazenou, e o HotXLS não volta a executar a grelha de simulação
O LET é o limite mais estrito a destacar. O HotXLS regista o LET e suporta um caminho de avaliação para o mesmo, mas o parser rejeita um nome simples que não seja um nome já definido, pelo que =LET(x;10;x) falha na análise antes de o avaliador ser alcançado. Trate o LET como não suportado até que o parser obtenha consciência de âmbito para os seus nomes vinculados. Uma pequena nota de portabilidade: as strings de fórmula nestes exemplos utilizam o separador de lista do motor, um ponto e vírgula, pelo que deve corresponder a qualquer separador que a sua compilação espere quando monta o texto da fórmula. As funções de matriz dinâmica aqui cobertas, desde o XLOOKUP e FILTER até ao SORT, UNIQUE, SEQUENCE e XMATCH, são fornecidas com o HotXLS Delphi Excel Component, cuja referência de fórmulas lista o catálogo completo de funções e os modos de argumento suportados de cada função