Послідовник спільної формули в XLSX не несе тексту формули. Його елемент <f t="shared" si="N"/> вказує на майстер-комірку деінде на аркуші, і читач мусить перебудувати текст, зсунувши формулу майстра на різницю рядків і стовпців. HotXLS Component для Delphi та C++Builder виконує це розгортання в момент відкриття, тож кожен послідовник звітує повну формулу
Якщо ти коли-небудь завантажував реальний XLSX у бібліотеці третьої сторони й виявляв, що стовпець з тисячею формул має текст рівно в одній комірці й порожні рядки в інших 999, ти зустрів цю функцію з неправильного боку. Нічого не пошкоджено. Файл робить те, що дозволяє ECMA-376, а читач просто зупинився в точці, де зупинився XML
Чому комірка спільної формули порожня?
Тому що формат навмисно зберігає формулу один раз. У ECMA-376 Part 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, може пропустити видачу комірки майстра твоєму callback, але парсер усе одно мусить записати його si, координати якоря, застосовний діапазон та текст формули, інакше кожен послідовник усередині проєкції розв'язується в ніщо. Безпечно пропустити лише роботу з боку послідовника, зсув та декодування значення. По-друге, таблиця — на аркуш, і її життєвий цикл треба керувати явно: TXLSRowCursor тримає один екземпляр на тривалість проходу по аркушу й очищає його при перезапуску, перемиканні аркуша, кінці файлу, винятку та закритті, тож група, визначена на аркуші один, ніколи не може просочитися в аркуш два. Оскільки потоковий шлях — гарячий цикл, він використовує цілочисловий хеш з відкритою адресацією, а не впорядковану рядкову таблицю, що уникає перетворення ціле-в-рядок на кожну комірку
Що стається при збереженні, і де межі
Щойно послідовник розгорнутий, він — звичайна формула, і HotXLS записує його назад як незалежний елемент <f> без t="shared" і без si. Циклічний обмін стабільний, а кешовані результати <v> виживають, але вихід більший за вхід для сильно спільного аркуша, а групування, яке створив Excel, не реконструюється при збереженні. Якщо побайтова точність спільних груп важливіша для тебе, ніж наявність реального тексту формули в кожній комірці, це той компроміс, який ти приймаєш. Бік XLS, до речі, інший: запис BIFF8 SHRFMLA має власне кодування і власний писач, з перемикачем спільної групи на книзі
Дві пов'язані речі явно не є спільними формулами, хоч і ділять елемент <f>. Застарілі масивні формули CSE використовують t="array" з ref, що покриває заякорений діапазон, а динамічні масиви використовують те саме написання t="array", але ідентифікуються атрибутом cm, що зчіплюється через cellMetadata із записом XLDAPR. Трактування комірки розливу динамічного масиву як спільного чи CSE-послідовника — справжній баг коректності, і поділ розглянутий у статті про динамічні масиви та формули розливу. Читай ці три випадки як три парсери, що випадково ділять назву тегу, і код лишається чесним
Розгортання спільних формул, потокові читачі та перекладач посилань, описані тут, постачаються як частина компонента Excel HotXLS для Delphi та C++Builder; сторінка продукту містить повний довідник формул і API прямого читання, включно з властивостями проєкції, використаними вище