이 글에서는 PowerShell 스크립트에서 직접 Excel 워크시트의 데이터를 읽고 쓰는 방법을 소개합니다. Excel과 PowerShell을 함께 활용하면 컴퓨터, 서버, 인프라, Active Directory 등에 대한 인벤토리 관리와 각종 보고서를 손쉽게 생성할 수 있습니다.
PowerShell에서 Excel에 접근하는 원리
PowerShell에서 Excel 시트에 접근하려면 별도의 COM 개체(Component Object Model)를 사용해야 합니다. 이 방식은 해당 컴퓨터에 Excel이 설치되어 있어야 한다는 점에 유의하세요.
Excel 셀의 데이터에 접근하는 방법을 살펴보기 전에, Excel 파일의 프레젠테이션 계층 구조를 이해하는 것이 중요합니다. 아래 그림과 같이 Excel 개체 모델은 4개의 중첩된 계층으로 구성되어 있습니다.
- Application 계층 – 실행 중인 Excel 애플리케이션 자체를 다룹니다.
- WorkBook 계층 – 여러 개의 통합 문서(Excel 파일)를 동시에 열 수 있습니다.
- WorkSheet 계층 – 하나의 XLSX 파일에는 여러 개의 시트가 포함될 수 있습니다.
- Range 계층 – 특정 셀 또는 셀 범위의 데이터에 접근합니다.
PowerShell로 Excel 스프레드시트 데이터 읽는 방법
직원 목록이 담긴 Excel 파일의 데이터에 PowerShell로 접근하는 간단한 예제를 살펴보겠습니다.
먼저 COM 개체를 사용해 컴퓨터에서 Excel 애플리케이션(Application 계층)을 실행합니다.
$ExcelObj = New-Object -comobject Excel.Application
명령을 실행하면 Excel이 백그라운드에서 실행됩니다. Excel 창을 화면에 표시하려면 COM 개체의 Visible 속성을 변경하면 됩니다.
$ExcelObj.visible=$true
Excel 개체의 모든 속성은 다음과 같이 확인할 수 있습니다.
$ExcelObj | fl
그다음 Excel 파일(통합 문서)을 엽니다.
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
하나의 Excel 파일에는 여러 개의 워크시트가 포함될 수 있습니다. 현재 통합 문서의 시트 목록을 표시해 보겠습니다.
$ExcelWorkBook.Sheets | fl Name, Index
원하는 시트를 이름이나 인덱스로 열 수 있습니다.
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")
현재 활성화된 Excel 워크시트의 이름은 다음 명령으로 확인할 수 있습니다.
$ExcelWorkBook.ActiveSheet | fl Name, Index
이제 워크시트의 셀에서 값을 가져올 차례입니다. 현재 워크시트의 셀 값은 범위(Range), 셀(Cell), 열(Column), 행(Row) 등 다양한 방법으로 가져올 수 있습니다. 아래는 동일한 셀에서 데이터를 읽어오는 여러 가지 방법의 예입니다.
$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text
PowerShell로 Active Directory 사용자 정보를 Excel로 내보내기
PowerShell에서 Excel 데이터에 접근하는 실용적인 예제를 살펴보겠습니다. Excel 파일에 있는 각 사용자에 대해 Active Directory에서 정보를 가져오고 싶다고 가정해 보겠습니다. 예를 들어 전화번호(t telephoneNumber 속성), 부서, 이메일 주소 등을 가져올 수 있습니다.
AD 사용자 속성 정보를 가져오려면 PowerShell Active Directory 모듈의 Get-ADUser cmdlet을 사용합니다.
# PowerShell 세션에 Active Directory 모듈 가져오기
import-module activedirectory
# 먼저 Excel 통합 문서 열기
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# XLSX 워크시트에 데이터가 입력된 행 수 가져오기
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# 2행부터 1열의 모든 행을 순회(이 셀에는 도메인 사용자 이름이 들어 있음)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# AD에서 사용자 속성 값 가져오기
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail | select-object name,telephoneNumber,department,mail
# AD에서 받은 데이터로 셀 채우기
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# XLS 파일 저장 후 Excel 닫기
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)
스크립트를 실행하면 Excel 파일의 각 사용자 행에 AD 정보가 담긴 열이 추가됩니다.
도메인 서버 서비스 상태 보고서 자동 생성하기
PowerShell과 Excel을 활용한 또 다른 보고서 작성 예제를 살펴보겠습니다. 도메인의 모든 서버에 대해 Print Spooler 서비스 상태를 담은 Excel 보고서를 만들고 싶다고 가정해 보겠습니다.
Get-ADComputer cmdlet으로 Active Directory에서 서버 목록을 가져오고, WinRM의 Invoke-Command cmdlet을 사용해 각 서버의 서비스 상태를 원격으로 확인할 수 있습니다.
# Excel 개체 생성
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# 통합 문서 추가
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# 워크시트 이름 변경
$ExcelWorkSheet.Name = 'Spooler Service Status'
# 표 머리글 채우기
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# 표 머리글을 굵게 표시하고 글꼴 크기와 열 너비 설정
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# 도메인의 모든 Windows 서버 목록 가져오기
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# 각 컴퓨터에 연결하여 서비스 상태 확인
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# 서버에서 받은 데이터로 Excel 셀 채우기
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# 보고서 저장 후 Excel 닫기
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)
PowerShell과 Excel 연동의 다양한 활용 사례
이처럼 PowerShell은 다양한 시나리오에서 Excel에 접근할 수 있습니다. 예를 들어 유용한 Active Directory 보고서를 만들거나, Excel 데이터로 AD 정보를 업데이트하는 PowerShell 스크립트를 작성할 수도 있습니다.
실제 활용 예로, 인사(HR) 담당자에게 Excel에서 사용자 명부를 관리하도록 할 수 있습니다. 그런 다음 PowerShell 스크립트와 Set-ADUser cmdlet을 사용하면 담당자가 AD의 사용자 정보를 자동으로 업데이트할 수 있습니다(AD 사용자 속성 변경 권한을 위임하고 PowerShell 스크립트 실행 방법만 안내하면 됩니다). 이렇게 하면 최신 전화번호, 직책, 부서 정보를 담은 주소록을 항상 최신 상태로 유지할 수 있습니다.