Ordene itens de pivô por valor em Delphi: autoSortScope e -2

O HotXLS ordena itens de PivotTable por valor em Delphi por meio de duas propriedades no TXLSPivotField: SortType escolhe crescente ou decrescente, e SortDataField nomeia o campo de dados base zero cujos totais comanda a ordem. Quando o HotXLS abre um XLSX autorado pelo Excel, ele recupera esse campo de dados do elemento autoSortScope, em que o Excel armazena o campo Values como field="4294967294", a grafia sem sinal de -2

Ordenar por valor é o pedido que todo relatório de vendas eventualmente recebe: "mostre-me as regiões da maior para a menor". Na UI do Excel são dois cliques. No formato de arquivo é um atributo, uma área de pivô aninhada, um índice de campo reservado e um ordinal de campo de dados selecionado, e um leitor que perca qualquer uma dessas peças ainda abre o arquivo sem reclamar. Só ordena pela coisa errada

Onde o Excel armazena uma medida de ordenação por valor?

O Excel armazena a medida de ordenação apenas dentro do autoSortScope, não no campo em si. O atributo sortType no <pivotField> (um valor ST_FieldSortType do ECMA-376 Part 1: manual, ascending ou descending) diz em que direção ordenar. Por qual medida ordenar mora em um filho <autoSortScope>, que envolve um <pivotArea> cuja referência do eixo de dados seleciona um campo de dados

<pivotField axis="axisRow" showAll="0" sortType="descending">
  <items count="5"> ... </items>
  <autoSortScope>
    <pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
      <references count="1">
        <reference field="4294967294" count="1" selected="0">
          <x v="0"/>
        </reference>
      </references>
    </pivotArea>
  </autoSortScope>
</pivotField>

O elemento reference normalmente aponta para um campo de pivô por índice. O índice de campo -2 é reservado na parte PivotTable do ECMA-376 para o pseudo-campo Values, aquele que o Excel mostra como "Values" quando duas ou mais medidas sentam em um eixo. O atributo é tipado como inteiro sem sinal de 32 bits, então -2 vai no fio como 4294967294. O <x v="0"/> por baixo dele seleciona o campo de dados 0, a primeira entrada em <dataFields>. Há também um atributo rankBy no <pivotField>, mas nos arquivos que comparamos o Excel o deixou de fora e confiou apenas no escopo

Por que a medida de ordenação voltava como -1?

O leitor do HotXLS originalmente perdia a medida de ordenação porque 4294967294 não cabe em um inteiro com sinal de 32 bits. O helper de atributos é um wrapper fino sobre o StrToIntDef, e um valor fora da faixa silenciosamente devolve o padrão, então o teste do eixo de dados nunca casava, o SortDataField ficava em -1, e o campo guardava apenas sortType="descending" sem medida anexada

A falha era silenciosa da pior forma. Um campo decrescente sem medida ainda ordena, só que pelo rótulo do item em reverso, então um relatório que o Excel ordenou pelo total voltava do HotXLS com a ordem dos itens invertida. Nada levantava exceção e os números em cada célula estavam corretos. Só a ordem das linhas estava errada, e apenas em arquivos que o Excel havia autorado

A correção no lxPivotXml.pas compara o token bruto do atributo como string antes de qualquer conversão inteira. Se o rankBy já definiu uma medida, ele vence; caso contrário, uma referência cujo token de field é exatamente '4294967294' resolve o SortDataField a partir de seu valor de x selecionado. O lado da avaliação não precisou de mudança: o comparador já finalizava totais por membro nos eixos de linha e coluna, tratava ordem decrescente e desempatava. Os dados simplesmente nunca chegavam até ele

A lição geral vale para qualquer leitor OOXML escrito em Delphi. O ECMA-376 tipa vários atributos de índice como xsd:unsignedInt e então usa o topo da faixa como sentinela, então um leitor com sinal e fallback padrão transforma um valor reservado em "atributo ausente". Compare o token, ou interprete para Int64 ou Cardinal, ondequer que um índice de campo possa significar Values

Ordenando itens de pivô por uma medida a partir de código Delphi

Para ordenar um campo de pivô do HotXLS por valor, defina o SortType como xlpfstAscending ou xlpfstDescending e o SortDataField como o índice base zero da medida na lista DataFields da tabela. Com SortType = xlpfstManual, que é o padrão, o SortDataField é ignorado e os itens mantêm sua ordem de cache ou programática

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Region';
    Data.Cells[1, 2].Value := 'Product';
    Data.Cells[1, 3].Value := 'Units';
    // ... preencha as linhas 2..41 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // campo de dados 0

    Region.SortType := xlpfstDescending;  // maior região primeiro
    Region.SortDataField := 0;            // ordena por Sum of Units
    Region.AutoSortScope := '0';          // também emite o autoSortScope do próprio Excel

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot layout was rejected');
    Book.SaveAs('units-by-region.xlsx');
  finally
    Book.Free;
  end;
end;

Dois detalhes merecem nota. O writer do HotXLS emite o SortDataField como rankBy, e quando o AutoSortScope não está vazio ele também grava um bloco autoSortScope com a referência de eixo de dados 4294967294, pondo a string da propriedade no slot de x v. No carregamento, o AutoSortScope recebe esse mesmo token de x v, então para ordenações por valor a propriedade guarda o ordinal do campo de dados como texto. Defini-lo como '0' portanto produz um arquivo que carrega a medida em ambos os lugares, que é o casamento mais próximo com o que o próprio Excel grava. O MaterializeNativePivotTable então prepara a grade ordenada e seus metadados de layout juntos e devolve 1 em sucesso ou -1 com um diagnóstico

Linhas de itens não são a única coisa que é ordenada. Campos de coluna passam pelo mesmo comparador, usando totais por coluna em vez de totais por linha, então um campo de coluna com SortType = xlpfstDescending e um SortDataField válido ordena suas colunas por valor exatamente da mesma forma

Inspecionando a ordenação que uma pasta do Excel já carrega

Para arquivos autorados pelo Excel, o HotXLS preenche SortType, SortDataField e AutoSortScope enquanto carrega a definição do pivô, então você pode auditar ou mudar a ordenação de um relatório sem reconstruir a tabela. O trecho abaixo lista todo campo que ordena por uma medida e nomeia a medida por seu DisplayName

var
  Book: TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Field: TXLSPivotField;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales-report.xlsx');
    Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Field := Pivot.Fields[I];
      if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
        Writeln(Field.Name, ' sorted by ',
          Pivot.DataFields[Field.SortDataField].DisplayName);
    end;

    // inverta a ordenação da Region para crescente e reconstrua a grade existente
    Field := Pivot.FindFieldByName('Region');
    if Field <> nil then
      Field.SortType := xlpfstAscending;
    if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
      raise Exception.Create('Could not re-materialize the pivot');
    Book.SaveAs('sales-report-asc.xlsx');
  finally
    Book.Free;
  end;
end;

O segundo argumento do MaterializeNativePivotTable diz ao HotXLS para adotar o intervalo de saída existente do pivô em vez de tratá-lo como colisão. O mesmo padrão aparece no guia de Show Values As rank e totais acumulados no HotXLS, em que a ordem importa porque totais acumulam na ordem de exibição

Como o HotXLS desempata quando dois itens têm o mesmo valor?

O HotXLS desempata pelo rótulo do item, de forma determinística, o que é uma diferença conhecida do Excel. Quando dois membros têm totais idênticos, o comparador recua para comparar suas legendas, e a flag decrescente inverte essa comparação também. Itens cuja métrica não é um número válido ordenam depois dos válidos em ordem crescente, e a flag decrescente inverte a comparação inteira, inclusive esse agrupamento

O Excel não promete nenhuma ordem de empate. Em um fixture de teste em que quatro membros, A, B, C e D, todos tinham o mesmo total, uma ordenação crescente no Excel produziu B, C, A, D. Nada nos metadados salvos prevê essa permutação; é um subproduto de uma ordenação instável, e pode diferir entre arquivos. O HotXLS devolve A, B, C, D para a mesma entrada. Nossa verificação para esse caso confere o conjunto de membros e os valores por membro em vez da sequência, e o fixture decrescente com empate 15/15 casou com o Excel célula a célula. Se seu código a jusante depende da posição exata de linhas empatadas, ordene por uma segunda medida ou torne os valores distintos

Membros recolhidos e o campo mais interno

Membros recolhidos se materializam no HotXLS como uma linha agregada por item recolhido. Quando o Excel oculta os detalhes de um item, ele grava sd="0" naquele <item>; o HotXLS o expõe como TXLSPivotItem.IsDetailHidden. O item recolhido renderiza com seu rótulo simples, substitui sua subárvore inteira, e a recolha se aplica àquele item sob todo pai, enquanto blocos envolventes mantêm seus próprios subtotais

var
  Category: TXLSPivotField;
begin
  // O pivô tem Category (externa) e Product (interna) nas linhas
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // recolhe a primeira categoria
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Recolher interage com a ordenação de forma previsível: uma linha recolhida ainda é um membro de seu campo, então toma sua posição pelo total como qualquer outro item. O campo mais interno é diferente. Ele não tem nível interno, então não tem nada a recolher e, desde uma correção anterior, o HotXLS também nunca renderiza uma linha de subtotal para ele, mesmo quando sua flag defaultSubtotal está definida. O Excel se comporta igual em eixos de campo único e multinível, e antes dessa mudança o HotXLS podia emitir uma linha Total fantasma sob o campo mais interno. Para os atributos de nível de campo envolvidos aqui, o passo a passo do HotXLS pela validade de esquema do CT_PivotField cobre o que o esquema exige no <pivotField> e seus itens

Armadilhas ao conferir contra automação do Excel

Quando você cruzar uma ordenação do HotXLS com o Excel por automação COM, chame o AutoSort com a legenda localizada da medida, grafada exatamente como o Excel a mostra. Em um Excel em chinês a legenda padrão de um campo Units somado é '求和项:Units'; passar 'Sum of Units' naquela máquina não levanta erro e não muda nada. O mesmo script que funciona em um Excel em inglês silenciosamente vira um no-op em outro lugar, então leia a legenda do campo de dados em vez de codificá-la

• O PivotField.SortType não pôde ser lido de volta via COM na build do Excel que testamos, então verifique a ordenação pelo XML salvo ou pelas células renderizadas em vez disso
• Nessa build, pôr um campo pai de grupo manual no mesmo eixo fora do campo ordenado suprimia a ordenação de membros por valor; renderização ordenada sob pais de grupo manual não é verificada
• Escritas em PivotField.Outline e PivotField.LayoutForm foram rejeitadas nessa build também, então defina um layout outline para um fixture por meio do PivotTable.RowAxisLayout

Nenhuma dessas peculiaridades muda o formato de arquivo, mas elas decidem se um teste de regressão prova algo. Um teste que chama o AutoSort com a legenda errada passa contra um pivô que nunca foi ordenado

FAQ

O que o campo 4294967294 significa em um arquivo XML de PivotTable?

O campo 4294967294 é a codificação sem sinal de 32 bits do índice de campo -2, que o ECMA-376 reserva para o pseudo-campo Values. Dentro do autoSortScope ele marca uma referência de eixo de dados, e seu filho x seleciona por qual campo de dados os itens são ordenados

O HotXLS ordena itens de pivô por valor sem o Excel instalado?

Sim. O HotXLS avalia a ordenação ele mesmo quando materializa a grade do pivô, usando os totais por membro do campo de dados escolhido, e grava sortType, rankBy e opcionalmente autoSortScope para o Excel manter a mesma ordem depois de um refresh. Apenas a ordem de empates entre valores iguais pode diferir do Excel

Por que meus itens de pivô saem em ordem alfabética em vez de por valor?

Normalmente porque o campo tem uma direção de ordenação mas nenhuma medida. Confira que o SortType não é xlpfstManual e que o SortDataField está entre 0 e DataFieldCount - 1; com -1, o HotXLS ordena os itens por rótulo na direção pedida

Ordenação por valor, membros recolhidos e o resto do pipeline nativo de pivôs vêm no HotXLS, o componente de planilhas Delphi para XLS e XLSX, que lê, edita e grava pastas de trabalho do Excel sem o Excel instalado. Se você está construindo o pivô do zero primeiro, o tutorial do HotXLS sobre construir e atualizar pivot tables cobre caches, campos e refresh antes de você chegar a ordená-los