Artigo Técnico

HotXLS: referências estruturadas de tabela no Delphi

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

Diagrama do HotXLS resolvendo a referência estruturada SalesTable[Amount] em Delphi encontrando a tabela pelo nome, casando o cabeçalho da coluna Amount e aplicando o especificador #Data para obter uma faixa concreta
HotXLS compila referências Table[Column] contra o modelo da tabela no momento da análise e as transforma no intervalo concreto atual, então a fórmula continua seguindo a tabela conforme ela cresce

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

Mapa da gramática de referências estruturadas que o HotXLS aceita no Delphi, mostrando quais regiões da tabela as formas #Data, #Headers, #Totals e #All cobrem e como as grafias entre colchetes mapeiam para os mesmos retângulos
Toda forma de tabela inteira cobre uma faixa contígua da tabela, e as variantes entre colchetes resolvem exatamente como as suas grafias puras

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

Fluxograma do HotXLS resolvendo a forma de linha atual [@Amount] no Delphi contra a linha da célula em avaliação enquanto o texto da fórmula faz round-trip sem mudanças, de modo que o Excel lê a mesma referência após salvar
Referências de linha atual são resolvidas por célula contra a linha em que a fórmula está, e o arquivo salvo ainda carrega o texto original da referência para o Excel 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