Дефинирани имена и формули между работни листове в Delphi с HotXLS

Преименувайте лист от „Summaryâ€?на „Overviewâ€?в генерирана книга и всяка вътрешна формула, която сочи към Summary!A1, ще спре да работи без съобщение при запис или отваряне. Формулата продължава да съществува, но препратката не води до никъде. Подобен проблем възниква и при преобразуване на .xls/.xlsx, когато относителна връзка загуби целта си. Дефинираното име (defined name) е етикет, който заменя константа, диапазон или формула, съхранен веднъж и използван символно навсякъде. Свързването на дефинирани имена с формули между листове позволява на сумарния лист да изчислява детайлния чрез символно име без твърдо зададени адреси, точно това, което се изисква за одит от счетоводители.

HotXLS предлага достъп до таблицата с имена в XLS и XLSX форматите, включително вградена машина за изчисления в процеса на работа. Двата формата обаче ползват различни класови йерархии и разликите в техните API са честа причина за грешки при портване на код.

Два списъка с имена без общ интерфейс

От страна на XLS методът TXLSWorkbook.GetNames връща колекцията IXLSNames, чието претоварване Add(Name, RefersTo, Visible) записва името в таблицата на BIFF. Елементите се връщат като обекти IXLSName, съдържащи име, референция и метод за изтриване. При XLSX колекцията TXLSXWorkbook.DefinedNames е от тип TXLSXDefinedNames с методи Add, FindByName и DeleteByName.

Разликите в търсенето се проявяват по време на изпълнение. XLS колекцията поддържа търсене с Variant, така че и Names[0], и Names['TaxRate'] работят. XLSX колекцията няма такова свойство по подразбиране и изисква извикване на FindByName('TaxRate'), което връща nil при липса на име. Код, написан за едната фасада, се компилира за другата само по случайност и грешката се проявява като празен достъп (nil access) по време на изпълнение.

Обхватът е първото решение при създаване

Дефинираното име може да има обхват на ниво работна книга (видимо навсякъде) или на ниво лист (видимо само в него). В XLSX API това се определя от незадължителен параметър: DefinedNames.Add(AName, AFormula) създава име на ниво книга, а Add(AName, AFormula, ASheetIndex) го обвързва с конкретен лист. Свойството TXLSXDefinedName.SheetIndex връща -1 за обхват книга и индекса на листа в противен случай.

Обхватът служи и как политика за избягване на конфликти. Excel позволява локално име Total на всеки лист, както и глобално Total, като формулата на листа разпознава първо локалното. Бизнес правилата, общи за целия документ (като данъчни или валутни курсове), трябва да се дефинират глобално. Помощните диапазони, нужни само за един лист, са по-безопасни с локален обхват, за да се избегнат конфликти.

var

  Book: TXLSXWorkbook;

  Data, Summary: TXLSXWorksheet;

begin

  Data := Book.Sheets.Add('Data');

  Summary := Book.Sheets.Add('Summary');

  // ... fill Data!A2:D100 with detail rows ...



  Book.DefinedNames.Add('TaxRate', '0.08');                // workbook scope, a constant

  Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // workbook scope, a range

  Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // scoped to sheet index 1 only



  // XLSX formulas take no leading '='

  Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';

  Book.SaveAs('model.xlsx');

end;

Името не е задължително да сочи към диапазон. Примерът TaxRate по-горе сочи към константата 0.08, което е най-чистият начин за дефиниране на бизнес параметри. Параметърът се показва веднъж в Name Manager, формулите го ползват символно, а промяната му се извършва на едно място в кода на генератора, вместо да се претърсват десетки формули.

Знакът за равенство, който принадлежи само на едната страна

Това е мястото, където портнатият код се чупи най-често. XLS клетките получават формули през свойството Value с водещ знак =. XLSX клетките имат отделно свойство Formula, което приема израза без знака за равенство. Ако запишете '=SUM(A1:A10)' в TXLSXCell.Formula, знакът за равенство се запазва като част от израза и формулата няма да работи.

var

  Book: IXLSWorkbook;   // interface-counted: do not Free

  Names: IXLSNames;

begin

  Book := TXLSWorkbook.Create;

  // assume a sheet named 'Data' already holds the detail rows

  Names := Book.GetNames;

  Names.Add('TaxRate', '0.08');

  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = hidden from the Name Manager



  // XLS formulas go through Value, with the '=' prefix

  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';

  Book.SaveAs('model.xls');

end;

Този пример показва още две особености на XLS фасадата: колекцията от листове започва от 1 (докато при XLSX започва от 0), а третият параметър на Add позволява създаване на скрито име (използваемо във формули, но невидимо в Name Manager). Скритите имена са подходящи за служебни цели на генератора, които потребителите не трябва да променят.

Връзки между листове и промени в редовете

Двете машини за формули приемат стандартния синтаксис за връзки между листове: обикновените имена се записват как Data!A1, а тези с интервали се ограждат с единични кавички (например 'Sheet With Space'!A1). При дефиниране на имена винаги ползвайте абсолютни адреси как Data!$A$2:$D$100. Относителните адреси се изчисляват спрямо клетката, която ги ползва, което може да доведе до грешки.

Промените в структурата се отразяват правилно на XLSX имената. Методите InsertRows и DeleteRows изместват диапазоните на имената заедно с клетките, обединените области и диаграмите. Формулите имат едно ограничение: вмъкването на ред променя само адресите, насочени към редактирания лист. Можете лесно да проверите резултата от изчисленията с метода Calculate:

// the calculation engine resolves names and cross-sheet references in-process

V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');

if VarIsNumeric(V) then

  Log('net total checks out: ' + FloatToStr(V));

Методът Calculate оценява произволен израз спрямо текущото състояние без записване на файла, което го прави идеален за автоматични тестове. Изчислете очакваната стойност в Pascal кода и я сравнете с резултата от формулата в Excel. Подробности за разширяването на този механизъм с ваши функции вижте в машината за формули на HotXLS.

Служебните имена _xlnm

Ако отворите генериран файл с нисконивоем инструмент, ще откриете служебни имена като _xlnm.Print_Area и _xlnm.Print_Titles. Така стандарта OOXML съхранява областите за печат и заглавните редове. HotXLS ги управлява автоматично през съответните свойства на листа (например PrintArea или PrintTitleRows).

Не променяйте ръчно тези служебни имена в пространството _xlnm.. Задаването им през DefinedNames.Add паралелно със свойствата на листа води до конфликти, които Excel разрешава по непредсказуем начин. Разглеждайте тези имена като запазени за свойствата на библиотеката, описани в защита и настройки на страници.

Ограничения при проектиране

Дефинираните имена не се прехвърлят при автоматично преобразуване на .xls към .xlsx с моста SaveXLSWorkbookAsXLSX. Тя копира само стойностите и базовото форматиране, така че имената трябва да бъдат пресъздадени ръчно след преобразуването. Това позволява и правилното им структуриране.

Друг проблем е несъответствието между формулите и имената на листовете. Excel автоматично обновява препратките при преименуване в неговата среда, но в Pascal кода промяната на името на листа на едно място без обновяване на формулите на друго води до неработещи референции. Поддържайте името на листа в единна Delphi константа, използвана за създаване и във формулите. Това съответства на практиката да се наименуват изходните клетки â€?шаблон с име на крайната клетка продължава да работи, дори ако дизайнерът вмъкне нови редове над нея, както е описано в генериране на отчети по шаблони.

Пълното описание на дефинираните имена и машината за формули за двата формата е част от пакета на HotXLS Component.