3.4.5.2. Шаблоны Excel

Шаблоном Excel является документ MS Excel, размеченный специальным образом. В системе Global существуют 2 способа построения отчетов на основе шаблона Excel:

  • Заполнение шаблона с помощью MS Excel через OLE Automation Object (OLE)
  • Прямое заполнение шаблона, без использования OLE

Из-за различий в методах заполнения существуют различия в их возможностях:

Характеристика Заполнение через OLE Заполнение без использования OLE
Скорость Низкая Высокая [1]
Использование функций MS Excel Да Нет
Использование встроенных функций Нет Да [2]
Возможность выполнения макроса «FinalMacros» после заполнения Да Да
Построение диаграмм, сводных таблиц Да Да [3]
Поддержка колонтитулов Да Частично [4]
Ограничение на длину запроса переменной части. 1024 символа 65 000* символов
[1]При больших объёмах данных (> 100 записей) на порядок выше, чем через OLE.
[2]Аналогично отчётам TXT и RTF.
[3]С ограничениями, читайте в описании шаблона.
[4]Подробнее в разделе Колонтитулы.

3.4.5.2.1. Шаблон Excel (*.xls) – Заполнение через OLE

Для шаблона Excel применяется та же разметка, что и для документа Word, но с небольшими особенностями:

  • Тэг должен полностью находиться в одной ячейке (при этом в одной ячейке может находиться несколько тэгов).
  • Открывающий тэг переменой части должен находиться в ячейке над левой верхней ячейкой переменной части, а закрывающий под правой нижней ячейкой.
  • Если документ состоит из нескольких страниц, эти страницы должны быль расположены одна под другой или на разных листах, но ни как ни рядом!!!!!
  • У MS Excel существует ограничение на длину текста в ячейке = 1024 символа. Поэтому, длинные запросы необходимо помещать в GetSQLText выборки.

3.4.5.2.1.1. Доступные параметры:

Имя параметра Возможные значения Описание
CurrentSession 0,1 По умолчанию, построение отчёта происходит в новой сессии. Это не всегда удобно для печати каких-то сиюминутных данных. Используйте данный параметр для построения отчёта в текущей сессии (без сохранения данных перед печатью).
FileName строка, содержащая имя файла

Указывает имя файла, куда будет сохранён отчет после построения.

Параметр FileName не указан: Уникальное имя файла будет сформировано автоматически.При отображении построенного отчёта, в заголовке приложения будет автосформированное имя файла.Перед сохранением построенного отчёта, имя файла будет запрошено у пользователя.

Параметр FileName указан:Если на диске уже существует файл, с указанным именем, и он не занят другим приложением, файл будет перезаписан.Если на диске уже существует файл, с указанным именем, и он занят другим приложением, к указанному имени файла будет добавляться окончание «_#», где # - целое число, до тех пор, пока не будет получено имя не занятого или не существующего файла.При отображении построенного отчёта, в заголовке приложения будет указанное имя файла (если файл был занят, имя файла с окончанием «_#») Если указано имя файла в несуществующем каталоге, будет произведена попытка создания каталога с заданным именем. В случае неудачи, будет выдана ошибка.

Имя файла может быть указано как полностью (с указанием диска и каталога), так и частично (только имя файла), в последнем случае в качестве каталога будет использоваться каталог для временных файлов, указанный в ОС Windows. Если имя файла указано с расширением не *.xls, то файл будет сохранён в соответствующем расширению формате. Поддерживаются следующие форматы экспорта:
  • Microsoft Windows текст (*.txt)
  • Rich text format (*.rtf)
  • Standard HTML (*.htm; *.html)
  • XML Spreadsheet(*.xml)
  • Open XML Workbook (*.xlsx)
  • Open XML Workbook Macro Enabled(*.xlsm)
  • Portable Document Format file (*.pdf) (поддерживается начиная с MS Office 2007)
  • Формат виртуального принтера Windows (*.xps) (поддерживается начиная с MS Office 2007)
  • OpenDocument Spreadsheet(*.ods)
  • Таблицы SYLK (*.slk)
  • Текстовая таблицы с разделителями (*.csv)
  • DBF4 (*.dbf)
  • Printer Text(*.prn)
  • Web Archive(*.mht) (формат поддерживается IE)
  • (*.dif)
Limit Целое число Указывает, какое максимальное кол-во записей может быть выведено в отчёт. Если кол-во суммарное кол-во записей, выведенных в отчёт, превысит заданное значение, пользователю будет задан вопрос о продолжении печати.
ShowProcess 0,1 По умолчанию, построение отчёта скрыто от пользователя (разработчика), но иногда необходимо видеть процесс построения отчёта (например: для отладки). Для визуализации построения отчёта необходимо указать сей параметр.

3.4.5.2.1.2. Дополнительные возможности

Иногда возникает необходимость после построения отчёта выполнить какой-либо VBA макрос шаблона. Для этих целей необходимо создать макрос с именем FinalMacros. Если в шаблоне будет присутствовать макрос с таким именем, он будет выполнен.

Attention

Макрос должен быть оформлен в виде модуля. Если макрос будет принадлежать книге или Листу, он не будет выполнен.

../../../../_images/FinalMacros.jpg

3.4.5.2.1.3. Построение сводных таблиц (MS Excel 2007 и выше)

Для построения сводных таблиц в MS Excel необходимо:

  1. Вывести данные на один из листов в виде таблицы, например, с помощью такого шаблона:
../../../../_images/template.jpg
  1. Поставить активную ячейку в таблицу с исзходными данными и на вкладке “ВСТАВКА” нажать кнопку “Сводная таблица”.
../../../../_images/pivotTable1.jpg
  1. В диалоге, в качестве диапазона указываем область, куда будут выведены данные.
../../../../_images/pivotTable2.jpg

Для приведённого выше шаблона эта область будет следующей:

Лист1!$A$1:$D$4

Обратите внимание, что в область данных попали открывающий и закрывающий тэги переменной части. Это необходимо, чтобы при заполнении таблицы данных происходило автоматическое расширение области данных сводной таблицы.

  1. Из списка полей сводной таблицы перетаскиваем поля на сводную таблицу, размещая в необходимых ячейках
../../../../_images/pivotTable3.jpg
  1. В результате получаем следующую картину:
../../../../_images/pivotTable4.jpg

Как видно, в сводной таблице присутствуют ячейки с содержимым «пусто» (также могут быть ячейки с открывающим или закрывающим тэгом), от них необходимо избавиться. Для этого, нажимаем на стрелочку (обведено красным кругом), и в появившемся диалоге снимаем галочки не нужных нам элементов.

../../../../_images/pivotTable5.jpg
  1. Наша сводная таблица содержит ячейки, в которых в качестве значения хранятся тэги [#….#], это очень отрицательно сказывается на построении отчёта. При заполнении шаблона будет произведена попытка заполнения этих ячеек, что приведёт к ошибке. Для того чтобы этого избежать, необходимо удалить тэги из ячеек сводной таблицы, после чего сохранить шаблон. Сводная таблица не перестраивается после изменения области данных, поэтому, после заполнения шаблона необходимо перестроить сводную таблицу. Для этого необходимо создать макрос с именем FinalMacros, с, например, следующим текстом:
Sub FinalMacros()
  ActiveWindow.SelectedSheets.Visible = False
  ActiveSheet.PivotTables("СводнаяТаблица2").PivotCache.Refresh
  Range("A1").Select
End Sub

или таким:

Sub FinalMacros()
  Application.Sheets(1).PivotTables("СводнаяТаблица2").PivotCache.Refresh
End Sub

В случае второго макроса, необходимо указать номер листа, на котором находится сводная таблица. Нумерация листов начинается с 1 (единицы).

В вашем случае имя сводной таблицы может отличаться. Узнать имя таблицы можно в параметрах таблицы.

3.4.5.2.2. Шаблоны Excel (*.xls, *.xlsx, *.xlsm) – Заполнение с помощью прямой записи в файл

Прямое заполнение шаблона Excel намного быстрее, чем заполнение через MS Excel OLE Automation Object, поэтому такой метод заполнения предпочтительнее при выводе в отчёт больших объемов данных (более 100 записей).

3.4.5.2.2.1. Виды шаблонов Excel

  xls xlsx xlsm
Поддержка макросов Да Нет Да
Максимальное количество строк 65536 1048576 1048576
Максимальное количество колонок 256 16384 16384
Допущение нескольких тегов в одной ячейке Нет Частично [5] Частично [5]
Поддержка всплывающих подсказок к ячейкам Нет Да Да
Поддержка всплывающих подсказок к ячейкам Нет Да Да
Поддержка колонтитулов Частично [6] Частично [6] Частично [6]
[5](1, 2) Не поддерживается для размножаемых.
[6](1, 2, 3) Подробнее в разделе Колонтитулы.

Разметка шаблона для прямого заполнения аналогична разметке для заполнения через OLE с небольшими ограничениями и дополнениями:

Ограничения

  • Невозможно настроить заранее сводную таблицу или график. (Для построения сводной таблицы или графика на основе выведенных данных необходимо записать макрос «FinalMacros», выполнение которого приведёт к созданию необходимых графиков и таблиц)

Дополнения

  • Поддержка встроенных функций
  • Стили раскарски записей. Для каждого уровня узлов можно задать стиль раскраски. Наименование стиля раскраски для уровней дерева должно соответствовать шаблону ReportTreeLevel_[НомерУровня] , т.е. например, ReportTreeLevel_0, ReportTreeLevel_1.

Доступные параметры:

Имя параметра Возможные значения Возможные значения
PassByDataSet 0,1 При построении отчёта по открытой выборке, по умолчанию, обход записей происходит в том порядке, как записи отображаются на экране, с учётом клиентских фильтров и сортировок. Используйте этот параметр, если необходимо обойти записи в том порядке, как они находятся в таблице, без учёта клиентских сортировок и фильтров. Обход записей по таблице быстрее, чем обход с учётом клиентских сортировок и фильтров.
CurrentSession 0,1 По умолчанию, построение отчёта происходит в новой сессии. Это не всегда удобно для печати каких-то сиюминутных данных. Используйте данный параметр для построения отчёта в текущей сессии (без сохранения данных перед печатью).
FileName строка, содержащая имя файла

Указывает имя файла, куда будет сохранён отчет после построения.

Параметр имеет смысл только при построении отчёта с типом действия rvmSave.

По расширению имени файла определяется формат, в который необходимо сохранить отчёт.Например, если будет задано имя файла C:testsave.txt, отчёт будет сохранён в текстовом формате. C:testsave.xls – в формате Excel.
VerticalGrouping 0,1 Включает вертикальную группировку строк, по уровням записей. Работает когда PassByDataSet = 0
HorizontalGrouping 0,1 Включает горизонтальную группировку столбцов, по свойству TbtkScriptFieldPrintInfo. HorizGroup Работает при PassByDataSet = 0
LevelIndent целочисленное значение Для древовидных списков вывод записей сделан с отступом, для имитации дерева. Каждый потомок узла имеет отступ относительно родительского узла от начала ячейки, и расчитывается по формуле RecordLevel * LevelIntend. Можно регулировать величену отступа с помощью свойства отчета “LevelIndent”. По умолчанию значение отступа 3. Максимальный возможный отступ в 15 единиц.

3.4.5.2.2.2. Встроенные функции

Встроенные функции позволяют производить различные действия над значениями атрибутов перед их выводом в шаблон.

Метод Описание
NVL (<имя атрибута>,<строка>): string Возвращает второй аргумент, если значение атрибута = Null
FNUM (<имя атрибута>, <число>): string Округляет числовое значение атрибута до указанного разряда
LPAD (<имя атрибута>, <число>): string Добавляет пробелы к значению атрибута слева, для получения в результате строки заданной длинны.
RPAD (<имя атрибута>, <число>): string Добавляет пробелы к значению атрибута справа, для получения в результате строки заданной длинны.
NumberToStrRUS (<имя атрибута>): string Возвращает число прописью на русском
ParamValueByName (< имя параметра>): string Возвращает значение параметра, переданного в метод CreateReportEx();
RowNum() :string Возвращает номер текущей записи в выборке переменной части
RowCount() :string Возвращает количество записей в выборке переменной части
Date() :string Возвращает текущую дату
Time() :string Возвращает текущее время
DateTime() :string Возвращает текущую дату со временем
SUM (<имя атрибута>, <имя параметра>)

Метод считает сумму значений атрибута выборки переменной части, и полученное значение записывает в параметр. Для хранения значения используются внутренние переменные отчёта, объявлять параметр заранее не нужно, он будет создан автоматически. Обращение к параметру аналогично обращению к атрибуту выборки. Параметры являются глобальными для всего отчёта, поэтому однажды записанное значение будет доступно в любой части отчёта.

Attention

Метод не возвращает результата, и может быть использован только в закрывающем тэге переменной части.

Пример использования

../../../../_images/ExcelFuncSumExample.png

Set (<имя атрибута>, <имя параметра>)

Set (<имя метода>(), <имя параметра>)

Устанавливает внутреннему параметру отчёта <имя параметра> значение параметра или результат вложенного метода. Данный метод, как и метод SUM можно использовать внутри закрывающего тэга переменной части.

Пример: [#/ChildSelect Sum(fSumma, ResultSumma);Set(RowCount(),DetRowCount)#]
Multiply (<имя атрибута>, <имя атрибута>) Произведение двух атрибутов
Divide (<имя атрибута>, <имя атрибута>) Деление первого атрибута на второй
Minus (<имя атрибута>, <имя атрибута>) Разность
Plus (<имя атрибута>, <имя атрибута>) Сумма двух атрибутов
Percent (<имя атрибута>, <число>)

Процент от значения атрибута

Пример: [# Percent(Multiply(nPrice, nQuantity), 20)#]
GetCaption (FieldName: string) Выводит наименование обычной колонки из структуры Selection.PrintInfos

При использовании методов, у которых нет входных параметров, круглые скобки после имени метода писать обязательно! Если круглых скобок не будет, эта строка будет считаться именем атрибута.

Об использовании встроенных функций читайте в разделе “Шаблон TXT” Встроенные функции

3.4.5.2.2.3. Пример шаблона

../../../../_images/CreateReports_NativeXLS_Example.PNG

3.4.5.2.2.4. Вывод размножаемых атрибутов

[#GetCaption(Имя колонки)#] - выводит наименование обычной колонки из структуры Selection.PrintInfos [#GetCaption(БазовоеИмяРазмножаемогоАтрибута[*]”)#] - выводит наименования всех динамических колонок, имена которых подпадают под шаблон “DynColName[*]” с учётом свойства TbtkScriptFieldPrintInfo .Order. Размножаемые колонки вставляются в тело отчета сдвигая все остальные ячейки вправо.

Мета-данные по размножаемым атрибутам можно заполнить до вызова CreateReport.Для этих целей реализована коллекция объевтов класс TbtkScriptFieldPrintInfo, объект класса хранит информацию о полях выборки которую можно использовать при печати отчета.Доступ к объектам класса можно получить через свойство Selection.PrintInfos[FieldName: string]: TbtkScriptFieldPrintInfo

TbtkScriptFieldPrintInfo = class
  Caption: String; // Наименование которое можно вывести в отчет используя команду GetCaption (см. ниже).
  Order: Integer; // Порядок, учитывается при выводе в отчёт размножаемых атрибутов. Порядок влияет только на вывод в рамках размножаемых атрибутов.
  HorizGroup: String; // Горизонтальная группа объединения столбцов.
  Style: String; // Системное имя стиля из списка "стилей раскраски", используемого для колонки. Данный стиль перекрывает стиль уровня записи.
end;

Коллекция создается перед вызовом AfterOpen операции выборки. Разработчик должен сам заполнить структуру до вызова CreateReport.

Пример заполнения коллеции:

Пример работы с Printinfos в операции AfterOpen:
<PASCAL>
  Selection.ExecOpScript('#AfterOpen');
  Selection.PrintInfos['sCaption'].Caption := 'КАПШЕН';
  Selection.PrintInfos['nPrice[0]'].Caption := 'ПРАЙС 0';
  Selection.PrintInfos['nPrice[1]'].Caption := 'ПРАЙС 1';
  Selection.PrintInfos['nPrice[1]'].Style := 'Test_ForReadOnlyFld';
  Selection.PrintInfos['nPrice[0]'].HorizGroup := 'Group1';
  Selection.PrintInfos['nPrice[1]'].HorizGroup := 'Group1';

  Selection.PrintInfos['nLevel'].HorizGroup := 'Group2';
  Selection.PrintInfos['nCount'].HorizGroup := 'Group2';
  Selection.PrintInfos['sCaption'].HorizGroup := 'Group2';

  Selection.PrintInfos['nPrice[1]'].Order := 0;
  Selection.PrintInfos['nPrice'].Order := 2;
</PASCAL>

Пример шаблона с размножаемыми атрибутами:

../../../../_images/ExcelReport_DynFieldsExample.PNG
../../../../_images/PrintInfoReport.png

3.4.5.2.3. Колонтитулы

New in version 5.6.0.

Содержимое каждого колонтитула обрабатывается отдельно от основного тела документа и других колонтитулов.

Warning

В шаблонах заполняемых, без использования OLE, не поддерживаются отдельные колонтитулы для первой и четных страниц. При использовании таких колонтитулов в этих шаблонах часть колонтитулов останется необработанной.

Статьи про использование колонтитулов в Excel на сайте MS Office

3.4.5.2.3.1. Особенности использования символа &

Для форматирования текста колонтитулов Excel использует коды, начинающиеся с символа &. Поэтому, при необходимости использовать символ & в тексте, следует экранировать его вторым символом &.

Примеры

Запрос для ячеек (без экранирования &)
[#&q = select 1 as n from dual#]
[#n#]
[#/q#]
Запрос для колонтитула (с экранированием &)
[#&&q = select 1 as n from dual#]
[#n#]
[#/q#]

3.4.5.2.3.2. Переносы строк

При разборе шаблона отчета и формировании результирующего текста колонтитулов, строки, на которых расположены только теги запросов, не удаляются. Следует учитывать это, чтобы избежать появления нежелательных переносов строк.

Примеры

Запрос с нежелательными переносами
[#&&q = select 1 as n from dual#]
[#n#]
[#/q#]

Перед и после значения поля n в текст колонтитула будет добавлен перенос строки.

Запрос без нежелательных переносов
[#&&q = select 1 as n from dual#][#n#][#/q#]

В тексте колонтитула будет только значение поля n.

3.4.5.2.3.3. Ограничение на длинну

При работе с колонтитулами нужно учитывать некоторые особенности их реализации в Excel. При изучении документации можно сделать вывод, что в Excel существует 6 типов колонтитулов:

  1. Верхний левый
  2. Верхний центральный
  3. Верхний правый
  4. Нижний левый
  5. Нижний центральный
  6. Нижний правый

На самом же деле колонтитулов всего два, - верхний и нижний. Но каждый из них при отображении на листе делится на три части. При этом колонтитулы хранятся в документе в виде строки длинной 256 символов каждый. То есть суммарно, длина всех трех частей каждого колонтитула не должна превышать 256 символов. Более того, у пользователя нету возможности использовать все 256 символов в колонтитуле, так как их часть Excel использует для разделения колонтитула на части (с помощью специальных кодов) и форматирования текста в нем.

Note

При добавлении элементов колонтитулов Excel использует меньше символов чем может показаться на первый взгляд, так как хранятся элементы колонтитулов в виде кодов. Например, при добавлении номера страницы и текущей даты Excel будет отображать в колонтитуле текст &[Страница]&[Дата], но храниться эта строка будет в виде &P&D.

Warning

При редактировании колонтитулов в Excel, если превышен лимит символов будет показано соответствующее сообщение. Если же лимит символов превышен при формировании отчёта сообщение показано не будет. Но при попытке открыть или распечатать отчёт будет выдано сообщение о том что Excel документ испорчен. Открытие продолжится, но для восстановления документа Excel попытается удалить все форматирование в проблемных колонтитулах, если же и после этого лимит останется превышен Excel обрежет текст колонтитулов до лимита в 256 символов.

3.4.5.2.4. Ошибки и особенности

Проблема Вероятная причина Решение
Сообщение “система Office обнаружила проблему с этим файлом. Чтобы обеспечить защиту компьютера, этот файл не будет открыт”. В файле XLS-шаблона используются всплывающие подсказки к ячейкам. Данная возможность не реализована в используемом компоненте TXLSFile. Отказаться от использования всплывающих подсказок, или использовать XLSX и XLSM отчеты, реализованные на основе компонента OExport.
При формировании отчета теряется объединение ячеек. Используется XLS-шаблон с прямым заполнением (без OLE). Для прямого заполнения XLS-шаблонов используется компонент TXLSFile, у которого имеются проблемы с объединением ячеек. Перейти на заполнение XLS-шаблона через OLE, или использовать XLSX и XLSM отчеты, реализованные на основе компонента OExport.