Execute AutoFilters do Excel sem o Excel em Delphi: HotXLS

O HotXLS avalia os critérios de AutoFilter já armazenados em um arquivo XLSX sem iniciar o Excel. Desde a v2.384.136, TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter e AutoFilterRowVisible executam listas de valores, grupos de datas, filtros Top/Bottom-n e porcentagem, filtros de data relativa e de média, filtros de cor de fill e de fonte e filtros de ícone de formatação condicional, sob uma data de referência congelada e limites explícitos de recursos

O gatilho habitual é uma pasta de trabalho que uma pessoa filtrou no Excel e um servidor que precisa agir sobre ela. Alguém define "Top 10 por receita", "Este mês" na data do pedido e um fill vermelho nas linhas vencidas, salva, e envia o arquivo. Seu serviço Delphi agora precisa do conjunto de linhas que o Excel mostraria: para contá-lo, exportá-lo, ou gravar o arquivo de volta com as linhas certas ocultas. O arquivo guarda apenas a definição. Antes desta versão o HotXLS sabia avaliar critérios customizados comuns, mas os tipos avançados ficavam no XML armazenado sem nada executando-os, então a resposta honesta era "abra no Excel"

O que o HotXLS realmente executa de um AutoFilter salvo?

O HotXLS executa um tipo de filtro por coluna, tirado do elemento filterColumn que o Excel gravou, e combina colunas com AND, exatamente como os dropdowns do Excel. Os elementos vêm do grupo AutoFilter do ECMA-376 Part 1 §18.3.2, e cada um mapeia para um caminho de avaliação distinto:

• filters com filhos filter e dateGroupItem: uma lista de valores marcada, uma entrada opcional de vazio e grupos de datas de calendário do ano até o segundo, combinados com OR
• customFilters: um ou dois critérios de comparação unidos por AND ou OR, o caminho que já existia
• top10: Top ou Bottom n itens, ou n por cento, com val limitado a 1-500 itens ou 1-100 por cento
• dynamicFilter: hoje, ontem, amanhã, esta/última/próxima semana, mês, trimestre e ano, year to date, M1-M12, Q1-Q4, acima da média e abaixo da média
• colorFilter e iconFilter: cor efetiva de fill ou de fonte resolvida pelo estilo diferencial, e ícones de formatação condicional por conjunto de ícones e id de ícone base zero

Qualquer coisa fora dessa lista falha com EXLSXAutoFilterUnsupported em vez de produzir um conjunto de linhas chutado. Isso inclui os tipos acima e abaixo do desvio padrão de ST_DynamicFilterType, valores de calendarType não gregorianos, elementos de extensão desconhecidos, referências dxfId não resolvidas e uma coluna que de alguma forma carregue dois tipos de filtro ao mesmo tempo. Um engine de filtros que silenciosamente devolve linhas "quase certas" é pior que um que recusa, porque ninguém nota até um relatório sair com os clientes errados dentro

Primeiro preview, depois apply

O HotXLS divide o trabalho em um preview somente leitura e um apply transacional, e ambos recebem o mesmo registro TXLSXAutoFilterExecutionOptions. O PreviewAutoFilterRows devolve o número de linhas de dados correspondentes e nunca toca na visibilidade das linhas. O ApplyAutoFilter(AResetVisible, Options) prepara toda decisão de linha primeiro e só então publica o novo conjunto de linhas ocultas em um passo, então uma exceção durante a preparação deixa a planilha exatamente como estava

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Options: TXLSXAutoFilterExecutionOptions;
  Matching: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders-filtered-by-sales.xlsx');
    Sheet := Book.Sheets[1];   // o indexador Sheets é base 1

    // Sempre parta dos padrões, depois mude campos individuais
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // apenas contagem
    WriteLn('Rows Excel would show: ', Matching);

    // True: linhas correspondentes ficam visíveis mesmo se estavam ocultas antes
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Inicialize o registro com XLSDefaultAutoFilterExecutionOptions toda vez. A função preenche o registro com zeros e então define cada limite, e o contexto de execução rejeita um registro cujos limites não sejam todos positivos com EXLSXAutoFilterLimit. Um registro declarado na stack e preenchido campo a campo geralmente carrega lixo ou zeros nos campos que você esqueceu. As sobrecargas sem parâmetro (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) usam os mesmos padrões, com a data atual como referência

A flag AResetVisible decide o que acontece com linhas que passam no filtro mas já estavam ocultas. True torna toda linha correspondente visível, que é o que o Excel faz quando você reaplica. False mantém ocultações manuais anteriores nas linhas correspondentes e só adiciona ocultação para linhas que falham. Linhas fora do intervalo do AutoFilter mantêm seu estado de qualquer forma. Se a pasta de trabalho mudou entre a preparação e a publicação, o ApplyAutoFilter levanta exceção em vez de publicar decisões computadas de dados obsoletos

Por que listas de valores ignoram asteriscos e pontos de interrogação?

Entradas de lista de valores em um elemento filters são tokens de exibição literais, então o HotXLS compara * e ? como caracteres comuns. Uma entrada marcada A* no dropdown significa que a célula mostra exatamente A*, não "começa com A". Wildcards pertencem ao customFilters, onde "começa com" e "contém" realmente são padrões, e esses mantêm as regras separadas cobertas em como o HotXLS casa critérios de wildcard do Excel e escapes com til. Misturar as duas semânticas é o caminho mais rápido para filtrar uma coluna de códigos de peça em absurdo

A comparação é contra o valor exibido, o que levanta a questão de qual exibição. O HotXLS formata a célula com seu formato de número base, incluindo overlays de estilo armazenados, e deliberadamente ignora um formato de número que a formatação condicional aplica por cima. Os controles nativos do Excel nos fixtures do HotXLS mostram por que o token importa: o número 1 e o texto "1" ambos casam com uma seleção de 1, um número 1 formatado como 0.0 em vez disso casa com 1.0 junto com o texto "1.0", e o texto "01" permanece distinto de ambos. Texto com cara de data jamais é interpretado como data, então uma célula de texto guardando "2026-10-19" não cai em um grupo de datas

Como filtros Top 10, média e datas dinâmicas decidem?

O HotXLS computa os cortes de ranking e de média uma vez sobre a coluna de origem inteira, não sobre as linhas que outro filtro deixou visíveis. Apenas números finitos entram na população, o que significa que texto com aparência numérica como "100" é excluído, e a população é coletada antes de qualquer decisão de linha ser tomada. Na pasta de controles nativos, um filtro acima da média mantém uma média de 27 pela coluna inteira mesmo depois de uma segunda coluna ocultar o valor 50; ele não troca para a média de 21,25 das linhas restantes

Empates são incluídos no corte, como no Excel. Um filtro Top 3 sobre valores com duas entradas empatadas em 20 seleciona quatro linhas. O modo porcentagem toma a parcela truncada da população numérica, com piso de um item, então Top 50 por cento de cinco valores seleciona dois valores com um limiar de 30. As variantes Bottom espelham a comparação. Esses números vêm dos próprios conjuntos de linhas do Excel registrados nos fixtures de teste do HotXLS, não da leitura da especificação, que é silenciosa sobre a maioria deles

Datas dinâmicas precisam de uma data de referência congelada

O TXLSXAutoFilterExecutionOptions.ReferenceDate congela o significado de "hoje" para uma operação inteira. Filtros relativos como esta semana ou último trimestre, e regras de data de formatação condicional que alimentam filtros de cor, são todos avaliados contra essa única data, então um preview às 23:59:59 e um apply um segundo depois concordam. Um valor zero seleciona a data atual uma vez quando a operação começa; valores seriais respeitam o sistema de datas 1900 ou 1904 da pasta de trabalho, incluindo o serial fictício 60 do Excel no sistema 1900 para os filtros M2 e Q1

As janelas são intervalos de calendário semiabertos: esta semana vai de domingo ao domingo seguinte, este trimestre do primeiro dia do trimestre ao primeiro dia do próximo, e year to date de 1 de janeiro até o fim do dia de referência. Fixe o ReferenceDate explicitamente em qualquer coisa que rode em lote ou em teste. Um job noturno que cruza a meia-noite, ou um teste unitário escrito em outubro que precisa passar em janeiro, de outra forma produz conjuntos de linhas que diferem sem razão visível

Células de fórmula são filtradas por seus valores em cache. O engine de filtros não recalcula a pasta de trabalho silenciosamente, então um arquivo salvo por um writer que não deixou resultados em cache precisa de uma passada de cálculo explícita antes de o filtro ver números ou datas com significado

Que cores um filtro de cor vê?

Um filtro de cor do HotXLS vê a cor efetiva da formatação direta da célula mais qualquer override de formatação condicional correspondente, e nada que venha apenas de um estilo de tabela. O Excel se comporta da mesma forma: uma linha zebrada pintada de azul claro por um estilo de tabela embutido não é "azul claro" para o dropdown de filtro. Nos controles nativos, uma célula pintada de vermelho por formatação condicional é selecionada por um filtro de fill vermelho, enquanto uma célula formatada de vermelho diretamente mas sobreposta por uma regra amarela ativa é excluída

Três refinamentos chegaram em versões de acompanhamento. A versão 2.384.138 trata fills de gradiente linear e de path como distintos tanto de cores sólidas quanto de ausência de fill, então um gradiente jamais casa com um critério de cor sólida mesmo quando um de seus stops tem aquela cor exata. A versão 2.384.140 adicionou filtragem de cor de fonte para rich text, em que uma célula casa se qualquer run não vazio carrega o RGB, automático ou herdado, pedido. A versão 2.384.150 completou as decisões de rich-text condicional, que se revelaram o caso estranho

O Excel nativo pode reutilizar uma decisão de cor entre uma célula de rich-text e uma célula simples com o mesmo texto, o mesmo estilo base e o mesmo caminho de regra condicional correspondente, então o conjunto de linhas visíveis pode depender da ordem dessas células. O HotXLS reproduz isso preparando o intervalo de dados completo em ordem crescente de linhas, mesmo quando você só pergunta ao AutoFilterRowVisible sobre uma linha. Inspecionar a linha 40 custa portanto uma passada pelo intervalo, mas inspeção de linha, preview e apply sempre devolvem a mesma resposta para a mesma linha

Todo recurso tem um limite, e o que capturar

O HotXLS limita cada tipo de trabalho em uma operação de AutoFilter com seu próprio campo em TXLSXAutoFilterExecutionOptions, porque uma pasta de trabalho filtrada vinda de um formulário de upload é entrada não confiável. Os padrões são 1048576 para MaxXmlCharacters, 16384 para MaxXmlNodes, 4096 para MaxFilterItems, 4000000 para MaxExaminedCells, 1000000 cada para MaxEffectiveFormatCells e MaxPopulationValues, 67108864 bytes para MaxContextBytes e 16000000 para MaxConditionalSteps. O MaxContextBytes mede estado lógico retido e envelopes temporários reservados, não memória do processo

type
  TFilterJob = class
  public
    Deadline: TDateTime;
    VisibleRows: TList<Integer>;
    procedure HandleRow(Sender: TObject; SheetIndex, Row: Integer;
      Visible: Boolean; var Cancel: Boolean);
    procedure HandleWork(Sender: TObject; ACompleted: Int64;
      var ACancel: Boolean);
  end;

procedure TFilterJob.HandleRow(Sender: TObject; SheetIndex, Row: Integer;
  Visible: Boolean; var Cancel: Boolean);
begin
  // Chamado para cada linha de dados; Visible é a decisão do filtro
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted conta células examinadas e pode ficar plano durante a formatação
  ACancel := Now > Deadline;
end;

O callback de linha do PreviewAutoFilterRows recebe toda linha de dados com sua decisão, não apenas as correspondentes, e a função ainda devolve a contagem de correspondências. Definir seu parâmetro Cancel interrompe a visita cedo sem exceção. O OnWork é o gancho de cancelamento: definir ACancel como True levanta EXLSXAutoFilterCancelled. Enquanto a operação prepara decisões ela segura um lease de leitura na pasta de trabalho, então nenhum callback pode modificar essa pasta

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // teto mais apertado para arquivos enviados
Options.OnWork := Job.HandleWork;
try
  Sheet.PreviewAutoFilterRows(Job.HandleRow, Options);
  Sheet.ApplyAutoFilter(True, Options);
except
  on E: EXLSXAutoFilterUnsupported do
    Log('Filter needs Excel: ' + E.Message);
  on E: EXLSXAutoFilterLimit do
    Log('Filter exceeded a budget: ' + E.Message);
  on E: EXLSXAutoFilterCancelled do
    Log('Filter cancelled at deadline');
  on E: EXLSXAutoFilterExecution do
    Log('Filter state invalid: ' + E.Message);
end;

Todas as três exceções específicas derivam de EXLSXAutoFilterExecution, que por si cobre estado inválido como uma data de referência fora do calendário suportado ou uma pasta de trabalho que mudou antes da publicação, então mantenha a classe base por último. Em todos os casos a visibilidade das linhas da planilha fica inalterada, o que torna "logar, pular, marcar o arquivo para revisão manual" uma política padrão segura

Onde estão os limites

A cobertura é ampla, mas algumas bordas valem conhecer antes de prometer paridade a um cliente:

• Filtros dinâmicos de desvio padrão são rejeitados até existir uma política verificada, mesmo que a enumeração tenha valores para eles
• Janelas de semana começam no domingo; pastas de trabalho que dependem de um início de semana diferente não são modeladas
• Resultados de fórmula vêm apenas do cache armazenado, sem recálculo dentro da chamada de filtro
• Cores de estilo de tabela jamais casam com um filtro de cor, por design e em acordo com o Excel
• Estrutura de rich-text desconhecida ou metadados de cor desconhecidos falham explicitamente, enquanto propriedades conhecidas que não são de cor, como a família da fonte, não bloqueiam uma correspondência de cor
• A fachada XLS e seu armazenamento BIFF8 são um caminho separado, coberto em como o HotXLS grava critérios DOPER de AutoFilter BIFF8

Se você ainda está decidindo como definições de AutoFilter, validação de dados e tabelas se relacionam em uma pasta de trabalho que você mesmo gera, comece por validação de dados, AutoFilter e tabelas do HotXLS em Delphi, que cobre o SetAutoFilter, ids de coluna e quando um filtro realmente entra em vigor

FAQ

O PreviewAutoFilterRows muda quais linhas estão ocultas?

Não. O PreviewAutoFilterRows apenas avalia os critérios armazenados e reporta decisões por seu valor de retorno e callback opcional. Apenas o ApplyAutoFilter publica um novo conjunto de linhas ocultas, e o faz depois de todas as decisões estarem preparadas, então uma falha deixa a visibilidade original intacta

Por que um filtro de data relativa dá linhas diferentes em dias diferentes?

Filtros como hoje, esta semana ou último mês são definidos relativos à data atual, exatamente como no Excel. Defina o ReferenceDate em TXLSXAutoFilterExecutionOptions para uma data fixa quando precisar de resultados repetíveis; um valor zero significa a data em que a operação começa

O HotXLS filtra por cores que vêm de um estilo de tabela?

Não, e o Excel também não. Filtros de cor do HotXLS casam apenas formatação direta de célula e overrides suportados de formatação condicional. Cores pintadas unicamente por um estilo de tabela embutido ou customizado não participam, então o resultado de um filtro de cor concorda com o que o dropdown do Excel selecionaria

A execução avançada de AutoFilter vem com a API de worksheet XLSX do HotXLS, o componente de planilhas Delphi para ler e gravar arquivos Excel sem o Excel, a partir da v2.384.136, com tratamento de cores de gradiente, rich-text e rich-text condicional adicionado até a v2.384.150