Đọc và ghi dữ liệu vào tệp Excel với PowerShell

Trong bài viết này, chúng tôi sẽ trình bày cách đọc và ghi dữ liệu từ trang tính Excel trực tiếp từ tập lệnh PowerShell. Bạn có thể sử dụng Excel cùng với PowerShell để kiểm kê và tạo các báo cáo khác nhau về máy tính, máy chủ, cơ sở hạ tầng, Active Directory, v.v.

Trước khi chỉ ra cách truy cập dữ liệu trong một ô Excel, bạn nên hiểu kiến ​​trúc của các lớp trình bày trong tệp Excel. Hình dưới đây cho thấy 4 lớp trình bày lồng nhau trong mô hình đối tượng Excel:

• Lớp ứng dụng - giao dịch với ứng dụng Excel đang chạy;
• Lớp WorkBook - có thể mở nhiều sổ làm việc (tệp Excel) cùng một lúc;
• Lớp Bảng tính - mỗi tệp XLSX có thể chứa nhiều trang tính;
• Lớp Phạm vi - tại đây, bạn có thể truy cập dữ liệu trong ô hoặc phạm vi ô cụ thể.

Cách đọc dữ liệu từ bảng tính Excel bằng PowerShell?

Hãy xem một ví dụ đơn giản về cách sử dụng PowerShell để truy cập dữ liệu trong tệp Excel có chứa danh sách nhân viên.

Đầu tiên, chạy ứng dụng Excel (lớp ứng dụng) trên máy tính của bạn bằng đối tượng COM:
$ExcelObj = New-Object -comobject Excel.Application

Sau khi chạy lệnh, Excel sẽ được khởi chạy trên máy tính của bạn ở chế độ nền. Để hiển thị cửa sổ Excel, hãy thay đổi tùy chọn Hiển thị thuộc tính của đối tượng COM:

$ExcelObj.visible=$true

$ExcelObj| fl

Sau đó, bạn có thể mở tệp Excel (sổ làm việc):

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

Mỗi tệp Excel có thể chứa một số trang tính. Hãy hiển thị danh sách các trang tính trong sổ làm việc Excel hiện tại:

$ExcelWorkBook.Sheets| fl Name, index

Sau đó, bạn có thể mở một trang tính mà bạn muốn (theo tên hoặc chỉ mục của nó):

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

Bạn có thể lấy tên của trang tính Excel hiện tại (đang hoạt động) bằng cách sử dụng lệnh sau:

$ExcelWorkBook.ActiveSheet | fl Name, Index

Sau đó, bạn có thể nhận giá trị từ các ô trong trang tính Excel. Bạn có thể sử dụng các phương pháp khác nhau để lấy giá trị ô trên trang tính Excel hiện tại:sử dụng một phạm vi, một ô, một cột hoặc một hàng. Xem các ví dụ về cách lấy dữ liệu từ cùng một ô bên dưới:

$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

Xuất Thông tin Người dùng Active Directory sang Bảng tính Excel bằng PowerShell

Hãy xem một ví dụ thực tế về cách truy cập dữ liệu Excel từ PowerShell. Giả sử, chúng ta muốn lấy một số thông tin từ Active Directory cho mỗi người dùng trong một tệp Excel. Ví dụ:số điện thoại của họ (thuộc tính phoneNumber), phòng ban và địa chỉ e-mail.

# 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)

Do đó, các cột chứa thông tin AD đã được thêm vào cho mỗi người dùng trong tệp Excel.

Hãy xem xét một ví dụ khác về việc tạo báo cáo bằng PowerShell và Excel. Giả sử, bạn muốn tạo báo cáo Excel về trạng thái dịch vụ Print Spooler trên tất cả các máy chủ miền.

# 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)

Bạn có thể sử dụng PowerShell để truy cập Excel trong nhiều tình huống khác nhau. Ví dụ:bạn có thể tạo báo cáo Active Directory tiện dụng hoặc tạo tập lệnh PowerShell để cập nhật dữ liệu AD từ Excel.