ShS's Blog

Just another sysadmin's weblog

Массовое заполнение телефонных номеров пользователей в AD. Использование в качестве источника данных рабочей книги Excel, доступ к которой производится при помощи COM-объекта Excel.Application

Posted by shs на 2010/07/08

Задачка была достаточно простая:

Имеется список сотрудников и их внутренних телефонных номеров, оформленный в виде «книги» MSExcel. Необходимо заполнить поле «Номер телефона» (атрибут telephoneNumber) для каждого пользователя в AD соответствующим значением, взятым из вышеназванного списка сотрудников.

 Для доступа к данным, содержащимся в excel’овском файле  можно использовать один из двух вариантов:

  1. Подцепить xls-файл, как ODBC-источник данных и, затем, при помощи ADODB обращаться к нему, как к таблице.
  2. Использовать COM-объект Excel.Application, для работы с файлом «силами и средствами» Excel.

 Первый вариант я раньше достаточно часто использовал при написании скриптов на Jscript/VBScript. Плюсы этого варианта такие: унифицированный интерфейс взаимодействия с любым источником данных, который может быть подключен в качестве ODBC-источника. Работа с источником данных ведется, как с таблицей(ами), состоящими из записей и полей. Для тех, кто когда-либо работал с БД, такой подход хорошо знаком. Минусом данного метода можно считать то, что данные (внутри рабочей книги excel) должны иметь регулярную структуру, а еще лучше — заранее отформатированы (созданы именованные диапазоны ячеек, к которым в последующем можно будет обращаться, как к таблицам при помощи SQL-like запросов). Это, в свою очередь, влечет за собой необходимость переформатирования (переопределения именованных диапазонов ячеек) после каждого внесения изменений в файл Excel или разработки специального программного интерфейса для внесения изменений в файл-источник данных, который будет осуществлять таковое переформатирование автоматически. В связи с тем, что список  сотрудников изначально не создавался изначально с прицелом на использование в качестве ODBC-источника, а так же то, что изменения в этот файл вносят неподготовленные пользователи, заставило меня отказаться от этого варианта.

Достоинства второго варианта – это отсутствие недостатков первого. ;) Посему, я решил попрактиковаться в использовании COM-объекта Excel.Application. Оказалось, что это совсем не сложно, но есть свои подводные камни.

Расскажу по порядку:
Первое, во что я уткнулся, было то, что нельзя тупо взять за образец пример на VBScript и переписать его на PoSh’ике. Дело в том, что при написании скриптов автоматизации Excel на VBScript часто пользуются таким фокусом, как пропуск ссылки на дефолтное свойство объекта. Например, в программе на VBScript можно запросто опустить упоминание свойства по умолчанию и вместо Set c = b.Worksheets.Item(1) писать так Set c = b.Worksheets(1). Причем, используется этот прием так часто, что начинаешь забывать о том, что Worksheets(1) на самом деле есть не что иное, как сокращенная запись Worksheets.Item(1). При записи на PowerShell любые свойства объекта необходимо указывать явно!

Второе (и самое противное) было то, что метод Quit() объекта Excel.Application” почему-то не приводил к его закрытию, и процесс Excel оставался висеть в памяти. Поначалу я поступал радикально: просто тупо убивал любой процесс с именем Excel (gps excel| stop-process). Но, сами понимаете, что этот метод очень и очень плох: во-первых, будут закрыты все приложения Excel (а не только объект автоматизации), во-вторых, это аварийное завершение Excel. Погуглив, обнаружил следующую kb: kb317109. Почитал про Marshal.ReleaseComObject решил попробовать предложенный метод: «Чтобы убедиться, что вызываемая оболочка CLR и исходный COM-объект освобождены, необходимо создать цикл, вызывающий этот метод до тех пор, пока возвращенная ссылка не будет равна нулю.»

Добавил в скрипт такую строку: While ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) -gt 0){} Но во время отладки обнаружил, что, не смотря на то, что последней итерацией вызова ReleaseComObject должна быть та, которая возвращает значение 0, на самом деле это не приводило к освобождению COM-объекта.  Попробовал вызвать метод еще раз, и в результате метод вернул мне значение -1 (?!), а excel исчез из списка процессов. Удивившись сему явлению, я  заменил gt на ge, получив While ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) ge 0){}, и уже было отправился пить пиво ;), но тут выяснилось, что и этот вариант работает не всегда. Процесс excel то завершался, то не завершался. Никакой системы в этом его поведении мне уловить не удавалось :( . Я уже начал думать о том, что «пришло время впадать в отчаяние» ;), но google в очередной раз не дал мне этого сделать ;) В блоге Scripting Guys я обнаружил запись о том, что, оказывается, если для запуска скрипта, работающего с COM-объектом Excel.Application, используется  PowerShell ISE (что, собственно говоря, и имело место быть в моем случае), то вызов метода Quit() этого объекта не приводит к немедленному освобождению памяти. Но можно подтолкнуть систему к активным действиям по освобождению памяти, для этого нужно обнулить объект и принудительно вызвать процесс сборки мусора, что и было проделано. В результате процесс завершения работы с объектов автоматизации Excel.Application выглядит следующим образом:

#Закрываем книгу Excel
#$objWorkbook.Close()
$objExcel.Workbooks.Close()
#Выходим из Excel (вернее даем команду на выход из Excel)
$objExcel.Quit()
#обнуляем объект
$objExcel = $null
#запускаем принудительную сборку мусора для освобождения памяти и окончательного завершения процесса
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Ну, а теперь пришло время показать весь скрипт целиком:

############################################################
# ADXLSSync.ps1 PowerShell shs 20100630
############################################################
cls
#Путь к справочнику сотрудников
$TelSPR="\\server\share\телефоны_сотрудников.xls"
#Имя листа (WorkSheet) рабочей книги Excel
$SheetName="номера"
#"Запускаем" Excel (создаем COM-объект Excel.Application)
$objExcel=New-Object -comobject Excel.Application
#выполняем открытие файла ("Рабочей книги") в Excel
$objWorkbook=$objExcel.Workbooks.Open($TelSPR)
#Номер колонки, содержащей ФИО
$ColumnName=3
#Номер колонки, содержащей первую часть № телефона
$ColumnTel1=4
#Номер колонки, содержащей вторую часть № телефона
#(некоторые номера телефонов могут занимать 2 ячейки)
$ColumnTel2=5
#
#Константа для использования с методом SpecialCells
$xlCellTypeLastCell = 11
#
#Получаем номер последней используемой строки на листе
$TotalsRow=$objWorkbook.Worksheets.Item($SheetName).UsedRange.SpecialCells($xlCellTypeLastCell).CurrentRegion.Row
#
#Выполняем перебор строк в открытом файле Excel
for ($Row=1;$Row -le $TotalsRow; $Row++) {
    #Сохраняем в переменных значения соответствующих ячеек
    $UserName=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnName).Value()
    $Tel1=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnTel1).Value()
    $Tel2=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnTel2).Value()
    #Если эти значения не нулевые, то...
    if (($UserName -ne $null) -and ($Tel1 -match "\d+")) {
        #Сформируем новый тел. номер
        if ($Tel2) {$Tel="($Tel1)$Tel2"}
        else {$Tel=$Tel1}
        #Работаем только с теми пользователями, чье имя состоит из 3х слов (т.е. полное ФИО),
        #если имя состоит менее, чем из 3х слов, то такую запись игнорируем
        if ($UserName -match "(?:\w+\s+){2}\w+") {
            #Пропишем пользователю тел. номер, если пользователь не отключен (enabled)
            try {
                Get-QADUser $UserName -enabled | Set-QADUser -PhoneNumber $Tel|Out-Null #-WhatIf
                $ReportString=("{0,-35} <-> {1,10}" -f $UserName, $Tel)
            }
            catch {
                $ReportString=("{0,-35} <-> {1,10}" -f $UserName, "Ошибка записи номера телефона")
            }
        Write-Host $ReportString
        }
    }
}
#Закрываем книгу Excel
#$objWorkbook.Close()
$objExcel.Workbooks.Close()
#Выходим из Excel (вернее даем команду на выход из Excel)
$objExcel.Quit()
#обнуляем объект
$objExcel = $null
#запускаем принудительную сборку мусора для освобождения памяти и окончательного завершения процесса
[gc]::collect()
[gc]::WaitForPendingFinalizers()
Реклама

комментария 2 to “Массовое заполнение телефонных номеров пользователей в AD. Использование в качестве источника данных рабочей книги Excel, доступ к которой производится при помощи COM-объекта Excel.Application”

  1. Fray said

    Очень вовремя нашел! Мне как раз такую задачу поставили =)

    PS: Может и ссылочками на блоги поменяемся раз уж мы с одного форума? ;)

    • shs said

      Давай, поменяемся. Только, вот, на форумах sysadmins (как ru, так и su) я уже почти не появляюсь.

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

      su — администрация форума полагает, что может позволить себе не соблюдать правила собственного форума. Ну, а г-н stellar — просто хам с больным себялюбием. Сам формат форума все больше скатывается к общению в курилке. (Кстати, за последнее время с него так же ушли Camelot и Xaegr. )
      Так что, найти меня сейчас можно только на технетовском форуме.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

 
%d такие блоггеры, как: