Техническая статья

Патчинг одного листа в большом XLSX из Delphi

Финансовая книга, ведёмая руками, копит кеши сводных таблиц, срезы, условные форматы и годы клеточных стилей, а ночное задание должно всего лишь поменять цифры на одной вкладке данных. Полная загрузка и сохранение снова разбирает, пересобирает и сжимает каждую часть. Переписать только часть того листа и перенести все прочие zip-записи нетронутыми — значит пропустить почти всю эту работу

Возможно это благодаря самому формату пакета. Файл .xlsx — это zip-архив XML-частей, разложенных по Open Packaging Conventions (ECMA-376 Part 2), и каждая zip-запись хранит собственный метод сжатия, CRC-32 и сжатый размер и в локальном заголовке, и в центральном каталоге. Поэтому запись может переехать из одного архива в другой, ни разу не распаковавшись, — если копировщик пишет те три значения вместе с сырыми байтами. Кеш сводной таблицы не обязан быть понятым, чтобы сохраниться. Его достаточно скопировать

Зачем копировать сжатые байты вместо пересохранения книги?

Копирование на сжатом уровне превращает большую часть патча в простое копирование потока. В HotXLS TXLSDirectWriter.BeginPatch открывает исходный пакет только на чтение и отдаёт каждую запись, которую патч не трогает, методу TZipArchive.CopyCompressedFrom, который пишет сжатые байты, CRC и размеры исходной записи прямо в целевой архив. Обычный путь записи платит за распаковку на входе и за сжатие на выходе, а сжатие — дорогая половина. На книге, чья масса — большой кеш сводной плюс пара десятков встроенных изображений, время уходит в лист, который вы реально пишете

Сырое копирование покрывает записи, хранящиеся без сжатия (zip-метод 0), и дефлированные записи (метод 8). Запись с каким-то другим методом сжатия всё равно переносится: писатель читает её через распаковывающий поток и пишет обратно, так что часть в целевом пакете оказывается в любом случае. Маркерные записи каталогов содержимого не несут и пропускаются, поскольку писатель делает собственные. Горстка записей вообще не копируется, потому что перегенерируется или правится: целевой лист и его часть _rels, xl/calcChain.xml, xl/_rels/workbook.xml.rels и [Content_Types].xml, плюс xl/workbook.xml, когда вставляется лист

Диаграмма точечного патча XLSX от HotXLS из Delphi: целевой лист переписывается через AddSheet, AddRow и вызовы Write, все прочие части пакета переходят через CopyCompressedFrom со своими сжатыми байтами дословно, а устаревшая часть calcChain отбрасывается, потому что Excel пересобирает её при пересчёте
HotXLS копирует нетронутые части на сжатом уровне и переписывает только целевой лист, тогда как устаревшая цепочка вычислений удаляется, а не копируется

Замена одного листа на месте через BeginPatch

Двухаргументная перегрузка BeginPatch(FileName, SheetName) заменяет существующий лист и пишет результат обратно по тому же пути. Она разрешает имя через реестр листов в xl/workbook.xml и карту связей, привязывающую каждый лист к его части, сравнивает имена без учёта регистра и бросает исключение с текстом "worksheet not found", когда ничего не совпало. Дальше лист авторится теми же вызовами AddSheet, AddRow и Write*, которыми потоковый писатель пользуется для новых файлов

var
  W: TXLSDirectWriter;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch('monthly-dashboard.xlsx', 'Data');  // замена на месте
    W.AddSheet('Data');               // имя в режиме патча информационное
    W.AddRow(1);
    W.WriteString(1, 'Region');
    W.WriteString(2, 'Revenue');
    W.AddRow(2);
    W.WriteString(1, 'North');
    W.WriteNumber(2, 184320.55);
    W.AddRow(3);
    W.WriteString(1, 'South');
    W.WriteNumber(2, 97410.20);
    W.AddRow(4);
    W.WriteString(1, 'Total');
    W.WriteFormula(2, 'SUM(B2:B3)');  // без ведущего '='
    W.Close;
  finally
    W.Free;
  end;
end;

Две детали в этом листинге легко прочесть неправильно. Имя, переданное AddSheet, ничего не переименовывает: вкладка сохраняет имя, sheetId и позицию, какие уже имеет в xl/workbook.xml, так что AddSheet('Totals') всё равно запишет в Data. А второй AddSheet бросает исключение, потому что один патч пишет ровно один лист. Обновление трёх вкладок — это три патча, и каждый снова копирует пакет

Формула входит без знака равенства. WriteFormula кладёт свой текст прямо в элемент <f> ячейки, а ECMA-376 Part 1 §18.3.1.40 хранит формулы там без «=», которую Excel показывает в строке формул; запись '=SUM(B2:B3)' положит в XML литеральный знак равенства. WriteFormula к тому же не выдаёт кэшированного результата. Если что-то ниже по цепочке читает кэш вместо пересчёта, WriteFormulaNumber пишет формулу вместе с числом, в которое вы и так знаете, что она вычисляется

Перегрузка «на месте» никогда не пишет в оригинал по ходу работы. Пропатченный пакет собирается в соседнем файле, названном по исходнику с добавкой .hxlpatch.tmp. На Close дескриптор источника освобождается, и подготовленный файл заменяет оригинал одним переименованием. Если переименование провалится, Close бросает исключение, чьё сообщение называет оба пути, сохраняет подготовленную копию и оставляет оригинал на месте. Процесс, убитый до Close, оставляет оригинал целым и бродячий .hxlpatch.tmp рядом, который следующий BeginPatch на этом файле удаляет перед стартом

Добавление нового листа в копию шаблона

Четырёхаргументная перегрузка BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) не трогает исходный файл и пишет новый пакет. При InsertSheet = False она заменяет названный лист в копии; при True — добавляет новый лист под этим именем и бросает "worksheet already exists", если шаблон уже носит вкладку с таким именем

  // template.xlsx остаётся нетронутым; output.xlsx получает одну лишнюю вкладку
  W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
  W.AddSheet('Extra');
  W.AddRow(1);
  W.WriteString(1, 'Appended by the nightly job');
  W.Close;

Вставка — то место, где правится пакетная бухгалтерия. Писатель сканирует существующие записи xl/worksheets/sheetN.xml в поиске наибольшего номера части, элементы <sheet> — наибольшего sheetId, связи книги — наибольшего rId, и берёт следующее значение каждого. Новый элемент <sheet> встаёт прямо перед </sheets>, так что новая вкладка оказывается последней. Атрибут связи переиспользует префикс, каким исходная книга связывает листы, — обычно это r:id, но не требуется, чтобы так было

Типы связей тоже следуют исходному пакету. Когда связи книги уже используют строгие пространства имён ISO/IEC 29500 под purl.oclc.org, новая связь листа пишется со строгим типом URI; переходная книга получает переходный. Пропатченная строгая книга, таким образом, не остаётся с одной переходной связью среди строгих

Диаграмма двух перегрузок BeginPatch в HotXLS в Delphi: форма «на месте» ставит пропатченную книгу во временный файл и завершается атомарными закрытием, удалением и переименованием, так что сбой оставляет оригинал целым; форма вставки пишет новый файл после хирургии реестра листов и карты отношений
Патч на месте выстаивает результат во временном файле перед атомарной подменой, тогда как вставка переписывает реестр листов и карту отношений отдельного целевого файла

Стили, строки и части, которые патч не трогает

Внутри патча всё, на что указывает новый лист, обязано уже существовать в исходном пакете. Поэтому строки пишутся встроенно в лист, а не в xl/sharedStrings.xml: таблица общих строк переходит байт в байт и расти не может. Установка UseSharedStrings в True заставляет AddSheet бросать исключение в режиме патча

То же правило меняет смысл StyleIndex. В патче это позиция в списке cellXfs исходной книги (ECMA-376 Part 1 §18.8.10), и писатель проверяет лишь то, что значение лежит между 0 и 65534. Он не может сказать, существует ли индекс 14 в данном конкретном файле, поэтому неверное число идёт прямо в вывод. Самый надёжный способ найти правильный — распаковать текущую версию листа и прочитать атрибут s у ячейки, которая уже несёт нужный формат; это и есть позиция в cellXfs. Excel добавляет записи в тот список всякий раз, когда кто-то форматирует ячейки в шаблоне, так что перепроверяйте индекс после каждой правки шаблона, а не доверяйте константе, записанной год назад

const
  // позиция cellXfs формата даты в ЭТОМ шаблоне, взятая из атрибута
  // s="..." существующей ячейки с датой; перепроверяйте после правок шаблона
  TemplateDateXf = 5;
begin
  ...
  W.AddRow(5);
  W.WriteString(1, 'Refreshed');
  W.WriteDateTime(2, Now, TemplateDateXf);  // явный индекс: принят

  // W.WriteDateTime(2, Now);  бросает в режиме патча: его неявный стиль даты
  //                           живёт в части стилей, которую патч никогда не пишет
end;

Всё, что добавляло бы новые части пакета, отвергается. AddTable, AddChart, семейство AddImage, AddComment, AddDefinedName и AddCellStyle бросают исключение сразу при вызове, а Close проверяет ещё раз перед фиксацией — это заодно ловит условные форматы, задающие собственную заливку, цвет шрифта, жирность или курсив, поскольку тем нужны новые записи в styles.xml. Отвергнутый вызов лучше пакета, где лист ссылается на часть таблицы, которую никто не регистрировал

Режим замены тоже стартует лист с нуля. Часть связей старого листа не копируется, так что рисунки, комментарии или внешние гиперссылки, прикреплённые к прежней версии, к новой больше не прикреплены, а ширины столбцов, закрепления, объединённые диапазоны и проверка данных, хранившиеся в старом XML листа, исчезли тоже. Те, что живут в XML листа, можно написать заново через SetColumnWidth, FreezePanes, MergeCells, AddDataValidation и AddHyperlink. Диаграмма или картинка, пришпиленные к вкладке данных, через патч не вернутся — держите диаграммы на собственном листе и направляйте их ряды на вкладку данных

Цепочка вычислений отбрасывается в обоих режимах. xl/calcChain.xml перечисляет формульные ячейки в том порядке, в каком Excel их последний раз вычислял; после замены её записи описывают ячейки, которых в таком виде больше нет, а после вставки индексы листов, на которые она ссылается, сдвинулись. Часть исключается из копии, а её связь и её Override в [Content_Types].xml удаляются, чтобы пакет не ссылался на отсутствующую часть. Excel пересобирает цепочку при следующем пересчёте

Когда цикл строк падает на середине

Деструктор TXLSDirectWriter доделывает пакет, если Close так и не вызвали, поэтому забытый Close не оставляет недописанный zip валяться. Поскольку он доделывает то, что писатель держит, исключение, разматывающееся через Free под перегрузкой «на месте», всё равно фиксирует написанные на тот момент строки и подменяет ими оригинал. Цикл, который выкачивает и пишет строки, падает на середине и освобождает писателя в блоке finally, — заканчивается ровно так

Для всего, что работает без присмотра, пишите в явную цель, откройте результат, проверьте — и только потом сами подменяйте живой файл. Исключение из цикла строк всё ещё позволяет Free доделать подготовленный файл, но пропускает подмену, так что живая книга остаётся со вчерашними цифрами вместо половины сегодняшних

uses
  Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;

procedure RefreshDashboard;
const
  Live = 'monthly-dashboard.xlsx';
  Staged = 'monthly-dashboard.next.xlsx';
var
  W: TXLSDirectWriter;
  Check: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch(Live, Staged, 'Data', False);
    W.AddSheet('Data');
    WriteRefreshedRows(W);  // ваш собственный цикл строк, не часть HotXLS
    W.Close;
  finally
    W.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    if Check.Open(Staged) <> 1 then
      raise Exception.Create('Staged workbook does not open');
    Sheet := Check.Sheets.SheetByName('Data');
    if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
      raise Exception.Create('Data sheet did not come back as written');
  finally
    Check.Free;
  end;

  // Windows API, не HotXLS: подменяйте живой файл только после проверок
  if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
    RaiseLastOSError;
end;

TXLSXWorkbook.Open возвращает 1 при успехе, и Sheets, и Cells индексируются с единицы: Check.Sheets[1] — первая вкладка, Cells[2, 1] — A2. Помимо пробного значения, две структурные проверки ловят большинство ошибок. Check.Sheets.Count должен равняться числу шаблона после замены и превосходить его на единицу после вставки, а листинг архива любым zip-инструментом не должен показывать ни xl/calcChain.xml, ни имени записи, встречающегося дважды. Дубликаты имён важны, потому что некоторые читатели берут последнюю запись с данным именем, из-за чего два листа выглядят так, будто их содержимое поменялись местами

Патчить, стримить или загружать книгу?

Патчинг подходит, когда книга большая, изменение ограничено одним листом, а остальной файл должен выжить бит в бит. Это неправильный инструмент, когда изменение затрагивает несколько листов, когда новым данным нужны форматы, которых шаблон ещё не определяет, когда надо создавать диаграммы или таблицы или когда файл достаточно мал, что обычная загрузка и сохранение не стоят ничего измеримого. Для массовой генерации с нуля лучше подходит потоковый прямой писатель, а поскольку он делит те же вызовы AddRow и Write*, перенос кода между ними — в основном дело подмены BeginFile на BeginPatch

Когда задаче действительно нужна полная объектная модель, работа на уровне листов внутри загруженной книги описана в статье дублирование листов в пакетах XLSX. А если патч на столе только потому, что обработка целой книги стала медленной, производительность больших книг разбирает, куда на самом деле уходят время загрузки-сохранения и память, прежде чем вы зафиксируете подход

Патч-писатель, потоковый писатель и полная объектная модель TXLSXWorkbook поставляются в одной библиотеке для Delphi и C++Builder; издания и полный список возможностей — на странице компонента HotXLS для электронных таблиц Delphi