HotXLS создает спарклайны Excel (мини-диаграммы, которые Excel рисует внутри отдельной ячейки) из Delphi и C++Builder за один вызов: метод TXLSXWorksheet.AddSparklineGroup строит группу спарклайнов по диапазону данных, а при сохранении книги записывает ее как блок расширения x14 внутри extLst листа, который отрисовывается в Excel 2010 и более поздних версиях, а более старые программы чтения пропускают его без ошибок. Никаких объектов диаграмм, графических элементов или привязок
Сценарий, в котором спарклайны приносят реальную пользу, всегда один и тот же. Книга отчета содержит несколько сотен строк (по одной на продукт, регион или учетную запись), по двенадцать ежемесячных показателей в каждой, и требуется отобразить маркер тренда в конце каждой строки. Если моделировать это с помощью полноценных диаграмм, вам придется создать сотни графических разделов DrawingML, каждый со своим XML-документом, связью и геометрией привязки, не говоря уже о том, как это повлияет на размер файла и время его открытия. Группа спарклайнов — это один XML-блок, который говорит: "нарисуй крошечную линию от B2:M2 в N2, и от B3:M3 в N3", что экономно повторяется для любого количества строк
Почему спарклайны реализованы как расширение x14, а не как диаграмма?
Спарклайны появились в Excel 2010, через три года после заморозки базовой схемы ECMA-376, поэтому они не могли находиться в стандартной разметке листа без риска сломать совместимость со всеми существующими программами чтения. Решением Microsoft стал механизм списков расширений: лист может заканчиваться элементом <extLst>, содержащим блоки <ext>, каждый из которых помечен URI, идентифицирующим функцию ([MS-XLSX] §2.3.6). Спарклайны используют URI {05C60535-1F16-4fd2-B633-F4F36F0B64E0} и пространство имен x14. Читатель, распознающий этот GUID, разбирает блок; тот, который не распознает, обязан его пропустить. В этом и заключается вся логика прямой совместимости, и именно поэтому Excel 2007 открывает книгу со спарклайнами без ошибок — он просто показывает исходные числа без графических элементов
Одну деталь этого механизма стоит упомянуть отдельно, так как она противоречит распространенному мнению: в корне листа не требуется объявлять mc:Ignorable. Ограничения по GUID в ext достаточно самого по себе, и собственный модуль записи Excel объявляет пространство имен x14 локально в элементе <ext>, не затрагивая корень. HotXLS следует тому же шаблону, сохраняя остальную часть XML-кода листа нетронутой. В этом заключается и структурное отличие от обычных диаграмм: диаграмма — это отдельная часть DrawingML со своим конвейером рендеринга, о чем рассказано в сопутствующей статье о диаграммах, изображениях и рисунках, тогда как спарклайн — это метаданные листа, которые отрисовывает сама сетка таблицы
Как добавить спарклайны в файл XLSX программно?
Метод TXLSXWorksheet.AddSparklineGroup(AType, ASourceRange, ALocation) позволяет сделать это за один вызов: он создает группу указанного типа, задает для нее стандартный цвет ряда Excel и добавляет первый спарклайн, связывающий диапазон источника с указанием листа с ячейкой размещения. Возвращаемый объект TXLSXSparklineGroup затем принимает дополнительные спарклайны через свой метод Add (по одной паре F/Sqref на строку), все из которых наследуют тип и форматирование группы. Для панели отчетов это именно то, что нужно: одна группа, один набор визуальных параметров и сотни элементов
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Grp: TXLSXSparklineGroup;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('dashboard.xlsx');
Sheet := Book.Sheets[0];
// One group: every product row shares type and formatting
Grp := Sheet.AddSparklineGroup(xlsxSparklineLine,
'Sheet1!B2:M2', 'N2');
for Row := 3 to 201 do
Grp.Add(Format('Sheet1!B%d:M%d', [Row, Row]),
Format('N%d', [Row]));
Book.SaveAs('dashboard-trends.xlsx');
finally
Book.Free;
end;
end;Перечисление TXLSXSparklineType охватывает три типа спарклайнов, предлагаемых Excel: xlsxSparklineLine для линий тренда, xlsxSparklineColumn для столбчатых мини-диаграмм и xlsxSparklineStacked для лент выигрышей/проигрышей (win/loss), где каждое значение отображается в виде направленного вверх или вниз блока. Каждый отдельный спарклайн задается всего двумя строками: F (диапазон источника с именем листа) и Sqref (ячейка, в которой рисуется элемент), поэтому для каждой строки больше ничего настраивать не нужно. Группы также можно создавать полностью вручную через коллекцию Worksheet.SparklineGroups, если вы хотите начать с пустой группы вместо использования значений по умолчанию
Маркеры, цветовые слоты и масштабирование осей в типизированной модели
Класс TXLSXSparklineGroup предоставляет полный набор параметров из ленты инструментов спарклайнов Excel в виде обычных свойств. Шесть логических переключателей выделяют ключевые точки данных: Markers для каждой точки на линейном спарклайне, а также HighPoint, LowPoint, NegativePoints, FirstPoint и LastPoint. С ними связаны восемь цветовых слотов ARGB (ColorSeries, ColorNegative, ColorAxis, ColorMarkers, ColorFirst, ColorLast, ColorHigh, ColorLow), причем значение 0 означает, что параметр опускается в файле, и Excel применяет собственный цвет по умолчанию. Тот же формат полностью непрозрачного цвета ARGB используется по всей библиотеке, поэтому $FFD00000 здесь означает тот же красный цвет, что и в стилях условного форматирования
Grp := Sheet.AddSparklineGroup(xlsxSparklineStacked,
'Sheet1!B2:M2', 'N2');
Grp.NegativePoints := True; // losing months stand out
Grp.ColorNegative := $FFD00000;
Grp.HighPoint := True;
Grp.ColorHigh := $FF00B050;
Grp.MinAxisType := xlsxSparklineAxisGroup; // one scale for all rows
Grp.MaxAxisType := xlsxSparklineAxisGroup;
Grp.DisplayEmptyCellsAs := xlsxSparklineEmptyGap;Свойства оси заслуживают внимания перед тем, как вы согласитесь на значения по умолчанию. MinAxisType and MaxAxisType принимают индивидуальное, групповое или пользовательское масштабирование: индивидуальное растягивает каждый спарклайн до его собственного минимума и максимума, из-за чего строка со значениями от 99 до 101 выглядит так же эффектно, как и строка с трехкратным ростом. Групповое масштабирование помещает все элементы на общую вертикальную шкалу, благодаря чему высота графика означает одинаковую величину в каждой строке, что важно для сравнительных отчетов. Свойство DisplayEmptyCellsAs определяет влияние пустой ячейки на линию: оставить пропуск, считать нулем или сгладить разрыв, соединив соседние точки
Что HotXLS записывает в extLst листа при сохранении
При сохранении HotXLS сериализует каждую группу как элемент x14:sparklineGroup, содержащий атрибуты типа и переключателей, цветовые слоты в качестве дочерних элементов и список x14:sparklines, элементы которого связывают формулу диапазона xm:f с ячейкой размещения xm:sqref. Вся структура помещается внутрь <extLst><ext uri="{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"> в конце XML листа с объявлением пространства имен x14 прямо в блоке, точно так же, как это записывает Excel. Лист без спарклайнов не генерирует extLst вообще, поэтому файлы, в которых эта функция не использовалась, получаются побайтово идентичными результатам работы прошлых версий HotXLS — это то же самое правило консервативной записи, которое описано в статье об инфраструктуре бесшовного импорта-экспорта
Одна деталь спецификации имеет значение при сравнении вывода с файлами Excel или ручной проверке XML: по умолчанию для lineWeight в схеме установлено значение 0.75 пункта, не 1.25, как утверждают некоторые неофициальные источники — стандарт [MS-XLSX] определяет CT_SparklineGroup@lineWeight со значением по умолчанию 0.75, что соответствует толщине линии в самом Excel. HotXLS соблюдает это соглашение: если установить LineWeight отличным от 0.75, атрибут будет записан в файл, если оставить 0.75 — нет. Ошибка в этом параметре приведет к тому, что линии будут отображаться более толстыми или тонкими по сравнению с файлами, сохраненными из самого Excel
Импорт-экспорт, копирование листов и расширения, не поддерживаемые в HotXLS
Открытие файла является зеркальным отражением сохранения. Когда HotXLS открывает книгу со спарклайнами (созданную в HotXLS или в самом Excel), парсер считывает GUID расширения спарклайнов и восстанавливает каждую группу, элемент, цвета и параметры осей в типизированной модели. Это обеспечивает их полное сохранение в цикле "открытие -> редактирование -> сохранение". При копировании листа в другую книгу группы спарклайнов клонируются вместе с остальным содержимым листа, что упрощает генерацию отчетов на основе шаблонов: держите настроенный лист-шаблон, копируйте его для каждого отчета и перенаправляйте диапазоны данных
Список extLst также может содержать блоки расширений, для которых в HotXLS нет типизированной модели — например, Excel записывает туда блоки x14 для срезов (slicers) и расширенного условного форматирования. Начиная с версии v2.131.0, эти сторонние блоки полностью сохраняются при импорте-экспорте: парсер захватывает их, предоставляет доступ только для чтения через Worksheet.RawWorksheetExts и записывает их обратно при сохранении. Также они переносятся вместе с листом при его копировании в другую книгу. Сохраненный XML восстанавливается по событиям парсера, поэтому он семантически эквивалентен оригиналу, хотя и не побайтово идентичен. Excel корректно принимает его, и функции сохраняют работоспособность. Практический результат наиболее важен для продакшена: открытие клиентской книги и ее повторное сохранение больше не приводят к удалению расширений, поддержку которых вы не запрашивали у HotXLS
Честная граница возможностей: спарклайны отображаются в Excel 2010 и выше, а также в любых других программах чтения, реализующих расширение x14. Excel 2007 показывает данные без графических элементов, что является нормальным уровнем деградации, заложенным в формат. Три типа спарклайнов, восемь цветовых слотов, переключатели точек и три режима осей полностью покрывают возможности, доступные в интерфейсе самого Excel. Поддержка спарклайнов, наряду с остальной частью описанного здесь движка XLSX, поставляется в составе HotXLS Delphi Excel Component для Delphi и C++Builder