อ่านและเขียนข้อมูลในไฟล์ Excel ด้วย PowerShell

ในบทความนี้ เราจะแสดงวิธีอ่านและเขียนข้อมูลจากเวิร์กชีต Excel โดยตรงจากสคริปต์ PowerShell คุณสามารถใช้ Excel ร่วมกับ PowerShell เพื่อตรวจสอบสินทรัพย์และสร้างรายงานต่างๆ บนคอมพิวเตอร์ เซิร์ฟเวอร์ โครงสร้างพื้นฐาน Active Directory ฯลฯ

คุณสามารถเข้าถึงชีต Excel จาก PowerShell ผ่านวัตถุ COM แยกต่างหาก (Component Object Model) ซึ่งต้องมีการติดตั้ง Excel บนคอมพิวเตอร์

ก่อนแสดงวิธีเข้าถึงข้อมูลในเซลล์ Excel ควรเข้าใจสถาปัตยกรรมของเลเยอร์การนำเสนอในไฟล์ Excel รูปด้านล่างแสดงเลเยอร์การนำเสนอ 4 ชั้นที่ซ้อนกันใน Excel object model:

• Application Layer – จัดการกับแอป Excel ที่กำลังทำงาน;
• WorkBook Layer – อาจมีสมุดงาน (ไฟล์ Excel) เปิดหลายเล่มพร้อมกัน;
• WorkSheet Layer – ไฟล์ XLSX แต่ละไฟล์อาจมีหลายชีต;
• Range Layer – ที่นี่คุณสามารถเข้าถึงข้อมูลในเซลล์เฉพาะหรือช่วงเซลล์ได้

วิธีอ่านข้อมูลจากสเปรดชีต Excel โดยใช้ PowerShell

มาดูตัวอย่างง่ายๆ ของการใช้ PowerShell เข้าถึงข้อมูลในไฟล์ Excel ที่มีรายชื่อพนักงาน

ขั้นแรก ให้รันแอป Excel (application layer) บนคอมพิวเตอร์ของคุณโดยใช้ COM object:
$ExcelObj = New-Object -comobject Excel.Application

หลังจากรันคำสั่ง Excel จะถูกเปิดบนคอมพิวเตอร์ของคุณในเบื้องหลัง เพื่อแสดงหน้าต่าง Excel ให้เปลี่ยนคุณสมบัติ Visible ของ COM object:

$ExcelObj.visible=$true

คุณสามารถแสดงคุณสมบัติ Excel object ทั้งหมดได้ดังนี้:

$ExcelObj| fl

จากนั้นคุณสามารถเปิดไฟล์ Excel (workbook):

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

ไฟล์ Excel แต่ละไฟล์อาจมีเวิร์กชีตหลายชีต มาแสดงรายการเวิร์กชีตในสมุดงาน Excel ปัจจุบัน:

$ExcelWorkBook.Sheets| fl Name, index

จากนั้นคุณสามารถเปิดชีตที่คุณต้องการ (โดยชื่อหรือดัชนี):

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

คุณสามารถรับชื่อของเวิร์กชีต Excel ปัจจุบัน (active) ได้โดยใช้คำสั่งนี้:

$ExcelWorkBook.ActiveSheet | fl Name, Index

จากนั้นคุณสามารถรับค่าจากเซลล์ในเวิร์กชีต Excel คุณสามารถใช้วิธีต่างๆ เพื่อรับค่าเซลล์บนเวิร์กชีต Excel ปัจจุบัน: โดยใช้ 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

การส่งออกข้อมูลผู้ใช้ Active Directory ไปยังสเปรดชีต Excel โดยใช้ PowerShell

มาดูตัวอย่างเชิงปฏิบัติของการเข้าถึงข้อมูล Excel จาก PowerShell สมมติว่าเราต้องการรับข้อมูลบางอย่างจาก Active Directory สำหรับแต่ละผู้ใช้ในไฟล์ Excel ตัวอย่างเช่น หมายเลขโทรศัพท์ (แอตทริบิวต์ telephoneNumber), แผนก และที่อยู่อีเมล

เพื่อรับข้อมูลเกี่ยวกับแอตทริบิวต์ AD user เราจะใช้ cmdlet Get-ADUser จากโมดูล PowerShell 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)

ผลคือ คอลัมน์ที่มีข้อมูล AD ถูกเพิ่มเข้ามาสำหรับแต่ละผู้ใช้ในไฟล์ Excel

มาพิจารณาอีกตัวอย่างหนึ่งของการสร้างรายงานโดยใช้ PowerShell และ Excel สมมติว่าคุณต้องการสร้างรายงาน Excel เกี่ยวกับสถานะของ Print Spooler service บนเซิร์ฟเวอร์โดเมนทั้งหมด

คุณสามารถใช้ cmdlet Get-ADComputer เพื่อรับรายการเซิร์ฟเวอร์ใน Active Directory และใช้ WinRM Invoke-Command cmdlet เพื่อตรวจสอบสถานะบริการบนเซิร์ฟเวอร์จากระยะไกล

# 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 เพื่ออัปเดตข้อมูล AD จาก Excel

ตัวอย่างเช่น คุณสามารถขอให้พนักงานแผนก HR เก็บบันทึกผู้ใช้ใน Excel จากนั้นโดยใช้สคริปต์ PowerShell และ cmdlet Set-ADUser พนักงานสามารถอัปเดตข้อมูลผู้ใช้ใน AD โดยอัตโนมัติได้ (เพียงแค่มอบสิทธิ์ให้พนักงานเปลี่ยนแอตทริบิวต์ AD user และแสดงวิธีรันสคริปต์ PowerShell) ดังนั้นคุณสามารถเก็บสมุดที่อยู่อัปเดตด้วยหมายเลขโทรศัพท์ ตำแหน่งงาน และแผนกที่เกี่ยวข้อง