Майже кожна частина застарілого бінарного формату Excel є окремим записом із чітким двобайтовим типом та двобайтовою довжиною. Комірка - це LABELSST або NUMBER. Об'єднана область - це MERGEDCELLS. Ви можете прочитати більшу частину робочого аркуша, обходячи записи по черзі та вибираючи дії на основі слова типу. Зведені таблиці (PivotTables) порушують цей ритм. Зведена таблиця - це не один запис, а невелика програма, що складається з десятків взаємопов'язаних записів, розташованих у двох різних місцях одного потоку складеного документа 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)) бітів. Додавання + 1 має значення: додаткове значення є маркером (sentinel), що означає "порожньо, немає значення для цього поля в цьому записі". Тому поле з трьома унікальними елементами має представляти чотири стани і відповідно вимагає двох бітів, а не одного, як можна було б подумати для трьох елементів. Поле без жодного елемента взагалі не дає бітів і повністю пропускається під час упакування.
Біти одного запису об'єднуються для всіх полів, а потім наступний запис починається з нової межі байта. Записи вирівнюються за байтами, а не пакуються бітами від початку до кінця, що робить довільний доступ до таблиці реалізованим за рахунок кількох бітів вирівнювання на рядок. Упакування всередині байта виконується починаючи з молодшого значущого біта. Якщо прийняти ці два правила, кодувальник стає простим бітовим насосом, а декодер - його дзеркальним відображенням.
// 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;Причина, чому цю деталь не можна ігнорувати, - обмеження у 8224 байти для одного запису BIFF. Кожен запис у цьому форматі, включаючи записи зведення, повинен вміщувати своє корисне навантаження щонайбільше у 8224 байти, і великий кеш зведення з тисячами вихідних рядків вийде за цю межу задовго до виведення всіх рядків. Тому таблиця індексів розділяється. HotXLS обмежує тіло одного SXDBB розміром 8220 байтів (ліміт запису 8224 мінус чотири байти заголовка типу та довжини), ділить це на ширину одного упакованого запису в байтах для визначення кількості рядків, що вміщуються, і виводить стільки продовжених записів 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 для форматування чисел, кожне з яких посилається на той самий кеш, що й лінії тіла.
Два записувачі в одному: необроблені двійкові дані (blobs) та типізована модель
Існує структурна причина, чому HotXLS підтримує два повністю окремі шляхи для запису зведеної таблиці, і вона випливає безпосередньо з вимог до точності відтворення. Коби книга зчитується з диска, її записи зведення вже були записані Excel або іншим інструментом, і вони можуть використовувати варіанти записів, особливості впорядкування або записи розширень, які жоден сторонній інструмент повністю не моделює. Єдиний безпечний спосіб поводження з цими байтами - повернути їх без змін. Тому зведена таблиця, отримана з файлу, позначається прапорцем FromRawBlobs = True, і під час збереження записувач відтворює збережені двійкові записи (blobs) дослівно. Ніщо не генерується заново, ніщо не переінтерпретується, і повний цикл відкриття-збереження залишається стабільним на рівні байтів.
Зведена таблиця, створена програмою, є протилежним випадком. Тут немає оригінальних байтів для збереження, є лише типізована модель об'єктів: 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 комірок, формул, діаграм та форматування, описаними в інших статтях цього блогу.