HotXLS, нативната Excel библиотека за Delphi и C++Builder, прочита стойността, която Excel вече е запазил до формула, чрез TryGetCachedFormulaValue и IXLSFormulaCacheReader. Нито една от двете входни точки не извиква калкулатора, не декомпилира формулни токени, не обновява dirty състояние и не пише нищо обратно в модела, така че работна книга, която само четете, остава точно в състоянието, в което сте я отворили
Сценарият, който мотивира това, е скучен и изключително разпространен. Нощна задача отваря няколкостотин работни книги, произведени от други, извлича по една колона с тотали от всяка и бута числата в склад за данни. Тоталите вече си седят във файловете — Excel ги е изчислил и запазил. Но в момента, в който задачата поиска стойността на формулна клетка, библиотека, която има само един отговор на този въпрос, изгражда граф на зависимостите и оценява целия лист, и задача, която трябваше да е обвързана с входно-изхода, се превръща в бенчмарк за изчисления
Защо четенето на формулна клетка струва пълно преизчисление?
Защото getter за стойност на формулна клетка е заявка да произведе стойност, а единственият универсално коректен начин да се произведе такава е да се оцени формулата. Това е правилното поведение по подразбиране за приложение, което редактира работни книги, и грешното за конвейер, който ги извлича. По-лошо, оценяването не е без странични ефекти: тя пише резултатите обратно в клетки, обръща dirty флагове и може да се разреши различно от произвеждащото приложение, когато функция не се поддържа или външна препратка е счупена. Задача, която сте описали на операционния си екип като само за четене, тихо произвежда работна книга, която вече не съответства на тази на диска, а ако нещо по-късно я запази, файлът на диска също се променя
Четенето на кеширани стойности е другата половина на договора. То отговаря на по-тесен въпрос — какво е съхранило произвеждащото приложение тук? — и отказва да отговори на каквото и да е друго. Когато наистина искате пресни числа, HotXLS все още ви дава инкрементално преизчисление, управлявано от граф на зависимостите; смисълът е, че извличането и оценяването трябва да са два различни извиквания, а не едно извикване с две настроения
Три ортогонални факта за една клетка
Първо изводът: една кеширана формулна стойност носи три независими факта, а сгъването им в един Variant губи информация, която ви трябва. TXLSFormulaCacheInfo ги държи разделени като State, Kind и Value. TXLSFormulaCacheState записва произхода в пет случая — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated и xlfcsInvalidated — докато TXLSFormulaCacheValueKind класифицира payload-а като 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 означава, че клетката наистина е формула, но производителят не е съхранил payload със стойност за нея — чест резултат, когато генератор пише формули и оставя Excel да попълни резултатите при първо отваряне. xlfcsInvalidated означава, че формул текстът е бил заменен след зареждането, така че стойността, която някога е била там, описва израз, който вече не съществува. xlfcsCalculated, напротив, е успешен случай: той маркира стойност, която вашият собствен код или оценителят на HotXLS е произвел по време на тази сесия, за разлика от xlfcsLoaded, която идва от файла
Честността за липсващ кеш тежи повече от зашиването му. HotXLS отказва да измисли стойност, а при запис е еднакво строг — само xlfcsLoaded и xlfcsCalculated излъчват кеширана стойност, докато xlfcsMissing и xlfcsInvalidated записват само формулата, вместо да замразяват остаряла цифра във файла. Това ви оставя три разумни отговора в конвейер: прескочете реда и запишете пролуката, преизчислете съзнателно точно тази работна книга и приемете цената, или оценете и съгласувайте. Ако оцененото число не се съгласува с това, което произвеждащото приложение би записало, трасьорът на оценяване на формули е инструментът, с който да разберете къде двете изчисления се разминават, вместо да гадаете от резултата
Един четец за класическите, OOXML и ODF двигатели
Конвейерът не бива да се интересува дали току-що отвореният файл е BIFF, OOXML или ODF. IXLSFormulaCacheReader е единствената входна точка само за четене и за трите: както TXLSWorkbook.CreateFormulaCacheReader, така и TXLSXWorkbook.CreateFormulaCacheReader връщат лек адаптер върху оскъдното търсене на клетки, което всеки двигател вече използва, с идентични базирани на едно координати за лист, ред и колона. Класовете на работната книга умишлено не имплементират интерфейса сами — интерфейсна препратка към работната книга би променила семантиката на притежание и би позволила на извикващите да се промъкнат покрай lease-а за време на живот. Вместо това унищожаването на работната книга изчиства суровия указател вътре в този lease, а всеки четец, все още държан от кода ви, вдига 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);
// Никакъв калкулатор не се изпълни, никакъв dirty флаг не мръдна, Book е непроменена
end;Къде всъщност живеят кешираните байтове
За класическите .xls файлове кешът е полето FormulaValue на Formula записа, осем байта, описани в [MS-XLS] §2.5.133. Когато горната дума е равна на $FFFF, payload-ът не е IEEE 754 double, а маркиран вариант, и подредбата е лесна за фино объркване: типът на варианта седи в val[0], а булевият или BErr payload седи в val[2], като val[1] е неопределен. HotXLS преди четеше payload-а от val[1], което е видът отместване с едно, което изплува само на конкретните файлове, кеширащи булева стойност или грешка, а не число. Четецът и пишещият за споделени формули вече се разбират за едни и същи отмествания, така че кеширано TRUE оцелява при зареждане и запис непокътнато, вместо да се разпада в шум
Типовата точност в пакетните формати е отделен проблем със собствен капан. В OOXML кешираната стойност виси от елемента c като <v>, като атрибутът t назовава типа съгласно ECMA-376 Part 1 §18.3.1.4. HotXLS чете t="e" директно в varError Variant и го картографира обратно към стандартния текст за грешка при запис, така че грешките никога не се маскират като обикновени цели числа — но Delphi RTL няма да ви помогне тук, защото 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 справка за входните точки на работната книга и четеца, показани тук