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

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

Pivot Show Values As в Delphi: ранг и накопительный итог

HotXLS Delphi Component

HotXLS задаёт режимы «Show Values As» у Excel pivot table из Delphi через два свойства на TXLSPivotDataField: ShowDataAs для классических режимов вроде накопительного итога, разницы и процента от строки, и ExtendedShowDataAs для ранга, процента от родителя и процента накопительного итога. TXLSXWorkbook.MaterializeNativePivotTable затем пишет вычисленную сетку так, как написал бы Excel. Два свойства взаимоисключающие, а расширенные зависят от URI расширения, который Excel сверяет с учётом регистра

Последняя деталь — причина существования этого поста. Pivot table с колонкой ранга выглядит мелочью для генерации, и при первом открытии обычно ею и является. Беда начинается, когда пользователь жмёт Refresh в Excel, и колонка ранга без всякого предупреждения превращается обратно в простую сумму. Сделать Show Values As правильно — значит правильно сделать два XML-словаря плюс горстку числовых правил, которые Excel никогда не документирует в UI: как ранжируются равные значения, что значит накопительный процент от среднего и какая пустая ячейка станет пустой, а какая — ошибкой

Почему на одно меню Excel — два свойства?

Excel показывает одно меню «Show Values As», но файл хранит его в двух разных местах, поэтому HotXLS открывает два свойства. Исходные режимы из Excel 2007 живут в атрибуте showDataAs элемента dataField внутри pivotTableDefinition и используют перечисление ECMA-376 ST_ShowDataAs. Режимы, добавленные в Excel 2010, — ранг, родительские и накопительный проценты — в то перечисление не влезли, поэтому ушли в элемент x14:dataField с атрибутом pivotShowAs, завёрнутым в блок расширения extLst на том же поле данных

TXLSPivotShowDataAs отражает первую группу: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal и xlpsdaIndex. TXLSPivotExtendedShowDataAs покрывает вторую: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending и xlpesdaRankDescending, с xlpesdaNone в качестве нейтрального значения. Поле данных использует одну группу или другую, никогда обе. Если оставить ShowDataAs в ненормальном значении и вдобавок задать расширенный режим, MaterializeNativePivotTable вернёт -1 и оставит существующий вывод ровно как был — включая ячейки и границы pivot

BaseField и BaseItem несут цель сравнения. BaseField — это индекс с нуля в списке полей pivot table, а не в именах заголовков исходных колонок, и он управляет сравнениями, накопительными итогами, процентом от выбранного родителя и рангами. BaseItem — позиция элемента с нуля внутри этого поля или одна из констант xlPivotBaseItemPrevious и xlPivotBaseItemNext для «сравнить с соседом». Проценты родительской строки и родительской колонки игнорируют BaseField целиком: они берут самое внутреннее поле на соответствующей оси

Сборка ранжированного pivot в Delphi

Кратчайший рабочий путь — собрать pivot, задать режим отображения на поле данных и материализовать его перед сохранением. Пример ниже ранжирует суммы по регионам внутри каждой категории по убыванию, так что крупнейшая сумма в каждой строке получает ранг 1

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Measure: TXLSPivotDataField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Category';
    Data.Cells[1, 2].Value := 'Region';
    Data.Cells[1, 3].Value := 'Amount';
    // ... заполнить строки 2..7 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
    Pivot.AddRowField('Category');     // поле pivot 0
    Pivot.AddColumnField('Region');    // поле pivot 1
    Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
    Measure.DisplayName := 'Rank by Region';

    Measure.ShowDataAs := xlpsdaNormal;              // базовый режим остаётся выключен
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // ранг по Region

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

Здесь окупаются две привычки. Во-первых, задавайте ShowDataAs := xlpsdaNormal явно при выборе расширенного режима, даже на свежем поле: один и тот же код часто переиспользуется против импортированного pivot, чьё поле данных уже несёт базовый режим. Во-вторых, проверяйте возвращаемое значение. Отвергнутая материализация атомарна — это хорошо, — но это также значит, что пропущенная проверка отгрузит файл с прежней сеткой внутри

BaseField переживает сохранение, даже если вы ни разу не вызвали MaterializeNativePivotTable. HotXLS пишет атрибут baseField для процента от родителя, процента накопительного итога и обоих режимов ранга сразу при задании расширенного режима, так что книга, сохранённая сразу после конфигурации, всё равно скажет Excel, по какому полю ранжировать при refresh

Что ломается с URI расширения?

Блок расширения, несущий pivotShowAs, идентифицируется URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, и Excel сверяет эту строку с учётом регистра при refresh. Заметьте строчную e в 4e99. GUID обычно нечувствительны к регистру, и многие XML-писатели приводят их к верхнему — это ровно сценарий отказа: файл с 4E99 открывается в Excel со всеми кэшированными ячейками правильными, потому что Excel просто рисует сохранённые значения. Но после RefreshTable Excel больше не распознаёт расширение, сбрасывает расширенный режим и пересчитывает колонку как обычную сумму. Ни запроса на починку, ни ошибки — просто колонка ранга тихо стала итогами

HotXLS пишет литеральный URI так, как его ждёт Excel, поэтому это кусает вас, только если что-то ниже по конвейеру переписывает пакет — например, шаг постобработки, нормализующий регистр GUID или пересобирающий элементы extLst. Ловящий это тест дёшев: откройте сгенерированный файл в Excel, сделайте refresh pivot и убедитесь, что расширенная колонка всё ещё показывает ранги или проценты, а не сырые агрегаты. Тест, который только читает кэшированные ячейки, будет проходить на битом файле каждый раз

Как Excel ранжирует равные значения и упорядочивает накопительные итоги?

Ранг pivot в Excel — dense rank, и HotXLS его воспроизводит. При убывающих суммах 30, 30 и 20 результат — 1, 1 и 2, а не спортивные 1, 1 и 3, которые даст RANK.EQ на листе. Равенство считается одним различным предшествующим значением. Если отчёту нужно спортивное ранжирование, считайте его в исходных данных или формулой на листе рядом с pivot: ни один режим отображения pivot его не выдаёт

Накопительные итоги, их проценты и сравнения с предыдущим или следующим следуют порядку, в котором элементы реально показаны, а не порядку списка элементов поля. Это различие становится важным, как только вызывающий меняет сортировку после построения кэша

// Строки отсортированы по убыванию: накопительный итог копится в порядке отображения
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // % разницы от...
Measure.BaseField := 0;                    // ...поля Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...предыдущий видимый элемент

if Book.MaterializeNativePivotTable(Pivot) <> 1 then
  raise Exception.Create('Pivot display mode was rejected');

После смены SortType на xlpfstDescending «предыдущий» означает элемент, показанный выше, а накопительный итог стартует с вершины отсортированного списка. Реализация, идущая по сохранённому списку элементов, выдаст числа, выглядящие правдоподобно и расходящиеся с Excel в момент первого же refresh. Краевые элементы тоже требуют внимания: у первого элемента нет предыдущего соседа, и только percent-of режим сравнивает краевой элемент с самим собой; режимы разницы оставляют эту ячейку собственному краевому поведению Excel, а не выдумывают ноль

Почему накопительный процент может превысить 100 процентов?

Процент накопительного итога по средним может законно перевалить за 100 процентов: Excel копит агрегаты показанных элементов и делит на агрегат, не являющийся их суммой. Для сумм это одно и то же, поэтому последний элемент останавливается ровно на 100 процентах. Для средних сложить три средних по категориям и разделить на общее среднее — не доля ничего, и финальная строка может уйти заметно выше 100 процентов. HotXLS здесь нарочно совпадает с Excel, а не зажимает значение: зажатое число разошлось бы с файлом после refresh

Делитель зависит и от того, где сидит базовое поле. Когда базовое поле — самое внутреннее на своей оси, делитель — исходный агрегат для этой позиции оси. Когда базовое поле — внешнее поле во вложенной раскладке, делитель — сумма по совпадающим элементам оставшейся оси. Обе позиции сверялись с выводом Excel независимо: правило, работающее на одном уровне вложенности и тихо падающее на другом, — классический pivot-баг

Простые проценты ведут себя иначе, чем накопительный. Процент от строки, колонки и общего итога делит на исходный агрегат в соответствующем охвате, в том числе для неаддитивных агрегаций вроде средних: процент от общего по средним — это среднее ячейки, делённое на общее среднее, а не доля просуммированного пула

Пустые, #NULL! и #DIV/0! в режимах сравнения

Сравнения percent-of-base различают три пустые ситуации, и каждая даёт свою ячейку. Когда на пересечении числителя нет исходных строк, ячейка показывает #NULL!. Когда у базового элемента на этом пересечении нет данных, ячейка пуста. Когда базовый элемент существует и его агрегат равен нулю, ячейка показывает #DIV/0!. Код, читающий материализованную сетку, должен проверять через VarIsEmpty и VarIsError, а не считать, что каждая ячейка значений держит число

Простые проценты снисходительнее: отсутствующий числитель считается там нулём. Проценты родительской строки и родительской колонки добавляют ещё один случай: pivot без поля на соответствующей оси не имеет родителя, на которого делить, поэтому такие результаты пусты. Общие итоги тоже следуют семантике каждого режима, включая пустые итоги вдоль оси, по которой идёт сравнение или накопительный итог; строка итогов из одних пустых под накопительным итогом — это правильно, а не провал материализации

Смена режимов на импортированном pivot

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

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // очистить базовый режим
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // BaseField не нужен
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

При смене режима HotXLS убирает устаревшие атрибуты showDataAs, pivotShowAs и baseItem из staged-определения, так что сохранённый файл никогда не несёт протухший базовый режим рядом с новым расширенным. Тот же шаг подготовки держит флаги compact и outline на полях данных и неактивных полях в согласии с раскладкой отчёта — это важно, потому что Excel выводит подписи данных после refresh из этих флагов, а не только из полей строк и колонок. Невалидные значения перечислений тоже отвергаются: приведение байта 255 к любому из перечислений может знакорасшириться до -1 в Delphi, поэтому HotXLS проверяет и нижнюю, и верхнюю границу и возвращает -1, не трогая сетку

Если к тому же отчёту вы добавляете calculated fields или calculated items, сперва прочтите построение и refresh pivot table с calculated fields в HotXLS, а о правилах XML на уровне полей, удерживающих Excel от починки файла, — валидность схемы полей XLSX pivot в Delphi

FAQ

Может ли поле данных HotXLS использовать ShowDataAs и ExtendedShowDataAs вместе?

Нет. TXLSPivotDataField.ShowDataAs и ExtendedShowDataAs взаимоисключающие: при заданных обоих MaterializeNativePivotTable вернёт -1 и оставит прежний вывод без изменений

Почему мой pivot-ранг после refresh в Excel превратился в сумму?

Скорее всего, URI расширения переписали с другим регистром. Excel сверяет {E15A36E0-9728-4e99-A89B-3F7291B0FE68} с учётом регистра и после RefreshTable откатывается к простой агрегации, если совпадения нет

Пропускает ли pivot-ранг HotXLS числа после равенства?

Нет. HotXLS следует dense rank pivot в Excel, поэтому 30, 30 и 20 получают ранги 1, 1 и 2

Поддержка Show Values As, нативная материализация pivot и остальной набор возможностей XLSX и XLS входят в HotXLS Delphi spreadsheet component для Delphi и C++Builder