Всеки обект, който плава над мрежата на работния лист (диаграма, лого, печат, текстово поле), е графичен обект (drawing object). Графичният обект се дефинира от две неща: какъв е той и къде е привързан (anchored). Привързването е частта, която най-често се бърка. Диаграмата не живее в клетка, тя се разполага в правоъгълник, привързан към диапазон от редове и колони, а данните, които представя, са отделен набор от 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;
// Frame anchored to rows 6..22, columns 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. Той е литерал, запазен в момента на извикването, и не се актуализира автоматично при добавяне на нови редове с данни. Изграждайте го въз основа на броя на редовете, изчислен след записването на данните, а не преди това. Диаграмите от тип Scatter и Bubble променят значението на тези два аргумента: диапазонът от категории предоставя X стойностите, а диапазонът от стойности предоставя Y, като радиусът на балона се задава чрез трети набор от референции в BubbleSizeRange на върнатия TXLSXChartSeries. Разглеждайте извикването като „X, Y, размер“, а не като „категории, стойности“ когато работите извън стълбовидните диаграми
TXLSXChartType поддържа стълбовидни, линейни, кръгови (pie), площни (area), понички (doughnut), точкови (scatter), мехурчести (bubble) и радарни диаграми, което покрива ежедневните нужди от отчети. За диаграма на цяла страница без таблична мрежа Book.AddChartSheet връща лист с истинска стойност на свойството IsChartSheet. Това е аналогът на традиционния лист за диаграми в .xlsx и носи същото изискване: не пишете клетъчно съдържание в него
Изображенията се вмъкват като байтове и се оразмеряват в EMU
Има две претоварвания (overloads) за вмъкване на изображение и смесването им е честа грешка при прегледа на кода. AddImage(ARow, ACol, AData, AFormat) изисква вече кодираните байтове на изображението в AData, суровото съдържание на PNG, JPEG, GIF или BMP. Подайте му път до файл и ще запишете обикновен низ от четиридесет знака, който никой браузър или четец не може да дешифрира, грешка с икона за повредено изображение, която трудно се открива след внедряване. Когато източникът е файл на диска, извикайте AddImageFromFile и оставете библиотеката да прочете байтовете и да разпознае формата
След това идва оразмеряването. DrawingML не измерва в пиксели, а в English Metric Units (EMU), където 914400 EMU съставляват един инч, а при 96 DPI един пиксел се равнява на 9525 EMU. Обектът TXLSXImage излага свойствата WidthEMU и HeightEMU, така че лого с размери 180 на 60 пиксела се нуждае от 1714500 на 571500 EMU. Дефинирайте това преобразуване като именована константа и изчислявайте спрямо нея. Директното въвеждане на числа като 1714500 прави кода труден за четене и води до грешки при промяна на целевия DPI. Редът и колоната за привързване са базирани на 1, съответствайки на останалата част от API за клетки, за разлика от математиката на EMU, базирана на 0
Листове за диаграми и фигури в традиционни XLS файлове
От страна на BIFF8 по-пълното претоварване на AddChartSheet приема типа на диаграмата, заглавията на осите и отворен масив от записи TXLSChartSeriesInfo, където всеки запис съдържа име и диапазон за категории и стойности като низове. Плаващите фигури се добавят на самия работен лист чрез неговата колекция Shapes, а не в листа за диаграми
var
Book: IXLSWorkbook;
Data, Trend: IXLSWorksheet;
Series: array[0..0] of TXLSChartSeriesInfo;
begin
Book := TXLSWorkbook.Create; // interface-counted: do not 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 is a chart substream: never call cell methods on it
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 маскира избран фонов цвят от битмап, което позволява поставяне на неправоъгълни печати (например печат „Одобрено“ или воден знак) върху мрежата във формат, който не поддържа 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 Component