Afalina XL-Report. NoRange наборы данных

В прошлой статье был описан компонент XL Report, кратко, на простом примере, приведен способ создания шаблонов в MS Excel. Теперь нам известно, что основная работа при использовании XL Report заключается в создании шаблонов отчетов, что доступ к шаблону можно получить прямо из контекстного меню в среде разработки Delphi во время редактирования проекта, что переносимые в отчет данные описываются специальными формулами полей. Для того, чтобы перенести одну запись из набора данных в отчет, достаточно добавить элемент в коллекцию DataSources экземпляра TxlReport и связать его с набором данных посредством свойства DataSet. Для того, чтобы перенести все данные из набора данных, необходимо добавить элемент в свойство-коллекцию DataSources экземпляра TxlReport, связать его с набором данных и поименованной областью в отчете (свойство Range), которая (имеется ввиду область) должна иметь определенный формат. XL Report сохраняет все изменения ячеек, форматы книги и листов в ней. С использованием компонента TxlReport возможно как вызывать отчеты, так и редактировать их — и во время выполнения программы, и во время проектирования.
В XL Report все наборы данных делятся по заполнению свойства Range элемента коллекции DataSources. Наборы данных, в элементах коллекции которых свойство Range оставлено пустым, относят к NoRange наборам данных. Такие наборы всегда отображают только одну текущую запись. В предыдущем наборе таким был Customers. Формулы полей этих наборов данных могут быть использованы в любой ячейке любого листа книги Excel, а Excel сам найдет их значения. Чтобы обеспечить работу такого механизма, XL Report создает в каждом шаблоне невидимый лист, куда переносит значения всех полей набора данных из текущей записи. В данном случае под всеми полями понимаются поля всех поддерживаемых типов. В настоящее время поддерживаются следующие типы: строковые (ftString, ftFixedChar, ftWideString, ftMemo, ftFmtMemo), целые (ftSmallint, ftInteger, ftWord, ftLargeInt, ftAutoInc), вещественные (ftFloat, ftCurrency), дата и время (ftDate, ftDateTime, ftTime), логические (ftBoolean). Для всех остальных типов передается пустое значение. После этого ячейки со значениями именуются. Имена, как вы уже видели, имеют вид Alias_FieldName. Это и обеспечивает увиденную вами работу формул полей для NoRange наборов данных.

Как уже было сказано в предыдущей части статьи, XL Report очень быстро передает данные в Excel. Компонент может использовать три варианта передачи данных: через вариантный массив прямым присваиванием значений ячейкам, через буфер обмена в формате CSV и с использованием DDE-вызовов (Fast Table Format). Любой из этих вариантов может быть установлен в свойстве DataExportMode экземпляра TxlReport. При использовании того или иного формата необходимо учитывать накладываемые им ограничения. Краткое описание каждого из вариантов с комментариями об их возможностях и ограничениях приведены ниже.

хdmVariant. Данные передаются порциями через вариантный массив с прямым присваиванием ячейкам значений. Используется ядром xlReport в некоторых ситуациях. Это самый медленный тип передачи данных. Из-за ошибки в Excel (во всех версиях без исключения) слишком длинные строки обрезаются примерно до 1800 символов. Иногда возникают проблемы при распознавании типов данных: строка “00033” может быть распознана как число.

хdmCSV. Данные передаются через буфер порциями в формате CSV. Обеспечивает достаточно высокую скорость. Но при распознавании данных также возможны ошибки: строку “3/7” он может посчитать как результат деления или заменить датой. Использование этого варианта передачи данных оправдывает себя только в случае передачи больших строковых значений. Ограничение на длину строки — 8 Кб.

хdmDDE. Вариант, использующийся по умолчанию. Данные передаются порциями с использованием DDE в формате xlTable. Обеспечивает самую высокую скорость передачи данных, так как использует native типы данных Excel. Проблем с распознаванием типов данных замечено не было. Существуют некоторые ограничения: длина строки ограничена 255 символами и все числа передаются как вещественные.

Форматирование ячеек и формулы Excel.
Можно форматировать ячейки с формулами полей из NoRange наборов данных любым известным способом, даже применяя условное форматирование. На условном форматировании можно остановиться поподробнее и показать это на примере, так как условное форматирование — мощный способ визуализации данных. Для примера в проект Qdemo.dpr добавим новую форму с названием FrmNoRange1 и назовем модуль fNoRange1. На форму поместим один компонент TTable и свяжем его с таблицей Customers.db. Чтобы отобразить текущую запись из набора данных, на форму был добавлен компонент TDataSource и несколько меток, полей редактирования, навигатор. Все визуальные компоненты были связаны с полями CustNo, Company, State и Country. Также на форму был помещен компонент TxlReport, а в его свойство-коллекцию DataSources добавлен один пункт, связанный с таблицей Customers.

Как и в предыдущем примере, были добавлены кнопки Report и Template с вызовами соответствующих обработчиков для создания отчета и вызова его шаблона. В свойстве XLSTemplate мы указали имя предполагаемого шаблона — tNoRange1.xls. Этот шаблон получен из предыдущего, мы его просто сохранили в папку нового проекта, предварительно удалив из него все, что касается таблицы счетов, оставив только ячейки с формулами полей из Customers. В отчете нам хотелось бы выделить каким-то образом ячейку с полем Country при значении этого поля, равном “US”. Для этого к этим ячейкам мы применили условное форматирование с формулой “=$I$9=”US”” и установили красный цвет шрифта. После выхода из шаблона попробуйте запустить проект на выполнение и создать отчет по нескольким записям так, чтобы в поле Country было включено значение “US”. Хочется верить, что вы увидели разницу (рис. 4). В формуле условного форматирования указание ячейки I9 можно сменить на формулу поля CountryCustomers_Country. Если сделать это, то результат не изменится, то есть такой вариант также вполне пригоден. Более того: с видом на последующее возможное изменение шаблона такой вариант предпочтительнее, так как ячейка I9 может сменить свое значение в процессе редактирования шаблона. Но это еще не все: данные отчета можно применять и в формулах Excel.

Напомним, что XL Report добавляет в шаблон невидимый лист, на котором содержатся все поля всех поддерживаемых XL Report типов данных из текущей записи NoRange наборов данных. Имена таких ячеек имеют вид Alias_FiledName. В построенном отчете можно убедиться в этом, вписав формулу поля для одного из полей в любую ячейку любого листа книги.

Имена этих ячеек вы можете использовать в формулах как имена любых других ячеек Excel. Можно обращаться к значениям полей NoRange наборов данных и в исходных текстах проектов VBA. Попробуем использовать формулы полей и формулы Excel. Для этого введем абстрактное понятие следующей продажи NextSale, которое будет равно дате последней осуществленной продажи, хранящейся в поле LastInvoiceDate таблицы Customers.DB, плюс какое-то прогнозируемое количество дней. В общем виде формула выглядела бы так: “=tblCustomers_LastInvoiceDate + tblCustomers_CustNo /25”. Именно эту формулу и используем в отчете. Для этого возьмем предыдущий пример, укажем экземпляру TxlReport новое имя шаблона — “tNoRange2.xls”, — а сам шаблон сохраним в новую книгу с таким же именем. В любую ячейку шаблона напишем формулу “=tblCustomers_LastInvoiceDate +tblCustomers_CustNo /25”. Чтобы как-то контролировать правильность вычислений, в соседнюю ячейку добавим формулу поля для LastInvoiceDate. После сохранения шаблона и выхода из Excel построим отчет прямо из среды Delphi, вызвав для этого пункт “Report…” контекстного меню компонента xlReport. В предыдущем примере мы показали вариант условного форматирования с формулой, содержащей данные из приложения. Некоторые пользователи XL Report используют условное форматирование для создания скрытых значений в отчете, создавая формат для определенных условий, в котором цвет фона ячейки совпадает с цветом шрифта ее содержимого.

А что же с Memo-полями?
Мы уже оговорились, что существуют некоторые ограничения на передачу строковых значений при разных вариантах передачи данных в шаблон. По сравнению с XL Report 2.xxx в XL Report работа с длинными строковыми полями упростилась. Теперь с такими полями в NoRange наборах данных можно работать как с обычными полями. На качество передачи и разбора таких полей влияет только вариант экспорта данных в Excel. Добавим в проект новую форму, на которую поместим один экземпляр TTable, связанный с таблицей Custoly.DB из DBDEMOS. Назовем его Custoly. Чтобы отобразить текущую запись из этого набора, на форму добавим экземпляр TDataSource, несколько меток, полей редактирования и навигатор. Свяжем визуальные компоненты с полями CustNo, First Name, Last Name и Phone. Добавим на форму один TDBMemo и свяжем его с полем Remarks. Поместим на форму один компонент TxlReport, добавим в его коллекцию DataSources один элемент и свяжем его с таблицей Custoly. Добавим кнопки Report и Template с обработчиками, вызывающими, соответственно, методы Report и Edit последнего компонента. В свойство XLSTemplate напишем имя предполагаемого шаблона — “tNoRange3.xls”. Создадим шаблон для отчета, используя в формулах полей только выбранные нами поля, и сохраним его в каталог проекта под именем “tNoRange3.xls”. Для вывода поля Remarks объединим несколько ячеек и напишем туда формулу для этого поля. Закроем Excel и попробуем создать отчет.

В случае, если длина строки в поле Remarks превысит 255 символов, строка обрезается. Это происходит потому, что для передачи данных в Excel по умолчанию используется формат DDE Fast Table Format. Если в свойстве DataExportMode экземпляра TxlReport указать вариант передачи данных xdmCSV, то строка будет выведена полностью.

А как же ограничения?
В случае, когда из NoRange-набора в отчете используются два-три поля, у вас может возникнуть вопрос о необходимости переноса значений всех полей. По умолчанию все поля участвуют в этом процессе. Было бы совсем неоптимально сканировать все ячейки шаблона для того, чтобы узнать, какие поля NoRange-наборов использовать в отчете. Но существует способ явно указать, какие поля из набора данных участвуют в отчете. Назовите любую неиспользуемую ячейку шаблона именем набора данных (Custoly) и перечислите в ней через точку с запятой имена всех необходимых полей. XL Report будет использовать только их, а в отчете эту ячейку очистит. Чтобы убедиться в этом, в шаблоне предыдущего проекта поименуйте выбранную ячейку как Custoly и впишите в нее строку “Remarks; First_Name; Last_Name”.После сохранения книги и закрытия Excel постройте отчет. Все не перечисленные в этой ячейке поля при переносе данных будут проигнорированы.

А можно ли создать набор данных динамически?
В общем виде мы показали динамическое создание отчета XL Report во втором примере этой статьи. Тем не менее, повторим его здесь. Вот код, необходимый для создания отчета с одним NoRange набором данных:

procedure TfrmQuickStart.btnReport2Click(Sender: TObject);
var
xlReport: TxlReport;
begin
xlReport := TxlReport.CreateEx(Self, 'tQuickStart.xls');
try
xlReport.AddDataSet(tblCustomers);
xlReport.Report(false);
finally
xlReport.Free;
end;
end;

Как было написано выше, строка с вызовом AddDataSet добавит NoRange набор данных в коллекцию DataSources компонента TxlReport. Такой код, по мнению некоторых, несколько неоптимален. В разработках чаще используется один глобальный экземпляр TxlReport. Вот как мы поступаем в таком случае. В каком-либо модуле (чаще это модуль данных) мы создаем секцию initialization, в ней создаем экземпляр TxlReport. В этом же модуле создается секция finalization, где уничтожается этот объект. Каждый раз при вызове какого-либо отчета мы очищаем предварительно свойство-коллекцию DataSources и только потом добавляем в него все требуемые наборы данных.

Стоит заметить, что разработчики XL Report старались обеспечить максимально удобные и простые механизмы работы с продуктом именно в исходном коде. Большое количество классов, их свойств и методов не способствует быстрому восприятию и безошибочному применению. Поэтому в базовых классах совсем немного свойств и методов, а вся работа по проектированию отчетов максимально перенесена в шаблоны — книги Excel.

Как вы уже поняли, если свойство Range элемента коллекции DataSources содержит наименование области в книге-шаблоне, то такой набор данных относится к Range-наборам, а все его записи выводятся в область, указанную в этом свойстве. Область (объект Range) — это основной объект в MS Excel, используемый во многих методах Excel Type Library и, соответственно, VBA.

Алгоритм XL Report также ориентирован на работу с этим объектом. Именно на его плечах лежит основная изобразительная и вычислительная нагрузка в отчетах XL Report. Область в XL Report: имеет строгий формат; может быть только прямоугольной формы; должна быть непрерывной. Ячейки области: могут содержать формулы полей, обычные формулы Excel; могут быть пустыми. Главное требование формата областей XL Report — любая область должна включать минимум два столбца и две строки. Крайний левый столбец и нижняя строка области рассматриваются как служебные и обрабатываются особым образом. После построения отчета все значения в ячейках служебного столбца очищаются, а служебная строка в зависимости от использованных опций уничтожается. Только на этих соглашениях формата области держится быстродействие XL Report под тяжелым грузом медлительности OLE Automation.

Разработчики XL Report четко разделяют два типа областей. К первому типу, называемому область-список, относятся области, в которых все данные расположены в одной строке, включая формулы полей и обычные формулы Excel. Над такой областью обязательно должен быть заголовок (заметим, однако, что заголовок не включается в поименованную область в любом случае). Проще говоря, такая область должна полностью соответствовать определению реляционной таблицы. Знающие Excel разработчики поймут также, что в терминах MS Excel это список данных или просто список. В завершение статей будут приведены рекомендации Microsoft по созданию списков данных.

Все области данных, не отвечающие приведенным выше требованиям, относятся ко второму типу областей, с которыми работает XL Report. Это произвольные области. В них допускается объединение ячеек, заголовок таблицы может также содержать объединенные ячейки или отсутствовать совсем. Область, использованная в самом первом примере документа, как раз и относится к этому типу. К ячейкам всех типов областей вы можете применять любые виды форматирования, в том числе и условное форматирование.

А если необходимо форматировать не ячейку, а целую область?
Для этого вида областей разрешены все возможности форматирования ячеек, доступные в Excel, кроме объединения ячеек. Мы взяли форму самого первого примера этого документа и преобразовали область OrdersRange в шаблоне к виду, удовлетворяющему требования формата областей-списков. На рисунке (рис. 5) показано, что область содержит теперь только одну строку с формулами полей и служебную строку и столбец.

Мы применили различное форматирование ячеек в области, в том числе задали формат даты для ячеек с формулами полей SaleDate и ShipDate. Формат числа с разделителями мы указали для полей Items Total и Amount Paid. Из предыдущего примера осталось условное форматирование для PaymentMethod. На рисунке слева вы видите полученный по этому шаблону готовый отчет. Обратите внимание на выделение области OrdersRange в готовом отчете (рис. 6). Теперь эта область не содержит нижнюю служебную строку, но содержит перенесенные в отчет данные. В готовом отчете вы можете обращаться к этим данным по имени области и, используя VBA, производить над ними какие-либо действия. При работе с областями этого типа XL Report придерживается четкой схемы, описанной ниже.

В область вставляется необходимое для переносимых записей количество строк. Обратите внимание, что при этом происходит вставка ячеек в область, а не строк в лист. Проверить это вы можете, поместив что-либо в ячейки шаблона, расположенные правее области, и снова построив отчет. Во вставленные строки переносятся все видимые записи набора. “Видимые” означает, что учитываются активные фильтры или ограничения SetRange у наборов данных. На вставленные строки накладываются форматы шаблонных ячеек. Затем шаблонные ячейки удаляются. В случае, если в служебных ячейках (нижняя служебная строка области) не описаны какие-либо дополнительные действия (опции), удаляется служебная строка. Если в служебной строке указаны дополнительные опции, то они обрабатываются, а затем в зависимости от набора указанных опций эта строка либо удаляется, либо в ней очищаются ячейки с опциями.

Comments are closed.