Еще один способ разработки Excel-отчета

Так уж получилось, что очень много отчетов выводится из DIRECTUM в MS Excel. Причин тут несколько: популярность и распространенность этого табличного редактора, богатые возможности работы с данными в таблицах, хорошо документированная объектная модель. На разработчика при этом ложится задача по подготовке данных и выводу их в отчет в требуемом заказчиком виде. Способов формирования отчета в Excel лично мне доводилось использовать ровно два.

Способ первый

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

...
Data = Sheet.UsedRange
Data.Item(RowNumber + 4; COLUMN_TAB2_CODECURRENCY).Value = 'Код валюты платежа'
Data.Item(RowNumber + 5; COLUMN_TAB2_CODECURRENCY).Value = PaymentCurrencyCode
Data.Item(RowNumber + 4; COLUMN_TAB2_CENACONT).Value = 'Сумма платежа в валюте контракта'
Data.Item(RowNumber + 5; COLUMN_TAB2_CENACONT).Value = Amount
Data.Item(RowNumber + 4; COLUMN_TAB2_CODECURRENCYCONTR).Value = 'Код валюты контракта'
Data.Item(RowNumber + 5; COLUMN_TAB2_CODECURRENCYCONTR).Value = ContractCurrencyCode
Data.Item(RowNumber + 4; COLUMN_TAB2_TYPEPAY).Value = 'Тип платежа (1 или 2)'
Data.Item(RowNumber + 5; COLUMN_TAB2_TYPEPAY).Value = PaymentType

CellBR = Data.Cells(RowNumber + 5; COLUMN_TAB2_TYPEPAY)
Selection = Data.Range(CellHR; CellBR)
Selection.HorizontalAlignment = -4108   // Выравнивание по центру
CellBR = Data.Cells(RowNumber + 4; COLUMN_TAB2_TYPEPAY)
Selection = Data.Range(CellHR; CellBR)
Selection.Font.Bold = true              // Выделение заголовка таблицы полужирным
//Рисуем границы таблицы  
CellBR = Data.Cells(RowNumber - 1; COLUMN_TAB2_TYPEPAY) // Нижний правый 
Selection = Data.Range(CellHR; CellBR)
Selection.WrapText = True
//обведем рамкой
Selection.Borders(1).LineStyle = 1
Selection.Borders(2).LineStyle = 1
Selection.Borders(3).LineStyle = 1
Selection.Borders(4).LineStyle = 1
...

Полужирным курсивом выделено программирование структуры и оформления отчета. Как видите, больше половины кода ушло на то, чтобы заполнить заголовки полей отчета (которые неизменны), а также на то, чтобы все это красиво оформить (тоже всегда одинаково). И это — заполнение и оформление всего нескольких ячеек листа отчета. Требуется изрядная доля воображения, чтобы разглядеть за этим хардкоромхардкодом облик конечного документа. Особенно не автору кода, или если код написан год назад. А разработка такого отчета с нуля или существенное изменение его внешнего вида — так и вообще забава на много часов в обнимку с MSDN.

Способ второй

Гораздо более высокотехнологичный способ — формирование отчета на основе xsd-схем. Несомненным преимуществом здесь является то, что настройка структуры и внешнего вида отчета (то есть — макета) осуществляется непосредственно в Excel. Получаем WYSIWYG, что удобно. В вычислении отчета мы полностью абстрагируемся от внешнего вида документа и просто собираем данные, что тоже очень здорово. Готовые данные передаются в макет в формате xml, Excel сам расставляет их по макету и форматирует. Кроме того, в самом макете можно на событие загрузки данных (AfterXmlImport) повесить макрос, который выполнит пост-обработку полученной информации (например, скроет оставшиеся незаполненными строки), а также использовать формулы в ячейках. Красота!

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

Однако и у этого способа есть свои ограничения. Они касаются вывода данных в многострочную часть отчета. Так, в многострочной части не должно быть объединенных ячеек — Excel попросту не даст вам сопоставить с такой ячейкой элемент схемы. Или вот такая экзотика — сложные ("многоэтажные") строки в многострочной части, когда ячейка в колонке дополнительно разбита на две и более частей по вертикали. Как такое вывести непосредственно через xsd, мне неизвестно.

А вот еще один способ

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

Для примера мы будем выводить в отчет список пользователей системы. Шаблон отчета представляет собой книгу Excel, содержащую два листа — "Макет" и "Отчет". На листе "Макет" мы рисуем фрагменты отчета, которые потом будем заполнять и переносить на лист "Отчет". Для того чтобы не связываться с координатами, всем фрагментам макета, которые будут независимо заполняться данными и переноситься в отчет, даем имена — получаем именованные диапазоны: макет шапки отчета, макет строки многострочной части, макет подвала отчета:

Отдельные ячейки в диапазонах "Строка" и "Подвал", куда выводятся данные, также именуем:

Именованные диапазоны позволят нам не привязываться к конкретным координатам. Если потребуется добавить в макет строку или колонку, границы диапазонов корректно пересчитаются, и отчет отработает как ни в чем не бывало. Оформление (шрифт, цвет текста, выравнивание, заливка, границы и т.д.) добавьте по вкусу. Собственно, макет готов. Лист с макетом после настройки можно вообще скрыть, чтобы не путать пользователя. А вот и вычисление:

xlPasteTypeColumnWidth = 8
Ex = CreateObject('Excel.Application')

// Считаем, что макет уже выгружен из системы на диск
Book = Ex.Workbooks.Open(TemplateFilename)

// Листы макета и отчета
Template = Book.Sheets('Макет')
Report = Book.Sheets('Отчет')

// Шапка
CaptionRange  = Template.Range('Шапка')

// Строка таблицы
LineRange       = Template.Range('Строка')
LineRangeHeight = LineRange.Rows.Count
IDRange         = Template.Range('ID')
NameRange       = Template.Range('Наименование')
LoginRange      = Template.Range('Логин')
StatusRange     = Template.Range('Статус')

// Подвал
BaseRange       = Template.Range('Подвал')
TotalRange      = Template.Range('Всего')

// Начнем выводить отсюда
CurrentPosition = Report.Range('A1:A1')

// Вывести шапку
CaptionRange.Copy()
CurrentPosition.PasteSpecial(xlPasteTypeColumnWidth) // скопировать ширину колонок диапазона
CaptionRange.Copy(CurrentPosition)

// Переместиться вниз по листу
CurrentPosition = CurrentPosition.Offset(CaptionRange.Rows.Count; 0)

Users = References.SYSREF_USERS_REFERENCE.GetComponent()
Users.AddWhere("MBAnalit.XRecStat = '+'")
Users.Open()
foreach User in Users
  
  // Заполнить строку таблицы в макете
  IDRange.Value     = User.SYSREQ_ID
  NameRange.Value   = User.Дополнение3
  LoginRange.Value  = User.Дополнение
  StatusRange.Value = User.SYSREQ_STATE
  
  // Перенести строку в отчет
  LineRange.Copy(CurrentPosition)
  CurrentPosition = CurrentPosition.Offset(LineRangeHeight; 0)
  
endforeach

TotalRange.Value = Users.RecordCount
Users.Close()

// Вывести подвал отчета
BaseRange.Copy(CurrentPosition)

Ex.Visible = true

Комментировать тут особо нечего, все достаточно просто. Единственный хардкод в данном случае — инициализация CurrentPosition. Можно и его вычислять через именованный диапазон, если нужно. Все остальное визуально настраивается в макете и аккуратно оттуда берется. А вот так выглядит результат работы:

В данном примере мы запросто вывели "двухэтажную" строку в многострочной части — полное наименование над логином пользователя с независимым форматированием, а также использовали объединенные ячейки — ID и статус. При этом макет отчета у нас удобно настраивается в самом Excel. Что и требовалось получить. Аналогичным образом можно формировать отчеты с изменяющимся количеством колонок. По своей сути такое формирование отчета ближе к первому способу, только всю работу по оформлению документа мы перенесли в Excel, оставив программисту лишь наполнение формы содержанием, плюс использовали именованные диапазоны вместо координат.

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

Использованный в примере шаблон: Report.xls (35,00 Кб)