Общий итог, который Excel показывает как 37, может вернуться как 67 после того, как программа открыла .xls файл и сохранила его, не отредактировав ни одной ячейки. Лишние 30 не случайны: это в точности сумма двух групповых промежуточных итогов, 10 и 20, лежащих внутри диапазона, который покрывает общий итог. Чтобы число уехало, должны сломаться две вещи одновременно: сохранение обязано пересчитать каждую формулу на выходе, а пересчитывающее обязано забыть, что SUBTOTAL пропускает другие ячейки SUBTOTAL. Уберите любую из двух — и файл выживает, поэтому первая проблема может незаметно сидеть за второй
Почему сохранение файла XLS меняет значение формулы?
Сохранение меняет значение формулы, когда писатель вычисляет число, которое записывает рядом с каждой формулой, вместо того чтобы скопировать загруженное. Каждая запись Formula BIFF8 несёт восьмибайтовое поле FormulaValue ([MS-XLS] §2.4.127, раскладка значения в §2.5.133), хранящее результат, который Excel последним показывал для этой ячейки. Писатель обязан положить в эти восемь байтов что-то, и кратчайший путь в коде — скормить каждую формулу вычислительному движку. Стоит это случиться — и каждое сохранение становится полным пересчётом, которого никто не просил, а каждое место, где движок расходится с Excel, становится изменившимся числом в файле, который пользователь считает нетронутым
Расхождение с SUBTOTAL — хороший пример, потому что правило электронной таблицы простое, а ловушка сидит в Variant-типизации Delphi. Excel определяет каждый номер функции SUBTOTAL как игнорирующий ячейки, чья собственная формула — другой SUBTOTAL. Для листа, чьи строки детализации в сумме дают 37 (строки под двумя группами с промежуточными итогами 10 и 20 плюс ещё 7 вне всякой группы), общий итог равен 37. Движок, не распознавший вложенные промежуточные итоги, посчитает 10 и 20 второй раз и получит 67. Распознавание обычно означает короткую проверку текста формулы, и очевидный вариант на Delphi компилируется, выполняется и никогда ни с чем не совпадает:
// Вспомогательная функция, которую вы пишете сами, — например, при обходе листа
// в поисках формул, которые объемлющий SUBTOTAL обязан пропустить
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// Соблазнительная проверка на Unicode Delphi никогда не проходит: String,
// присвоенная Variant, — это varUString, а не varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // принимает varString, varOleStr и varUString
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(');
end;
// Использование с HotXLS (листы, строки и столбцы отсчитываются от 1):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr — та проверка, которую вычислитель HotXLS применяет к тому же вопросу, и он также считает ячейки AGGREGATE исключаемыми из объемлющего промежуточного итога, как Excel. Но правка предиката — ещё не вся история. С корректной проверкой пересчитывающее сохранение пишет 37 лишь потому, что движку случайно удалось сойтись с Excel на этом конкретном файле. Следующая формула, которую движок обработает чуть иначе, уедет снова. Правила того, какие ячейки пропускают SUBTOTAL и AGGREGATE, включая скрытые строки, разобраны в статье о скрытых строках в SUBTOTAL и AGGREGATE. При сохранении с «сначала кеш» файл, который никто не просил вычислять, возвращается на диск с 37 в ячейке — чем бы движок ни закончил
Что классическое сохранение XLS пишет в каждую запись Formula
HotXLS сохраняет книгу BIFF8 с «сначала кеш»: для каждой ячейки с формулой TXLSWorkbook.SaveAs пишет кешированное значение всякий раз, когда TryGetCachedFormulaValue его выдаёт, и запускает вычислитель только когда не выдаёт. Решает состояние кеша. Ячейка в состоянии xlfcsLoaded (значение пришло из исходного файла) или xlfcsCalculated (HotXLS вычислил его в этом сеансе) записывается значением CacheInfo.Value байт в байт. Ячейка в состоянии xlfcsMissing или xlfcsInvalidated вычисляется при сохранении. Это совпадает с тем, как Excel обращается с сохранением: снимок того, что показывает каждая ячейка, — в режиме ручного вычисления сильно устаревший, — записывается добросовестно
Вычислительный путь остаётся по веским причинам. Формула, присвоенная в коде через Cells[Row, Col].Formula, кеша ещё не имеет, а присваивание новой формулы загруженной ячейке переводит её в xlfcsInvalidated, потому что старый результат принадлежал старому выражению. Обе вычисляются при сохранении, поэтому книга, сгенерированная с нуля, открывается в Excel с вычисленными числами вместо нулей. Если даже вычислитель не может выдать значение (неподдерживаемая функция, битая внешняя ссылка), писатель сохраняет ноль и выставляет fAlwaysCalc, бит 0 флагов записи в §2.4.127, чтобы Excel пересчитал ячейку при открытии, а не доверял заглушке
Одно свойство выглядит причастным, но не причастно. TXLSWorkbook.RecalcOnSave повторяет опцию Excel «Пересчитывать книгу перед сохранением» из записи CalcSaveRecalc ([MS-XLS] §2.4.37). Она важна только самому Excel и только в режиме ручного вычисления, а HotXLS просто записывает её в файл. SaveAs не спрашивает её, решая, что положить в FormulaValue. Круговой рейс ниже — быстрейший способ увидеть контракт на одном из собственных файлов:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Лист, строка и столбец отсчитываются от 1
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // кешированные ячейки записываются как есть
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Ожидание: After.Value равен Before.Value, а After.State снова
// xlfcsLoaded, потому что значение пришло из сохранённого файла
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;Сторона чтения этого контракта, включая значение каждого состояния и то, почему кешированная пустота или False всё равно считается значением, описана в статье Read Excel Cached Formula Values in Delphi Without Recalc. Прогоните проверку выше на ячейке общего итога книги с вложенными промежуточными итогами, и обе строки напечатают одно число
Где корневая ячейка общей формулы BIFF хранит кешированное значение?
В собственной записи Formula, как и всякая другая ячейка с формулой, но выражение приходит на одну запись позже, и именно в этом зазоре небрежный ридер теряет кеш. BIFF8 хранит общую формулу записью ShrFmla ([MS-XLS] §2.4.260), идущей сразу после записи Formula верхней левой ячейки группы. Каждая ячейка-участник, включая корень, несёт rgce из единственного токена PtgExp (§2.5.198): байт $01 и следом строка и столбец корня. Ячейки-последователи просты: к моменту их появления выражение корня уже существует, а их собственный FormulaValue лежит в их собственной записи. Корень — не прост. Когда его запись Formula прочитана, у него есть кешированное значение 37 и ещё нет выражения
Установка выражения на ячейку чистит её кеш, и это обязано оставаться правдой, потому что заменённая формула действительно обесценивает старый результат. Применённое вслепую к записи ShrFmla, то же правило выбрасывает 37 корня в момент прихода выражения, TryGetCachedFormulaValue сообщает корень как xlfcsMissing, и сохранение откатывается к вычислителю ровно для той ячейки, на которую пользователь вероятнее всего взглянет. Ридер HotXLS держит декодированное значение корня, пока ждёт запись ShrFmla, устанавливает выражение, затем возвращает загруженное значение с состоянием xlfcsLoaded. Формулы массива (запись Array, §2.4.4) следуют тому же порядку записей и получают то же обращение. Строковая форма кеша этой проблемой не страдала никогда: её текст приходит отдельной записью String и сопоставляется ячейке по строке и столбцу, так что её позиция в потоке не важна
Если вы работаете и со стороной OOXML, статья про раскрытие si общих формул в XLSX объясняет, почему у пакетного формата нет зазора с порядком записей, но есть свои ловушки раскрытия
Последователи общих формул владеют сдвинутой копией выражения
Выражение в записи ShrFmla записано относительно корневой ячейки, поэтому каждому последователю нужна собственная копия, смещённая на его расстояние от корня. Скопируйте дословно — и группа с корнем в B1 и =A1*3 выдаст одну и ту же =A1*3 ячейкам B2 и B3. HotXLS даёт каждому последователю копию, чьи относительные ссылки смещены на расстояние последователя по строке и столбцу, так что B2 владеет настоящей =A2*3, а B3 — =A3*3
По той же причине простой круговой рейс не может сказать, правильны ли выражения последователей. Последователи переживают сохранение с загруженными значениями, поэтому дословная копия прошла бы проверку. Проверка, доказывающая хоть что-то, обязана изменить вход и пересчитать. Процедура ниже пишет общую группу с включённым UseSharedFormulas (по умолчанию False, что пишет по записи Formula на ячейку), заново открывает файл, правит A2 и следит за B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 становятся одной группой ShrFmla
Book.SaveAs(FileName); // кешей ещё нет: вычислено в 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // правим вход последователя
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Ожидание: 12, всё ещё загружено
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Ожидание: 30, из =A2*3
finally
Book.Free;
end;
end;Последователь, переиспользовавший бы =A1*3 дословно, напечатал бы 6 во второй строке. Первая строка важна не меньше: она показывает, что правка литерала оставляет загруженное значение зависимой формулы на месте, пока что-нибудь не пересчитается
Что сохранение с «сначала кеш» оставляет на вашей ответственности
Сохранение с «сначала кеш» оберегает то, что было загружено. Оно не следит за тем, остаётся ли загруженное верным. Правка ячейки, от которой зависит формула, помечает граф зависимостей для следующего пересчёта, но зависимая ячейка хранит своё значение xlfcsLoaded, и SaveAs запишет это устаревшее число, если вы не вызовете сначала Recalculate или не прочтёте Value ячейки — что вычислит формулу и переведёт её в xlfcsCalculated. Recalculate возвращает число ячеек с формулами, которые он не смог вычислить, так что ненулевой результат стоит залогировать до сохранения
В типовом конвейере программа открывает книгу, собранную кем-то другим, заполняет несколько подписей или шапку и сохраняет. Ничто в этом файле не должно менять значение, и при сохранении с «сначала кеш» ничего не меняет. Программа, которая правит входные данные, сама владеет шагом пересчёта — и этот шаг принадлежит одному видимому вызову Recalculate рядом с правками, где любой читающий код его увидит. Excel делает тот же компромисс в режиме ручного вычисления. У писателя XLSX своя политика, RecalcBeforeSave с режимами xrmManual, xrmIfDirty и xrmAlways, задаваемая на классе книги XLSX и независимая от всего вышеперечисленного
Из этого устройства следуют две границы. Путь кеша помогает только ячейкам в состоянии xlfcsLoaded или xlfcsCalculated, поэтому генератор, пишущий тысячи новых формул и никогда их не вычисляющий, всё равно платит одним вычислением на ячейку при сохранении. И контракт управляет тем, что пишет сохранение, а не тем, что вычисляет явный пересчёт: нетронутый файл хранит числа, записанные Excel, а Recalculate записывает результаты вычислителя HotXLS. Сравните оба варианта на нескольких представительных книгах, прежде чем переводить конвейер на сохранения с пересчётом
HotXLS Delphi Spreadsheet Component читает и пишет книги классического BIFF8 и XLSX из Delphi и C++Builder без установленного Excel или OLE-автоматизации. Страница продукта документирует вызовы работы с книгой, кешированными значениями и пересчётом, использованные в этих примерах