Формулен двигател и потребителски функции в HotXLS за Delphi

Библиотека за електронни таблици, която съхранява само низове с формули, и библиотека с работещ изчислителен двигател за формули са два различни продукта, които изглеждат идентични до момента, вкоито поискате число от някой от тях. Повечето код за електронни таблици в Delphi никога не забелязва тази разлика, тъй като Excel я маскира: запишете SUM(B2:B501) в клетка, запазете и Excel преизчислява сумата в момента, в който потребител отвори файла. Премахнете човешкия фактор от веригата, прекарайте същата работна книга през сървърен процес, който експортира директно в CSV, и разликата спира да бъде чисто академична. CSV файлът съдържа буквалния текст =SUM(B2:B501) там, където трябва да има число, защото в нито един момент нищо не е оценило реално формулата.

Това е границата, от правилната страна на която стои HotXLS. Той третира формулата по начина, по който го правят файловите формати - като записан текст плюс незадължителен кеширан резултат, така че обикновеният CSV експорт възпроизвежда рецептата, а не готовото ястие. Но той също така съдържа изчислителен двигател, който можете да извикате директно, същият двигател както в XLS, така и в XLSX фасадите, плюс механизъм за обработка на имена на функции, за които двигателят никога не е чувал. HotXLS е оригинална Object Pascal библиотека, която чете и записва XLS и XLSX от Delphi и C++Builder без автоматизация на Excel, а изчислителната й част е това, което превръща записаните формули обратно в стойности при поискване.

Формулите се съхраняваха, а не се оценяват незабавно

Записването на формула в клетка не изчислява нищо. При запис работната книга регистрира текста на формулата. От страна на XLS тя също така записва флагове, управлявани от RecalcOnSave, който по подразбиране е True и указва на Excel да преизчислява при отваряне. Този модел е правилен за файлове, предназначени за Excel, и грешен за процеси, които консумират стойностите на клетките директно, независимо дали става въпрос за CSV експорт, HTML експорт или ваш собствен код, който чете клетките обратно. За тях правете оценка изрично с Calculate. Той съществува в четири входни точки: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook и TXLSXWorksheet излагат function Calculate(const Formula: WideString): Variant.

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

Изразът, подаден на Calculate, е обикновен текст на формула на Excel. Препратките между листове, дефинираните имена и вложените функции се разрешават спрямо текущата работна книга в паметта, което прави извикването полезно далеч отвъд коригирането на CSV експорти. Третирайте го като механизъм за проверка (assertion). Генератор, който току-що е записал петстотин подробни реда, може да поиска от работната книга общата си сума и да я сравни с числото, което е изчислил независимо в Pascal, улавяйки грешка от тип "off-by-one" в диапазона преди одиторът на клиента да го направи.

Това също така очертава правилната стратегия за тестване при изходни данни с голям брой формули. Excel си остава еталонната имплементация на езика за формули, така че за малкото формули, които имат бизнес последици, поддържайте одобрен тестов файл с данни (fixture), чиито очаквани стойности са генерирани от самия Excel, и накарайте изграждащия процес (build pipeline) да оценява формулите на генерираната работна книга с Calculate спрямо тези тестови файлове. Разликите тогава ще се появят като неуспешни тестове в Delphi, а не като несъответствия, открити от клиент при сравняване на два отчета.

Добавяне на бизнес функции с OnUserFunction

Когато двигателят срещне име на функция, което не разпознава, той генерира събитие, вместо да се провали напълно. Задайте OnUserFunction на който и да е клас на работна книга и можете сами да разрешите извикването:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Три детайла заслужават внимание. Първо, задайте Handled := True само когато действително сте разпознали името. Оставянето му като False позволява на двигателя да продължи нормалната си обработка на непознати функции, така че един и същ манипулатор може да обслужва няколко работни книги, без да претендира за всичко, което преминава през него. Второ, сравнявайте имената без значение от регистъра на буквите чрез SameText, тъй като авторите на формули пишат discount( и DISCOUNT( взаимозаменяемо. Трето, аргументите пристигат предварително оценени: DISCOUNT(A1) ви предава стойността на A1, а не препратката, така че функцията не може да определи откъде идват нейните входни данни. Тази последна точка поставя ограничението, за което се отнася следващият раздел.

Третирайте тялото на манипулатора със същата защита, както всяка външна входна точка. Масивът Args отразява това, което авторът на формулата е въвел, така че валидирайте броя на аргументите и техните типове преди индексиране в него и вземете решение предварително какво връща невалидно извикване - грешна Variant стойност или повдигнато изключение. Изборът е важен, тъй като изключение, хвърлено в манипулатора, се разпространява навън през извикването на Calculate, което е задействало оценката. Това е приемливо в строго контролиран генератор и грубо в услуга, която оценява работни книги, създадени от потребители, където една лоша формула би провалила цялата заявка. В такава среда прихващайте грешките вътре в манипулатора и връщайте специална стойност (sentinel), която обграждащият работен процес може да разпознае и запише в лога.

Функциите, зависещи от позицията, се нуждаят от варианта Ex

Някои функции легитимно зависят от това къде се оценяват. Коефициент, който се различава за всеки лист, търсене спрямо реда, множител за регион, който се прилага само върху регионалните листове: нито едно от тези не може да бъде решено само чрез стойностите на аргументите. Обикновеното събитие не може да изрази това, така че двигателят предлага OnUserFunctionEx, идентично с изключение на един допълнителен параметър:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // the same formula yields a different rate on each regional sheet
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext съдържа SheetIndex, Row и Col на клетката, която се оценява. Ако резултатът на функцията зависи от нейното местоположение дори минимално, свържете събитието Ex от самото начало. Добавянето на контекст в манипулатор, който тридесет формули вече извикват, е много по-сложно от избора на правилната сигнатура в първия ден, а двете събития са толкова сходни по друг начин, че има малко причини да започвате с по-тясното.

Потребителските функции не се прехвърлят в Excel

Потребителската функция живее изцяло във вашия процес. Името DISCOUNT означава нещо само докато вашият Delphi код и неговият събитиен манипулатор работят. Отворете записания файл в Excel и DISCOUNT ще бъде просто неразпознато име; клетката ще покаже #NAME?, освен ако на машината на потребителя случайно не съществува съответстваща VBA функция или добавка. Това е проектният факт, който отличава демонстрацията от готов за разпространение продукт, и той налага избор, който трябва да направите съзнателно, вместо да откриете впоследствие.

Вземете решение за всяка клетка кой от двата договора доставяте. Клетките, които потребителят трябва да вижда как се преизчисляват в Excel, трябва да бъдат изградени единствено от собствения набор от функции на Excel. Клетките, чиято логика е фирмена собственост, трябва да бъдат оценявани в процеса с Calculate и запазвани като обикновени стойности, така че потребителската функция да се държи като вътрешно изчислително правило, а не като файлово съдържание. Режимът на отказ, който надеждно генерира тикети за поддръжка, е средното положение - записване на формула с потребителска функция и очакване Excel да я приложи.

Има едно скрито предимство на договора само за стойности: той защитава интелектуалната собственост. Правило за ценообразуване, оценено във вашия Delphi процес и изпратено като число, не може да бъде подложено на обратно инженерство от работната книга по начина, по който би могло с видима формула, и потребителят не може да го наруши чрез редактиране на междинна клетка. Генераторите на фактури, отчетите за комисионни и тарифните карти почти винаги спадат към тази категория. Случаят, който наистина се нуждае от активни формули, е интерактивният модел за анализи тип "какво-ако", при който се очаква клиентът да променя входните данни и да наблюдава промяната на сумите, и те трябва да бъдат изградени от собствения речник на Excel плюс дефинирани имена.

Режими на изчисление, итерация и R1C1: контролите на XLS фасадата

XLS фасадата излага настройките за изчисление на ниво BIFF, които Excel чете от файла. CalculationMode приема xlCalcManual, xlCalcAutomatic (по подразбиране) или xlCalcAutomaticExceptTables и определя как се държи Excel след отварянето на файла. Работна книга с хиляди формули често е по-удобно да бъде доставена в ръчен режим, така че получателят да реши кога да се случи вълната от преизчисления. EnableIteration (по подразбиране False), заедно с MaxIterations (по подразбиране 100) и MaxIterationChange (по подразбиране 0.001), отключва съзнателните кръгови препратки от типа на итеративно сближаване, които се появяват в някои финансови модели. ReferenceStyle превключва между A1 и R1C1 визуализация, а UseFullPrecision отразява опцията на Excel за прецизност според визуализацията.

Тези свойства живеят в XLS фасадата, защото се съпоставят с BIFF записи; при генериране на .xlsx планирайте формулите така, че да не зависят от итеративни настройки, или изчислете конвергираните стойности в Delphi и запишете резултатите.

Масиви от формули: публичната входна точка е XLSX

Остарелите CSE масиви от формули се създават чрез TXLSXRange.SetArrayFormula:

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Еквивалентният метод съществува в йерархията на класовете XLS, но се намира в частна (private) секция, така че няма поддържан начин за създаване на нови масиви от формули в .xls файлове. Съществуващите в отворените файлове се прехвърлят невредими двупосочно; това, което не можете да направите, е да ги създавате. Следващото правило е достатъчно просто: когато семантиката на масивите е част от изискването, насочете се към .xlsx. Ако остарял .xls краен продукт наистина се нуждае от поведение на масив, прагматичният начин е да изчислите резултата от масива в Delphi и да запишете отделните стойности в клетките.

Две свързани четива на този сайт: дефинирани имена и формули между листове покрива разрешаването на имена, което двигателят извършва, а статията за експортиране на CSV и TSV описва подробно поведението при експортиране, което прави необходимо изричното изчисление. Пълната справочна информация за двигателя, включително набора от поддържани функции, се доставя с компонента HotXLS.