PowerShell을 사용하여 Excel 파일에 데이터 읽기 및 쓰기

이 기사에서는 PowerShell 스크립트에서 직접 Excel 워크시트의 데이터를 읽고 쓰는 방법을 보여줍니다. PowerShell과 함께 Excel을 사용하여 컴퓨터, 서버, 인프라, Active Directory 등에 대한 인벤토리를 작성하고 다양한 보고서를 생성할 수 있습니다.

Excel 셀의 데이터에 액세스하는 방법을 보여주기 전에 Excel 파일의 프레젠테이션 계층 아키텍처를 이해하는 것이 좋습니다. 아래 그림은 Excel 개체 모델의 4개의 중첩된 프레젠테이션 레이어를 보여줍니다.

• 애플리케이션 계층 – 실행 중인 Excel 앱을 다룹니다.
• 통합 문서 계층 – 여러 통합 문서(Excel 파일)를 동시에 열 수 있음
• 워크시트 레이어 – 각 XLSX 파일에는 여러 시트가 포함될 수 있습니다.
• 범위 레이어 – 여기에서 특정 셀 또는 셀 범위의 데이터에 액세스할 수 있습니다.

PowerShell을 사용하여 Excel 스프레드시트에서 데이터를 읽는 방법

PowerShell을 사용하여 직원 목록이 포함된 Excel 파일의 데이터에 액세스하는 방법의 간단한 예를 살펴보겠습니다.

먼저 COM 개체를 사용하여 컴퓨터에서 Excel 앱(응용 프로그램 계층)을 실행합니다.
$ExcelObj = New-Object -comobject Excel.Application

명령을 실행하면 백그라운드에서 컴퓨터의 Excel이 실행됩니다. Excel 창을 표시하려면 표시를 변경하세요. COM 개체의 속성:

$ExcelObj.visible=$true

$ExcelObj| fl

그런 다음 Excel 파일(통합 문서)을 열 수 있습니다.

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

각 Excel 파일에는 여러 워크시트가 포함될 수 있습니다. 현재 Excel 통합 문서에 있는 워크시트 목록을 표시해 보겠습니다.

$ExcelWorkBook.Sheets| fl Name, index

그런 다음 원하는 시트를 열 수 있습니다(이름 또는 색인으로):

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

다음 명령을 사용하여 현재(활성) Excel 워크시트의 이름을 가져올 수 있습니다.

$ExcelWorkBook.ActiveSheet | fl Name, Index

그런 다음 Excel 워크시트의 셀에서 값을 가져올 수 있습니다. 범위, 셀, 열 또는 행을 사용하여 다양한 방법을 사용하여 현재 Excel 워크시트에서 셀 값을 가져올 수 있습니다. 아래에서 동일한 셀에서 데이터를 가져오는 방법의 예를 참조하세요.

$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에서 일부 정보를 가져오려고 한다고 가정합니다. 예를 들어 전화번호(전화번호 속성), 부서 및 이메일 주소 .

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from 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
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

그 결과 Excel 파일에 각 사용자에 대해 AD 정보가 포함된 열이 추가되었습니다.

PowerShell과 Excel을 사용하여 보고서를 만드는 또 다른 예를 살펴보겠습니다. 모든 도메인 서버에서 인쇄 스풀러 서비스 상태에 대한 Excel 보고서를 작성한다고 가정합니다.

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$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
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$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++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

PowerShell을 사용하여 다양한 시나리오에서 Excel에 액세스할 수 있습니다. 예를 들어 편리한 Active Directory 보고서를 만들거나 PowerShell 스크립트를 만들어 Excel에서 AD 데이터를 업데이트할 수 있습니다.