Последователь общей формулы в XLSX не несёт текста формулы. Его элемент <f t="shared" si="N"/> указывает на мастер-ячейку в другом месте листа, и читатель должен восстановить текст, сдвинув мастер-формулу на разницу строки и столбца. Компонент HotXLS для Delphi и C++Builder выполняет это раскрытие при открытии, так что каждый последователь сообщает полную формулу
Если вы когда-либо загружали реальный XLSX в стороннюю библиотеку и обнаруживали, что колонка из тысячи формул имеет текст ровно в одной ячейке и пустые строки в остальных 999, вы столкнулись с этой функцией не с той стороны. Ничто не повреждено. Файл делает то, что позволяет ECMA-376, а читатель просто остановился в точке, где остановился XML
Почему ячейка общей формулы пуста?
Потому что формат намеренно хранит формулу один раз. В ECMA-376 части 1 и ISO/IEC 29500-1 элемент <f> (§18.3.1.40) несёт атрибут t типа ST_CellFormulaType, и значение shared означает, что эта ячейка участвует в группе, идентифицированной атрибутом si. Ровно одна ячейка в группе, мастер, также несёт атрибут ref, дающий диапазон, к которому применяется группа, и только эта ячейка несёт текст формулы как содержимое элемента. Каждая другая ячейка в группе — последователь. Она повторяет t="shared" и тот же si, а содержимое её элемента пусто. Excel пишет эти группы агрессивно, потому что протяжка вниз по колонке из 200 000 строк схлопывается с 200 000 строк формул в одну строку плюс 199 999 крошечных элементов-заполнителей. Экономия реальна, и цена целиком ложится на читателя: без раскрытия последователь не имеет смысла сам по себе
Сдвиг — это перевод, а не копирование текста
HotXLS разрешает последователя, находя мастер, зарегистрированный под тем же si, вычисляя дельту строки и столбца от якоря мастера до текущей ячейки и переводя каждую ссылку в мастер-формуле на эту дельту. Относительные измерения двигаются, абсолютные — нет, а смешанные ссылки двигают только свою неабсолютную половину. Строковые литералы пропускаются полностью, так что формула, случайно содержащая текст "A1", сохраняет этот текст неизменным в каждом последователе
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;Атрибут ref — это затвор, а не украшение. Последователь, чьи координаты выпадают за пределы применимого диапазона мастера, не раскрывается, потому что тогда файл делает заявление, которого группа не поддерживает. Аналогично, когда сдвиг вытолкнул бы ссылку выше строки один или левее столбца A, HotXLS выпускает #REF! для этого токена вместо молчаливого зажима, что и произвёл бы сам Excel для той же правки. Этот перевод — близкий родственник, но не то же самое, что перезапись ссылок, происходящая при вставке или удалении строк. У того пути свои собственные правила о том, что делает диапазон, когда правка его пересекает, и это описано отдельно в статье о корректировке ссылок формул при вставке и удалении. Раскрытие общих формул проще: это чистое смещение от известного якоря, применённое один раз, во время парсинга
Какие формы ссылок обязан покрывать сдвигатель?
Все, иначе раскрытие — замаскированный баг потери данных. Наивный сдвигатель, понимающий только A1 и A1:B2, испортит или отбросит более экзотические формы, а реальные книги ими полны. Транслятор общих формул HotXLS распознаёт всё семейство A1, прежде чем решить, что двигать. Ссылки на внешние книги вроде [Book.xlsx]Sheet1!A1 и 3D-ссылки вроде Sheet1:Sheet3!A1 сохраняют свой префикс нетронутым, пока хвостовая ссылка на ячейку сдвигается. Имена листов в кавычках выживают, включая неприятный случай, когда лист буквально назван A1, так что 'A1'!A1 сдвигает только часть после восклицательного знака. Целый столбец A:A двигает своё измерение столбца и ничего больше; целая строка 1:1 двигает своё измерение строки и ничего больше; $A:$A не двигается вовсе. Структурированные ссылки на таблицу вроде Table[A1] остаются нетронутыми, потому что часть в скобках — это имя столбца, а не координата
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Имена функций — тихая ловушка здесь. Токенный сканер, хватающий буквы, за которыми следуют цифры, с готовностью перепишет LOG10 в LOG11 на строку ниже. HotXLS требует границу ссылки до кандидата-токена и после него, так что идентификатор, продолжающийся буквой, цифрой, подчёркиванием, точкой или открывающей скобкой, — не ссылка на ячейку. Если вы работаете в другом семействе нотации, та же проблема границы проявляется иначе, и статья о нотации R1C1 покрывает, где расходятся эти две модели
Почему самозакрывающийся элемент f проглатывает следующее значение?
Потому что самозакрывающийся элемент не производит события конца элемента. Это самый дорогой единичный баг во всей функции, и он не специфичен ни для какого одного XML-парсера. В TXMLReader <f t="shared" si="4"/> поднимает ровно одно событие Element с установленным IsEmptyElement в True, и никогда не поднимает соответствующий EndElement. Парсер, закрывающий своё состояние захвата формулы только на EndElement, поэтому остаётся внутри формулы, а следующий текст, который он видит, — а это закешированный результат внутри <v>, — присоединяется к буферу формулы. Хуже, состояние переживает границу ячейки, так что следующая ячейка, владеющая настоящим <f>, имеет свой текст формулы поглощённым предыдущей ячейкой. Исправление — завершать состояние формулы прямо на событии Element всякий раз, когда IsEmptyElement — True, и выполнять всё разрешение последователя там же, а не ждать. Это значит читать t, si, ref, aca и ca из атрибутов, применять раскрытие общей формулы, записывать атрибуты пересчёта на ячейку и очищать общее состояние — всё внутри ветки, обрабатывающей пустой элемент. Обратите внимание, что формат допускает оба написания, <f t="shared" si="4"/> и <f t="shared" si="4"></f>, и второе действительно поднимает EndElement. Корректный читатель обязан обрабатывать эту пару одинаково, поэтому HotXLS покрывает оба написания в одном регрессионном файле
Разрежённые, неупорядоченные значения si и очередь ожидания
Атрибут si — это предоставленное файлом целое число без знака, а не позиция в массиве, которую вы контролируете. Ничто в схеме не требует, чтобы общие индексы были плотными, начинались с нуля или появлялись в возрастающем порядке, и ничто не мешает враждебному или просто странному файлу использовать si="4294967290" на первой ячейке. Размер таблицы поиска по наибольшему наблюдённому si — это поэтому примитив исчерпания памяти, а не оптимизация. HotXLS держит путь открытия книги на отсортированной разрежённой таблице вместо этого: общие группы регистрируются под своим целочисленным ключом в отсортированном TStringList, что делает поиск бинарным по тому числу групп, что реально существует, без связи с численным размером индексов. Порядок — вторая половина проблемы. Мастер обычно предшествует своим последователям в порядке документа, но это соглашение, а не правило, так что любой последователь, не способный разрешить свой si в момент парсинга, попадает в очередь ожидания. Когда лист заканчивается, очередь воспроизводится против теперь уже полной таблицы, и запоздалые мастера разрешают своих сирот. Ячейки, никогда не находящие мастера, сохраняют пустую формулу, что честный исход для файла, ссылающегося на группу, которую он никогда не определил
Раскрытие общих формул без загрузки книги
Потоковые читатели сталкиваются с тем же требованием при гораздо более жёстком бюджете памяти, и они решают его таблицей, локальной для листа. И TXLSDirectReader, и TXLSRowCursor раскрывают последователей в полные поячеечные формулы, сохраняя своё поведение с ограниченной памятью и проекцией, так что однопроходный прямой обход листа в 300 МБ всё ещё выдаёт вам реальный текст формулы
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Из этого дизайна вытекают два ограничения. Во-первых, проекция никогда не может пропустить мастер. Фильтр строк, заданный FirstRow и LastRow, или фильтр столбцов, построенный через IncludeColumn, может пропустить выдачу мастер-ячейки вашему обратному вызову, но парсер всё равно обязан записать её si, координаты якоря, применимый диапазон и текст формулы, иначе каждый последователь внутри проекции разрешится в ничто. Только работа со стороны последователя, сдвиг и декодирование значения, безопасно пропускать. Во-вторых, таблица — на лист, и её временем жизни нужно управлять явно: TXLSRowCursor держит один экземпляр на длительность прохода листа и очищает его при рестарте, смене листа, конце файла, исключении и закрытии, так что группа, определённая на листе один, никогда не может протечь на лист два. Поскольку потоковый путь — горячий цикл, он использует хеш целых чисел с открытой адресацией вместо отсортированной строковой таблицы, что избегает преобразования целого в строку на ячейку
Что происходит при сохранении, и где границы
Как только последователь раскрыт, это обычная формула, и HotXLS записывает её обратно как независимый элемент <f> без t="shared" и без si. Циклическое сохранение стабильно, а закешированные результаты <v> выживают, но вывод больше входа для сильно общего листа, а группировка, созданная Excel, не восстанавливается при сохранении. Если байтовая верность общих групп важнее для вас, чем наличие реального текста формулы в каждой ячейке, это компромисс, который вы принимаете. Сторона XLS отличается, к слову: запись SHRFMLA BIFF8 имеет собственную кодировку и собственный писатель, с переключателем общей группы на книге
Две связанные вещи явно не являются общими формулами, хотя разделяют элемент <f>. Устаревшие формулы массива CSE используют t="array" с ref, покрывающим заякоренный диапазон, а динамические массивы используют то же написание t="array", но идентифицируются атрибутом cm, сцепляющимся через cellMetadata с записью XLDAPR. Трактовка ячейки разлива динамического массива как общего или CSE-последователя — настоящий баг корректности, и это разделение покрыто в статье о формулах динамического массива и разлива. Читайте эти три случая как три парсера, которые случайно делят имя тега, и код останется честным
Раскрытие общих формул, потоковые читатели и транслятор ссылок, описанные здесь, поставляются как часть Excel-компонента HotXLS для Delphi и C++Builder; страница продукта содержит полный справочник API формул и прямого чтения, включая используемые выше свойства проекции