Кеш-перше збереження XLS і корінь ShrFmla в Delphi

HotXLS — нативна бібліотека Excel для Delphi і C++Builder — зберігає класичну книгу BIFF8 .xls з кешу: TXLSWorksheet.WriteFormula питає TXLSWorkbook.TryGetCachedFormulaValue про значення, яке Excel зберіг поряд із кожною формулою, і викликає обчислювач лише тоді, коли цей кеш відсутній або інвалідований. Книга, яку ви відкрили й жодного разу не торкалися, зберігає ті самі числа назад, а свіжі результати вимагають одного явного виклику Recalculate замість того, щоб бути прихованим побічним ефектом SaveAs

Баг, який витяг цей контракт на поверхню, був соромливо малий. Файл корпусу nested-subtotals.xls тримає загальний підсумок у R2C4, чиє кешоване значення — 37. Відкрийте його в HotXLS, спитайте TryGetCachedFormulaValue про цю клітинку — отримаєте 37. Збережіть, не змінивши жодної клітинки, відкрийте збережену копію, задайте те саме питання — отримаєте 67. Ніщо в API не просили нічого обчислювати, а число у файлі зсунулося рівно на 30 — і 30 якраз дорівнює сумі двох підсумків груп, 10 і 20, які сидять усередині діапазону, що його покриває загальний підсумок

Чому збереження файлу XLS змінює значення формули?

Щоб 37 став 67, мусили зійтися два незалежні дефекти, і виправлення будь-якого з них окремо замаскувало б другий. Перший був структурний: класичний записувач перераховував кожну формулу при кожному збереженні. Другий — перевірка типу, яка ніколи не могла бути істинною для формули, завантаженої з диска, через що обчислювач рахував вкладені клітинки SUBTOTAL двічі. Файл корпусу просто виявився першим входом, де перерахунок під час збереження дав іншу відповідь, ніж Excel, і хтось ті дві відповіді порівняв. Структурний дефект легко сформулювати: до v2.382.3 TXLSWorksheet.WriteFormula і його побратим для спільних формул WriteFormulaWithTExp отримували восьмибайтове поле FormulaValue кожного запису Formula, викликаючи TXLSWorkbook.GetFormulaValue, тобто обчислювач. Кеш, який ParseFormula так ретельно декодував із вихідного файлу при завантаженні, на виході ніхто не питав. Фактично кожне збереження було повним перерахунком в обхід API перерахунку рівня книги, тож ніщо з того, що ви могли б виставити на книзі, його не зупинило б. Будь-яке місце, де обчислювач HotXLS розходився з Excel — чи легітимно непідтримувана функція, чи звичайний баг, — ставало тихою зміною даних при збереженні

Другий дефект жив у callback для вкладених підсумків, який використовує обчислювач. Excel визначає кожну форму SUBTOTAL так, що вона ігнорує клітинки, чия власна формула — теж SUBTOTAL, тож калькулятор у lxCalc.pas озброює FIgnoreSubtotalCells під час агрегації й питає книгу через TXLSWorkbook.GetClassicIsSubtotalCell, чи кожна клітинка в діапазоні така. Цей callback діставав текст формули як Variant і перевіряв його через VarType(f) = varOleStr. Текст повертається з GetUnCompiledFormula як Delphi String, а String, присвоєний Variant, — це varUString, ніколи не varOleStr. Твердження було хибним для кожної клітинки в кожному завантаженому файлі, підсумки груп укочувалися в загальний підсумок удруге, і при збереженні, яке все перераховувало, 10 + 20 + 7 ставало 67

// HotXLS 2.381 і раніше: Variant формули, збудований із String,
// це varUString, тож це порівняння ніколи не було істинним
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr приймає varString, varOleStr і varUString,
// а AGGREGATE виключається із зовнішніх підсумків, як і робить Excel
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 випустив виправлення VarIsStr і, заодно в тій самій функції, навчив callback, що клітинки AGGREGATE теж виключаються із зовнішніх підсумків. Уже цього вистачило, щоб твердження корпусу пройшло, бо перераховане 37 тепер збігалося із завантаженим 37. Але чеснішою бібліотеку це не зробило: збереження все ще перераховувало, і тест був зелений лише тому, що обчислювач випадково збігався з Excel на цьому конкретному файлі. Правила, які клітинки пропускають SUBTOTAL і AGGREGATE, включно з прихованими рядками, розібрано в статті про приховані рядки в SUBTOTAL і AGGREGATE; важливо тут те, що жоден обчислювач не повинен мати права голосу щодо файлу, який ви не просили обчислювати

Що Excel гарантує про кешовані значення при збереженні?

Excel трактує збереження як знімок, а не як подію обчислення. Значення, записане в поле FormulaValue запису Formula ([MS-XLS] §2.4.127, розкладка в §2.5.133), — це те, що клітинка зараз показує, а в режимі ручного обчислення воно може бути застарілим на роки, і Excel усе одно пише його сумлінно. Перерахунок — окрема операція з власним тригером. HotXLS тепер дотримується того самого правила для класичних збережень: WriteFormula і WriteFormulaWithTExp спершу викликають TryGetCachedFormulaValue, беруть CacheInfo.Value, коли стан — xlfcsLoaded або xlfcsCalculated, і переходять до GetFormulaValue лише для xlfcsMissing та xlfcsInvalidated. Читацьку половину цього контракту, включно з тим, що означає кожен стан і чому кешований порожній рядок або False усе ще рахується значенням, описано в Читання кешованих значень формул Excel у Delphi без перерахунку

Шлях відкату збережено навмисно, а не прибрано. Формула, яку ви призначили в цій сесії через Cells[Row, Col].Formula, приходить без кешу, а формула, яку ви замінили на завантаженій клітинці, позначається xlfcsInvalidated через _SetCompiledFormula; обидві обчислюються під час збереження точно як раніше, тож згенерована книга все ще відкривається в Excel із числами. Коли навіть обчислювач не може дати значення, записувач видає нульове навантаження й виставляє fAlwaysCalc (біт 0 у grbit із §2.4.127), щоб Excel перерахував клітинку при відкритті замість того, щоб довіряти плейсхолдеру

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // Аркуш, рядок і колонка з відліком від 1: R2C4 на першому аркуші
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // для кешованих клітинок обчислювач не задіяний
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 для nested-subtotals.xls
    // Збереження, яке перераховувало б, записало б тут 67
  finally
    Book.Free;
  end;
end;

Де корінь спільної формули BIFF зберігає своє кешоване значення?

У власному записі Formula, як і кожна інша клітинка з формулою, і саме це й зробило кореневу клітинку спільної групи тим єдиним місцем, де збереження з кешу все ще втрачало. Спільна формула в BIFF8 зберігається як запис ShrFmla ([MS-XLS] §2.4.260), що йде за записом Formula верхньої лівої клітинки, і кожна клітинка-член, включно з коренем, несе rgce, що складається з єдиного токена PtgExp (§2.5.198): перший байт розібраного виразу — $01, за ним рядок і колонка кореневої клітинки. Клітинки-послідовники самодостатні — HotXLS читає FormulaValue кожної з них і розв'язує вираз, звертаючись до скомпільованої формули кореня. Коренева клітинка інша, бо коли її запис Formula розбирають, виразу ще не існує; він приходить одним записом пізніше

Саме в цьому проміжку в один запис кеш і подівся. TXLSReader.ParseFormula декодує кешоване значення і, побачивши PtgExp, чиї координати збігаються з координатами самої клітинки, запам'ятовує клітинку в FSharedFormulaRow і FSharedFormulaCol та публікує кеш у клітинку. Коли приходить запис ShrFmla ($04BC), ParseSharedFormula компілює вираз і встановлює його через _SetCompiledFormula, а _SetCompiledFormula робить те, що мусить робити за будь-якої зміни формули: очищає FCachedFormulaValue і скидає стан у xlfcsMissing. Тож завантажене 37 кореня викидалося ще до того, як хтось міг його прочитати, TryGetCachedFormulaValue повідомляв, що корінь без кешу, а записувач із кешу слухняно відкочувався до обчислювача саме для тієї клітинки, на яку всі дивилися. Запис Array (§2.4.4) має той самий порядок і мав ту саму дірку

Виправлення у v2.382.3 додає третє поле, FSharedFormulaCachedValue, поряд із відкладеними координатами кореня. ParseFormula складає туди декодований кеш, коли розпізнає корінь, а ParseSharedFormula і ParseArrayFormula відтворюють його через _SetCellCachedFormulaValue одразу після встановлення скомпільованого виразу, після чого скидають схованку в Unassigned. Рядковий варіант кешу всього цього не зачіпає, бо його навантаження приходить окремим записом String і маршрутизується за координатами клітинки, а не за порядком записів. Якщо ви працюєте з боку OOXML для тієї самої концепції, стаття про розгортання si спільних формул XLSX пояснює, чому в пакетному форматі немає еквівалентної проблеми порядку, але є власні пастки розгортання

Чому послідовникам спільної формули потрібен відносний зсув?

Бо вираз, збережений у ShrFmla, записано відносно кореневої клітинки, і послідовник, який використає його дослівно, обчислює посилання кореня замість власних. Старий читач встановлював на кожному послідовнику Value.GetCopy() — глибоку копію без зсуву, — тож група з коренем у B1 з =A1*3 давала кожному послідовнику теж =A1*3. Збереження з кешу насправді маскувало це для завантажених файлів, бо послідовники мали власні FormulaValue і вираз їм не був потрібен, щоб зберегтися правильно; це виринало тієї ж миті, коли щось перераховувало. Тепер читач встановлює TXLSCompiledFormula.GetCopy(row - srow, col - scol), що проходить синтаксичне дерево й зсуває кожне відносне посилання на відстань послідовника від кореня, тож послідовник у B2 володіє справжнім =A2*3

Регресійний тест, який фіксує обидві поведінки, варто прочитати, бо він відмовляється пропустити випадковий збіг. Він будує книгу з =A1*3 і =A2*3 над входами 2 і 4, а тоді вкидає навмисно хибні кеші 999 і 888 через _SetCellCachedFormulaValue — раз з увімкненим UseSharedFormulas, раз із вимкненим. Після збереження й повторного завантаження обидві клітинки мусять усе ще повідомляти 999 і 888 — доказ, що збереження не зачепило ні кеш кореня, ні кеш послідовника. І лише після явного Recalculate вони мусять стати 6 і 12 — доказ, що зсунутий вираз послідовника правильний. Тест, який підклав би справжні значення, пройшов би й під старим записувачем, і в цьому вся суть підкладання хибних

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // змінити вхідні дані

    // Завантажені кеші залежних формул НЕ інвалідуються через
    // правку літерала, тож звичайний SaveAs зберіг би старі числа.
    // Проси перерахунок, коли справді хочеш свіжі результати:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Чого контракт «кеш спершу» для вас не робить

Збереження з кешу зберігає те, що було завантажено; воно не відслідковує, чи завантажене ще істинне. Зміна літерала, від якого залежить формула, помічає граф залежностей брудним для обчислювача, але лишає xlfcsLoaded-кеш залежної клітинки на місці, і класичний записувач радо запише те застаріле значення, якщо ви не викличте Recalculate або спершу не прочитаєте Value клітинки, що обчислює її й переводить стан у xlfcsCalculated. Це той самий компроміс, який Excel робить у режимі ручного обчислення, і для конвеєра, який відкриває чужі файли, править кілька підписів і зберігає, він правильний — але це означає, що книга, яка редагує вхідні дані, мусить сама явно володіти кроком перерахунку. Політика RecalcBeforeSave у записувачі XLSX цією роботою не змінена й має власний ручний режим, який зберігає кеші в тому самому дусі. З цього випливають дві менші межі: шлях «кеш спершу» допомагає лише клітинкам, чий стан — xlfcsLoaded або xlfcsCalculated; генератор, який пише формули й ніколи їх не обчислює, усе одно платить за одне обчислення на клітинку під час збереження, точно як і раніше. А виправлення вкладених підсумків виправляє те, які клітинки обчислювач пропускає, а не кожну функцію, яку він реалізує — файл, чиї формули HotXLS не може обчислити ідентично до Excel, тепер безпечно ганяти round-trip без змін, але свідомий Recalculate на такому файлі все одно дасть відповідь бібліотеки, а не Excel, і вам варто порівняти ці дві, перш ніж довіряти перерахованому збереженню

Класичні збереження з кешу, відновлені кеші коренів спільних і масивних формул, зсув відносних посилань для послідовників спільних формул і виправлені правила вкладеності SUBTOTAL та AGGREGATE — усе це постачається у стандартному HotXLS Delphi Spreadsheet Component для Delphi і C++Builder, без залежності від Excel чи будь-якого OLE automation сервера; сторінка продукту містить повний довідник API для книги, читача кешу й точок входу перерахунку, використаних тут