Дефинираното име е етикет, който замества константа, диапазон от клетки или формулен израз, съхранява се веднъж в работната книга и се използва символно навсякъде, където е необходимо. Напишете TaxRate във формула и машината ще го разреши до съдържанието на дефиницията, независимо дали това е literal 0.08 или диапазонът Data!$A$2:$D$100. Препратката между листове е отделна идея: Data!D2 достига клетка в друг лист, като квалифицира адреса с име на лист. Комбинацията позволява на обобщаващ лист да сумира детайлен лист чрез име, което не съдържа literal адрес, точно както е необходимо в работна книга, сглобена от генератор и проверявана по-късно от счетоводител
HotXLS предлага достъп до таблицата с имена в XLS и XLSX форматите, включително вградена машина за изчисления в процеса на работа. Двата формата обаче ползват различни класови йерархии и разликите в техните API са честа причина за грешки при портване на код
Два списъка с имена без общ интерфейс
При XLS TXLSWorkbook.GetNames връща колекция IXLSNames, чието претоварване Add(Name, RefersTo, Visible) записва име в BIFF таблицата с имена. Отделните записи се връщат като обекти IXLSName със свойства Name, RefersTo, разрешен диапазон RefersToRange и метод Delete. При XLSX TXLSXWorkbook.DefinedNames е колекция TXLSXDefinedNames с Add, FindByName и DeleteByName
Правилата за търсене се различават по начин, който се проявява при пренасяне, а не при компилиране. Свойството по подразбиране Item на XLS колекцията приема Variant, затова и Names[0], и Names['TaxRate'] се разрешават чрез него. XLSX колекцията няма такова свойство по подразбиране; извиквате FindByName('TaxRate'), което връща nil, когато името липсва. Кодът за едната фасада се компилира за другата само по случайност, а грешката обикновено се проявява като достъп през nil по време на изпълнение вместо като предупреждение в IDE
Обхватът е първото решение при създаване
Дефинираното име може да има обхват на ниво работна книга (видимо навсякъде) или на ниво лист (видимо само в него). В XLSX API това се определя от незадължителен параметър: DefinedNames.Add(AName, AFormula) създава име на ниво книга, а Add(AName, AFormula, ASheetIndex) го обвързва с конкретен лист. Свойството TXLSXDefinedName.SheetIndex връща -1 за обхват книга и индекса на листа в противен случай
Обхватът служи и как политика за избягване на конфликти. Excel позволява локално име Total на всеки лист, както и глобално Total, като формулата на листа разпознава първо локалното. Бизнес правилата, общи за целия документ (като данъчни или валутни курсове), трябва да се дефинират глобално. Помощните диапазони, нужни само за един лист, са по-безопасни с локален обхват, за да се избегнат конфликти
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
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');
finally
Book.Free;
end;
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, така че Sheets[1] е първият лист, за разлика от базираното на 0 XLSX Sheets[0]. Третият параметър на Add създава скрито име: то присъства във файла и може да се използва във формули, но не се вижда в Name Manager на Excel. Скритите имена са правилният механизъм за вътрешни връзки на генератора, които крайните потребители не трябва случайно да редактират или изтриват
Връзки между листове и промени в редовете
И двете машини за формули приемат стандартния синтаксис за препратки между листове. Обикновените имена на листове се квалифицират директно като Data!A1; име с интервали или пунктуация изисква единични кавички, както в 'Sheet With Space'!A1. В текста RefersTo на име почти винаги използвайте абсолютни препратки като Data!$A$2:$D$100. Относителната препратка в дефинирано име се разрешава спрямо клетката, която го използва, което е умишлена функция на Excel и надежден източник на объркване, когато се задейства случайно
Структурните промени показват ползата от поддържането на препратките между листове, а XLSX запазва имената съгласувани при тях. InsertRows и DeleteRows изместват диапазоните на дефинираните имена заедно с клетките, сливанията, хипервръзките и котвите на диаграмите, така че име към Data!$A$2:$D$100 продължава да покрива блока от данни, след като генераторът отвори място над него. Формулите имат едно документирано ограничение: вмъкването на редове коригира само препратките към редактирания лист. Формула в Summary, която сочи към Data!D2:D100, се пренаписва, когато редове се вмъкнат в Data, което обикновено е желаното поведение. Проверете го, вместо да го предполагате, защото машината може да ви даде отговор евтино:
// 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 (ECMA-376 / ISO 29500) съхранява така областите за печат и повтарящите се заглавни редове, като дефинирани имена със запазени идентификатори. HotXLS ги управлява чрез специални свойства на листа, така че задаването на PrintArea или PrintTitleRows записва съответния _xlnm.* запис вместо вас
Капанът е ръчното използване на това запазено пространство. Ако добавите _xlnm.Print_Area чрез DefinedNames.Add и едновременно зададете свойството PrintArea, работната книга ще съдържа две конфликтни дефиниции за едно запазено име, състояние, което Excel разрешава по начин, на който продуктът не трябва да разчита. Третирайте всеки идентификатор, започващ с _xlnm., като собственост на слоя със свойства. За проверка на настройките за печат четете свойствата, а не таблицата с имена. Статията за защита и настройка на страници разглежда свойствата за област за печат в контекст
Ограничения при проектиране
Дефинираните имена не преминават през удобния мост от XLS към XLSX. SaveXLSWorkbookAsXLSX копира съдържанието на клетките и основното форматиране, а таблицата с имена не е в документирания списък за копиране, затова работна книга, която зависи от имената си, ги губи при преобразуването. Пресъздайте имената чрез DefinedNames.Add след преобразуването. Тази стъпка е полезна възможност да нормализирате обхватите им, вместо да пренасяте каквото е съдържал XLS файлът
Другата граница е разминаването между низовете на формулите и имената на листовете. Excel пренаписва препратките във формули и имена при интерактивно преименуване, така че редактираните от потребител файлове остават съгласувани. Рискът е при генератора: когато Pascal кодът сглобява формулни низове от literal име на лист, преименуването на листа на едно място без промяна на другото създава препратка към несъществуващ лист. Съхранявайте името в една Delphi константа и я подавайте както на Sheets.Add, така и при сглобяването на формулата. Същият принцип подкрепя именуването на изходните клетки вместо твърдо зададени адреси: шаблон с именувана клетка за общ резултат продължава да работи, след като дизайнерът вмъкне три реда над нея, докато генератор, който записва в literal B17, тихо поставя стойността на грешно място. Статията за генериране на отчети по шаблони надгражда точно този модел
Пълното описание на дефинираните имена и машината за формули за двата формата е част от пакета на HotXLS Component