Диаграммы и изображения HotXLS: рисунки Excel в Delphi

Всё, что плавает над сеткой листа (диаграмма, логотип, штамп, выноска), — это объект-рисунок, а объект-рисунок определяется двумя вещами: тем, что он такое, и тем, к чему он привязан. Именно с привязкой чаще всего ошибаются. Диаграмма не живёт в ячейке; она занимает прямоугольник, приколотый к диапазону строк и столбцов, а данные, которые она строит, — это отдельный набор ссылок A1, о котором привязка ничего не знает. Сдвиньте рамку — построение останется на месте. Вставьте строки под ней — рамка поедет вниз вместе с ними. Умение не путать эти две системы координат и составляет бо́льшую часть того, что заставляет код рисования вести себя правильно

HotXLS — это нативная библиотека Object Pascal, которая читает и пишет XLS и XLSX без автоматизации Excel, и она несёт две отдельные модели рисования, потому что два формата файлов хранят рисунки по-разному. Формат BIFF8 .xls держит диаграммы на их собственных выделенных листах, а плавающие фигуры — в потоке OfficeArt, прикреплённом к листу. Формат OOXML .xlsx может внедрить диаграмму прямо в сетку, привязав её к прямоугольнику из ячеек, наряду с такими же плавающими картинками и фигурами. Объектная модель отражает это разделение, а поломки, о которых стоит писать, все происходят от применения правил одного формата к другому

Какой контейнер что может содержать

Выбор контейнера обязан предшествовать любому коду диаграмм, потому что доступные типы объектов у этих двух форматов различаются:

• XLS (BIFF8): диаграммы живут на выделенных листах диаграмм, создаваемых через AddChartSheet у коллекции Sheets. Картинки, текстовые поля, прямоугольники, овалы и линии — это фигуры OfficeArt, управляемые через коллекцию Shapes листа. API для внедрения диаграммы внутрь обычной сетки листа не существует
• XLSX (OOXML): диаграммы можно внедрить прямо в лист через TXLSXWorksheet.AddChart с привязкой к прямоугольнику из ячеек либо разместить на выделенном листе диаграммы через TXLSXWorkbook.AddChartSheet. Изображения добавляются через AddImage или AddImageFromFile, а плавающие подписи — через AddTextBox

Так что требование, сформулированное как «лист-панель с диаграммой рядом с цифрами», на самом деле является требованием формата .xlsx. Приблизиться к нему в .xls можно только вытолкнув диаграмму на отдельный лист, а это меняет и то, как пользователь перемещается по файлу, и то, как должен вести себя ваш код. Лист, который возвращает AddChartSheet со стороны XLS, — это подпоток диаграммы, а не сетка: запись в него через Cells.Item порождает несогласованный поток рисования, который генерируется без ошибок и который Excel затем отбрасывает при открытии. Диаграмма просто исчезает, и ничто в журнале сборки не говорит почему. Относитесь к возвращённому листу как к предназначенному только для диаграммы — и целый класс жалоб на «пропавшую диаграмму» исчезнет

Внедрение диаграммы в лист XLSX

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // Рамка привязана к строкам 6..22, столбцам 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

Аргумент, который кусается, — это строка диапазона, передаваемая в AddSeries. Это литерал, зафиксированный в момент вызова, и он понятия не имеет, что вы можете дописать после этого ещё двадцать строк данных. Стройте его по количеству строк, вычисленному после записи данных, и никогда до неё. Точечные и пузырьковые диаграммы нагружают те же два аргумента другим смыслом: диапазон категорий теперь даёт значения X, диапазон значений даёт Y, а радиус пузырька берётся из третьей ссылки, задаваемой через BubbleSizeRange у возвращённого TXLSXChartSeries. Читайте вызов как «X, Y, размер», а не «категории, значения», как только вы выходите за пределы семейства гистограмм и линейчатых диаграмм

TXLSXChartType охватывает гистограммы, линейчатые, графики, круговые, с областями, кольцевые, точечные, пузырьковые и лепестковые построения, что покрывает повседневный репертуар отчётности. Для полностраничной диаграммы без окружающей сетки Book.AddChartSheet возвращает лист, у которого свойство IsChartSheet истинно. Это аналог устаревшего листа диаграммы в .xlsx, и он несёт то же ожидание: не пишите в него содержимое ячеек

Изображения вставляются байтами, а размеры задаются в EMU

Для вставки картинки есть две перегрузки, и их путаница — самая частая ошибка с изображениями, всплывающая на код-ревью. AddImage(ARow, ACol, AData, AFormat) ждёт в AData уже закодированные байты картинки: сырое содержимое PNG, JPEG, GIF или BMP. Передайте туда путь к файлу — и вы сохранили строку в сорок байт, которую не сможет декодировать ни одна программа просмотра, а это ровно та жалоба на значок битой картинки, которую вы не хотите отлаживать после развёртывания. Когда источник — файл на диске, вызывайте вместо этого AddImageFromFile и позвольте библиотеке прочитать байты и определить формат за вас

Дальше идут размеры. DrawingML измеряет не в пикселях, а в английских метрических единицах, где 914400 EMU составляют дюйм, а при 96 DPI 9525 EMU составляют пиксель. Объект TXLSXImage открывает WidthEMU и HeightEMU, поэтому логотипу, который должен отрисоваться как 180 на 60 пикселей, нужны 1714500 на 571500 EMU. Положите это преобразование в именованную константу и считайте через неё. Магические числа вроде 1714500, разбросанные по коду, нечитаемы и станут тихо неверными в первый же раз, когда кто-нибудь изменит целевое DPI. Строка и столбец привязки, к слову, нумеруются с единицы, как и остальной API ячеек, в отличие от арифметики EMU, которая начинается с нуля

Листы диаграмм и фигуры в устаревших файлах XLS

На стороне BIFF8 более богатая перегрузка AddChartSheet принимает тип диаграммы, названия осей и открытый массив записей TXLSChartSeriesInfo, где каждая запись держит имя, а также диапазоны категорий и значений в виде строк. Плавающие фигуры — отдельное дело: они ложатся на сам лист с данными, через его коллекцию Shapes, а не на лист диаграммы

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // считается по ссылкам через интерфейс: не вызывайте Free
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend — это подпоток диаграммы: никогда не вызывайте на нём методы ячеек

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

Здесь важны две детали времени жизни, и они тянут в противоположные стороны. TXLSWorkbook удерживается через интерфейс IXLSWorkbook и считается по ссылкам, поэтому самостоятельный вызов Free для него вызывает двойное освобождение. TXLSXWorkbook из предыдущих разделов — обычный объект, и его необходимо освобождать в try..finally. Тот же ревьюер, который отмечает отсутствующий Free на стороне XLSX, обязан отметить присутствующий на стороне XLS, а это настоящая ловушка, когда вы работаете с обоими форматами в одном модуле. Сами помощники для фигур единообразны: AddRectangle, AddOval и AddLine, а также DeleteInRange для очистки области от рисунков, — все привязываются парами строк и столбцов, поэтому шаблон, вставляющий строки над ними, сдвигает их вместе с сеткой

Ещё одно свойство отрабатывает своё место на устаревших файлах. TXLSPicture.TransparentColor вырезает выбранный цвет фона из растрового изображения — именно так вы кладёте непрямоугольный штамп (печать «Approved», водяной знак) поверх сетки в формате, чья отрисовка BIFF так и не научилась альфа-каналу PNG. Задайте цвет, под который штамп рисовали, и окружающий прямоугольник исчезнет

Цвета темы не переживают круг через BIFF8

Заливки рисунков OOXML могут ссылаться на слот цвета темы — вот почему перекрасить целый .xlsx подменой темы дёшево. У записей рисования BIFF8 такого слота нет. Когда HotXLS применяет цвет темы к рисунку XLS, он разрешает цвет в буквальное значение RGB и сохраняет именно его; индекс темы, из которой цвет пришёл, исчезает в тот же миг, когда файл записан, и повторное открытие его не восстановит. Особенно это ловит инструменты отчётности под чужим брендом — те, что переоформляют один и тот же сгенерированный документ для многих заказчиков. Держите отображение темы в RGB в собственной конфигурации и применяйте его заново при каждой генерации, а не рассчитывайте вычитать его обратно из сохранённого .xls

Смежное решение всплывает со стороны производительности. Фасаду XLS можно велеть вообще не разбирать слой рисования, когда от большого устаревшего файла вам нужны только данные ячеек, установив _DisableGraphics в true, и это отыгрывает заметное время на массовом чтении. Подвох необратим: у книги, открытой таким образом, в памяти нет потока OfficeArt, поэтому её сохранение стирает рисунки из существования. Приберегите этот флаг для аналитических заданий только на чтение. Более широкая картина производительности — в наших заметках о производительности на больших книгах в HotXLS

Как удержать привязки стабильными, пока меняется сетка

Отчёты редко остаются того размера, в каком были сгенерированы, и вот здесь модель привязки из вступления окупается. Структурные операции фасада XLSX (InsertRows, DeleteRows и их столбцовые аналоги) двигают зависимые слои вместе с ячейками. Объединённые области, гиперссылки, примечания, закреплённые области, диапазоны фильтров, условные форматы, проверки, таблицы, определённые имена и, для нашей темы, привязки изображений и диаграмм — всё едет вместе. Логотип, привязанный к строке 1, остаётся наверху, когда под ним вставляют десять строк. Рамка диаграммы, привязанная под блоком данных, съезжает вниз по мере роста блока. Единственное, что не переписывается, — это любая строка диапазона, которую вы зафиксировали как литерал до вставки, поскольку это просто текст, к которому у библиотеки нет причин возвращаться. Отсюда безопасный порядок заполнения шаблона: сначала пишите и перекраивайте данные, а диаграммы создавайте и изображения размещайте последним проходом, выводя каждую строку диапазона из количества строк, которое у вас есть после вставок, а не до них

Комплект размещения довершают два инструмента поменьше. TXLSTextBox.SetArea на стороне XLS заново привязывает существующее текстовое поле или автофигуру к новому прямоугольнику из ячеек, и это лучше, чем удалять и создавать её заново, когда блок нижнего колонтитула сдвигается. А растровая перегрузка AddPicture принимает живой TBitmap с необязательным флагом прозрачности, так что всё, что умеет нарисовать ваш собственный код VCL (шкала, полоска спарклайнов, тип диаграммы, которого нет в родном списке), можно проштамповать прямо в лист, не создавая сначала временный файл

Диаграммы и изображения почти всегда являются завершающим слоем уже структурированного отчёта, поэтому именно подготовка решает, лягут ли они чисто. Заполнение данных, на которые будет ссылаться диаграмма, разобрано в статье генерация отчётов по шаблонам, а удержание сетки стабильной под вашими привязками — тема статьи объединённые ячейки и управление раскладкой. Полная документация по классам и методам находится на странице продукта HotXLS Delphi Component