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

18 14

Так уж получилось, что очень много отчетов выводится из 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 Кб)

18
Авторизуйтесь, чтобы оценить материал.
3
Алексей Пестов

Как-то у меня очень уж долго делается отчет. Где-то 1сек. 1 запись. Если 2000 записей это примерно пол часа ждать вывода :(

Денис Архипов

есть еще XMLSPREADSHEETS наверное самый быстрый способ, но по трудоемкости примерно равен первому.

Алексей Семакин
Как-то у меня очень уж долго делается отчет. Где-то 1сек. 1 запись.

Время на запуск самого Excel и открытие шаблона в нем учитываете? Я заметил: если при формировании отчета сам Excel уже запущен (например, параллельно открыт какой-то другой документ), то отчет формируется быстро. В моем случае (вывод 116 записей) - считанные секунды.
Алексей Семакин
есть еще XMLSPREADSHEETS наверное самый быстрый способ
Очень интересно, Денис. Если знаете, где об этом доходчиво изложено, ссылку в студию, пожалуйста. Ну а в идеале бы материал в продолжение темы написать, с примерами на ISBL.
Алексей Пестов
Время на запуск самого Excel и открытие шаблона в нем учитываете? Я заметил: если при формировании отчета сам Excel уже запущен (например, параллельно открыт какой-то другой документ), то отчет формируется быстро. В моем случае (вывод 116 записей) - считанные секунды.

Прошу прощения. У меня наверно вчера к концу рабочего дня комп был забит :) сегодня проверил на только что загрузившимся компе. Время выполнения 24 сек. записей 2078. Такой тип формирования отчетов очень напомнил 1с. Конечно со скоростью формирования в 1с не сравниться, но всё же :)
Алексей Семакин
очень напомнил 1с
В яблочко, Алексей :)
со скоростью формирования в 1с не сравниться
и не только со скоростью. Там к отчетам вообще особое отношение. Формирование отчетов в Предприятии - главный смысл и цель существования системы. Наверно, поэтому и инструментов разработки отчетов (я имею в виду не только работу с макетами, но и всякие штуки типа Построителей отчетов и Схем компоновки данных) на любой вкус и под любую задачу. Там даже проектирование системы идет "от отчета": сначала определяется список и содержание отчетов, а уж на основе этого становится понятно, какие нужны справочники и документы и т.д.
Тем не менее, я лично доволен, что подобный подход реализуем и за пределами Предприятия.
Денис Архипов
Очень интересно, Денис. Если знаете, где об этом доходчиво изложено, ссылку в студию, пожалуйста. Ну а в идеале бы материал в продолжение темы написать, с примерами на ISBL.
подробнее чем на MSDN наверно нет нигде :) http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx
 
Алексей Пестов
есть еще XMLSPREADSHEETS наверное самый быстрый способ, но по трудоемкости примерно равен первому.

Взяли на вооружение. Просто супер! Выполняется за секунды. Раньше отчет формировался 116 сек теперь 15 сек.
Illya Yeldinov

Добрый день. А как можно в шапку добавить именованую ячейку, например название организации. У меня в шаблоне остается, а в отчете пусто.

Алексей Семакин

Если в макете ячейка заполняется, а в отчете нет, то проблема с переносом из макета в отчет. Проверьте, что новая ячейка находится внутри диапазона "Шапка". В противном случае переносить ее придется отдельно.

Юлия Абдуллина

Коллеги, подскажите идеи пожалуйста, как вывести табличку из карточки в Excel? 

Сначала хотела как в справочниках сделать: кнопка Экспорт в Excel. Думала там под кнопкой какой-то стандартный код. Кнопка есть - кода нет)

Потом хотела применить способ из статьи. Но это не поля. Это таблица.


 

Алексей Семакин

Юлия, в вашем случае подойдут все способы, упомянутые в материале.

Непонятно, почему не подошел способ из статьи. Он (как и первый способ) не привязан ни к каким полям и пригоден для вывода в отчет любой информации.

Вывод через xsd тоже выглядит рабочим в вашем случае — вы ведь хотите просто вывести табличную часть один-в-один на лист Excel. Ваша задача создать XML с нужными вам данными (опять же никакой жесткой связи с данными в системе) и передать ее в шаблон отчета с настроенной xsd-схемой.

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

Арсений Сыров

Здравствуйте. Подскажите, есть ли способ экспортировать подобные данные в Google Sheets?

Алексей Семакин

Арсений, подобные данные — это что именно? Google Sheets может импортировать данные из форматов: csv, txt, tsv, tab, htm, html, xls, xlsx, xlsm, xlt, xltm, xltx, ods. Следовательно, ваша задача — подготовить выгрузку в одном из перечисленных форматов на основе данных системы.
Как полностью автоматизировать процесс, чтобы по нажатию кнопки в системе данные появлялись сразу в Google Sheets — не подскажу, это нужно изучать возможности интеграции с этим сервисом.

Авторизуйтесь, чтобы написать комментарий