Метод 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 источника обратно в пару имён листов и регистрирует или переиспользует эквивалентную запись в собственных таблицах внешних ссылок назначения, создавая совершенно новую ссылку на внешнюю книгу, если назначение ещё никогда не ссылалось на этот исходный файл
Именно здесь проблема локальной для книги нумерации проявляется наиболее буквально, потому что токен внешней ссылки объединяет три отдельные координаты в одном поле, и каждая из них приватна для книги, что её записала: какая внешняя книга — слот в собственном списке внешних книг назначения, назначенный в том порядке, в каком эта книга случайно их зарегистрировала; какой лист внутри собственного списка листов той внешней книги, хранимый как индекс с отсчётом от единицы, привязанный именно к этой внешней книге, — совершенно другой домен нумерации, нежели собственные внутренние идентификаторы листов назначения; и сам диапазон ячеек, простые координаты строк и столбцов, не нуждающиеся в переводе, поскольку они никогда и не были относительными к книге. Ошибитесь в любом из первых двух, и Excel всё равно откроет файл, всё равно покажет формулу и вычислит её относительно неправильных внешних ячеек без единой жалобы. Один вид узла побеждает даже эту перепривязку на уровне дерева: ссылка на определённое имя, индекс в приватную таблицу имён собственной книги точно так же, как индекс листа приватен для собственного EXTERNSHEET, без доступного эквивалентного исправления на уровне дерева, — в момент, когда обход перепривязки встречает ссылку на имя где-либо в дереве, он отказывается от всей формулы целиком, а не записывает частично корректную. Даже когда перепривязка проходит успешно, целевая ячейка не показывает свежевычисленное число; она показывает то значение, что уже содержала исходная ячейка на момент копирования, хранимое в кэшированном слоте точно так же, как сам Excel кэширует последнее известное значение любой внешней ссылки, пока вы явно не обновите связи, что является правильным поведением по умолчанию, поскольку пересчёт через живую связь в другой файл — именно та операция, которую вы хотите запускать один раз, намеренно, а не при каждом открытии
Чего стоит эта конструкция
Механизм декомпиляции-перекомпиляции в AddCopy не бесплатен, и эту цену стоит спланировать заранее, прежде чем скриптовать крупное задание консолидации, а не после. Копирование листа внутри одной и той же книги идёт по дешёвому пути — простому дублированию скомпилированного дерева в памяти, потому что каждый индекс внутри него уже действителен в той книге, где он остаётся; копирование между книгами вместо этого платит за настоящий разбор на каждой ячейке с формулой — декомпиляция в текст, а затем компиляция этого текста заново с нуля, и хотя эта разница не стоит измерения на листе с несколькими десятками формул, исходная книга с десятками тысяч ячеек с формулами, копируемая как один лист среди десятков в пакетном задании, должна ожидать, что перекомпиляция будет доминировать во времени выполнения, а не ввод-вывод файлов вокруг неё. Порядок копирования важен ещё по одной причине помимо скорости: формула, ссылающаяся на лист, до которого AddCopy ещё не дошёл в этом пакете, проваливает перекомпиляцию по той же причине, что и формула, ссылающаяся на действительно несуществующий лист, так что задание, копирующее лист B перед листом A, от которого зависит формула, увидит, что эта формула деградирует именно так, как описано выше, — текстом строки или откатом к внешней ссылке, указывающей прямо обратно на исходный файл, из которого она только что пришла. И поскольку каждая исходная книга в пакете консолидации обычно создаётся независимо, стоит явно протестировать тот единственный режим отказа, о котором ни один отдельный исходный файл никогда не смог бы предупредить: пять книг филиалов, каждая из которых суммирует цифры соседнего филиала, могут скомбинироваться в настоящую циклическую ссылку внутри сводной книги, причём ни один отдельный исходный файл никогда не содержал бы такого цикла, — цикл, что существует только тогда, когда каждый лист оказался в одном месте, а пересчёт выполняется по объединённому набору
Копирование листов между книгами поставляется как стандартное поведение AddCopy в компоненте Excel для Delphi HotXLS для Delphi и C++Builder; на странице продукта представлен полный справочник API листов и книг, включая описанное здесь поведение диаграмм, форматированного текста и внешних ссылок