LBlog de Desenvolvimento de Software losLabNotas de engenharia para desenvolvedores de PDF, Delphi e software de documentos.

Artigo Técnico

Fórmulas de despejo de matriz dinâmica no Delphi com o HotXLS

Componente HotXLS

O HotXLS avalia fórmulas de despejo de matriz dinâmica do Excel 365 nativamente no Delphi e C++Builder: você entrega a ele uma planilha cujas células contêm XLOOKUP ou FILTER, e seu motor de fórmulas calcula o conjunto de resultados e o despeja (spills) no retângulo de saída ancorado, os mesmos valores que o Excel produziria. O componente Excel HotXLS para Delphi modela uma fórmula de despejo como uma célula raiz que possui o cálculo mais um bloco de células dependentes que leem a partir dela, que é a visão geral necessária antes de escrever uma linha de código

Esse modelo é importante porque a tarefa diária não é abstrata. Um cliente envia um arquivo .xlsx criado no Excel 365, suas planilhas estão cheias de =FILTER(...) e =XLOOKUP(...), e seu serviço precisa reproduzir exatamente os mesmos números em modo headless, em uma máquina sem Excel instalado, para então ler os valores despejados ou gravar uma nova região de despejo por conta própria. As matrizes dinâmicas alteraram 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 simular isso com uma grade pré-expandida

Como avaliar XLOOKUP e FILTER no Delphi?

O HotXLS despacha cada função de matriz dinâmica por meio de um único ponto de entrada do avaliador, o CalcDynArrayFunc em lxCalc.pas, de modo que toda a família compartilha o mesmo caminho de código de despejo. O conjunto suportado é XLOOKUP e FILTER (los dois originais), acompanhados por XMATCH, SORT, UNIQUE e SEQUENCE. Cada uma retorna uma matriz de variante bidimensional (2-D) em vez de um escalar: SEQUENCE(3;2;1;1) produz uma grade de 3 por 2, SORT reordena linhas por qualquer coluna de chave ascendente ou descendente, UNIQUE condensa linhas duplicadas mantendo a primeira ocorrência, e XMATCH relata a posição baseada em 1 de um valor sob os mesmos modos de correspondência exata, curinga, próximo menor e próximo maior que o XLOOKUP. Ao contrário das funções escalares de distribuição estatística, que retornam um número por chamada, essas funções devolvem uma forma inteira

Para calcular uma planilha que já carrega essas fórmulas, abra-a, chame o Recalculate uma vez e leia as células onde o despejo ocorreu. O TXLSXWorkbook.Recalculate percorre o grafo de dependência e avalia cada fórmula modificada em ordem topológica, de modo que a raiz de despejo é calculada uma única vez e 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, despejos inclusos
    Sheet := Book.Sheets[1];          // Sheets é baseado em 1
    // Lê de volta o bloco onde o XLOOKUP / FILTER despejou, digamos 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 a planilha precisar de uma função que o motor não fornece, o mesmo avaliador permite conectar sua própria lógica por meio do hook de funções personalizadas da planilha, e uma função personalizada é livre para retornar uma matriz de variante para que ela ocorra o despejo exatamente como as funções integradas

Ancorando o intervalo de despejo com o SetArrayFormula

O HotXLS nunca adivinha o tamanho que um despejo deve ter: você indica o retângulo de saída e o TXLSRange.SetArrayFormula o ancora. O método compila a fórmula uma vez, armazena a árvore de sintaxe na célula raiz superior esquerda junto com o intervalo ancorado e fornece a cada outra célula no retângulo uma fórmula leve que faz uma referência fraca à raiz. Na recalculação, a raiz avalia uma vez e seus elementos de matriz são transmitidos diretamente para cada célula membro, o que também é como uma raiz de despejo aparece como um nó no grafo de dependência de recalculação incremental. Este é o caminho a ser usado quando a tarefa é gravar uma região de despejo de volta no arquivo, em vez de ler uma

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Ajusta o tamanho do retângulo para o resultado: uma grade 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 pelas linhas
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

A consequência da ancoragem explícita é um limite que vale a pena expor claramente: o HotXLS não redimensiona automaticamente um despejo como o Excel interativo faz. O Excel expande e encolhe a região despejada conforme suas entradas mudam e gera um erro #SPILL! quando as células de destino estão ocupadas. No HotXLS, o retângulo que você passa para o SetArrayFormula é o retângulo que você obtém, portanto, ajuste o tamanho dele para o resultado esperado e, se uma função produzir mais linhas do que você ancorou, o excedente não terá onde ficar

Transmissão de matriz elemento a elemento nos operadores

O HotXLS aplica os operadores aritméticos elemento a elemento quando qualquer um dos lados da operação é uma matriz. O auxiliar ApplyArrayBinaryOp cobre +, -, *, / e ^ sobre matrizes variantes 1-D e 2-D: um operando escalar é transmitido para cada elemento, duas matrizes devem coincidir em formato ou a operação é rejeitada, e a divisão por zero se propaga como um erro em vez de travar. O caminho aritmético escalar não é afetado, de modo que isso só é ativado quando pelo menos um operando é genuinamente uma matriz, como um intervalo despejado ou o resultado de uma função de matriz. Isso significa que uma fórmula como =D2:D13*1.1 ancorada em uma coluna multiplica cada elemento sucessivamente, e =D2:D13*E2:E13 multiplica duas colunas de mesmo formato posição por posição

// Transmissão escalar: cada célula da âncora recebe D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Duas matrizes de formato idêntico se multiplicam elemento por elemento.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

O que o operador @ faz em comparação com uma matriz CSE herdada?

O HotXLS lê um @ explícito como o operador de interseção de referência, não como a interseção implícita de seleção de linha do Excel ativo. No motor, A1:A3 @ B1:B3 retorna a única célula onde os dois intervalos se cruzam, e o operador vincula mais fortemente do que ^ e menos fortemente do que %. Uma nota de honestidade acompanha isso: a forma de interseção implícita separada por espaço ainda não foi tokenizada, porque o analisador léxico ainda ignora os espaços em branco, portanto, você obtém a interseção apenas por meio do símbolo @ explícito

A distinção mais profunda é entre uma fórmula de matriz CSE herdada e uma matriz dinâmica moderna, e no HotXLS ambas fluem pela mesma engrenagem de ancoragem. Uma fórmula de matriz clássica era o bloco {=...} inserido com Ctrl+Shift+Enter sobre uma seleção pré-dimensionada, cada célula compartilhando uma fórmula compilada. Uma matriz dinâmica é uma única fórmula cujo resultado determina seu próprio formato. O HotXLS as unifica sob o SetArrayFormula: você sempre indica o retângulo, a raiz possui a árvore compilada e os membros fazem referência a ela, seja a fórmula uma expressão de matriz do estilo antigo ou um novo SORT. O que você nunca obtém é a propagação automática do Excel, onde a região é redimensionada silenciosamente, e manter essa linha em mente evita que os dois modelos mentais se misturem

Onde o suporte a matriz dinâmica para

Conhecer os limites poupa uma tarde de depuração. O SORT respeita uma direção por chave, ascendente por padrão e descendente quando o argumento de ordem é negativo; a direção de comparação é um local que vale a pena testar suas próprias colunas de chave contra o Excel, já que uma comparação invertida retorna linhas na ordem oposta silenciosamente, em vez de falhar. O UNIQUE mantém a primeira ocorrência de cada linha distinta, o que o motor confirma escaneando as linhas anteriores em vez de confiar em uma contagem de duplicatas bruta. A função TABLE (tabelas de dados de análise de cenários) é reconhecida para que faça a ida e volta pelo arquivo, mas ela é avaliada como um marcador porque os resultados de substituição reais são os valores armazenados em cache que o Excel já salvou, e o HotXLS não executa novamente a grade de cenários

O LET é o limite mais acentuado a ser apontado. O HotXLS registra o LET e carrega um caminho de avaliação para ele, mas o parser rejeita um nome puro que não seja um nome já definido, de modo que =LET(x;10;x) falha durante a análise antes mesmo de o avaliador ser alcançado. Trate o LET como não suportado até que o parser ganhe consciência de escopo para seus nomes vinculados. Uma pequena nota de portabilidade: as strings de fórmula nestes exemplos usam o separador de lista do motor, um ponto e vírgula, portanto, coincida com o separador que sua versão espera quando você montar o texto da fórmula. As funções de matriz dinâmica cobertas aqui, desde XLOOKUP e FILTER até SORT, UNIQUE, SEQUENCE e XMATCH, são fornecidas no Componente HotXLS para Delphi, cuja referência de fórmulas lista o catálogo completo de funções e os modos de argumentos substituídos de cada função