Почти всяка част от наследения двоичен формат на Excel е единичен запис с изчистен двубайтов тип и двубайтова дължина. Дадена клетка е LABELSST или NUMBER. Слята област е MERGEDCELLS. Можете да прочетете по-голямата част от работния лист, като обхождате записите един по един и разпределяте по думата за тип. Обобщените таблици (PivotTables) нарушават този ритъм. Единичната обобщена таблица не е просто запис, тя е малка програма, съставена от десетки сътрудничещи си записи, разпръснати на две различни места в същия OLE комбиниран документен поток, а връзките между тях са позиционни, битово пакетирани и безмилостни. Това е структурата, която повечето BIFF8 четци или прескачат изцяло, или запазват като непрозрачни байтове, тъй като записването на такава структура от нулата означава възпроизвеждане на всяка кръстосана препратка, която самият Excel поддържа.
Причината обобщената таблица да е трудна за изграждане е, че тя всъщност представлява два заварени заедно артефакта. Има кеш на обобщената таблица (pivot cache) – самостоятелна снимка на изходните данни със собствен подпоток, и има изглед на таблицата (table view) – оформлението, което казва кои полета на коя ос стоят. Кешът и изгледът се реферират един друг чрез индекс. Сбъркайте един индекс и файлът се отваря с грешка при опресняване или с тиха, празна решетка.
Кешът на обобщената таблица е самостоятелен подпоток
Кешът живее в глобалния поток на работната книга като пълен 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 е важно: допълнителната стойност е маркер със значение „празно, няма стойност за това поле в този запис“, така че поле с три уникални елемента трябва да представя четири състояния и следователно изисква два бита, а не единия бит, който три елемента сами по себе си биха подсказали. Поле без никакви елементи допринася с нула бита и се прескача изцяло по време на пакетирането.
Битовете за один запис са конкатенирани във всички полета, след което следващият запис започва на нова граница на байта. Записите са подравнени по байтове, а не битово пакетирани край до край, което прави произволния достъп до таблицата възможен за сметка на няколко бита за запълване на ред. Пакетирането в рамките на байт е с най-малко значимия бит на първо място. След като приемете тези две правила, кодиращият механизъм е проста битова помпа, а декодиращият е негово огледало.
// 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 байта не наложи нов запис, и той използва малък трик за компресиране: всяка линия съхранява само как се различава от линията над нея, изразено като брой общи префикси, така че дълбоко вложена ос не повтаря стойностите на външното поле на всеки ред. Линията за общата сума (grand-total) и първата линия на всеки запис винаги нулират този брой префикси, така че четецът никога да не трябва да гледа назад през границата на записа, за да възстанови линия.
Оста на страницата – падащите менюта за филтри, които седят над обобщената таблица, е отделен запис. SXPI (тип запис 0x00B6, [MS-XLS] §2.4.276) носи един десетбайтов запис за всяко поле на страницата: индекс на полето на обобщената таблица isxvd, избрания елемент в кеша iCache, дума за позиция ipos и наследствено ID на обект objId. Стойността на iCache е тази, която трябва да се следи. Поле на страница, което показва „(All)“, без да филтрира нищо, съхранява маркера 0x7FFD вместо реален индекс на елемент. Програмно изградена обобщена таблица се отваря с всяко поле на страницата, зададено на „(All)“, докато извикващият не избере предварително елемент, в който момент индексът на този елемент в кеша замества маркера и Excel се отваря с вече приложен филтър. Заедно с тях стоят поддържащите записи, които описват отделните полета и тяхното форматиране (SXVD и SXVDEx за дефиниции на изглед на поле, SXIVD за списъци с индекси на полета, които подреждат всяка ос, и SXFormat за форматиране на числа), като всеки от тях се индексира обратно в същия кеш, който редовете на тялото реферират.
Два начина на записване в едно: необработени блокове (raw blobs) и типизиран модел
Има структурна причина 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, софтуерът за записване поема по пътя от нулата: той изгражда самостоятелен кеш, който не зависи от това дали изходният диапазон все още присъства по време на опресняване, което е точно свойството, което искате, когато обобщената таблица се изпраща на получател, който може да премести или изтрие основните данни.
Реждането и съгласуването на обобщените и кеш записите на файл, който не сте произвели, включително пътя за запазване на raw-blobs, са покрити в ръководството за одит на работна книга и работна среда за преобразуване. Когато изходният диапазон достигне десетки хиляди редове и SXDBB потокът обхваща много продължени записи, техниките в бележките за производителност на големи работни книги предпазват изграждането на кеша да доминира времето за изпълнение. И двете се допълват с обобщения софтуер за запис, който се доставя в HotXLS spreadsheet component за Delphi и C++Builder, заедно с API за клетки, формули, диаграми и форматиране, разгледани на други места в този блог.