HotXLS, нативная библиотека Excel для Delphi и C++Builder, читает значение, которое Excel уже сохранил рядом с формулой, через TryGetCachedFormulaValue и IXLSFormulaCacheReader. Ни одна из этих точек входа не вызывает вычислитель, не декомпилирует токены формул, не трогает признаки изменённости и не пишет ничего обратно в модель, поэтому книга, которую вы только читаете, остаётся в точности такой, какой была при открытии
Сценарий, который это вызывает, скучен и крайне распространён. Ночное задание открывает несколько сотен книг, созданных кем-то другим, вытягивает из каждой один столбец итогов и отправляет числа в хранилище. Итоги уже лежат в файлах — Excel вычислил и сохранил их. Но в момент, когда задание спрашивает у формульной ячейки её значение, библиотека, у которой на этот вопрос есть только один ответ, строит граф зависимостей и пересчитывает весь лист, и задание, которое должно упираться в ввод-вывод, превращается в бенчмарк вычислений
Почему чтение формульной ячейки стоит полного пересчёта?
Потому что геттер значения у формульной ячейки — это запрос произвести значение, а единственный универсально корректный способ его произвести — вычислить формулу. Для приложения, редактирующего книги, это правильное умолчание; для конвейера, который их извлекает, — неправильное. Хуже того, вычисление не свободно от побочных эффектов: оно записывает результаты обратно в ячейки, взводит признаки изменённости и может разойтись с породившим файл приложением, когда функция не поддерживается или внешняя ссылка сломана. Задание, которое вы описали своей эксплуатации как read-only, тихо создаёт книгу, уже не совпадающую с той, что на диске, а если её позже кто-то сохранит, изменится и файл на диске
Чтение кэшированных значений — вторая половина этого контракта. Оно отвечает на более узкий вопрос — что породившее приложение сохранило здесь? — и отказывается отвечать на что-либо ещё. Когда вам действительно нужны свежие числа, HotXLS по-прежнему даёт инкрементальный пересчёт на графе зависимостей; суть в том, что извлечение и вычисление должны быть двумя разными вызовами, а не одним вызовом с двумя настроениями
Три ортогональных факта об одной ячейке
Сначала вывод: кэшированное значение формулы несёт три независимых факта, и сведение их в один Variant теряет нужную вам информацию. TXLSFormulaCacheInfo разделяет их на State, Kind и Value. TXLSFormulaCacheState фиксирует происхождение в пяти случаях — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated и xlfcsInvalidated — а TXLSFormulaCacheValueKind классифицирует полезную нагрузку как xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean или xlfcvError. Именно это разделение позволяет честно сообщать о наличии: кэшированный пробел, кэшированная пустая строка, кэшированный False, кэшированный ноль и кэшированная ошибка — всё это реальные значения, поэтому наличие никогда нельзя выводить из VarIsEmpty или VarIsNull. TryGetCachedFormulaValue возвращает True только для xlfcsLoaded и xlfcsCalculated, а при False всё равно заполняет диагностируемое состояние
var
Book: TXLSXWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('quarterly-model.xlsx');
// SheetIndex, Row и Col здесь все с единицы
if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
Writeln('cached value: ', VarToStr(Info.Value))
else
Writeln('no usable cache, state ordinal ', Ord(Info.State));
finally
Book.Free;
end;
end;Почему кэшированного значения нет?
Ровно четыре причины заставляют TryGetCachedFormulaValue вернуть False, и состояние говорит, какая из них применима. xlfcsNotFormula значит, что ячейка хранит литерал или вообще ничего, а выход координат за пределы схлопывается в тот же ответ. xlfcsMissing значит, что ячейка действительно формула, но создатель не сохранил для неё полезную нагрузку значения — частый итог, когда генератор пишет формулы и оставляет Excel заполнять результаты при первом открытии. xlfcsInvalidated значит, что текст формулы был заменён после загрузки, поэтому бывшее здесь значение описывает выражение, которого больше не существует. xlfcsCalculated, напротив, случай успеха: он отмечает значение, которое ваш код или вычислитель HotXLS произвели в этой сессии, в отличие от xlfcsLoaded, пришедшего из файла
Честность об отсутствующем кэше важнее замазывания. HotXLS отказывается выдумывать значение, и при сохранении столь же строг — только xlfcsLoaded и xlfcsCalculated выдают кэшированное значение, тогда как xlfcsMissing и xlfcsInvalidated записывают одну формулу, а не вмораживают устаревшее число в файл. Это оставляет вам три разумных ответа в конвейере: пропустить строку и записать пробел, намеренно пересчитать эту одну книгу, приняв издержки, или вычислить и сверить. Если вычисленное число расходится с тем, что написало бы породившее приложение, трассировщик вычисления формул — инструмент, чтобы найти, где два вычисления разошлись, а не гадать по результату
Один читатель для движков classic, OOXML и ODF
Конвейеру не должно быть дела, был ли только что открытый файл BIFF, OOXML или ODF. IXLSFormulaCacheReader — единственная точка входа только для чтения для всех трёх: и TXLSWorkbook.CreateFormulaCacheReader, и TXLSXWorkbook.CreateFormulaCacheReader возвращают лёгкий адаптер над разреженным поиском ячеек, который каждый движок уже использует, с одинаковыми координатами листа, строки и столбца от единицы. Классы книг намеренно не реализуют интерфейс сами — ссылка на интерфейс книги изменила бы семантику владения и позволила бы вызывающим обойти аренду времени жизни. Вместо этого уничтожение книги очищает сырой указатель внутри этой аренды, и любой читатель, ещё удерживаемый вашим кодом, при следующем запросе поднимает EXLSFormulaCacheReaderInvalidated вместо разыменования освобождённой памяти. Это fail-fast проверка времени жизни, а не гарантия конкурентности
var
Reader: IXLSFormulaCacheReader;
Info: TXLSFormulaCacheInfo;
Row, Missing, Errors: Integer;
Total: Double;
begin
Reader := Book.CreateFormulaCacheReader;
Total := 0;
Missing := 0;
Errors := 0;
for Row := 2 to LastRow do
if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
begin
case Info.Kind of
xlfcvNumber: Total := Total + Double(Info.Value);
xlfcvError: Inc(Errors);
end;
end
else if Info.State = xlfcsMissing then
Inc(Missing);
// Ни один вычислитель не запускался, ни один признак изменённости не сдвинулся, Book не изменена
end;Где физически лежат кэшированные байты
Для классических файлов .xls кэш — это поле FormulaValue записи Formula, восемь байт, описанных в [MS-XLS] §2.5.133. Когда старшее слово равно $FFFF, полезная нагрузка — не double IEEE 754, а помеченный вариант, и раскладку легко тонко испортить: тип варианта лежит в val[0], полезная нагрузка boolean или BErr — в val[2], а val[1] не определён. HotXLS раньше читал нагрузку из val[1] — то самое смещение на единицу, которое всплывает только на конкретных файлах, кэширующих boolean или ошибку, а не число. Читатель и писатель общих формул теперь согласованы по одним и тем же смещениям, поэтому кэшированный TRUE переживает загрузку и сохранение невредимым вместо распада в шум
Точная типизация в пакетных форматах — отдельная проблема со своей ловушкой. В OOXML кэшированное значение висит на элементе c как <v>, где атрибут t называет тип согласно ECMA-376 Part 1 §18.3.1.4. HotXLS читает t="e" прямо в Variant varError и при сохранении отображает обратно в стандартный текст ошибки, так что ошибки никогда не прикидываются обычными целыми — но RTL Delphi здесь не поможет, потому что VarAsType(Integer, varError) поднимает исключение конвертации. Рабочая конструкция задаёт TVarData.VType и TVarData.VError напрямую. Даты следуют той же дисциплине в обратную сторону: t="d" и тип значения даты ODF — явные декларации типа и становятся varDate, тогда как числовой кэш BIFF вообще не несёт флага даты и потому остаётся Double. HotXLS никогда не угадывает дату по числовому формату ячейки, потому что числовой формат — представление, а кэш — данные. ODF добавляет ещё один случай, о котором стоит знать — office:value-type="void" выражает присутствующий кэш без значения, а поскольку в ODF нет типа значения ошибки, похожий на ошибку текст сохраняется как текст, а не повышается до ошибки
function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
case Info.State of
xlfcsNotFormula: Result := 'not a formula cell';
xlfcsMissing: Result := 'formula stored with no cached value';
xlfcsInvalidated: Result := 'formula replaced since load';
else
case Info.Kind of
xlfcvError: Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
xlfcvBoolean: Result := BoolToStr(Info.Value, True);
xlfcvNumber: Result := FloatToStr(Double(Info.Value));
xlfcvString: Result := VarToStr(Info.Value);
else
Result := 'present but blank';
end;
end;
end;Разделяют ли общие формулы свои кэшированные значения?
Нет, и предположение об обратном — вот как обход заканчивается одним и тем же числом для целого столбца. Общая формула OOXML разделяет только выражение формулы и оптимизацию хранения; каждая ячейка-участник по-прежнему владеет своим <v>. Поэтому HotXLS никогда не транслирует кэш корневого участника последователю, пришедшему без значения, а последователь, загруженный как xlfcsMissing, и после сохранения и повторного открытия сообщает xlfcsMissing. Если вы разбираетесь, как группа хранится и разворачивается в первую очередь, механика атрибута si общей формулы и его развёртывания разобрана отдельно; для чтения кэша правило сводится к одной строке — спрашивайте каждую ячейку, не доверяйте ничему, о чём не спросили
Чтение кэшированных значений, единый кросс-движковый читатель и движок пересчёта, который можно не вызывать, поставляются в стандартном HotXLS Delphi Spreadsheet Component для Delphi и C++Builder без зависимости от Excel или какого-либо OLE-сервера автоматизации; на странице продукта лежит полный справочник API точек входа книги и читателя, показанных здесь