Методът AddCopy на HotXLS копира работен лист от една Excel книга в друга, като декомпилира всяка формула в текстов вид със синтаксис A1 и след това компилира текста в целевата книга, вместо да копира директно компилираното дърво на формулата, защото препратките към серии на диаграми, индексите на шрифтовете в обогатен текст и номерацията на външните връзки се задават независимо във всеки файл на работна книга
Проблемът се появява точно в очаквания сценарий: задача в края на месеца извлича по един лист от отчета на всеки клон и го добавя към обобщаващ файл. Отваряте резултата и диаграма за междинната сума показва числата на съвсем друг клон, бележка, която е била удебелена и червена в източника, отново е обикновен черен текст, а формула, която е вземала данъчна ставка от спомагателна книга за справки, сега показва замръзнало число без очевидна причина. Тук няма изключение — файлът се отваря, числата изглеждат правдоподобно и повредата остава, докато някой не забележи диаграма с неправилно заглавие
Защо AddCopy не може просто да копира компилираното дърво на формулата
AddCopy не може да премести компилираното дърво на формулата без промяна, защото компилираната BIFF формула не е самостоятелен текст, а последователност от токени, някои от които са малки цели числа и се разчитат правилно само в книгата, която ги е създала. 3D препратка като Sheet2!A1:A10 не съдържа буквалното име Sheet2, след като бъде компилирана, а поле, което спецификацията BIFF нарича ixti и HotXLS съхранява в собственото си компилирано дърво под името на полето FExternID, тоест индекс в частната таблица EXTERNSHEET на тази книга, номериран според реда, в който конкретната книга е регистрирала своите листове и външни книги. Ако преместите токена без промяна в книга, чиято таблица EXTERNSHEET е изградена в друг ред, индекс 3 вече не означава Sheet2, а означава листа, който заема трета позиция там, и Excel няма как да сигнализира за грешката, защото според файловия формат формулата е напълно валидна. Точно този проблем предотвратява TXLSWorksheets.AddCopy: извикан от колекцията с листове на която и да е от двете книги в код на Delphi или C++Builder, той копира работен лист — стойности на клетки, формати, формули, диаграми, коментари, обединявания, настройки на страницата и още много — от изходна книга, която може да е или да не е книгата, върху която го извиквате, и добавя резултата в целевата книга с избрано от вас име или с разграничено копие на оригиналното име
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;Решението: декомпилиране до текст и повторно компилиране в целевата книга
HotXLS решава проблема с индексите, като не позволява на самото компилирано дърво да пресече границата между книгите. При всяка клетка с формула, копирана между книги, AddCopy декомпилира формулата от източника до същия текст със синтаксис A1, който потребителят би видял в лентата за формули на Excel, след което подава текста на целевата книга, която го анализира отначало и изгражда дърво чрез собствените си таблици — препратка с име на лист като Data!D2:D100 в този момент е просто низ, а един и същ низ означава едно и също във всяка книга, така че ако целевата книга вече има лист с име Data, препратката се разрешава правилно без превод на индекс, защото никога не е имало необработен индекс за превод. HotXLS плаща за това преобразуване само когато е необходимо: копирането на лист в същата книга използва по-евтин път, при който компилираното дърво просто се дублира в паметта, тъй като всеки индекс в него вече е валиден там, където остава, а отклонението през текст се изпълнява едва след като AddCopy установи, че източникът и целевата книга са различни екземпляри. Важно е да се уточни и какво не представлява това преобразуване. То няма общо с преместването на редове и колони при вмъкване или изтриване на редове в един лист, което е разгледано подробно в придружаваща статия — онзи механизъм пренаписва текста A1 на място, за да следва клетките, преместени с няколко реда нагоре или надолу в една книга, докато този механизъм се задейства, когато формулата напусне книгата, в която е компилирана, и проблемът вече е частната номерация на книгата, а не преместването на редове
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Какво става, ако целевата книга още няма този лист или това име
Повторното компилиране на AddCopy успява само когато целевата книга вече съдържа всичко, към което се обръща текстът на формулата, а двата практически пропуска са лист със същото име, който още не е копиран в текущата операция, и име с обхват на книгата, което никога не е съществувало в целевата книга. HotXLS не повдига изключение, когато компилацията се провали по средата на копирането на лист — присвояването на Value на клетката тихо съхранява текста на формулата като обикновен низ, което е умишлен и проверим режим на отказ, защото клетка с формула, която неочаквано показва буквален текст като =SUM(Q1!B2:B12) вместо изчислено число, е ясен знак, че нещо по-рано в копирането не се е разрешило. Преди да се откаже, AddCopy прави един опит за поправка: обхожда синтактичното дърво на неуспешната формула, събира всеки идентификатор на дефинирано име, до който формулата се допира, и за всяко име с обхват на книгата, което съществува в източника, но още не съществува в целевата книга, го копира и компилира същия текст втори път. Имената с обхват на лист са извън възможностите на тази поправка, тъй като име, видимо само за формулите в един лист на източната книга, няма еквивалентен слот, който да бъде пренесен, а име със същия правопис, което вече съществува в целевата книга, остава непокътнато, защото се приема, че име, създадено умишлено от извикващия код, трябва да бъде запазено. В една книга търсенето на име от формула между листове автоматично преминава от обхвата на листа към обхвата на книгата, което е механизмът, описан в статията на HotXLS за дефинираните имена и формулите между листове, но преминаването през реална граница между книги премахва тази защита и името трябва да бъде пренесено умишлено, иначе зависимата от него формула се превръща в текст
Препратките към серии на диаграми изискват същата поправка, но по различен път
Серия на диаграма в HotXLS, която начертава диапазон от клетки, има точно същия проблем с номерацията като обикновена формула на клетка, защото препратката към диапазона от данни на диаграмата също е компилиран поток от токени на формула — спецификацията BIFF нарича записа, който го съдържа, BRAI ([MS-XLS] раздел 2.4.51) — но AddCopy не може да го поправи, като използва обичайния път за зареждане на диаграми, защото именно този път създава грешката. Когато запис на диаграма се анализира от диска при обичайното отваряне на файл, дървото на формулата му се изгражда чрез превод на необработените байтове през екземпляра на изчислителя, който извършва анализа; ако подадете необработените BRAI байтове на изходната диаграма през обичайния записващ модул на целевата книга, вложеният в тези байтове ixti ще бъде разрешен спрямо таблицата EXTERNSHEET на целевата книга и серията тихо ще сочи към листа, който заема тази позиция там — същият клас грешка като копирането без промяна на компилираното дърво на клетка, само че е по-труден за забелязване, защото никой не чете формулите на сериите на диаграми така, както чете формулите на клетките. HotXLS избягва капана чрез специален път за клониране: TXLSCustomChart.AssignFrom копира дословно собствените неформулни байтове на хедера на всеки запис на диаграмата, след което изгражда наново свързания диапазон чрез същия примитив за декомпилиране и повторно компилиране, използван за обикновените клетки, така че новото дърво се създава спрямо таблицата EXTERNSHEET на целевата книга отначало, вместо да бъде разтълкувано спрямо нея след факта
Същият проблем с номерацията, по един индекс на шрифт
Не всяко число, локално за книгата, вътре в диаграма или клетка с обогатен текст е формула, а индексът на шрифт е същият проблем в умален вид. Обхватите с обогатен текст, както и още два типа записи на диаграми, които съдържат шрифт за заглавие или ос, съхраняват препратката към шрифта като необработен целочислен индекс в собствената таблица с шрифтове на книгата и този индекс не означава нищо в таблицата на друга книга — там може да сочи към напълно различен шрифт, размер или цвят. HotXLS решава това по стойност, а не по номер: намира действителните атрибути на шрифта на този индекс в изходната таблица, намира или създава съвпадащ запис в таблицата на целевата книга и променя съхранения индекс, така че да сочи към новата позиция. Една особеност на формата прави самото търсене по-неудобно — индексът във файла пропуска позиция 4, празнина в номерацията, описана в [MS-XLS] раздел 2.5.339, затова кодът трябва да намали индекса с едно, преди да сравни шрифтовете, и да го увеличи обратно с едно, преди да запише резултата
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Какво става с формула, която още сочи извън книгата
Формула, която достига до трета книга още преди да извикате AddCopy, е единственият случай, който преобразуването през текст не може да пренесе, защото собственият декомпилатор на HotXLS от формула към текст умишлено не създава текст със скоби във вида [Book]Sheet! за външна препратка, а компилаторът от другата страна също не приема този синтаксис като вход — затова този случай използва втори механизъм, който изобщо не докосва текста. Когато описаната по-горе поправка за пренасяне на имена все още оставя клетката като низ и изходната книга има действително име на файл, AddCopy сменя стратегията: копира дълбоко самото компилирано дърво на формулата, вместо текста му, и подава копието на специален етап за пренасочване, RebindExternRefsInTree, който го обхожда възел по възел. За всяка намерена препратка към диапазон този етап разрешава записа EXTERNSHEET от източника обратно до двойка имена на листове и регистрира или използва повторно еквивалентен запис в собствените таблици на целевата книга за външни препратки, като създава чисто нова връзка към външна книга, ако целевата книга още не е сочила към този файл
Тук проблемът с локалната за книгата номерация е най-буквален, защото токенът за външна препратка събира три отделни координати в едно поле и всяка от тях е частна за книгата, която го е записала: коя е външната книга — позиция в собствения списък на целевата книга с външни книги, присвоена според реда, в който тази книга е регистрирала съответните записи; кой е листът вътре в списъка на листовете на тази външна книга — индекс, започващ от 1 и ограничен специално до външната книга, което е отделна номерационна област от вътрешните идентификатори на листовете в целевата книга; и самият диапазон от клетки — обикновени координати на редове и колони, които не изискват превод, защото никога не са били относителни към книга. Ако сгрешите някое от първите две, Excel пак отваря файла, пак показва формула и без оплакване я изчислява спрямо грешните външни клетки. Един вид възел побеждава дори това пренасочване на ниво дърво: препратка към дефинирано име, индекс в собствената частна таблица с имена на книгата по същия начин, по който индексът на лист е частен за собствения му EXTERNSHEET, без налична поправка на ниво дърво — в момента, в който обхождането срещне препратка към име някъде в дървото, то изоставя цялата формула, вместо да запише частично коректна версия. Дори когато пренасочването успее, клетката в целевата книга не показва прясно изчислено число, а стойността, която изходната клетка вече е съдържала при копирането, съхранена в кешираната позиция по същия начин, по който Excel кешира последната известна стойност на всяка външна препратка, докато изрично не обновите връзките, което е правилната настройка по подразбиране, защото преизчисляването по жива връзка към друг файл е операция, която трябва да задействате веднъж и умишлено, а не при всяко отваряне
Каква е цената на този дизайн
Механизмът за декомпилиране и повторно компилиране на AddCopy не е безплатен и е добре да планирате цената му, преди да напишете голяма задача за консолидиране, а не след това. Копирането на лист в същата книга използва евтиния път, директно дублиране на компилираното дърво в паметта, защото всеки индекс в него вече е валиден в книгата, в която остава; копирането между книги плаща за реален анализ на всяка клетка с формула, декомпилира я до текст и след това компилира текста отначало. Разликата не си заслужава измерването при лист с няколко десетки формули, но изходна книга с десетки хиляди клетки с формули, копирана като един лист сред десетки в пакетна задача, трябва да очаква повторната компилация да доминира времето за изпълнение, а не файловият вход и изход около нея. Редът на копиране е важен и по втора причина освен скоростта: формула, която сочи към лист, до който AddCopy още не е стигнал в текущата операция, не успява при повторната компилация по същата причина като формула, сочеща към действително несъществуващ лист, така че задача, която копира лист B преди лист A, от който зависи формулата, ще види как тази формула се влошава точно както е описано по-горе — до текстов низ или резервна външна връзка, сочеща обратно към изходния файл. Тъй като всяка изходна книга в пакет за консолидиране обикновено е създадена независимо, е важно изрично да проверите единствения режим на отказ, за който нито един самостоятелен файл не би могъл да ви предупреди — пет клона, които всеки сумират числата на съседен клон, могат да образуват истинска циклична препратка в обобщаващата книга, без нито една изходна книга да съдържа такава сама по себе си, защото цикълът се появява едва след като всички листове попаднат на едно място и преизчисляването обхване общия набор
Копирането на работни листове между книги е стандартно поведение на AddCopy в компонента HotXLS за Excel в Delphi за Delphi и C++Builder; продуктовата страница съдържа пълната справка за API на работните листове и книгите, включително описаното тук поведение на диаграмите, обогатения текст и външните препратки