Объекты камеры Excel из Delphi: живые снимки

HotXLS создаёт объекты камеры Excel на листах XLSX из Delphi через AddCamera. Объект камеры — это картинка, постоянно связанная с диапазоном ячеек: Excel перерисовывает её из исходного диапазона при каждом открытии книги или изменении источника, поэтому панель показателей может отображать живой вид таблицы, находящейся на другом листе, в любом размере и, если нужно, повёрнутой

Эта возможность существует в Excel как инструмент «Камера» — кнопка, которую большинство пользователей никогда не видело, поскольку по умолчанию её нет на ленте. Она решает реальную проблему панелей показателей лучше альтернатив: скопированный диапазон устаревает, диаграмма не может показать произвольное содержимое ячеек, а связанная картинка, вставленная вручную, не может быть сгенерирована кодом. Объект камеры — единственная конструкция, одновременно живая и произвольная

Где это документировано и почему это важно?

Основная спецификация электронных таблиц не описывает объекты камеры; они трактуются как деталь реализации. Авторитетное описание находится в документации по разметке рисунков, в разделе про инструмент «Камера», и знание этого экономит целый вечер поисков не в том документе

Структурно объект камеры — это обычный элемент картинки, чьи невизуальные свойства картинки несут список расширений. Расширение идентифицируется фиксированным GUID, а внутри него элемент из пространства имён рисунков 2010 года записывает две вещи: исходный диапазон как абсолютную ссылку в стиле A1, опционально с указанием листа, и идентификатор фигуры. Всё остальное в картинке — обычное

Создание объекта камеры

Существуют две перегрузки, потому что удобны два способа именования диапазона. Текстовая форма принимает ссылку ровно в том виде, в каком она будет сохранена, — это то, что нужно для источника с другого листа. Координатная форма принимает четыре координаты ячеек на том же листе и строит абсолютную ссылку за вас:

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Dashboard: TXLSXWorksheet;
  Cam: TXLSXImage;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('reporting.xlsx') <> 1 then
      Exit;
    Dashboard := Book.Sheets[0];

    // Живой вид Data!$B$2:$D$4, размещённый поверх B10:F20 на панели показателей
    Cam := Dashboard.AddCamera('Data!$B$2:$D$4', 10, 2, 20, 6);

    // Рамка размещения по умолчанию использует собственные столбцы Excel по 64 px
    // и строки по 20 px; при необходимости скорректируйте нарисованный размер в EMU
    Cam.WidthEMU := Round(12.5 * 914400 / 2.54);   // 12,5 см
    Cam.HeightEMU := Round(6.0 * 914400 / 2.54);

    Book.SaveAs('reporting-dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

Возвращаемый объект — это обычный объект картинки с одним дополнительным свойством, CameraRange, которое непусто ровно тогда, когда картинка является камерой. Именно так вы обнаруживаете камеры в книге, которую создавали не вы: перечислите картинки и проверьте это свойство

Почему изображение-заглушка не обязано быть настоящим?

Каждой картинке в пакете XLSX нужна часть изображения, и объект камеры не исключение. Но Excel игнорирует это изображение: при загрузке он перерисовывает связанный диапазон и рисует результат. Встроенные байты существуют исключительно как кэш отображения для инструментов, не реализующих поведение камеры

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

Одно следствие, которое стоит учитывать: программа просмотра, отрисовывающая XLSX без реализации объектов камеры, покажет заглушку, а она пустая. Если ваши книги потребляются таким инструментом, объект камеры — неверная конструкция, и отрисованное изображение диапазона — правильная

Преобразование единиц, в котором легко ошибиться

Геометрия рисунков в Open XML измеряется в английских метрических единицах (EMU), где один дюйм — это 914 400 EMU, а один сантиметр — 360 000. Однако заголовки метафайлов записывают свою рамку в единицах по 0,01 миллиметра. Преобразование между ними — это деление на 360

Об этом стоит сказать отдельно, потому что ошибка проходит незаметно. Преобразование через пиксели при 96 DPI вместо этого — умножение на 2540 и деление на 9525 — даёт значение в 96 раз больше нужного, и ничто это не отклоняет: пакет валиден, картинка корректно размещена якорем, и только заявленная рамка метафайла оказывается бессмысленной. Модель EMU и её поведение при округлении описаны в статье геометрия изображений, единицы EMU и масштабирование

Повторное сохранение книги, в которой уже есть камеры

Открытие и сохранение книги сохраняет объекты камеры, включая элемент расширения и его ссылку на диапазон. Это важнее, чем их создание: большинство книг с объектами камеры сделаны в Excel аналитиком, и библиотека, молча превращающая их в обычные картинки при сохранении, разрушает живое поведение, на которое рассчитывал аналитик

// Проверяем, какие картинки являются камерами и куда они указывают
for I := 0 to Sheet.Images.Count - 1 do
  if Sheet.Images[I].CameraRange <> '' then
    Writeln(Format('camera %d -> %s',
      [I, Sheet.Images[I].CameraRange]));

Когда вы генерируете панели показателей программно, эта проверка становится ещё и регрессионным тестом: после цикла сохранения и повторного открытия то же число камер должно указывать на те же диапазоны. Общая обработка картинок и рисунков, включая модель привязки, задающую их позицию, описана в статье диаграммы, изображения и рисунки

Когда камера превосходит альтернативы

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

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

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

Объекты камеры, рисунки и стоящий за ними писатель пакета XLSX поставляются в одной библиотеке для Delphi и C++Builder; полный список возможностей — на странице компонента HotXLS Delphi Spreadsheet Component