Діаграми, зображення та графічні об'єкти HotXLS у Delphi

Усе, що плаває над сіткою аркуша (діаграма, логотип, штамп, виносне поле), — це об'єкт малюнка, а об'єкт малюнка визначається двома речами: чим він є і де він закріплений. Саме з прив'язкою (anchor) найчастіше помиляються. Діаграма не живе в комірці; вона сидить у прямокутнику, приколотому до діапазону рядків і стовпців, а дані, які вона будує, — це окремий набір посилань 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 дорівнює true. Це аналог застарілого аркуша діаграм для .xlsx, і він несе те саме очікування: не записуйте в нього вміст комірок

Зображення вставляються як байти, а розмір задається в EMU

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

Далі йде розмір. DrawingML вимірює не в пікселях; він вимірює в English Metric Units, де 914400 EMU становлять дюйм, а за 96 DPI 9525 EMU становлять піксель. Об'єкт TXLSXImage відкриває WidthEMU і HeightEMU, тож логотип, що має відрендеритися розміром 180 на 60 пікселів, потребує 1714500 на 571500 EMU. Помістіть це перетворення в іменовану константу й рахуйте від неї. Магічні числа на кшталт 1714500, розкидані по коду, нечитабельні й тихо стають неправильними, щойно хтось змінить цільове DPI. Рядок і стовпець прив'язки, до речі, відлічуються з 1, як і решта API комірок, а не з 0, як математика 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 (індикатор, смужку sparkline, тип діаграми, якого немає в нативному списку), можна відбити прямо на аркуш, не записуючи спершу тимчасовий файл

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