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

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

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

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

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

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

ขั้นแรก ให้เรียกใช้แอป Excel (เลเยอร์แอปพลิเคชัน) บนคอมพิวเตอร์ของคุณโดยใช้วัตถุ COM:
$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

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

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

# 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 เกี่ยวกับสถานะบริการตัวจัดคิวงานพิมพ์บนเซิร์ฟเวอร์โดเมนทั้งหมด

# 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