Спри XLS записите да преизчисляват формули тихо в Delphi

HotXLS, нативната Delphi и C++Builder Excel библиотека, записва класическа BIFF8 работна книга .xls с кеша първо: TXLSWorksheet.WriteFormula пита TXLSWorkbook.TryGetCachedFormulaValue за стойността, която Excel е съхранил до всяка формула, и вика evaluator-а само когато този кеш липсва или е инвалидиран. Работна книга, която сте отворили и никога не сте пипнали, записва същите числа обратно, а свежи резултати идват с едно изрично извикване Recalculate, вместо да са скрит страничен ефект на SaveAs

Бъгът, който изкара този договор наяве, беше срамно малък. Corpus файл с име nested-subtotals.xls държи общ сбор в R2C4, чиято кеширана стойност е 37. Отворете го с HotXLS, попитайте TryGetCachedFormulaValue за клетката, получите 37. Запишете го без да смените нито една клетка, отворете записаното копие, задайте същия въпрос, получите 67. Нищо в API-то не беше помолено да изчисли каквото и да е, а едно число във файла се беше преместило с точно 30 — и 30 се оказва сборът на двете групови subtotals, 10 и 20, седящи вътре в диапазона, който общият сбор покрива

Защо записът на XLS файл сменя стойност на формула?

Два независими дефекта трябваше да се подредят, за да стане това 37 на 67, и оправянето на който и да е сам щеше да скрие другия. Първият беше структурен: класическият writer преизчисляваше всяка формула на всеки запис. Вторият беше type проверка, която никога не можеше да е истина за формула, заредена от диска, което накара evaluator-а да брои вложени SUBTOTAL клетки два пъти. Corpus файлът беше просто първият вход, при който recalculation по време на запис произведе различен отговор от Excel и някой сравни двете. Структурният дефект лесно се формулира: преди v2.382.3 TXLSWorksheet.WriteFormula и неговият shared-formula брат WriteFormulaWithTExp получаваха осем-байтовото поле FormulaValue на всеки Formula запис чрез викане на TXLSWorkbook.GetFormulaValue, който е evaluator-ът. Кешът, който ParseFormula беше внимателно декодирал от source файла при зареждане, никога не се питаше на изхода. На практика всеки запис беше пълно преизчисляване с заобиколено recalc API на ниво работна книга, така че нищо, което можете да зададете върху книгата, нямаше да го спре. Всяко място, където HotXLS evaluator-ът се разминава с Excel — дали легитимно неподдържана функция, или чист бъг — ставаше тиха промяна на данните при запис

Вторият дефект живееше в nested-subtotal callback-а, който evaluator-ът ползва. Excel дефинира всяка форма SUBTOTAL като игнорираща клетки, чиято собствена формула е друг SUBTOTAL, така че калкулаторът в lxCalc.pas въоръжава FIgnoreSubtotalCells по време на агрегиране и пита работната книга, чрез TXLSWorkbook.GetClassicIsSubtotalCell, дали всяка клетка в диапазона е такава. Този callback взимаше текста на формулата като Variant и го тестваше с VarType(f) = varOleStr. Текстът се връща от GetUnCompiledFormula като Delphi String, а String, присвоен на Variant, е varUString, никога varOleStr. Предикатът беше false за всяка клетка във всеки зареден файл, груповите subtotals се търкаляха в общия сбор втори път, а при запис, който преизчисляваше всичко, 10 + 20 + 7 ставаше 67

// HotXLS 2.381 и по-рано: formula 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 се изключва от обгръщащите subtotals, както прави 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 клетки също се изключват от обгръщащите subtotals. Това само по себе си накара corpus assertion-ът да пасне, защото преизчисленото 37 вече съвпадаше с зареденото 37. То не направи библиотеката честна: записът все още преизчисляваше, а тестът беше зелен само защото evaluator-ът случайно съгласуваше с Excel на този конкретен файл. Правилата кои клетки SUBTOTAL и AGGREGATE прескачат, включително скрити редове, са разгледани в статията за SUBTOTAL и AGGREGATE скрити редове; тук важното е, че никакъв evaluator не бива да има глас върху файл, който не сте го помолили да изчисли

Какво гарантира 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 без преизчисляване

Fallback пътят е нарочно запазен, не премахнат. Формула, която сте задали в тази сесия чрез Cells[Row, Col].Formula, пристига без кеш, а формула, която сте заменили на заредена клетка, се маркира xlfcsInvalidated от _SetCompiledFormula; и двете се оценяват по време на запис точно както преди, така че генерирана работна книга все още се отваря в Excel с числа вътре. Когато дори evaluator-ът не може да произведе стойност, writer-ът излъчва нулев payload и задава fAlwaysCalc (grbit бит 0 на §2.4.127), така че Excel преизчислява клетката при отваряне, вместо да вярва на placeholder-а

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);        // без участие на evaluator за кеширани клетки
  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 shared formula?

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

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

Поправката във v2.382.3 добавя трето поле, FSharedFormulaCachedValue, до чакащите координати на корена. ParseFormula скрива декодирания кеш там, когато разпознае корен, а и ParseSharedFormula, и ParseArrayFormula го възпроизвеждат чрез _SetCellCachedFormulaValue веднага след инсталирането на компилирания израз, после рестартират stash-а на Unassigned. String вариантът на кеша не се засяга от всичко това, защото неговият payload пристига в отделен String запис и се маршрутизира по клетъчни координати, не по ред на записи. Ако работите с OOXML страната на същата концепция, статията за XLSX shared formula si expansion обяснява защо пакетният формат няма еквивалентен проблем с подреждането, но има собствени капани на разширяване

Защо shared formula follower клетките се нуждаят от относително отместване?

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

Регресионният тест, който закова двете поведения, си заслужава да се прочете, защото отказва да остави съвпадение да мине. Той построява работна книга с =A1*3 и =A2*3 върху входове 2 и 4, после инжектира нарочно грешните кешове 999 и 888 чрез _SetCellCachedFormulaValue, веднъж с UseSharedFormulas включено и веднъж изключено. След запис и повторно зареждане и двете клетки трябва все още да докладват 999 и 888 — доказателство, че записът не е пипнал нито корена, нито follower кеша. Само след изричен Recalculate трябва да станат 6 и 12 — доказателство, че изместеният израз на follower-а е верен. Тест, който посееше истинските стойности, щеше да мине и под стария writer, което е целият смисъл на сеитбата на грешни

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;

Какво кеш-първо договорът не прави за вас

Записът кеш-първо пази това, което е заредено; той не следи дали зареденото все още е вярно. Смяна на литерал, от който зависи формула, маркира dependency графа мръсен за evaluator-а, но оставя xlfcsLoaded кеша на зависимата клетка на място, а класическият writer с удоволствие ще запише тази остаряла стойност, освен ако не викнете Recalculate или прочетете Value на клетката първо, което я изчислява и мести състоянието на xlfcsCalculated. Това е същият trade, който Excel прави в ръчен режим на изчисление, и той е правилният за pipeline, който отваря чужди файлове, редактира няколко етикета и записва — но означава, че работна книга, която редактира входове, трябва да притежава своя стъпка на преизчисляване изрично. Политиката RecalcBeforeSave на XLSX writer-а не се засяга от тази работа и има собствен ръчен режим, който пази кешовете в същия дух. От това следват две по-малки граници: пътят кеш-първо помага само на клетки, чието състояние е xlfcsLoaded или xlfcsCalculated; генератор, който записва формули и никога не ги оценява, все още плаща по една оценка на клетка по време на запис, точно както преди. И вложения-subtotal поправката коригира кои клетки evaluator-ът прескача, не всяка функция, която evaluator-ът имплементира — файл, чиито формули HotXLS не може да изчисли идентично с Excel, вече е безопасен за недокоснат round-trip, но изричен Recalculate върху него все още ще произведе отговора на библиотеката, а не на Excel, и трябва да сравните двете, преди да вярвате на преизчислен запис

Класическите записи кеш-първо, възстановените кешове на shared и array formula корените, относително-референтното отместване за shared follower-и и поправените правила за влагане на SUBTOTAL и AGGREGATE всички се доставят в стандартния HotXLS Delphi Spreadsheet Component за Delphi и C++Builder, без зависимост от Excel или какъвто и да е OLE automation сървър; продуктовата страница носи пълната API справка за работната книга, четеца на кеш и entry point-ите за преизчисляване, ползвани тук