Метод AddCopy у HotXLS копіює аркуш з однієї книги Excel в іншу, декомпілюючи кожну формулу на цьому аркуші в текст у стилі A1 і перекомпілюючи цей текст усередині цільової книги, а не копіюючи скомпільоване дерево формули напряму, бо посилання рядів даних діаграм, індекси шрифтів форматованого тексту та нумерація зовнішніх посилань — усе це призначається незалежно всередині кожного файлу книги
Збій проявляється саме в тій книзі, яку й очікуєш: завдання наприкінці місяця, що витягує один аркуш зі звіту кожного філіального офісу та додає його до зведеного файлу. Відкрийте результат — і діаграма підсумку малює цифри геть іншої філії, примітка, що була жирною й червоною у джерелі, повернулася до звичайного чорного тексту, а формула, що колись витягувала податкову ставку із супутньої книги-довідника, тепер показує застигле число, яке ніхто не може пояснити. Тут нічого не піднімає виняток — файл відкривається, цифри виглядають правдоподібно, і пошкодження сидить там, доки хтось не помітить діаграму з неправильним заголовком поряд
Чому AddCopy не може просто скопіювати скомпільоване дерево формули?
AddCopy не може перенести скомпільоване дерево формули незмінним, бо скомпільована формула BIFF — не самодостатній текст, а послідовність токенів, і кілька з цих токенів — невеликі цілі числа, що коректно розв'язуються лише всередині книги, яка їх створила. Тривимірне посилання на кшталт Sheet2!A1:A10 не несе буквального імені Sheet2 після компіляції; воно несе поле, яке специфікація BIFF називає ixti (HotXLS зберігає те саме значення у власному скомпільованому дереві під назвою поля FExternID), індекс у приватну таблицю EXTERNSHEET цієї книги, пронумеровану так, як саме ця конкретна книга випадково зареєструвала свої аркуші та зовнішні книги. Перенесіть токен незмінним у книгу, чия таблиця EXTERNSHEET була побудована в іншому порядку, і індекс 3 більше не означає Sheet2 — він означає будь-який аркуш, що випадково займає слот 3 там, і в 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 Delphi Excel для Delphi та C++Builder; сторінка продукту містить повний довідник API аркушів та книг, включно з поведінкою діаграм, форматованого тексту та зовнішніх посилань, описаною тут