Executar AutoFilters do Excel sem Excel no Delphi: HotXLS

O HotXLS avalia os critérios de AutoFilter já guardados num ficheiro XLSX sem arrancar o Excel. Desde a v2.384.136 que o TXLSXWorksheet.PreviewAutoFilterRows, o ApplyAutoFilter e o AutoFilterRowVisible executam listas de valores, grupos de datas, filtros Top/Bottom-n e percentagem, filtros relativos a datas e médias, filtros de cor de preenchimento e de fonte e filtros de ícones de formatação condicional, sob uma data de referência fixada e limites explícitos de recursos

O gatilho habitual é um workbook que uma pessoa filtrou no Excel e um servidor que tem de agir sobre isso. Alguém define "Top 10 por receita", "Este mês" na data da encomenda e um preenchimento vermelho nas linhas em atraso, grava, e carrega o ficheiro. O seu serviço Delphi agora precisa do conjunto de linhas que o Excel mostraria: para o contar, exportá-lo, ou gravar o ficheiro de volta com as linhas certas ocultas. O ficheiro só guarda a definição. Antes desta versão o HotXLS conseguia avaliar critérios personalizados ordinários, mas os tipos avançados ficavam no XML guardado sem nada a executá-los, pelo que a resposta honesta era "abra no Excel"

O que executa afinal o HotXLS de um AutoFilter guardado?

O HotXLS executa um tipo de filtro por coluna, tirado do elemento filterColumn que o Excel escreveu, e combina colunas com AND, exatamente como as dropdowns do Excel. Os elementos vêm do grupo AutoFilter na ECMA-376 Parte 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 marcados, uma entrada opcional de vazios e grupos de datas de calendário do ano até ao segundo, unidos por 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, ano até à data, M1-M12, Q1-Q4, acima da média e abaixo da média
• colorFilter e iconFilter: cor efetiva de preenchimento ou de fonte resolvida através do estilo diferencial, e ícones de formatação condicional por conjunto de ícones e id de ícone de base zero

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

Pré-visualizar primeiro, aplicar depois

O HotXLS divide o trabalho numa pré-visualização só de leitura e uma aplicação transacional, e ambos recebem o mesmo registo TXLSXAutoFilterExecutionOptions. O PreviewAutoFilterRows devolve o número de linhas de dados correspondentes e nunca toca na visibilidade das linhas. O ApplyAutoFilter(AResetVisible, Options) prepara primeiro todas as decisões de linhas e só depois publica o novo conjunto de linhas ocultas num único passo, pelo que uma exceção durante a preparação deixa a folha 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 é de base 1

    // Comece sempre pelos predefinidos, depois mude campos individuais
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

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

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

Inicialize o registo com XLSDefaultAutoFilterExecutionOptions todas as vezes. A função enche o registo com zeros e depois define cada limite, e o contexto de execução rejeita um registo cujos limites não sejam todos positivos com EXLSXAutoFilterLimit. Um registo declarado na pilha e preenchido campo a campo costuma transportar lixo ou zeros nos campos de que se esqueceu. Os overloads sem parâmetros (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) usam os mesmos predefinidos, com a data atual como referência

A flag AResetVisible decide o que acontece às linhas que passam o filtro mas já estavam ocultas. True torna todas as linhas correspondentes visíveis, que é o que o Excel faz quando reaplica. False mantém a ocultação manual anterior nas linhas correspondentes e só acrescenta ocultação às linhas que falham. Linhas fora do intervalo do AutoFilter mantêm o seu estado de qualquer forma. Se o workbook mudou entre a preparação e a publicação, o ApplyAutoFilter levanta em vez de publicar decisões calculadas a partir de dados obsoletos

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

As entradas de lista de valores num elemento filters são tokens literais de exibição, pelo que o HotXLS compara * e ? como caracteres ordinários. Uma entrada marcada A* na dropdown significa que a célula mostra exatamente A*, não "começa por A". Os wildcards pertencem ao customFilters, onde "começa por" e "contém" são realmente padrões, e esses mantêm as regras separadas cobertas em como o HotXLS compara critérios wildcard do Excel e escapes de til. Misturar as duas semânticas é a forma mais rápida de filtrar uma coluna de números de peça em disparates

A comparação é feita contra o valor exibido, o que levanta a questão de qual exibição. O HotXLS formata a célula com o seu formato de número base, incluindo sobreposições de estilo guardadas, e deliberadamente ignora um formato de número que a formatação condicional aplique por cima. Os controlos nativos do Excel nas fixtures do HotXLS mostram por que o token importa: o numérico 1 e o texto "1" ambos correspondem a uma seleção de 1, um numérico 1 formatado como 0.0 corresponde antes a 1.0 juntamente com o texto "1.0", e o texto "01" mantém-se distinto de ambos. Texto com aspeto de data nunca é analisado como data, pelo que uma célula de texto a guardar "2026-10-19" não cai num grupo de datas

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

O HotXLS calcula os cortes de ordenação e de média uma vez sobre a coluna de origem inteira, não sobre as linhas que outro filtro deixou visíveis. Só números finitos entram na população, o que significa que texto com aspeto numérico como "100" é excluído, e a população é recolhida antes de qualquer decisão de linha ser tomada. No workbook de controlos nativos um filtro acima da média mantém uma média de 27 sobre a coluna completa mesmo depois de uma segunda coluna ocultar o valor 50; não muda 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 percentagem toma a parte truncada da população numérica, com um piso de um item, pelo que Top 50 por cento de cinco valores seleciona dois valores com um limiar de 30. As variantes Bottom espelham a comparação. Estes números vêm dos conjuntos de linhas do próprio Excel registados nas 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 fixada

O TXLSXAutoFilterExecutionOptions.ReferenceDate fixa o significado de "hoje" para uma operação inteira. Filtros relativos como esta semana ou último trimestre, e regras de datas de formatação condicional que alimentam filtros de cor, são todos avaliados contra essa única data, pelo que uma pré-visualização às 23:59:59 e uma aplicação 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 do workbook, 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 corre de domingo ao domingo seguinte, este trimestre do primeiro dia do trimestre ao primeiro dia do seguinte, e ano até à data de 1 de janeiro até ao fim do dia de referência. Fixe o ReferenceDate explicitamente em qualquer coisa que corra num lote ou num teste. Um trabalho noturno que atravesse a meia-noite, ou um teste unitário escrito em outubro que tem de passar em janeiro, produzirão de outra forma conjuntos de linhas que diferem sem razão visível

Células de fórmula são filtradas pelos seus valores em cache. O motor de filtros não recalcula silenciosamente o workbook, pelo que um ficheiro gravado por um writer que deixou nenhum resultado em cache precisa de uma passagem de cálculo explícita antes de o filtro ver números ou datas com significado

Que cores vê um filtro de cor?

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

Três refinamentos chegaram em versões de seguimento. A versão 2.384.138 trata preenchimentos de gradiente linear e path como distintos tanto de cores sólidas como de ausência de preenchimento, pelo que um gradiente nunca corresponde a um critério de cor sólida mesmo que uma das suas paragens tenha essa cor exata. A versão 2.384.140 acrescentou filtragem de cor de fonte para rich text, em que uma célula corresponde se qualquer run não vazio transportar o RGB pedido, cor automática ou herdada. A versão 2.384.150 completou as decisões de rich text condicional, que se revelou 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, pelo que o conjunto de linhas visível pode depender da ordem dessas células. O HotXLS reproduz isso preparando o intervalo de dados completo em ordem ascendente de linhas, mesmo que só pergunte ao AutoFilterRowVisible sobre uma linha. Inspecionar a linha 40 custa portanto uma passagem pelo intervalo, mas a inspeção de linhas, a pré-visualização e a aplicação devolvem sempre a mesma resposta para a mesma linha

Todos os recursos têm um limite, e o que apanhar

O HotXLS delimita cada tipo de trabalho numa operação de AutoFilter com o seu próprio campo no TXLSXAutoFilterExecutionOptions, porque um workbook filtrado vindo de um formulário de carregamento é entrada não fidedigna. Os predefinidos 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 manter-se plano durante a formatação
  ACancel := Now > Deadline;
end;

O callback de linha do PreviewAutoFilterRows recebe todas as linhas de dados com a sua decisão, não só as correspondentes, e a função continua a devolver a contagem correspondente. Definir o seu parâmetro Cancel para 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, segura um lease de leitura no workbook, pelo que nenhum callback pode modificar esse workbook

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // limite mais apertado para ficheiros carregados
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;

As três exceções específicas derivam todas do EXLSXAutoFilterExecution, que por sua vez cobre estado inválido como uma data de referência fora do calendário suportado ou um workbook que mudou antes da publicação, por isso mantenha a classe base por último. Em todos os casos a visibilidade das linhas da folha fica inalterada, o que torna "registar, saltar, marcar o ficheiro para revisão manual" uma política predefinida segura

Onde ficam as fronteiras

A cobertura é ampla, mas algumas arestas valem a pena 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
• As janelas de semana começam ao domingo; workbooks que dependam de um início de semana diferente não são modelados
• Os resultados de fórmulas vêm da cache guardada apenas, sem recálculo dentro da chamada de filtro
• Cores de estilo de tabela nunca correspondem a um filtro de cor, por design e em concordância com o Excel
• Estrutura de rich text desconhecida ou metadados de cor desconhecidos falham explicitamente, enquanto propriedades conhecidas não de cor como a família da fonte não bloqueiam uma correspondência de cor
• A fachada XLS e o seu armazenamento BIFF8 são um caminho separado, coberto em como o HotXLS escreve critérios DOPER de AutoFilter BIFF8

Se ainda está a decidir como as definições de AutoFilter, a validação de dados e as tabelas se relacionam num workbook que gera você mesmo, comece por validação de dados, AutoFilter e tabelas do HotXLS no Delphi, que cobre SetAutoFilter, ids de coluna e quando um filtro realmente surte efeito

FAQ

O PreviewAutoFilterRows muda que linhas estão ocultas?

Não. O PreviewAutoFilterRows só avalia os critérios guardados e reporta decisões através do seu valor de retorno e callback opcional. Só o ApplyAutoFilter publica um novo conjunto de linhas ocultas, e fá-lo depois de todas as decisões estarem preparadas, pelo que uma falha deixa a visibilidade original intacta

Por que é que um filtro de datas relativas dá linhas diferentes em dias diferentes?

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

Pode o HotXLS filtrar por cores que vêm de um estilo de tabela?

Não, e o Excel também não consegue. Os filtros de cor do HotXLS correspondem apenas a formatação direta de células e sobreposições de formatação condicional suportadas. Cores pintadas apenas por um estilo de tabela incorporado ou personalizado não participam, pelo que um resultado de filtro de cor concorda com o que a dropdown do Excel selecionaria

A execução avançada de AutoFilter viaja com a API de worksheet XLSX do HotXLS, o componente de folhas de cálculo Delphi para ler e escrever ficheiros Excel sem Excel, a partir da v2.384.136, com tratamento de cor de gradiente, rich text e rich text condicional acrescentado até à v2.384.150