Se SUBTOTAL(109, ...) e SUBTOTAL(9, ...) devolvem o mesmo número numa folha de cálculo que contém linhas ocultas, um dos dois está errado. O HotXLS, o componente nativo de folha de cálculo Excel para Delphi e C++Builder, comportava-se exatamente assim até à versão 2.197.0, porque o seu motor de cálculo não tinha forma de perguntar a uma folha se uma dada linha estava oculta
O sintoma raramente chega como um relatório de bug sobre códigos de fórmula. Chega como uma discrepância: um trabalho em lote no servidor calcula um total, um utilizador abre o mesmo ficheiro no Excel com um filtro aplicado, e os dois números diferem exatamente pelo que as linhas filtradas somavam. Ninguém suspeita da função de agregação, porque a string de fórmula na célula é idêntica em ambos os sítios. A diferença está inteiramente naquilo que o avaliador tinha permissão para ver
Porque é que o SUBTOTAL 109 inclui linhas ocultas?
Porque na maioria dos designs de motor a camada que avalia uma fórmula nunca fica a saber sobre a visibilidade de linhas. O HotXLS era um caso de manual: o motor de cálculo em lxCalc.pas chegava aos valores de célula através de um único callback TXLSGetValue que responde com um valor para um triplo (folha, linha, coluna) e nada mais. A visibilidade é um atributo de apresentação guardado no registo de linha, e nenhuma parte desse registo viajava pela cadeia de chamadas abaixo. O motor tinha, portanto, um único caminho de agregação, e ambas as metades da tabela de números de função do SUBTOTAL resolviam para ele. Isso não é uma classe de defeito de erro de arredondamento: é toda a razão pela qual a segunda metade da tabela existe. O ECMA-376 Parte 1, publicado como ISO/IEC 29500-1, define SUBTOTAL nas suas definições de funções de fórmula (§18.17.7) com um primeiro argumento que seleciona tanto a agregação interna como a política de linhas ocultas. Os códigos 1 a 11 mapeiam para AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR e VARP incluindo valores em linhas ocultas manualmente. Os códigos 101 a 111 selecionam as mesmas onze agregações e excluem-nos. Um utilizador que digita 109 em vez de 9 está a fazer uma afirmação deliberada sobre dados ocultos, e um motor que colapsa a distinção sobrepõe-se silenciosamente a essa afirmação
Para onde mapeiam os números de função dentro do motor
O HotXLS resolve o primeiro argumento do SUBTOTAL em CalcSubtotalFunc, que normaliza os códigos 101 a 111 para baixo até aos mesmos identificadores de função internos que os códigos 1 a 11 e depois despacha sobre a própria agregação. A maior parte da família flui através do acumulador incremental ExcelSum, aquele que trata SUM, COUNT, COUNTA, MIN, MAX e AVERAGE. Cinco deles não conseguem: STDEV, VAR, STDEVP, VARP e PRODUCT precisam de uma passagem de forma fechada sobre os dados, pelo que CalcSubtotalFunc encaminha os códigos internos 12, 46, 193, 194 e 183 para um redutor separado, SubtotalReduceVariance. Essa divisão é a primeira coisa que vale a pena mapear antes de tocar em qualquer coisa, porque dois caminhos de agregação independentes significam dois ciclos de percurso de célula independentes, e uma correção aplicada apenas a um deles produz o pior resultado possível: SUBTOTAL(109, ...) respeita o filtro enquanto SUBTOTAL(107, ...) no mesmo intervalo não respeita. Contar os ciclos no HotXLS revelou seis assim que o AGGREGATE foi incluído, espalhados pela avaliação de intervalos, a recolha simples de intervalos, e três redutores separados
Porque é que um campo de trabalho em vez de seis novas assinaturas?
Porque passar um novo parâmetro através de seis funções de percurso de célula, mais tudo o que as chama, é uma alteração larga a um caminho de código quente por causa de um único booleano. O HotXLS já tinha um precedente para a alternativa: um campo transitório no calculador, no mesmo espírito do campo de trabalho que o GetRangeInfo usa para registar quando uma referência 3D resolveu para dentro de uma folha de cálculo externa. A versão 2.197.0 acrescentou um segundo. O motor ganhou um tipo de callback, TXLSIsRowHidden, declarado como uma função de (SheetIndex, row) que devolve Boolean, guardado em FIsRowHidden, mais uma flag transitória FIgnoreHiddenRows. A flag é armada à entrada de CalcSubtotalFunc quando o código de função cai entre 101 e 111, e à entrada de CalcAggregateFunc para os códigos de opção do AGGREGATE que selecionam a exclusão de linhas ocultas. Cada ciclo de percurso de célula inspeciona-a então e salta uma linha quando está definida, acrescentando uma única linha a cada um
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Dois pormenores no código de armamento carregam a correção de todo o esquema. A flag é guardada e restaurada em vez de simplesmente definida e limpa, porque um argumento de SUBTOTAL pode conter uma expressão que corre a sua própria avaliação enquanto a agregação exterior ainda está na pilha, e esse trabalho aninhado não pode herdar nem destruir o portão exterior. E a restauração vive num bloco finally, porque CalcSubtotalFunc tem várias saídas antecipadas para códigos de erro; uma flag deixada armada depois de um retorno de erro corromperia silenciosamente a próxima fórmula não relacionada na ordem de recálculo
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;O teste Assigned é o que mantém a alteração compatível. O HotXLS estendeu o construtor do calculador com um terceiro parâmetro predefinido como nil, pelo que qualquer código que construa um TXLSCalculator com a antiga chamada de dois argumentos continua a compilar e continua a obter o comportamento antigo de incluir ocultas. Nada na forma da API existente mudou
De onde vem realmente o bit de linha oculta?
Da folha de cálculo, através de duas fontes diferentes, porque o HotXLS transporta dois motores de folha de cálculo. O lado BIFF legado responde a partir de TXLSRowInfoList.GetHidden, alcançado através de TXLSWorkbook.GetRowHidden. O lado OOXML responde a partir de TXLSXWorksheet.GetRowHidden, alcançado através de TXLSXWorkbook.GetCalcRowHidden. Ambos estão ligados ao calculador no momento da construção, ao lado do callback de valor de célula que espelham. As convenções de linha são onde este tipo de ponte normalmente corre mal, pelo que vale a pena declará-las explicitamente. O calculador entrega ao callback uma linha baseada em 0, correspondendo às coordenadas que TXLSGetValue já usa. A folha XLSX indexa o seu mapa de linhas ocultas por número de linha baseado em 1, exatamente como o Excel numera linhas, o que é também o que a propriedade pública RowHidden[ARow] expõe. A ponte XLSX, por isso, acrescenta um antes da pesquisa, e a ponte BIFF não, porque TXLSRowInfoList já é baseada em 0. Ambas as pontes tratam um índice de folha ou uma linha fora do intervalo válido como visível, pelo que uma consulta fora dos limites degrada para a antiga resposta de incluir ocultas em vez de perder dados
O que muda para folhas de cálculo filtradas
Este é o caso que gera os pedidos de suporte. Aplicar um AutoFilter no HotXLS através de ApplyAutoFilter avalia os critérios de coluna e oculta cada linha de dados que não corresponda, o que é precisamente o que o Excel faz quando um utilizador clica num dropdown de filtro. Antes da v2.197.0 essas linhas ocultas eram invisíveis para o utilizador e totalmente visíveis para o motor de cálculo, pelo que um SUBTOTAL(109, ...) do lado do servidor reportava o total não filtrado. Agora a mesma chamada reporta o filtrado
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;A ocultação manual funciona da mesma forma, já que RowHidden[ARow] := True é o mesmo estado que o filtro escreve. Essa equivalência é deliberada no Excel e agora também se verifica no HotXLS. Uma consequência merece uma nota em qualquer documentação distribuída com as suas folhas de cálculo geradas: um total calculado com o código 109 é um número dependente da vista, pelo que um destinatário que limpa o filtro muda-o. Quando um relatório tem de declarar uma cifra fixa independentemente do que o leitor faça à vista, o código 9 é a escolha correta e sempre foi. Filtros, validação e tabelas são cobertos em conjunto no artigo sobre validação de dados, AutoFilter e tabelas. Como ocultar linhas não toca em nenhuma fórmula, também não suja o grafo de dependências por si só, o que vale a pena saber se depender do recálculo incremental sobre o subgrafo sujo para manter folhas de cálculo grandes responsivas
Códigos de opção do AGGREGATE e um limite que ainda está aberto
O AGGREGATE é o SUBTOTAL com um segundo argumento de política, e o HotXLS trata-o em CalcAggregateFunc. O argumento de opção codifica interruptores independentes: se as chamadas SUBTOTAL e AGGREGATE aninhadas dentro do intervalo são saltadas, se os valores em linhas ocultas são saltados, e se os valores de erro são suprimidos em vez de propagados. O HotXLS arma o portão partilhado de linhas ocultas para os códigos de opção 2, 3, 6 e 7, e suprime valores de erro para os códigos de opção 4 a 7. O argumento de número de função seleciona então a agregação exatamente como o SUBTOTAL faz, incluindo o encaminhamento de variância, desvio-padrão e produto através dos seus próprios redutores. Falta uma lacuna documentada, e é melhor declará-la aqui do que descobri-la em produção: a semântica de ignorar-SUBTOTAL-aninhado associada aos códigos de opção baixos não está implementada no HotXLS. Detetar um SUBTOTAL aninhado dentro de um intervalo referenciado exige marcar o estado de recursão do avaliador para que uma agregação interior se possa anunciar à exterior, o que é uma alteração maior do que o portão de linhas ocultas. Na prática a exposição é pequena, porque folhas de cálculo reais quase sempre colocam fórmulas SUBTOTAL fora dos intervalos que outras fórmulas SUBTOTAL agregam. Se o seu gerador construir de facto intervalos de agregação sobrepostos, não confie nos códigos de opção baixos para os deduplicar
A proteção de aridade que foi lançada em conjunto
A versão 2.197.0 também fechou uma lacuna de validação no mesmo despachante, e a razão de design é a mesma que motivou o campo de trabalho: colocar a verificação onde possa ser escrita uma única vez. Cerca de 280 corpos de funções incorporadas verificavam cada um a sua própria contagem de argumentos contra Item.ChildCount, o que não deixava uma fronteira consistente para o caso de argumentos a mais. Uma chamada como =SIN(1,2) chegava a um corpo de função que examinava o seu primeiro argumento, ignorava o excedente, e devolvia um número plausível onde o Excel devolve #VALUE!. O HotXLS já guardava a aridade declarada de cada função incorporada no seu registo de funções, exposta como THashFunc.ArgsCnt com -1 a marcar uma função variádica como SUM, IF ou CONCAT. A versão 2.197.0 encaminhou isso através de uma nova propriedade TXLSFormula.FuncArgsCntByPtg e acrescentou um portão no topo de GetValueItemFunc, o despachante principal
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;A proteção rejeita argumentos a mais e deliberadamente não diz nada sobre argumentos a menos. Omitir um argumento opcional final é legal no Excel para VLOOKUP, SUBSTITUTE, e uma longa lista de outras, pelo que uma verificação simétrica teria quebrado fórmulas corretas para apanhar as incorretas. Identificadores desconhecidos reportam-se como variádicos e saltam o portão por completo, o que é o que mantém as funções definidas pelo utilizador fora do seu caminho; se registar as suas próprias funções, o comportamento descrito no guia sobre o motor de fórmulas e funções personalizadas não é afetado. Centralizar o caso de argumentos a menos é um trabalho separado, porque cada um desses 280 corpos tem a sua própria semântica de código de erro e têm de ser revistos um de cada vez em vez de assumidos
O motor de cálculo aqui descrito, ambas as fachadas de folha de cálculo, e as APIs de AutoFilter e visibilidade de linhas que o alimentam fazem parte do componente de folha de cálculo Delphi HotXLS, que vem com código-fonte completo para Delphi e C++Builder e não exige nenhuma instalação do Excel na máquina que o corre