Почти каждый элемент устаревшего двоичного формата Excel представляет собой одну запись с двухбайтовым типом и двухбайтовой длиной. Ячейка является записью LABELSST или NUMBER, а объединенная область описывается как MERGEDCELLS. Вы можете прочитать большую часть рабочего листа, последовательно обходя записи и распределяя их по типу. Сводные таблицы нарушают этот простой ритм. Сводная таблица представляет собой не отдельную запись, а небольшую программу, состоящую из десятков взаимосвязанных записей, распределенных по двум разным местам в потоке OLE-документа, а связи между ними позиционны, упакованы на уровне бит и требуют абсолютной точности. Большинство средств чтения BIFF8 либо полностью пропускают эту структуру, либо сохраняют ее в виде неразбираемых байтов, поскольку ее запись с нуля требует воссоздания всех перекрестных ссылок, поддерживаемых самим Excel
Сложность сводной таблицы объясняется тем, что она объединяет два независимых артефакта. Это кэш сводной таблицы (автономный снимок исходных данных со своим собственным подпотоком) и представление таблицы (макет, определяющий расположение полей на осях). Кэш и представление ссылаются друг на друга по индексам. Ошибка в любом индексе приводит к сбою обновления или к пустому листу при открытии файла
Кэш сводной таблицы как отдельный подпоток
Кэш находится в глобальном потоке книги в виде полноценного подпотока BIFF, начинающегося с записи BOF с типом документа 0x0006 (значение для кэша сводной таблицы, в отличие от 0x0005 для книги или 0x0010 для рабочего листа) и завершающегося записью EOF. Внутри этого блока структура жестко определена. Запись SXDB служит заголовком кэша. Она содержит количество записей, число полей кэша и идентификатор потока, который представление таблицы будет указывать для связи с этим кэшем. Каждая колонка источника представлена записью определения поля SXFDB и классифицирующей записью SXFDBType, за которыми следуют уникальные значения этой колонки по одной типизированной записи элемента на каждое уникальное значение
Записи элементов составляют основу полезной работы кэша. Текстовое значение преобразуется в SXSTRING, числовое в SXNUM, логическое в SXBOOLEAN, а ошибка формулы в SXERR. Кэш хранит не исходную сетку ячеек, а именно уникальные значения для каждого поля и таблицу индексов, указывающую, какой элемент выбран для каждого поля в записи n. Поэтому программное построение сводной таблицы отличается от обычного копирования ячеек. Вам необходимо просканировать исходный диапазон, определить тип каждого поля по его значениям, устранить дубликаты и записать каждую строку как кортеж индексов элементов. HotXLS работает именно так: числовая колонка выводится в виде элементов SXNUM, колонка со смешанным текстом преобразуется в элементы SXSTRING, а даты передаются как порядковые значения по числовому пути
SXDBB и особенности упаковки битов
Таблица индексов для каждой записи является наиболее интересной частью всей структуры и содержится в записи SXDBB. Простое кодирование хранило бы индекс каждого поля как 16-битное слово. Excel оптимизирует этот процесс, упаковывая индекс поля ровно в то количество бит, которое необходимо для адресации элементов этого поля. Ширина составляет ceil(log2(itemCount + 1)) бит. Прибавление единицы имеет значение: дополнительное значение является признаком отсутствия данных в поле для текущей записи. Поэтому для поля с тремя уникальными элементами нужно закодировать четыре состояния, что требует двух бит вместо одного. Поле без элементов не занимает биты и полностью пропускается при упаковке
Биты одной записи объединяются по всем полям, после чего следующая запись начинается с границы нового байта. Записи выравниваются по байтам, а не упаковываются непрерывно, что упрощает произвольный доступ к таблице ценой добавления нескольких битов выравнивания на строку. Упаковка внутри байта выполняется от младшего бита к старшему. С учетом этих правил кодировщик представляет собой обычный побитовый конвейер, а декодер зеркально повторяет его работу
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;Эту деталь нельзя игнорировать из-за ограничения размера одной записи BIFF в 8224 байта. Любая запись формата (включая записи сводных таблиц) должна умещать свои полезные данные в пределах этого лимита, а большой кэш сводной таблицы с тысячами строк превысит этот объем задолго до записи всех данных. Поэтому таблица индексов разбивается на части. HotXLS ограничивает тело отдельной записи SXDBB размером 8220 байт (лимит 8224 минус 4 байта заголовка записи с типом и длиной), делит это значение на ширину одной упакованной записи для расчета вместимости строк в одну запись и затем выводит необходимое количество связанных записей SXDBB. Каждое продолжение начинается с границы новой записи, поэтому строка никогда не разрывается между записями. Программа чтения, знающая битовую ширину одной записи, может последовательно считывать все блоки SXDBB как один непрерывный битовый массив
Макет представления: SXLI для тела таблицы, SXPI для фильтра страницы
После построения кэша создается представление таблицы. Его основой являются элементы строк осей, перечисляющие все комбинации значений строк и колонок, выводимые в таблице. Они хранятся в записях SXLI (тип записи 0x00B5, описана в [MS-XLS] §2.4.275). Одна запись SXLI содержит много строк до достижения предела в 8224 байта, после чего создается новая запись. При этом используется сжатие: каждая строка сохраняет только отличие от предыдущей строки в виде количества общих префиксов, благодаря чему при глубокой вложенности внешние значения полей не дублируются в каждой строке. Итоговая строка и первая строка любой новой записи сбрасывают счетчик префикса в ноль, избавляя средство чтения от необходимости анализировать предыдущие записи для восстановления строки
Фильтры страниц, расположенные над сводной таблицей, записываются в отдельную запись SXPI (тип записи 0x00B6, [MS-XLS] §2.4.276), которая содержит по одной десятибайтовой записи на каждое поле страницы: индекс поля isxvd, выбранный элемент кэша iCache, слово позиции ipos и устаревший идентификатор объекта objId. Особое внимание следует обратить на значение iCache. Поле страницы со значением «(Все)», не фильтрующее данные, сохраняет значение 0x7FFD вместо индекса элемента. Программно созданная сводная таблица открывается со значением фильтра «(Все)» по умолчанию, пока вызывающий код не выберет конкретный элемент, индекс кэша которого заменит это значение, после чего Excel откроет файл с уже примененным фильтром. Рядом находятся вспомогательные записи описания полей и форматирования: SXVD и SXVDEx для определений представлений полей, SXIVD для списков индексов полей осей и SXFormat для форматирования чисел, каждый из которых ссылается на тот же кэш
Два механизма записи: исходные блоки данных и типизированная модель
HotXLS поддерживает два независимых пути записи сводной таблицы для обеспечения абсолютной точности данных. Когда книга считывается с диска, записи сводной таблицы, созданные Excel или другой программой, могут содержать особенности структуры, специфичный порядок или служебные записи расширений, которые сторонний инструмент не моделирует. Единственным надежным способом обработки таких байтов является их сохранение в неизменном виде. Сводная таблица, импортированная из файла, помечается флагом FromRawBlobs = True, и при сохранении средство записи воспроизводит сохраненные блоки записей побайтово. Никакие данные не генерируются повторно, сохраняя байтовую идентичность при перезаписи
Сводная таблица, создаваемая программно, обрабатывается иначе. Здесь нет исходных байтов для сохранения, есть только типизированная объектная модель: TXLSPivotCache с полями и списками элементов, а также TXLSPivotTable с распределением осей. Такая таблица получает флаг FromRawBlobs = False, и средство записи сериализует ее с нуля: выводит подпоток кэша BOF = 0x0006, упаковывает таблицу индексов SXDBB и формирует записи SXLI и SXPI. Этот флаг позволяет обоим типам таблиц сосуществовать в одной книге. Без него средство записи было бы вынуждено либо жертвовать точностью импортированных таблиц, либо отказаться от создания новых. Записи расширений импортированных таблиц сохраняются как дополнительные и доступны через список SupplementalRecords, гарантируя полноту данных при анализе через модель
Создание сводной таблицы в коде
Вся описанная работа скрыта за одним вызовом. Метод AddPivotTable принимает исходный диапазон в формате A1, координаты левой верхней ячейки для привязки таблицы и ее имя. Он анализирует диапазон, сканирует данные для определения типов полей и строит кэш (повторно используя существующий кэш, если другая таблица привязана к тому же диапазону), после чего возвращает объект TXLSPivotTable. Все его поля изначально находятся вне осей: вы самостоятельно распределяете их по осям и выбираете тип агрегации данных. Кэш, упаковка SXDBB и записи представления формируются автоматически при сохранении документа
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;Первая строка исходного диапазона считывается как заголовок с именами полей кэша, поэтому вызов AddRowField('Region') находит колонку по тексту ее заголовка, а не по позиции. Поскольку возвращаемая таблица представляет собой типизированную модель с флагом FromRawBlobs = False, средством записи строит ее с нуля: создает автономный кэш, который не зависит от исходного диапазона при последующем обновлении, что необходимо при отправке файла пользователям, которые могут изменить или удалить исходные данные
Считывание и согласование записей сводных таблиц и кэша в сторонних файлах, включая путь сохранения исходных блоков данных, описаны в руководстве по аудиту книг и конвертации данных. Когда исходный диапазон содержит десятки тысяч строк, а поток SXDBB занимает много связанных записей, методы из заметок о производительности больших книг помогут избежать высокой нагрузки при построении кэша. Оба решения дополняют механизм записи сводных таблиц, поставляемый в составе компонента HotXLS для Delphi и C++Builder вместе с API для работы с ячейками, формулами, диаграммами и форматированием