Excel как генератор отчетов

VBA Excel. Генерация документов и отчетов

Генерация документов и отчетов из реестра или базы данных Excel с помощью кода VBA для просмотра, анализа и вывода на печать. Создание, заполнение, удаление.

Создание документа или отчета из шаблона

Допустим, у нас есть реестр документов или база данных Excel с какой-либо информацией. Чтобы создать документ или отчет и заполнить его данными, необходим его шаблон (печатная форма).

Шаблон создаем вручную на отдельном рабочем листе или с помощью кода VBA при заполнении документа. Способы создания шаблона определяют место его хранения:

• В коде VBA. Такой способ возможен для несложных печатных форм.
• В рабочей книге Excel с реестром или базой данных на скрытом листе.
• В отдельном файле, который не обязательно сохранять как шаблон Excel (*.xlt*).

Я предпочитаю создавать шаблон вручную и хранить его на скрытом листе в книге с реестром или базой данных. Такой способ подходит для подавляющего большинства случаев.

Из редких исключений можно назвать печать ценников на основании товарной накладной, сохраненной в Excel. Шаблон ценника и программный код VBA приходится хранить в отдельном файле, по команде из которого открывается и обрабатывается товарная накладная.

Назовем лист с шаблоном документа или отчета – «Shablon». Для создания новой печатной формы скрытый лист с шаблоном необходимо скопировать:

Данный код вставит новый лист с шаблоном в ту же книгу после всех имеющихся листов. Чтобы шаблон скопировать в новую книгу, заменяем строку .Copy After:=Worksheets(Worksheets.Count) на .Copy . Печатная форма откроется в новом файле.

Сразу после создания, обращаться к новому листу можно как к активному (ActiveSheet), даже в том случае, если он скопирован в новую книгу. Чтобы воспользоваться этой возможностью, порядок действий в программе должен быть следующим:

1. Копирование информации в переменную массива из реестра или базы данных.
2. Создание нового документа или отчета из шаблона.
3. Заполнение документа или отчета, обращаясь к нему как к ActiveSheet.

Копирование информации и заполнение формы

Копирование информации

Информация о документе обычно хранится в реестре или базе данных Excel в одной строке. Информация для отчета может располагаться как в одной, так и в нескольких или во многих строках.

Если информация нужна из одной строки, перед запуском кода VBA следует выделить любую ячейку в строке с данными. Тогда в ходе выполнения программы можно будет определить номер строки по активной ячейке и извлечь из нее информацию.

Допустим, что лист называется «Reestr», а необходимая информация расположена в десяти первых ячейках каждой строки:

Источник

Создаем свой генератор отчетов Microsoft Excel для Delphi

Зачем нужна система формирования отчетов

В свое время, когда для системы учета потребовалось сделать систему вывода информации на печать перебрал много разных систем формирования отчетов — некоторые, как FastReport, имели в составе редакторы, и замечательно формировали печатные формы, но чаще всего пользователям нравится, когда можно быстро и красиво получить информацию в ихнем любимом Excel, не прибегая к конверторам и т.п.

В интернете уже давно существует море информации, по взаимодействию Delphi и Microsoft Excel, и я не буду писать еще одно руководство. Вместо этого я предлагаю посмотреть, как можно довольно просто разработать свою систему вывода отчетов в Excel, причем достаточную для решения пусть и не всех, но большинства задач.

Когда каждый отчет полностью формировался кодом на Delphi недостаток такого метода был в том, что за делфийским кодом не было видно логики построения отчета, а самое главное — для изменения любой мелочи в оформлении приходилось вносить изменения в код и перекомпилировать проект. Тогда возникла идея написать свой маленький генератор отчетов, причем шаблон отчета — это обычный экселевский файл, в котором любой пользователь сможет подправить то что ему нужно, и определить заранее формат вывода на печать.

Устройство собственного генератора отчетов для Excel

Основу шаблона составляют “бэнды”. Бэнд это одна или более строк, расположенных подряд и имеющих одну и ту-же метку — название бэнда. Оно проставляется в первой колонке каждой строки. Вот так примерно будет выглядеть готовый шаблон отчета:

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

Сам класс отчета будет очень простым:

Начинаться построение любого отчета будет вызовом процедуры OpenTemplate, которая будет запускать эксель, открывать в нем нужный шаблон, а также инициализировать нужные далее переменные:

Здесь упоминается объект TA7xProgress, который представляет собой просто форму, с парой меток, для вывода пользователю прогресса построения отчета, чтобы он не подумал что программа висит.

После того как шаблон открыт, код построения отчета будет вызвать метод PasteBand всякий раз, как нужно будет добавить на изначально чистый лист очередной кусочек отчета из шаблона.

Технология добавления бэндов предполагает что мы вставляем бэнды сразу перед шаблоном, оставляя таким образом шаблон всегда в конце документа. Для этого мы находим нужный нам бэнд по имени, начиная искать после последнего вставленного нами бэнда и далее, а кроме того определяем и длину найденного бэнда. Тоесть следующий кусок кода в переменных FirstBandLine и LastBandLine сохранит начало и конец бэнда:

Далее этот найденный шаблон копипастим сразу за последним вставленным нами бэндом, если такой уже был:

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

Следующий метод, который будет использоваться для вывода значений на уже скопированный кусок шаблона – SetValue. Код этого метода предельно прост, так как выполняет поиск-замену значения в заданном диапазоне средствами самого экселя:

Метод SetValue должен вызываться столько раз, сколько у нас находится значений на нужном нам бэнде. Ну и наконец после того как все бэнды будут выведены, вызывается последнй метод – Show, в котором наконец удаляется сам шаблон, который вытеснялся в конец документа, и эксель перевдится из невидимого состояния в видимое.

Здесь стоит упомянуть, что если скажем прервать отладку отчета до вызова Excel.Visible := true; то эксель так и останется висеть в памяти невидимкой, а для следующего отчета будет создана новый экземпляр Excel.

Как выглядит применение генератора отчетов в Delphi-коде

Теперь самое интересное – пример использования этого генератора отчетов:

Привожу полностью процедуру, которая используя показанный выше шаблон выводит в эксель заданного формата накладную:

Как видно, логика формирования печатной формы полностью прозрачна и не отягощена техническим кодом вывода значений в нужные ячейки Microsoft Excel – обо всем этом заботится наш компонент отчета, а кроме того в отличии от многих генераторов отчетов, у нас имеется вся мощь Delphi для расчета и получения нужных нам значений в разных частях процедуры построения отчета.

Источник

Генератор отчетов для excel

Когда я начинал читать тренинги по MS Excel то «стер» пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы «Сводные таблицы». Решил я эту проблему просто — создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это «чудо» на очередном тренинге они очень сильно «возбудились». Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для «студента».

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

Скачайте архив надстройки и подключите к вашему MS Excel ( Как подключать надстройки к MS Excel? ).

Чтобы сгенерировать тот или иной пример перейдите во вкладку «GT» главного меню, нажмите на соответствующую кнопку (Массивы, Таблицы или Отчеты) и в выпавшем списке выберите необходимый пример:

Программа создаст в активной рабочей книге новый лист на котором сформирует необходимый пример — массив, таблицу или отчет:

Если вам не понравился тот или иной сгенерированный пример — нажмите кнопку отмены:

• Структура рабочей книги не должна быть защищена.

Источник

Zen Reports и %XML.Writer для генерации отчётов Excel в Caché

Как известно MS Excel последних версий поддерживает описание структуры документа в формате xml. Это обстоятельство позволяет создавать отчеты в Excel с помощью генерации xml-файлов. В СУБД Caché существует несколько способов создания xml. В этой статье будут рассмотрены два, возможно наиболее удобных, способа эффективной программной генерации отчетов в MS Excel: с помощью Zen Reports и с использованием класса %XML.Writer.

В качестве примера отчета MS Excel взята печатная форма учебного плана из системы управления учебным планированием, о которой здесь уже писалось, поэтому перейдём непосредственно к постановке задачи и способам её решения. Требуется получить отчёт учебного плана в формате MS Excel, который должен состоять из графика учебного процесса (титульный лист) и содержания учебного плана (перечень всех дисциплин, их характеристик и вычисляемых параметров). Фрагмент отчёта учебного плана представлен на рисунке, готовый отчёт можно посмотреть здесь.

Общая схема формирования отчёта

В Cache существует несколько способов ручного изготовления отчётов (здесь не будут рассматрены возможности полуавтоматической сборки на базе DeepSee). Самый удобный способ реализован в ZEN и включает в себя набор средств, обеспечивающий полный цикл процесса формирования отчётов в формате XHTML и PDF. Описание этого процесса можно посмотреть в документации. Тем не менее, для решения нашей задачи этот способ можно задействовать только частично.
Рассмотрим общий механизм формирования отчёта в формате MS Excel с применением как технологии ZEN, так и других возможностей Caché (см. рисунок ниже).

Данная схема формирования xls документа включает три этапа: 1) данные из базы конвертируются при помощи технологии Zen Reports или стандартной технологии Caché в xml файл (входной xml); 2) посредством механизма трансформации XSL (eXtensible Stylesheet Language) модифицируется подготовленный заранее шаблон отчёта в формате xml; 3) генерируется документ Excel (xls) путём заполнения шаблона отчёта xml, расширенного вставками XSL, данными из входного xml.

Структура входного xml-файла

Формирование исходного xml

Рассмотрим два способа получения исходного xml файла: при помощи класса %XML.Writer и с использованием механизма Zen Reports.

Формирование исходного xml с использованием %XML.Writer

Описанная выше структура xml может быть получена посредством класса XML.Writer, который позволяет:

1. Создавать корневой элемент
   do fWriter . RootElement ( «имя корневого элемента» )
   do fWriter . EndRootElement ()
2. Создавать элемент
   do fWriter . Element ( «имя элемента» )
   do fWriter . Write ( значение элемента )
   do fWriter . EndElement ()
3. Создавать атрибут
   do fWriter . WriteAttribute ( «имя атрибута» , «значение атрибута» )

Кроме того, XML.Writer обладает методом, позволяющим извлекать все данные из переданного в него объекта.
Writer . RootObject ( «имя объекта» )

В задаче формирования отчёта учебного плана метод RootObject не подошел, т.к. класс дисциплины имеет ссылку сам на себя, и работа этого метода была не корректна. В связи с этим все элементы выходного xml файла были созданы вручную. Для этого был создан класс sp.Report.spExcelWriter, включающий метод genWriterData (iDSelectCur As %Integer) для генерации xml-файла, в который передаётся id выбранного учебного плана. Используя данный метод, с помощью SQL-запросов извлекаются данные из БД, и в нужном месте выполняется их вставка. После этого генерируется выходной xml файл с помощью другого метода OutputToFile(«путьимя файла.xml»).

Формирование исходного xml с использованием механизма Zen Reports

XData ReportDefinition [ XMLNamespace = «www.intersystems.com/zen/report/definition» ]
<
report xmlns = «www.intersystems.com/zen/report/definition» name = «Curriculum» sql = «SELECT * FROM sp.cCurriculum WHERE >
parameter expression = ‘..idCurr’/>
element name = «CurrName» field = «Name»/>
element name = «sumСurEx» field = «ID» expression = «##class(sp.cCurriculum).getCountFCInCur(%val,1)»/>
element name = «sumСurZa» field = «ID» expression = «##class(sp.cCurriculum).getCountFCInCur(%val,2)»/>
element name = «sumСurKP» field = «ID» expression = «##class(sp.cCurriculum).getCountFCInCur(%val,4)»/>
element name = «sumСurKR» field = «ID» expression = «##class(sp.cCurriculum).getCountFCInCur(%val,3)»/>
element name = «sumСurZET» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,6)»/>
element name = «sumСurAll» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,1)»/>
element name = «sumСurGos» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,2)»/>
element name = «sumСurAud» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,3)»/>
element name = «sumСurKsr» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,4)»/>
element name = «sumСurSR» field = «ID» expression = «##class(sp.cCurriculum).getComTimeInCur(%val,5)»/>

group name = «sumCurseme1» >
element name = «sumCurLec» field = «ID» expression = «##class(sp.cCurriculum).getTimeInCur(%val,1,1)»/>
element name = «sumCurLab» field = «ID» expression = «##class(sp.cCurriculum).getTimeInCur(%val,1,2)»/>
element name = «sumCurPra» field = «ID» expression = «##class(sp.cCurriculum).getTimeInCur(%val,1,3)»/>
element name = «sumCurKsr» field = «ID» expression = «##class(sp.cCurriculum).getTimeInCur(%val,1,4)»/>
group >
…
group name = «Cicls» sql = «SELECT * FROM sp.cCicl WHERE Curriculum = ?» >
parameter expression = ‘..idCurr’/>

group name = «Cicl» >
attribute name = «CiclName» field = «Name»/>
attribute name = «CodeOfCicl» field = «CodeOfCicl»/>
element name = «sumCiclEx» field = «ID» expression = «##class(sp.cCicl).getCountFCInCicl(%val,1)»/>
element name = «sumCiclZa» field = «ID» expression = «##class(sp.cCicl).getCountFCInCicl(%val,2)»/>
element name = «sumСiclKP» field = «ID» expression = «##class(sp.cCicl).getCountFCInCicl(%val,4)»/>
element name = «sumСiclKR» field = «ID» expression = «##class(sp.cCicl).getCountFCInCicl(%val,3)»/>
element name = «sumCiclchAll» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,1)»/>
element name = «sumCiclchGos» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,2)»/>
element name = «sumСiclchAud» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,3)»/>
element name = «sumСiclchKsr» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,4)»/>
element name = «sumСiclchSR» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,5)»/>
element name = «sumСiclZet» field = «ID» expression = «##class(sp.cCicl).getComTimeInCicl(%val,6)»/>

group name = «sumCiclseme1» >
element name = «sumCiclLec» field = «ID» expression = «##class(sp.cCicl).getTimeInCicl(%val,1,1)»/>
element name = «sumCiclLab» field = «ID» expression = «##class(sp.cCicl).getTimeInCicl(%val,1,2)»/>
element name = «sumCiclPra» field = «ID» expression = «##class(sp.cCicl).getTimeInCicl(%val,1,3)»/>
element name = «sumCiclKsr» field = «ID» expression = «##class(sp.cCicl).getTimeInCicl(%val,1,4)»/>
group >
…
group name = «Blocks» sql = «SELECT * FROM sp.cBlock WHERE Cicl = ?» breakOnField = «ID» >
parameter field = «ID»/>

group name = «Block» >
attribute name = «BlocName» field = «Name»/>
element name = «countEx» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getCountFCInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1)’/>
element name = «countZa» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getCountFCInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),2)’/>
element name = «countKR» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getCountFCInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),3)’/>
element name = «countKP» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getCountFCInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),4)’/>
element name = «sumBAll» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1)’/>
element name = «sumBGos» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),2)’/>
element name = «sumBAud» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),3)’/>
element name = «sumBKSR» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),4)’/>
element name = «sumBSR» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),5)’/>
element name = «sumBZET» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getComTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),6)’/>

group name = «sumBseme1» >
element name = «sumBLec» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1,1)’/>
element name = «sumBLab» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1,2)’/>
element name = «sumBPra» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1,3)’/>
element name = «sumBKSR» fields = «Cicl,Name,ID» expression = ‘##class(sp.cBlock).getTimeInBlock(%val(«ID»),%val(«Name»),%val(«Cicl»),1,4)’/>
group >
…
group name = «Disciplines» sql = «SELECT * FROM sp.cDiscipline WHERE ( Blok=? AND Cicl=? And Parent is null)» breakOnField = «ID» >
parameter field = «ID»/>
parameter field = «Cicl»/>

group name = «Discipline» >
element name = «DiscName» field = «Name»/>
element name = «Exam» field = «ID» expression = ‘##class(sp.cDiscipline).getFormContr(%val,1)’/>
element name = «Zachet» field = «ID» expression = ‘##class(sp.cDiscipline).getFormContr(%val,2)’/>
element name = «KR» field = «ID» expression = ‘##class(sp.cDiscipline).getFormContr(%val,3)’/>
element name = «KP» field = «ID» expression = ‘##class(sp.cDiscipline).getFormContr(%val,4)’/>
element name = «chAll» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,1)’/>
element name = «chGos» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,2)’/>
element name = «chKsr» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,4)’/>
element name = «chAud» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,3)’/>
element name = «chSamRab» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,5)’/>
element name = «Zet» field = «ID» expression = ‘##class(sp.cDiscipline).getComTime(%val,6)’/>
element name = «naBlock» field = «ID» expression = ‘##class(sp.cDiscipline).getNameBlock(..idCurr,%val)’/>

group name = «seme1» >
element name = «Lec» field = «ID» expression = ‘##class(sp.cDiscipline).getTime(%val,1,1)’/>
element name = «Lab» field = «ID» expression = ‘##class(sp.cDiscipline).getTime(%val,1,2)’/>
element name = «Pra» field = «ID» expression = ‘##class(sp.cDiscipline).getTime(%val,1,3)’/>
element name = «KSR» field = «ID» expression = ‘##class(sp.cDiscipline).getTime(%val,1,4)’/>
group >
…
group >
group >
group >
group >
group >
group >
report >
>

Zen Report предлагает использование собственного синтаксиса для описания структуры данных для генерируемого xml — это накладывает некоторые ограничения на формат выходного xml. В результате структура полученного xml файла незначительно отличается от описанной выше: в генерируемый xml файл дополнительно добавляются узлы Cicls и Blocks, в которых содержатся подузлы Cicl и Block.
Покажем некоторые особенности вывода связанных данных.

Пример 1. Передача ID выбранного учебного плана в sql запрос элемента .
report sql = «SELECT * FROM sp.cCurriculum WHERE >>

Далее на место «?» передается параметр со значением переменной ..idCurr
parameter expression = ‘..idCurr’/>

Переменная является свойством класса ZenReport и при вызове метода генерации отчета, значение idCurr принимает значение переданного в метод параметра id текущего учебного плана.

Пример 2. Передача параметра зависящего от результата выполнения SQL запроса, например связь Цикл – Блок:
group sql = «SELECT * FROM sp.cCicl WHERE Curriculum = ?» >
parameter expression = ‘..idCurr’/>
group sql = «SELECT * FROM sp.cBlock WHERE Cicl = ?» breakOnField = «ID» >
parameter field = «ID»/>
group >
group >

Здесь передача «ID» осуществляется с использованием атрибута breakOnField = «ID».
Покажем выполнение группировки для «Циклов».
group name = «Cicls» sql = «SELECT * FROM sp.cCicl WHERE Curriculum = ?» >
parameter expression = ‘..idCurr’/>
group name = «Cicl» >
attribute name = «CiclName» field = «Name»/>
attribute name = «CodeOfCicl» field = «CodeOfCicl»/>
…
group >
…
group >

Блоки группируются аналогично.
Изменённый формат сгенерированного XML-файла теперь имеет следующий вид.

Также изменится вызов цикла при XSL трансформациях (общий способ применения XSL трансформаций описан ниже):

Сформулируем некоторые правила, которые следует учитывать при проектировании структуры выходных данных.

1. Поле Name будет взято из Table1:
   report sql = «SELECT Name FROM Table1» >
   element name = «A» field = «Name»/>
2. Поле Name выдаст ошибку:
   report sql = «SELECT Name FROM Table1» >
   attribute name = «A» field = «Name»/>
3. Поле Name получится из Table2:
   report sql = «SELECT Name FROM Table1» >
   group name = «Name» sql = «SELECT Name FROM Table2 WHERE. » >
   element name = «A» field = «Name»/>
4. Поле Name получится из Table1:
   report sql = «SELECT Name FROM Table1» >
   group name = «Name» sql = «SELECT Name FROM Table2 WHERE. » >
   attribute name = «A» field = «Name»/>

Создание шаблона Excel

В приведённом фрагменте видно, что вначале создаётся список стилей, который затем используется для форматирования ячеек. Например:

На этот стиль ссылается следующая ячейка:

Элемент «Worksheet» создаёт листы в книге Excel, например:

Элемент «Table» создаёт таблицу. Таблица состоит из строк «Row», а строки в свою очередь из ячеек «Cell».
Шаг 3. Посредством любого текстового редактора вносятся изменения в структуру xml путём удаления лишних атрибутов. В нашем случае удаляются атрибуты: ss:ExpandedColumnCount = «67»; ss:ExpandedRowCount = «45»; x:FullColumns = «1»; x:FullRows = «1», так как учебный план имеет произвольное количество дисциплин, и если у элемента «Table» сохранить эти атрибуты, возникнет ошибка при генерации документа Excel из-за несоответствия количества строк и столбцов. Также желательно удалить атрибут ss:Height у , так как если строка будет сильно длинная и в ячейке будет указано «переносить по словам», то переноса по словам не будет в сгенерированном Excel-документе.

XSL-трансформация

Для использования стандартного метода трансформации (в классе %XML.XSLT.Transformer) xml-данных в формат xls требуется подготовить специальный блок xml со встроенными конструкциями XSL. В нашем случае в качестве основы для XSL взят шаблон Excel, подготовленный в предыдущем пункте. Этот шаблон нужно доработать, используя следующие конструкции XSL:

Конструкция используется для выбора каждого xml элемента заданного набора. Конструкция позволяет выводить значения выбранного узла. Ниже приведён простой пример вставки XSL в Excel шаблон:

В приведённом примере показано, что в Excel таблице во вложенном цикле идёт обращение ко всем элементам «Cicl», затем в каждом цикле (укрупнённая группа дисциплин) ко всем элементам «Block», затем в каждом блоке к элементам /Disciplines/Discipline, и после этого выводится информация соответствующая указанному полю , т.е. названия дисциплин.
После того как выполнилась вставка элементов XSL в нужные места шаблона можно приступать к процессу генерации отчёта. Для этого можно создать специальный метод в некотором классе, который будет выполнять трансформацию данных из xml формата в xls, используя подготовленный шаблон Excel, который можно разместить в блоке XData этого же класса (в приведённом ниже примере блок XData называется «xsl»). Пример этого метода приведён ниже.

ClassMethod generateReportStadyPlan( outFileName As %String ) As %Status
<
set xslStream = ##class ( %Dictionary.CompiledXData ). %OpenId (.. %ClassName (1)_ «||xsl» ). Data
set xmlStream = ##class ( %FileBinaryStream ). %New ()
set xmlStream . Filename = «Путь к файлу xml»

set outStream = ##class ( %FileCharacterStream ). %New ()
set outStream . TranslateTable = «UTF8»
set outStream . Filename = outFileName

Источник

Many small-scale businesses do not use database management systems to generate reports. Most of them stick to spreadsheet packages. Unfortunately Spreadsheet softwares are not equipped with  advanced query and report generating features. However, some Excel users struggle with Reports that they update, save and print manually. It is a very tedious job, but VBA can make it better. I have created a spreadsheet application you may used to fill a template/report with different “Records“, save each in a separate workbook, and print automatically.

Borrowing a few terms from Database Packages, first, we need to organize the data that goes into the report in a neat ‘Table’. A database entry, also called a ‘Record‘, has information under various headers called ‘Fields‘. A record needs to be uniquely Identifiable with a ‘Key‘. In this application, the ‘Table‘ is stored in the ‘Data‘ Sheet. The ‘Field‘ names are stored in a Row named ‘Field Names‘. Each Row corresponds to a ‘Record‘ and the ‘Keys‘ are generated automatically when the user clicks the ‘Set-up‘ button.

The next thing we need to do is to set-up the Report which needs to be filled with data from the ‘Table‘.  This is done in the ‘Template‘ sheet. The cells in this sheet need to be linked to ‘Fields‘ corresponding to the required ‘Record‘. This is accomplished by using an intermediate ‘Mapping‘ Sheet. In this sheet, the user can extract all the fields of a particular record, by specifying its ‘Key‘. Clicking the ‘Export‘ button loops through all the ‘Keys‘, updates the ‘Mapping‘ sheet and exports the ‘Template’ into individual workbooks.

I urge you to download the application from the section below, and give it a whirl.

How to use this application?

Step 1 : Import your records into the ‘Data’ sheet.

• Fill in the Field Names/Column Headers
• Copy paste records. Preferably as Values.
• Click the ‘Set Up’ Button

Step 2 : Setup your Template

• Paste your Template into the ‘Template’ Sheet. Copy pasting all (Ctrl+V).
• Link the “Fields” to the cells in the ‘Mapping’ spreadsheet
• Print Page Setup the sheet. Also set the Default Printer to the available one.

Step 3 : Set Preferences in the ‘Mapping’ sheet

• Which records you want to save?  – Set the Start and Stop Record Numbers
• Do you want to print each export when they are created? – Yes/No
• Set up a file name tag that is unique for each record. Example: =”Record – “&TEXT(RecordNumber,”000”). Feel free to use the mapping ranges to add in any unique Identification Numbers. Make sure it does not contain any special characters. Set up a similar name for the sheet name.
• Choose if you’d like to protect the sheets, workbooks and File; and specify the respective password. Remember Protecting the sheet does not allow users to make changes to the sheet; protecting the workbook does not allow the user to change the structure of the workbook. And protecting the file does not allow the user to open the workbook without keying in the correct password.
• While saving a workbook, Excel usually prompts the user for confirmation if a workbook with the same name exists already. Setting the ‘Replace Without Prompt’ option to ‘Yes’ will replace existing files without the prompt.
• Click the browse button to specify where the output files should be saved. If left blank, the files would be saved along with this workbook.
• Hit the ‘Reset’ button to bring back the default options.

Step 4 : Hit the Export Button

How to incorporate these into your other projects?

I understand that you May not be interested in using this Spreadsheet application in its entirety. However, you may interested in incorporate the Loop-and-Export functionality into your existing applications. Therefore, I have two versatile Macros that you can use in your applications.

Macro 1 : Macro that exports a Spreadsheet into a Workbook.

'====================================================================================
'A macro that exports a sheet to a workbook. It has a few optional arguments that
'make it versatile.
'Arguments:
'WhichSheet - Worksheet Object. The sheet that needs to be exported. If not specified,
'               The active sheet will be exported.
'ToWhichBook - Workbook Object. The sheet specifed earlier will be copied to this
'               workbook. If not specified, a new workbook will be created.
'               If you'd like a reference to the workbook that was created, pass an
'               empty workbook object as argument to this sub. Will come on handy while
'               exporting multiple WorkSheets to a new workbook. And it will also help
'               in saving the created workbook later.
'SheetName - String Variable. An optional arguemnt that will be used to set the name of
'               the newly created sheet. If not specified, it will be set to the name
'               of 'WhichSheet'
'OnlyValues - Boolean Variable. An optional argument that can be set to True to convert
'               all the references to values. It is set to True by defauly. Set it to
'               False if you'd like to retain the formulas.
'ProtectSheet - Boolean Variable. Set to true if you'd like to protect the newly created
'               WorkSheet.
'SheetPassword - String Variable. Password to protect the sheet with.

'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 30 January 2013
'Website : http://strugglingtoexcel.wordpress.com/
'====================================================================================
Sub ExportSheetToBook(Optional ByRef WhichSheet As Worksheet, _
                        Optional ByRef ToWhichBook As Workbook, _
                        Optional ByVal SheetName As String = "", _
                        Optional ByVal OnlyValues As Boolean = True, _
                        Optional ByVal ProtectSheet As Boolean = False, _
                        Optional ByVal SheetPassword As String = "")

'Declare Sub Level Objects/Variables

'A newly created workbook contains a number of sheets. All of them cannot
'be deleted, one has to remain. It has to be deleted only after the intended
'sheet has been imported.
Dim shtDeleteFinal As Worksheet
'A WorkSheet object to use in the for loops
Dim shtEach As Worksheet
'A WorkSheet object to hold the sheet that was imported
Dim shtCopied As Worksheet
'A long variable to use in for loops
Dim lngCount As Long
'A Boolean variable to hold the DisplayAllert status temporarily
Dim booStatusAlerts As Boolean

'Set the boolean variable to the current DisplayAlerts status
booStatusAlerts = Application.DisplayAlerts

'Set WhichSheet to ActiveSheet if a sheet was not passed to the sub
If WhichSheet Is Nothing Then
    Set WhichSheet = Application.ActiveSheet
End If

'Create a new workbook if no workbook was passed to the sub. A new
'workbook will be created even if an uninitialized workbook object
'was passed to the sub.
If ToWhichBook Is Nothing Then
    'Create a new Workbook
    Set ToWhichBook = Application.Workbooks.Add
    'Set the First Sheet to a WorkSheet object, for deleting it later
    Set shtDeleteFinal = ToWhichBook.Sheets(1)
        'Supress Alerts, for excel prompts for a confirmation when the
        'user deletes a WorkSheet
        Application.DisplayAlerts = False
        'Delete all the WorkSheets except the first one
        For Each shtEach In ToWhichBook.Sheets
            If Not shtEach Is shtDeleteFinal Then
                shtEach.Delete
            End If
        Next shtEach
End If

'Copy the WorkSheet to the intended workbook before the first WorkSheet
WhichSheet.Copy Before:=ToWhichBook.Worksheets(ToWhichBook.Sheets(1).Name)
'Now the newly copied WorkSheet will be the first sheet in the workbook
'Initialize a WorkSheet object to remember the sheet
Set shtCopied = ToWhichBook.Worksheets(ToWhichBook.Sheets(1).Name)

'If the user wants only Values, convert formula to values.
If OnlyValues Then
    Call modSupport.ChangeValueToFormula(shtCopied.Cells.SpecialCells(xlCellTypeFormulas))
End If

'Delete the One sheet that was not deleted earlier
If Not shtDeleteFinal Is Nothing Then
    shtDeleteFinal.Delete
    Set shtDeleteFinal = Nothing
End If

'Restore the DisplayAlerts status
Application.DisplayAlerts = booStatusAlerts

'If the user specified a WorkSheet name, then rename it
If Not SheetName = vbNullString Then
    shtCopied.Name = SheetName
End If

'If the user wants to protect the sheet, Protect it with the
'specified password.
If ProtectSheet Then
    shtCopied.Protect Password:=SheetPassword
End If

End Sub

Macro 2 : Loops through a specified range of ‘Keys’ and Exports the ‘Templates’.

'====================================================================================
'A macro that loops through a specified range of 'Key' and exports the 'Templates'.
'It contains a lot of optional arguments that may be used the customize the export
'Process
'Arguments:
'LngStart - Long Variable. Starting 'Key' number
'LngEnd - Long Variable. Ending 'Key' number
'rngUpdate - Range Object - The cell that needs to be updated with the current
'               'Key' number that is being exported. The Index functions used to
'               lookup the records depend on this cell.
'shtTemplate = WorkSheet object. The Template sheet that needs to be exported
'strOutputFolder - String Variable - Folder path  where the files need to be
'                   saved. This workbook's path will be used if left blank.
'booProtectSheet, booProtectBook, booProtectFile - Boolean Variables. The
'                   respective objects will be protected with corresponding
'                   passwords specified in strPwdSheet, strPwdBoook, strPwdFile
'                   respectively.
'strPwdSheet, strPwdBoook, strPwdFile - String Variables. Passwords to protect the
'                   sheet, book and file respectively.
'rngSheetName - Range Object - The user has the option to set up a cell that will
'                   create a unique name for the WorkSheet that is exported. If
'                   this arguement is not specified a default name is generated.
'rngFileName - Range Object - The user has the option to set up a cell that will
'                   create a unique name for the WorkBook that is created. If this
'                   arguement is not specified a default name is generated.
'booSaveFile - Boolean Variable. User can choose if each export is saved as an
'               individual file.
'booCloseFile - Boolean Variable. The user can choose if the workbook should be
'               closed after Saving/Printing.
'booPrint - Boolean Variable. The user can choose if the sxported sheet should be
'               printed using the default printer.
'booReplace - Boolean Variable. The user can choose to supress the "Do you want to
'               replace the file? prompt.
'booUpdateStatus - Boolean Variable. The user can choose if the status bar should
'               show a quick update on which record is being processed.
'booPromptComplete - Boolean Variable. The user can choose to dispaly an alert when
'               the loop is done
'
'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 30 January 2013
'Website : http://strugglingtoexcel.wordpress.com/
'====================================================================================
Sub ExportRun(ByVal LngStart As Long, _
                ByVal LngEnd As Long, _
                ByRef rngUpdate As Range, _
                ByRef shtTemplate As Worksheet, _
                Optional ByVal strOutputFolder As String = vbNullString, _
                Optional ByVal booProtectSheet As Boolean = False, _
                Optional ByVal strPwdSheet As String = vbNullString, _
                Optional ByVal booProtectBook As Boolean = False, _
                Optional ByVal strPwdBook As String = vbNullString, _
                Optional ByVal booProtectFile As Boolean = False, _
                Optional ByVal strPwdFile As String = vbNullString, _
                Optional ByRef rngSheetName As Range, _
                Optional ByRef rngFileName As Range, _
                Optional ByVal booSaveFile As Boolean = True, _
                Optional ByVal booCloseFile As Boolean = True, _
                Optional ByVal booPrint As Boolean = False, _
                Optional ByVal booReplace As Boolean = True, _
                Optional ByVal booUpdateStatus As Boolean = False, _
                Optional ByVal booPromptComplete As Boolean = True)

'An empty WorkBook object to retain a reference to the new
'workbook that would be created by the 'ExportSheetToBook' macro
Dim wkbBook As Workbook
'A Long variable used in the For Loops
Dim lngCount As Long
'String Variables to hold the Sheet, Book and File names.
Dim strSheetName As String
Dim strBookName As String
Dim strBookPath As String

'If the user did not specify a part, use ThisWorkBook's path
If strOutputFolder = vbNullString Then
    strOutputFolder = shtTemplate.Parent.Path & ""
End If

'If the user chooses to Replace Files without Prompt
'Suppress the Alerts
If booReplace Then
    Dim booStatusAlerts As Boolean
    booStatusAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
End If

'Start the Loop
For lngCount = LngStart To LngEnd
    'Update the Record Number
    rngUpdate.Value = lngCount
    'Recalculate Worksheet (in case the user is in Manual Calculate Mode)
    rngUpdate.Worksheet.Calculate

    'If the user has specifed a Cell that contains a SheetName Tag,
    'Use it, else set it to Null
    If Not rngSheetName Is Nothing Then
        strSheetName = rngSheetName.Value
    Else
        strSheetName = vbNullString
    End If

    'If the SheetName tag is empty, create a Generic Name using the
    'Record Number
    If strSheetName = vbNullString Then
        strSheetName = shtTemplate.Name & "_" & _
                        Format(lngCount, String(Len(CStr(LngEnd)), "0"))
    End If

    'If the user wants the status bar to be updated, update it
    'with Record Number and Sheetname
    If booUpdateStatus Then
        Application.StatusBar = "Record " & _
                        Format(lngCount, String(Len(CStr(LngEnd)), "0")) & _
                        " of " & LngEnd & " | " & strSheetName
    End If

    'Export the Sheet using an Empty WorkBook argument to retain a reference
    'to the newly created WorkBook
    Call ExportSheetToBook(shtTemplate, wkbBook, strSheetName, True, _
                                booProtectSheet, strPwdSheet)

    'If the user has specifed a range that contains a FileName tag,
    'then use it. Else, set it to Null.
    If Not rngFileName Is Nothing Then
        strBookName = rngFileName.Value
    Else
        strBookName = ""
    End If

    'If the FileName tag is empty, create a Generic Name using the
    'Record Number
    If strBookName = "" Then
        strBookName = strSheetName
    End If

    'Update a String Variable to hold the full Path
    strBookPath = strOutputFolder & strBookName

    'Protect Workbook
    If booProtectBook Then
        wkbBook.Protect Password:=strPwdBook
    End If

    'Save the file
    If booSaveFile Then
        'If the user chooses to Password protect the file,
        'protect it with the specifed password
        If booProtectFile Then
            wkbBook.SaveAs FileName:=strBookPath, Password:=strPwdFile
        Else
            wkbBook.SaveAs FileName:=strBookPath
        End If
    End If

    'Print the worksheet
    If booPrint Then
        wkbBook.Worksheets(strSheetName).PrintOut
    End If

    'Close the File
    If booCloseFile Then
            wkbBook.Close
    End If

    'Set the WorkBook object to Nothing for the next iteration
    Set wkbBook = Nothing
Next lngCount

'Restore the StatusBar Message
If booUpdateStatus Then
    Application.StatusBar = False
End If

'Restore the Dsiplay Alerts Status
If booReplace Then
    Application.DisplayAlerts = booStatusAlerts
End If

'Show the user a Completed Message
If booPromptComplete Then
    Dim strMsgbox As String
    strMsgbox = "The Export is now done." & vbNewLine & _
                "Records " & LngStart & " to " & LngEnd & _
                " have been exported." & _
                vbNewLine & "The files are saved in : " & _
                strOutputFolder
    MsgBox strMsgbox, vbInformation
End If

End Sub

In fact, even in my application, I have used a macro that fetches all the arguments that need to be passed on to the aforementioned macros. I have added in detailed comments to help you follow my code.

Download

Update

I extended this tool to handle upto 500 fields for a fellow struggler. Here is the link to that file. It has not been tested as much as I would have liked.