Быстрая разработка отчетов с Microsoft Excel

15 6

Предлагаю один из способов создания отчетов, многие из Вас о нем знают.

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

1. Создаем в Excel файл шаблона, в шаблоне создаем источники данных для нашего отчета.

 

 

 

 

 

 

2. Задаем имя источнику данных.

3. Подготовленный шаблон сохраняем в системе.

4. Создаем модуль отчета

На событие кнопки "Расчет" помещаем следующий код:

// Строка подключения к БД (для хранения использую справочник констант)
lp_ODBC = GetConstant("Link_ControlDirectumODBC")
lp_FilePath = GetTempFolder() & "366344.xlt"


// Получаем шаблон
Doc = EDocuments.GetObjectByID(366344)

// Разворачиваем шаблон во временное хранилище
Doc.Export(1; lp_FilePath; False)

// Создаем подключение к объектной модели Excel
Ex = CreateObject('Excel.Application')

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


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

idxPage = 1
while (Book.Sheets.Count >= idxPage )

  idxQuery = 1
  while (Book.Sheets(idxPage).QueryTables.Count >= idxQuery)

    If Book.Sheets(idxPage).QueryTables(idxQuery).Name == "Rep_JobOverdue"
      Book.Sheets(idxPage).QueryTables(idxQuery).Connection = lp_ODBC
      Book.Sheets(idxPage).QueryTables(idxQuery).Sql = "EXEC sp_Rep_JobOverdue"
      // Обновляем источник данных
      Book.Sheets(idxPage).QueryTables(idxQuery).Refresh 
    endIf
  
    // Удаляем источники данных после обновления
    Book.Sheets(idxPage).QueryTables(idxQuery).Delete
    idxQuery = idxQuery + 1
  endwhile 
  idxPage = idxPage + 1
endwhile


// Можно запустить макрос
//Report.Application.Run( "'" & Book.name & "'" & "!fRep")

// Делаем отчет доступным для пользователя
Ex.Visible = true
Book.Activate

Exit()

5. Получаем результаты.

 

 

 

 

 

 

 

 

Алексей Язынин

Олег, а какие права нужны пользователю в БД, чтобы у него корректно выполнялся отчет? Как вообще обстоит дело с правами доступа к БД при выполнении отчета? У каждого пользователя будет запрашиваться его личный логин/пароль? Будет ли работать Win-аутентификация?

Олег Быков

Приветствую, Алексей. Играет роль строка коннекта lp_ODBC = GetConstant("Link_ControlDirectumODBC") / можно создать login  test  с правами на запуск хранимых процедур (например, на базе DIRECTUM_SERVICES) и от этого логина запускать отчеты. В самих хранимых процедурах обращение будет к базе и таблицам DIRECTUM. База DIRECTUM_SERVICES используется для хранения собственных объектов (хранимых процедур - отчетов и др. метаданных). 

 

 

Олег Быков

Пользователи в системе работают под своим логином, который имеют / запуская отчет формируют подключение через ODBC от пользователя test и отрабатывает хранимая процедура. Далее, данные выводим в EXCEL и удаляем источники подключения из отчета.

Адик Крымгужин

Олег, отличное решение! Но в тексте упущен формата шаблона отчета. Судя по картинке вы создали источник данных в пустом документе Excel.

Олег Быков

Рад, что статья помогла. Да, источник создается в шаблоне и шаблон используется для построения конечного отчета. Формат шаблона опущен, дабы донести идею.

Алексей Пестов

Не могу найти такую табличку как в скриншоте 2. Появляется совсем другая.

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