LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

Техническая статья

Сортировка pivot по значению в Delphi: autoSortScope и -2

HotXLS Delphi Component

HotXLS сортирует элементы PivotTable по значению в Delphi через два свойства на TXLSPivotField: SortType выбирает возрастание или убывание, а SortDataField называет нулебазное поле данных, чьи итоги задают порядок. Открывая созданный Excel XLSX, HotXLS восстанавливает то поле данных из элемента autoSortScope, где Excel хранит поле Values как field="4294967294" — беззнаковое написание -2

Сортировка по значению — запрос, который рано или поздно получает каждый отчёт о продажах: «покажи регионы от большего к меньшему». В интерфейсе Excel это два клика. В формате файла это атрибут, вложенная pivot-область, зарезервированный индекс поля и ordinal выбранного поля данных, и читатель, промахнувшийся мимо хоть одной из тех частей, всё равно открывает файл без жалоб. Просто сортирует не по тому

Где Excel хранит меру для сортировки по значению?

Excel хранит меру сортировки только внутри autoSortScope, а не на самом поле. Атрибут sortType на <pivotField> (значение ST_FieldSortType из ECMA-376 Part 1: manual, ascending или descending) говорит, в какую сторону сортировать. А чем мерить — живёт в дочернем <autoSortScope>, который оборачивает <pivotArea>, чья ссылка на ось данных выбирает одно поле данных

<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>

Элемент reference обычно указывает на pivot-поле по индексу. Индекс поля -2 зарезервирован в части PivotTable ECMA-376 за псевдополем Values — тем, что Excel показывает как "Values", когда две и более мер сидят на оси. Атрибут типизирован как беззнаковое 32-битное целое, поэтому -2 уходит по проводам как 4294967294. Дочерний <x v="0"/> под ним выбирает поле данных 0 — первую запись в <dataFields>. Есть ещё атрибут rankBy на <pivotField>, но в сравненных нами файлах Excel его опускал и полагался на одну только область

Excel хранит сортировку по значению PivotTable в ссылке pivotArea внутри autoSortScope, где поле 4294967294 — беззнаковое 32-битное написание -2 для псевдополя Values, а потомок x выбирает поле данных, по которому сортирует HotXLS
Вложенная ссылка превращает абстрактное направление сортировки в конкретную меру: x v=0 указывает на поле данных 0 — тот ordinal, который HotXLS выставляет как SortDataField на TXLSPivotField

Почему мера сортировки возвращалась как -1?

Читатель HotXLS изначально терял меру сортировки, потому что 4294967294 не влезает в знаковое 32-битное целое. Хелпер атрибутов — тонкая обёртка над StrToIntDef, и вне-диапазонное значение тихо возвращает значение по умолчанию, поэтому тест на ось данных никогда не совпадал, SortDataField оставалось на -1, а поле держало только sortType="descending" без всякой меры

Сбой был тихим наихудшим образом. Поле убывающей сортировки без меры всё равно сортирует, просто по метке элемента в обратную сторону, поэтому отчёт, который Excel упорядочил по итогу, возвращался из HotXLS как обратный порядок элементов. Ничего не поднимало исключения, числа в каждой ячейке были верны. Неверен был только порядок строк, и только у файлов, созданных Excel

Фикс в lxPivotXml.pas сравнивает сырой токен атрибута как строку до всякой целочисленной конверсии. Если rankBy уже задал меру, побеждает он; иначе ссылка, чей токен field равен ровно '4294967294', разрешает SortDataField из своего выбранного значения x. Вычисляющей стороне изменения не понадобились: компаратор уже финализировал по-членные итоги на осях строк и колонок, обрабатывал убывание и разбивал ничьи. Данные просто никогда до него не доезжали

Читатель pivot в HotXLS терял сортировку по значению Excel, потому что 4294967294 переполняет знаковый 32-битный StrToIntDef и оставлял SortDataField на -1; фикс сравнивает сырой токен-строку до всякой целочисленной конверсии
Старый путь вырождал убывающую сортировку по значению в обратную сортировку по метке без исключений; новый путь в lxPivotXml.pas разрешает меру всякий раз, когда токен поля равен ровно 4294967294

Общий урок применим к любому читателю OOXML, написанному на Delphi. ECMA-376 типизирует несколько индексных атрибутов как xsd:unsignedInt и затем использует верх диапазона как сентинел, поэтому знаковый читатель с fallback-значением по умолчанию превращает зарезервированное значение в «атрибут отсутствует». Сравнивайте токен или парсите в Int64 или Cardinal всюду, где индекс поля может означать Values

Сортировка pivot-элементов по мере из кода Delphi

Чтобы отсортировать pivot-поле HotXLS по значению, поставьте SortType в xlpfstAscending или xlpfstDescending, а SortDataField — в нулебазный индекс меры в списке DataFields таблицы. При SortType = xlpfstManual, что по умолчанию, SortDataField игнорируется, и элементы держат кешовый или программный порядок

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';
    // ... заполнить строки 2..41 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // поле данных 0

    Region.SortType := xlpfstDescending;  // сначала самый большой регион
    Region.SortDataField := 0;            // сортировать по Sum of Units
    Region.AutoSortScope := '0';          // также выпустить собственный autoSortScope 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;

Две детали заслуживают заметки. Писатель HotXLS выпускает SortDataField как rankBy, и при непустом AutoSortScope он ещё пишет блок autoSortScope со ссылкой на ось данных 4294967294, кладя строку свойства в слот x v. При загрузке AutoSortScope получает тот же токен x v, так что для сортировок по значению свойство держит ordinal поля данных как текст. Установка его в '0' потому даёт файл, несущий меру в обоих местах, — ближайшее совпадение с тем, что пишет сам Excel. MaterializeNativePivotTable затем ставит на подготовку отсортированную сетку вместе с её метаданными раскладки и возвращает 1 при успехе или -1 с диагностикой

Сортируются не только строки элементов. Колонковые поля идут через тот же компаратор, используя итоги по колонкам вместо итогов по строкам, поэтому колонковое поле с SortType = xlpfstDescending и валидным SortDataField упорядочивает свои колонки по значению ровно так же

Инспекция сортировки, которую книга Excel уже несёт

Для файлов, созданных Excel, HotXLS заполняет SortType, SortDataField и AutoSortScope при загрузке определения pivot, так что аудит или смену сортировки отчёта можно делать без перестройки таблицы. Сниппет ниже перечисляет каждое поле, сортирующее по мере, и называет меру по её 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;

    // перевернуть сортировку Region на возрастание и перестроить существующую сетку
    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;

Второй аргумент MaterializeNativePivotTable велит HotXLS принять существующий выходной диапазон pivot вместо трактовки его как коллизии. Тот же паттерн появляется в руководстве по Show Values As: ранг и накопительные итоги в HotXLS, где порядок сортировки важен, потому что накопительные итоги складываются в порядке показа

Как HotXLS разбивает ничьи, когда у двух элементов одинаковое значение?

HotXLS разбивает ничьи по метке элемента, детерминированно, что является известным отличием от Excel. Когда у двух членов одинаковые итоги, компаратор откатывается к сравнению их заголовков, и флаг убывания разворачивает и то сравнение. Элементы, чья метрика не валидное число, сортируются после валидных при возрастании, а флаг убывания разворачивает всё сравнение целиком, включая и ту группировку

Excel вообще не обещает никакого порядка ничьих. В тестовой фикстуре, где четыре члена A, B, C и D имели одинаковый итог, возрастающая сортировка в Excel дала B, C, A, D. Ничто в сохранённых метаданных не предсказывает ту перестановку; это побочный продукт нестабильной сортировки, и между файлами она может различаться. HotXLS возвращает A, B, C, D на тот же вход. Наша верификация для того случая проверяет множество членов и по-членные значения, а не последовательность, а убывающая фикстура с ничьей 15/15 совпала с Excel ячейка в ячейку. Если ваш нижестоящий код зависит от точной позиции ничейных строк, сортируйте по второй мере или сделайте значения различимыми

HotXLS разбивает ничьи сортировки pivot Excel по значению в Delphi детерминированно по заголовку элемента, возвращая A, B, C, D для четырёхчленной фикстуры, где нестабильная сортировка Excel дала B, C, A, D
Равные итоги проваливаются к сравнению заголовков, которое флаг убывания тоже разворачивает, поэтому между двумя движками может отличаться лишь порядок ничейных строк

Свёрнутые члены и самое внутреннее поле

Свёрнутые члены материализуются в HotXLS как одна агрегатная строка на свёрнутый элемент. Скрывая детали элемента, Excel пишет sd="0" на том <item>; HotXLS выставляет это как TXLSPivotItem.IsDetailHidden. Свёрнутый элемент рендерится со своей простой меткой, заменяет всё своё поддерево, и свёртка применяется к нему под каждым родителем, тогда как охватывающие блоки сохраняют собственные промежуточные итоги

var
  Category: TXLSPivotField;
begin
  // В pivot на строках Category (внешнее) и Product (внутреннее)
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // свернуть первую категорию
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Свёртка взаимодействует с сортировкой предсказуемо: свёрнутая строка всё ещё член своего поля, поэтому позицию она берёт от своего итога, как любой другой элемент. Самое внутреннее поле — другое. У него нет внутреннего уровня, ему нечего сворачивать, и, начиная с раннего фикса, HotXLS не рендерит для него и строки промежуточного итога, даже когда выставлен флаг defaultSubtotal. Excel ведёт себя так же на одно- и многоуровневых осях, а до того изменения HotXLS мог выпустить фантомную строку Total под самым внутренним полем. По полевым атрибутам, замешанным здесь, разбор валидности схемы CT_PivotField в HotXLS рассказывает, чего схема требует от <pivotField> и его элементов

Грабли при сверке с автоматизацией Excel

Когда сверяете сортировку HotXLS с Excel через COM-автоматизацию, вызывайте AutoSort с локализованным заголовком меры, написанным ровно так, как его показывает Excel. На китайскоязычном Excel заголовок по умолчанию для суммируемого поля Units — '求和项:Units'; передача 'Sum of Units' на той машине не поднимает ошибки и не меняет ничего. Тот же скрипт, что работает на английском Excel, в другом месте тихо становится no-op, поэтому читайте заголовок из поля данных, а не хардкодьте

  • PivotField.SortType в протестированной нами сборке Excel через COM не читался обратно, поэтому проверяйте сортировку по сохранённому XML или отрисованным ячейкам
  • В той сборке помещение родительского поля ручной группы на ту же ось вне сортируемого поля подавляло сортировку членов по значению; сортированный рендер под родителями ручных групп не верифицирован
  • Записи в PivotField.Outline и PivotField.LayoutForm в той сборке тоже отвергались, поэтому outline-раскладку фикстуры задавайте через PivotTable.RowAxisLayout

Ни одна из тех причуд не меняет формат файла, но они решают, доказывает ли регрессионный тест хоть что-то. Тест, вызывающий AutoSort с неверным заголовком, проходит против pivot, который никогда не сортировался

FAQ

Что значит поле 4294967294 в XML-файле PivotTable?

Поле 4294967294 — беззнаковое 32-битное кодирование индекса поля -2, зарезервированного ECMA-376 за псевдополем Values. Внутри autoSortScope оно помечает ссылку на ось данных, а его потомок x выбирает, по какому полю данных сортируются элементы

Сортирует ли HotXLS pivot-элементы по значению без установленного Excel?

Да. HotXLS вычисляет сортировку сам при материализации pivot-сетки, используя по-членные итоги выбранного поля данных, и пишет sortType, rankBy и опционально autoSortScope, чтобы Excel после обновления держал тот же порядок. Различаться с Excel может только порядок ничьих среди равных значений

Почему мои pivot-элементы отсортированы по алфавиту, а не по значению?

Обычно потому, что у поля есть направление сортировки, но нет меры. Проверьте, что SortType не xlpfstManual, а SortDataField лежит между 0 и DataFieldCount - 1; при -1 HotXLS упорядочивает элементы по метке в запрошенном направлении

Сортировка по значению, свёрнутые члены и остальной нативный pivot-конвейер поставляются в HotXLS, Delphi spreadsheet component для XLS и XLSX, читающем, правящем и пишущем книги Excel без установленного Excel. Если вы сперва строите pivot с нуля, учебник HotXLS по построению и обновлению pivot-таблиц покрывает кеши, поля и обновление до того, как вы дойдёте до их упорядочивания