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

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

Имеется список сотрудников и их внутренних телефонных номеров, оформленный в виде «книги» 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()

29 Comments

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

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

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

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

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

  2. >>1.Подцепить xls-файл, как ODBC-источник данных и, затем, при помощи ADODB обращаться к нему, >>как к таблице.

    Извините, а можно с этого места поподробней?
    Дело в том, что не хочется мне устанавливать на сервера Excel…
    В то же время потребность читать иногда там “базы” нарисованные в Excel есть иногда.
    Хочу воспользоваться AccessDatabaseEngine .
    Но что-то не могу найти документации по PS где было бы описано как подцепиться к базе Excel через ODBC… Все примерчики которые вижу на PS посвящены работе с AD, в частности и как с источником данных. =(

  3. Спасибо за информацию о выгрузке COM объекта из под ISE. Честно говоря, пока не наткнулся на эту проблему, но наткнулся бы, это факт.

    Сам подобную же задачу решал через csv файл. Всё-таки, excel – не самый дешёвый продукт, посему для таких простых таблиц планирую Open Office, а с ним интеграции планирую только через csv.

    P.S. Кстати, опираясь на номера телефонов, можно создать удобный (естественно – имхо) сервис для своих сотрудников, вынужденных постоянно диктовать по телефону свой адрес почты: группы рассылок по абонентам телефонных номеров, чтобы просто по телефону диктовать 555@novgaro.ru (ну – например) :-).

  4. Добрый день!
    А не поможете сделать скрипт, только с параметром employeeID. Есть документ xls, где указаны имена пользователей и соответствующие им поля с параметром. Нужно значения атрибута для каждого пользователя влить в домен. спасибо

  5. надо будет сделатать так:
    где-то в скрипте считываем из excel значение employeeID в переменную $employeeID
    затем присваиваем…

    Для присваивания значения атрибуту employeeID надо будет заменить
    Get-QADUser $UserName -enabled | Set-QADUser -PhoneNumber $Tel|Out-Null
    на
    Get-QADUser $UserName -enabled | Set-QADUser -ObjectAttributes @{employeeID=$employeeID}|Out-Null
    как-то так

  6. Приветствую!
    Спасибо за пост – очень информативный и полезный!
    Единственный вопрос – какая версия экселя использовалась в данном примере?
    Попытался применить его в домене 2008-го и версия экселя была 2010.
    Получилось, что TotalsRow всегда равен единице в моей среде. Очевидно, метод определения занятых строк в экселевском файле отличается?
    Поэтому ради интереса хотел уточнить версию домена и экселя в этом примере.
    Спасибо!

    • В моем случае был Office 2003. Возможно что-то изменилось в последующих версиях Office. Если у вас есть возможность отказаться от работы через Excel, то, наверное, это будет самым правильным выбором (например, работать с результатом экспорта xls-файла в csv-формат). Иначе, читайте документацию об объектной модели Office, которую вы используете (соответствующие файлы справки включены в стандартную поставку и находятся в паке, в которую вы установили Office). Попробуйте выполнить необходимые действия при помощи горячих клавиш (например, переход к последним занятым ячейкам можно произвести при помощи +), предварительно включив запись макроса, а затем проанализировать полученный код для того, чтобы понять какие объекты, для чего и как можно использовать.

  7. #Получаем номер последней используемой строки на листе
    $TotalsRow=$objWorkbook.Worksheets.Item($SheetName).UsedRange.SpecialCells($xlCellTypeLastCell).CurrentRegion.Row

    не работает данная процедура! переменная постоянно остается со значением равным 1

    • у меня на win xp sp3 и MSOffice 2k3 sp3 работает. Можете сами попробовать записать макрос: Для этого включите запись макросов а затем нажмите + (это команда перехода к последней использованной ячейке на листе. Остановите хзапись макроса и просмотрите его код.

  8. Доброго времени суток!
    У меня проблема такая что надо внести массово не телефоны, а почту. И на сервере АД нет ничего кроме Windows 2008 R2 x64 и поднятых ролей. Как быть с этим, подскажите пожалуйста!

  9. Добрый день!
    Я пока что в скриптах понимаю только то, что ничего в них не понимаю, но пытаюсь разобраться.
    Насколько я понял, Ваш скрипт меняет телефоны во всём домене. А можно как-то указать конкретный OU, в котором будут производиться изменения? (как в dsquery)

    • Нет, этот скрипт работает по другому принципу: перебирает записи в excel’овской таблице, содержащей имена и телефоны пользователей и, если находит такового пользователя в AD, то проставляет ему номер телефона из этой самой excel’овской таблички.

      • Спасибо большое за скрипт, очень пригодился. Тоже столкнулся с проблемой определения последней строки в Excell 2007. В строке определения переменной $TotalsRow ($TotalsRow=$objWorkbook.Worksheets.Item($SheetName).UsedRange.SpecialCells($xlCellTypeLastCell).CurrentRegion.Row) нужно убрать .CurrentRegion, т.е. строка должна быть $TotalsRow=$objWorkbook.Worksheets.Item($SheetName).UsedRange.SpecialCells($xlCellTypeLastCell).Row тогда всё работает

  10. Однако, фокус с уничтожение объекта и освобождение ресурсов, им занятых, не прошёл:

    $decoder = new-object -typeName System.Windows.Media.Imaging.TiffBitmapDecoder -argumentList `
        ( new-object -type System.Uri -argumentList ( 'X:ПорталыАутсорсингПроцессыИзготовление офисовЧертежи2СТС10У.24.04.00.000.спКДСТС10У.24.04.00.000.сп.tif' ) ) `
        , ( [System.Windows.Media.Imaging.BitmapCreateOptions]::None ) `
        , ( [System.Windows.Media.Imaging.BitmapCacheOption]::None ) `
    ;
    $encoder = new-object -typeName System.Windows.Media.Imaging.TiffBitmapEncoder;
    
    foreach ($frame in $decoder.Frames) {
        $encoder.Frames.Add( [System.Windows.Media.Imaging.BitmapFrame]::Create(
            ( new-object -typeName System.Windows.Media.Imaging.FormatConvertedBitmap -argumentList `
                ($frame.Clone()) `
                , ([System.Windows.Media.PixelFormats]::BlackWhite) `
                , ([System.Windows.Media.Imaging.BitmapPalettes]::BlackAndWhite) `
                , 1.0 `
            )
        ) );
    };
    
    $decoder = $null;
    [gc]::collect();
    [gc]::WaitForPendingFinalizers();
    

    суть в том, что и после выполнения последний 3х строк файл остаётся занятым! до выгрузки ISE. Не подскажете, не сталкивались с подобной проблемой объектов dotNet?

    • Либо не сталкивался, либо не заметил. А, что если и $encoder занулить ($encoder=$null) до начала процесса сбора мусора?

  11. Добрый день!

    Не могли бы помочь, оочень нужна помощь, PoShe только начинаю изучать и возникла задача, назначения аргумента employeeID всем пользователям в AD, пользователей больше 100. Есть документ xls, в нём указаны в одной строке имена пользователей, в другой параметр employeeID. Я чуть изменил скрипт согласно Вашим рекомендациям, но что-то не работает, как итог не меняет значения в AD, закомментил те блоки которые посчитал не нужными,можете пожалуйста помочь привести его в боевое состояние… Огромное спасибо!

    ############################################################
    # ADXLSSync.ps1 PowerShell shs 20100630
    ############################################################
    #Путь к справочнику сотрудников
    $TelSPR=”C:\bat\3.xls”
    #Имя листа (WorkSheet) рабочей книги Excel
    $SheetName=”3″
    #”Запускаем” Excel (создаем COM-объект Excel.Application)
    $objExcel=New-Object -comobject Excel.Application
    #выполняем открытие файла (“Рабочей книги”) в Excel
    $objWorkbook=$objExcel.Workbooks.Open($TelSPR)
    #Номер колонки, содержащей ФИО
    $ColumnName=3
    #Номер колонки, содержащей первую часть № телефона (eID)
    $ColumnemployeeID=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()
    $employeeID=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnemployeeID).Value()
    ###$Tel2=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnTel2).Value()
    #Если эти значения не нулевые, то…
    if (($UserName -ne $null) -and ($employeeID -match “\d+”)) {
    #Сформируем новый тел. номер
    ###if ($Tel2) {$Tel=”($Tel1)$Tel2″}
    ###else {$Tel=$Tel1}
    ###if ($Tel2) {$Tel=”($Tel1)$Tel2″}
    ###else {$Tel=$Tel1}
    #Работаем только с теми пользователями, чье имя состоит из 3х слов (т.е. полное ФИО),
    #если имя состоит менее, чем из 3х слов, то такую запись игнорируем
    #if ($UserName -match “(?:\w+\s+){1}\w+”) {
    #Пропишем пользователю тел. номер, если пользователь не отключен (enabled)
    try {
    ###Get-QADUser $UserName -enabled | Set-QADUser -PhoneNumber $Tel|Out-Null #-WhatIf

    Get-QADUser $UserName -enabled | Set-QADUser -ObjectAttributes @{employeeID=$employeeID}|Out-Null #-WhatIf
    $ReportString=(“{0,-35} {1,10}” -f $UserName, $employeeID)
    }
    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()

    • Запустите скрипт в отладчике для того, чтобы посмотреть то, как он выполняется, а также просмотреть значения переменных в процессе работы скрипта. Ну, или повтыкайте в скрит команд Write-Host для вывода промежуточных значений переменных. Тогда вы сможете понять, почему скрипт не выполняет то, что вы задумали.

  12. Здравствуйте!
    Подскжите как реализовать…
    Есть Excel. Таблица с данными.
    Например 10 колонок и 20 строк
    Есть веб форма, которую нужно заполнить данными из этой таблицы, как реализовать такое: скорпировать данные всех яеек одной строки, выбранной, и заткем устанавливая курсор в нужное поле вставлять скопированные данные…

    • На вскидку не подскажу.
      Обычно я делаю так:
      Включаю запись макроса.
      Выполняю требуемые действия.
      Останавливаю запись макроса. Смотрю, что получилось. Понимаю, какие объекты потребуются мне для решения поставленной задачи. Читаю справку (справочные файлы) по объектам Excel, которая входит в состав пакета MSOffice.

  13. Уважаемые помогите создать скрипт,на днях прислали файл блокнота в нём 400тыщ. номеров сейчас это нужно упорядочить в 1 столбик в exele.
    Заранее благодарен.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.