Построение отчётов. Решение проблемы с зависанием процессов Excel.

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

  Excel = CreateObject("Excel.Application")
  WorkBook = Excel.Workbooks.Open(FilePath)

  ... заполнение отчёта ...
  Excel.DisplayAlerts = FALSE
  WorkBook.Save()
  WorkBook.Close() 
  Excel.Quit()
  WorkBook = nil
  Excel = nil

На нашем сервере разработки этот код работал нормально - процессы Excel, после построения отчётов, корректно завершались. На сервере клиента часть процессов Excel оставалась "висеть" в памяти.
С ростом числа процессов Excel в памяти начинались проблемы с новыми процессами Excel - когда число запущенных процессов Excel достигало пары-тройки десятков, то в ходе построения новых отчётов начинали возникать исключения при работе с объектной моделью Excel - ошибки при запуске новых процессов Excel, ошибки при формировании отчётов, блокировки открытых файлов Excel. При этом в часто используемых TM создавалось несколько отчётов в Excel и каждая задача, запущенная по такому маршруту, добавляла несколько "висящих" процессов в память сервера. При этом было замечено следующее - если завершить все работающие процессы Excel и заново запустить формирование отчётов, в которых ранее возникали ошибки, то при повторном формировании эти отчёты создавались без ошибок.
На сервере разработки "зависание" процессов Excel возникло один раз и повторить его не удалось.

Первоначальное решение проблемы было таким - на сервере клиента было создано назначенное задание Windows, которое через заданные промежутки времени выполняло следующую команду в командной строке:

taskkill.exe /F /IM Excel.exe

При выполнении этой команды завершались все запущенные процессы Excel. В блоках создания отчётов в типовых маршрутах с помощью "try...except" контролировался процесс создания отчёта и если отчёт не был создан (в результате ошибок из-за большого количества "висящих" процессов Excel или из-за того что используемый процесс Excel был завершён в ходе заполнения отчёта), то типовой маршрут переходил на блок ожидания и через минуту попытка создания отчёта повторялась.

С ростом нагрузки на сервер (при увеличении количества одновременно выполняющихся задач) данное решение перестало эффективно работать, т.к. стали возникать ситуации, когда за несколько секунд количество запущенных процессов Excel достигало нескольких десятков, дальнейшее формирование отчётов прекращалось и очередь задач WorkFlow оказывалась перегружена задачами по повторному формированию отчётов.
Поэтому было разработано решение, позволяющее контролировать завершение конкретных процессов Excel, запущенных посредством DIRECTUM. Рассмотрим его подробнее.
Для программного завершения конкретного запущенного процесса небходимо знать его PID - идентификатор процесса. У COM-объекта "Excel.Application" свойства PID нет, но есть свойство hWnd - дескриптор основного окна приложения. Казалось бы дальше всё просто - с помощью функции CallProcedure можно вызывать Windows API функцию GetWindowThreadProcessId, получить PID процесса по hWnd его окна и, при необходимости, завершить приложение.
В ходе разработки выяснилось что этот метод не подходит, поскольку функция GetWindowThreadProcessId возвращает два значения, а функция CallProcedure возвращает только из этих значений. Собственно само возвращаемое функцией GetWindowThreadProcessId значение - это идентификатор потока, создавшего то окно, чей дескриптор передаётся в функцию. А PID процесса возвращается через передаваемый в функцию указатель на переменную. Функция CallProcedure не изменяет значения переданных в неё параметров, т.е. при выполнении следующего кода

  Excel = CreateObject('Excel.Application')
  PID = 0
  ThreadID = CallProcedure('user32.dll'; 'GetWindowThreadProcessId'; 'EEE'; Excel.hWnd; PID)

будет возвращено значение ThreadID, а PID так и останется равным нулю.

Поэтому получение PID было перенесено в сам процесс Excel, для чего была написана ISBL функция GetExcelAndPID(). Исходный код этой функции:

  Macros = 'Public Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Any) As Long

Public Function GetPID()
  Dim hWnd As Long
  Dim PID As Long
      
  hWnd = Application.hWnd
  GetWindowThreadProcessId hWnd, PID
  GetPID = PID
End Function'

  Excel = CreateObject('Excel.Application')

  WorkBook = Excel.WorkBooks.Add
  Module = WorkBook.VBProject.VBComponents.Add(1)
  ModuleName = Module.Name
  Module.CodeModule.AddFromString(Macros)
  PID = Excel.Run('GetPID')
  DisplayAlerts = Excel.DisplayAlerts
  Excel.DisplayAlerts = FALSE
  WorkBook.Close
  Excel.DisplayAlerts = DisplayAlerts
  
  Result = ArrayOf(Excel; PID)

Функция GetExcelAndPID() создаёт объект "Excel.Application", создаёт новую книгу Excel, в книгу добавляет новый модуль, а в модуль вставляет макрос с функцией GetPID, которая, в свою очередь, вызывает Windows API функцию GetWindowThreadProcessId.
Далее из кода функции GetExcelAndPID() вызывается функция GetPID, возвращающая идентификатор запущенного на предыдущем шаге процесса Excel. Полученный идентификатор процесса сохраняется в переменной PID, созданная книга закрывается без сохранения и возвращается результат работы функции GetExcelAndPID в виде массива из двух значений. В первом элементе массива содержится ссылка на готовый к работе объект "Excel.Application", а во втором элементе массива - PID процесса Excel.
Для корректной работы функции GetExcelAndPID() необходимо чтобы в настройках Excel, на той  рабочей станции где будет использоваться эта функция, была включена опция "Доверять доступ к объектной модели проектов VBA". В Excel 2010 и выше эта опция располагается по следующему пути: Пункт меню "Файл" -> Параметры -> Центр управления безопасностью -> Параметры центра управления безопасностью -> Параметры макросов. Если эта опция не включена, то при вызове функции GetExcelAndPID() будет сгенерировано исключение.

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

  PROCESS_TERMINATE = 1
  Result = FALSE
  if Assigned(PID)
    hProcess = CallProcedure('kernel32.dll'; 'OpenProcess'; 'EEAE'; PROCESS_TERMINATE; FALSE; PID)
    if hProcess > 0
      CallProcedure('kernel32.dll'; 'TerminateProcess'; 'AEE'; hProcess; 0)
      Result = TRUE    
    endif
  endif

Эта функция открывает процесс по его PID, с уровнем доступа PROCESS_TERMINATE (необходимо для принудительного завершения процесса). Если открытие процесса прошло успешно (процесс с таким PID существует и правами доступа разрешено открытие данного процесса с уровнем доступа PROCESS_TERMINATE), то в результате вызова Windows API функции OpenProcess будет получено значение hProcess - дескриптор процесса. Если дескриптор получен - вызывается Windows API функция TerminateProcess, которая принудительно завершает процесс. Функция ExcelCheckAndKill возвращает TRUE если процесс найден и принудительно завершён, и FALSE - если процесс с указанным PID не найден или не удалось получить его дескриптор.

Архив с разработкой ниже по ссылке.
ExcelFunc.zip (3,63 Кб)