Ordenar itens pivot por valor no Delphi: autoSortScope e -2

O HotXLS ordena itens de PivotTable por valor no Delphi através de duas propriedades no TXLSPivotField: o SortType escolhe ascendente ou descendente, e o SortDataField nomeia o campo de dados de base zero cujos totais comandam a ordem. Quando o HotXLS abre um XLSX autorado no Excel, recupera esse campo de dados do elemento autoSortScope, onde o Excel guarda o campo Values como field="4294967294", a grafia sem sinal de -2

Ordenar por valor é o pedido que todo o relatório de vendas acaba por receber: "mostre-me as regiões da maior para a menor". Na interface do Excel são dois cliques. No formato de ficheiro é um atributo, uma área pivot 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 ficheiro sem queixa. Só ordena pela coisa errada

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

O Excel guarda a medida de ordenação só dentro do autoSortScope, não no próprio campo. O atributo sortType no <pivotField> (um valor ST_FieldSortType da ECMA-376 Parte 1: manual, ascending ou descending) diz que direção ordenar. Que medida ordenar vive num filho <autoSortScope>, que embrulha um <pivotArea> cuja referência de 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 aponta normalmente para um campo pivot por índice. O índice de campo -2 é reservado na parte PivotTable da ECMA-376 para o pseudo-campo Values, aquele que o Excel mostra como "Values" quando duas ou mais medidas estão num eixo. O atributo é tipado como inteiro sem sinal de 32 bits, pelo que -2 vai pela linha 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 ficheiros que comparámos o Excel deixou-o de fora e confiou apenas no escopo

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

O leitor do HotXLS perdia originalmente a medida de ordenação porque 4294967294 não cabe num inteiro com sinal de 32 bits. O auxiliar de atributos é um invólucro fino sobre o StrToIntDef, e um valor fora do intervalo devolve silenciosamente o predefinido, pelo que o teste de eixo de dados nunca casava, o SortDataField ficava em -1, e o campo mantinha só sortType="descending" sem medida anexada

A falha era silenciosa da pior forma. Um campo descendente sem medida ainda ordena, só que pelo rótulo do item em reverso, pelo que um relatório que o Excel ordenou por total regressava do HotXLS com a ordem de itens invertida. Nada levantava uma exceção e os números em todas as células estavam corretos. Só a ordem das linhas estava errada, e só nos ficheiros que o Excel tinha autorado

A correção em lxPivotXml.pas compara o token bruto do atributo como string antes de qualquer conversão para inteiro. Se o rankBy já definiu uma medida, ele ganha; caso contrário uma referência cujo token field é exatamente '4294967294' resolve o SortDataField a partir do seu valor 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 a ordem descendente e desempatava. Os dados simplesmente nunca chegavam lá

A lição geral aplica-se a qualquer leitor OOXML escrito em Delphi. A ECMA-376 tipa vários atributos de índice como xsd:unsignedInt e depois usa o topo do intervalo como sentinela, pelo que um leitor com sinal e fallback predefinido transforma um valor reservado em "atributo em falta". Compare o token, ou analise para Int64 ou Cardinal, ondequer que um índice de campo possa significar Values

Ordenar itens pivot por uma medida a partir de código Delphi

Para ordenar um campo pivot do HotXLS por valor, defina o SortType como xlpfstAscending ou xlpfstDescending e o SortDataField como o índice de base zero da medida na lista DataFields da tabela. Com SortType = xlpfstManual, que é o predefinido, o SortDataField é ignorado e os itens mantêm a 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;  // a maior região primeiro
    Region.SortDataField := 0;            // ordena por Soma de Units
    Region.AutoSortScope := '0';          // também emite o autoSortScope próprio do 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 escritor do HotXLS emite o SortDataField como rankBy, e quando o AutoSortScope não está vazio também escreve um bloco autoSortScope com a referência de eixo de dados 4294967294, pondo a string da propriedade no espaço x v. Ao carregar, o AutoSortScope recebe esse mesmo token x v, pelo que para ordenações por valor a propriedade guarda o ordinal do campo de dados como texto. Defini-lo como '0' produz portanto um ficheiro que transporta a medida em ambos os lugares, que é a correspondência mais próxima do que o próprio Excel escreve. O MaterializeNativePivotTable depois prepara a grelha ordenada e os seus metadados de layout em conjunto e devolve 1 em caso de sucesso ou -1 com um diagnóstico

As linhas de itens não são a única coisa que se ordena. Os campos de coluna passam pelo mesmo comparador, usando totais por coluna em vez de totais por linha, pelo que um campo de coluna com SortType = xlpfstDescending e um SortDataField válido ordena as suas colunas por valor exatamente da mesma forma

Inspecionar a ordenação que um workbook Excel já transporta

Para ficheiros autorados no Excel, o HotXLS preenche SortType, SortDataField e AutoSortScope enquanto carrega a definição da pivot, pelo que pode auditar ou mudar a ordenação de um relatório sem reconstruir a tabela. O excerto abaixo lista todos os campos que ordenam por uma medida e nomeia a medida pelo 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;

    // vire a ordenação de Region para ascendente e reconstrua a grelha 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 da pivot em vez de o tratar como colisão. O mesmo padrão aparece no guia de Show Values As rank e totais acumulados no HotXLS, em que a ordem de ordenação importa porque os totais acumulados somam pela ordem de exibição

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

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

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

Membros colapsados e o campo mais interno

Membros colapsados materializam-se no HotXLS como uma linha agregada por item colapsado. Quando o Excel oculta os detalhes de um item, escreve sd="0" nesse <item>; o HotXLS expõe-o como TXLSPivotItem.IsDetailHidden. O item colapsado renderiza com o seu rótulo simples, substitui a sua subárvore inteira, e o colapso aplica-se a esse item sob todos os pais, enquanto os blocos que o envolvem mantêm os seus próprios subtotais

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

Colapsar interage com a ordenação de forma previsível: uma linha colapsada continua a ser um membro do seu campo, pelo que toma a sua posição do seu total como qualquer outro item. O campo mais interno é diferente. Não tem nível interno, pelo que não tem nada para colapsar e, desde uma correção anterior, o HotXLS também nunca renderiza uma linha de subtotal para ele, mesmo quando a sua flag defaultSubtotal está definida. O Excel comporta-se 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 ao nível do campo envolvidos aqui, a caminhada do HotXLS pela validade de esquema CT_PivotField cobre o que o esquema exige no <pivotField> e nos seus itens

Armadilhas ao verificar contra automação do Excel

Quando cruza uma ordenação do HotXLS com o Excel através de automação COM, chame o AutoSort com a legenda localizada da medida, grafada exatamente como o Excel a mostra. Num Excel em língua chinesa a legenda predefinida de um campo Units somado é '求和项:Units'; passar 'Sum of Units' nessa máquina não levanta erro e não muda nada. O mesmo script que funciona num Excel inglês torna-se silenciosamente um no-op noutro lugar, por isso leia a legenda do campo de dados em vez de a codificar à mão

• O PivotField.SortType não podia ser lido de volta através do COM na build do Excel que testámos, por isso verifique a ordenação a partir do XML guardado ou das 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; a renderização ordenada sob pais de grupo manual não é verificada
• Escritas no PivotField.Outline e no PivotField.LayoutForm eram rejeitadas nessa build também, por isso defina um layout de contorno para uma fixture através do PivotTable.RowAxisLayout em vez disso

Nenhuma dessas esquisitices muda o formato de ficheiro, mas decidem se um teste de regressão prova alguma coisa. Um teste que chama o AutoSort com a legenda errada passa contra uma pivot que nunca foi ordenada

FAQ

O que significa o campo 4294967294 num ficheiro XML PivotTable?

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

O HotXLS ordena itens pivot por valor sem Excel instalado?

Sim. O HotXLS avalia a ordenação ele próprio quando materializa a grelha pivot, usando os totais por membro do campo de dados escolhido, e escreve sortType, rankBy e opcionalmente autoSortScope para que o Excel mantenha a mesma ordem depois de uma atualização. Só a ordem de empates entre valores iguais pode diferir do Excel

Por que os meus itens pivot estão ordenados alfabeticamente em vez de por valor?

Normalmente porque o campo tem uma direção de ordenação mas nenhuma medida. Confirme 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 colapsados e o resto do pipeline pivot nativo viajam no HotXLS, o componente de folhas de cálculo Delphi para XLS e XLSX, que lê, edita e escreve workbooks Excel sem Excel instalado. Se está a construir a pivot do zero primeiro, o tutorial do HotXLS sobre construir e atualizar tabelas dinâmicas cobre caches, campos e atualização antes de chegar a ordená-los