Trong bài viết này chúng tôi sẽ chỉ cách đọc và ghi dữ liệu từ worksheet Excel trực tiếp từ script PowerShell. Bạn có thể dùng Excel cùng PowerShell để inventory và tạo các báo cáo khác nhau trên máy tính, server, cơ sở hạ tầng, Active Directory, v.v.
Bạn có thể truy cập Excel sheet từ PowerShell qua một COM object (Component Object Model) riêng biệt. Điều này yêu cầu Excel phải được cài trên máy tính.
Trước khi chỉ cách truy cập dữ liệu trong ô Excel, đáng giá để hiểu kiến trúc các lớp trình bày trong file Excel. Hình dưới显示 4 lớp trình bày lồng nhau trong Excel object model:
- Application Layer – xử lý ứng dụng Excel đang chạy;
- WorkBook Layer – nhiều workbook (file Excel) có thể mở cùng lúc;
- WorkSheet Layer – mỗi file XLSX có thể chứa vài sheet;
- Range Layer – ở đây bạn truy cập dữ liệu trong ô cụ thể hoặc phạm vi ô.
Cách đọc dữ liệu từ Spreadsheet Excel bằng PowerShell?
Hãy xem ví dụ đơn giản cách dùng PowerShell truy cập dữ liệu trong file Excel chứa danh sách nhân viên.
Trước tiên, chạy ứng dụng Excel (application layer) trên máy tính dùng COM object:
$ExcelObj = New-Object -comobject Excel.Application
Sau khi chạy lệnh, Excel sẽ khởi động trên máy tính ở background. Để hiển thị cửa sổ Excel, thay đổi thuộc tính Visible của COM object:
$ExcelObj.visible=$true
Bạn có thể hiển thị tất cả thuộc tính đối tượng Excel như sau:
$ExcelObj| fl
Sau đó bạn có thể mở file Excel (một workbook):
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
Mỗi file Excel có thể chứa nhiều worksheet. Hãy hiển thị danh sách worksheet trong workbook Excel hiện tại:
$ExcelWorkBook.Sheets| fl Name, index
Sau đó bạn có thể mở sheet bạn muốn (theo tên hoặc index):
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")
Bạn có thể lấy tên worksheet hiện tại (active) bằng lệnh:
$ExcelWorkBook.ActiveSheet | fl Name, Index
Sau đó bạn có thể lấy giá trị từ ô trong worksheet Excel. Bạn có thể dùng các phương pháp khác nhau để lấy giá trị ô trên worksheet hiện tại: dùng range, cell, column hoặc row. Xem ví dụ cách lấy dữ liệu từ cùng một ô:
$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 User Active Directory ra Spreadsheet Excel bằng PowerShell
Hãy xem ví dụ thực tế cách truy cập dữ liệu Excel từ PowerShell. Giả sử, chúng ta muốn lấy thông tin từ Active Directory cho mỗi user trong file Excel. Ví dụ, số điện thoại (thuộc tính telephoneNumber), phòng ban và địa chỉ email.
Để lấy thông tin thuộc tính AD user, chúng ta sẽ dùng cmdlet Get-ADUser từ module PowerShell Active Directory.
# Import Active Directory module vào session PowerShell
import-module activedirectory
# Mở Excel workbook trước:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Lấy số hàng đã điền trong worksheet XLSX
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Lặp qua tất cả hàng trong Cột 1 bắt đầu từ Hàng 2 (ô này chứa domain username)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Lấy giá trị thuộc tính user trong AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Điền dữ liệu nhận từ AD vào ô
$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
}
# Lưu file XLS và đóng Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)
Kết quả, các cột chứa thông tin AD đã được thêm cho mỗi user trong file Excel.
Hãy xem ví dụ khá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ả server domain.
Bạn có thể dùng cmdlet Get-ADComputer để lấy danh sách server trong Active Directory, và dùng Invoke-Command WinRM để kiểm tra trạng thái dịch vụ từ xa trên các server.
# Tạo đối tượng Excel
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Thêm workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Đổi tên worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Điền đầu bảng
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Làm đậm đầu bảng, set font size và độ rộng cột
$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
# Lấy danh sách tất cả Windows Server trong domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Kết nối từng máy và lấy trạng thái dịch vụ
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# Điền dữ liệu từ server vào ô 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++
}
# Lưu báo cáo và đóng Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)
Bạn có thể dùng PowerShell truy cập Excel trong nhiều kịch bản. Ví dụ, bạn có thể tạo báo cáo Active Directory tiện lợi hoặc tạo script PowerShell cập nhật dữ liệu AD từ Excel.
Ví dụ, bạn có thể nhờ nhân viên HR giữ sổ đăng ký user trong Excel. Sau đó dùng script PowerShell và cmdlet Set-ADUser, nhân viên có thể tự động cập nhật info user trong AD (chỉ cần delegate quyền thay đổi thuộc tính AD user cho nhân viên và chỉ cách chạy script PowerShell). Như vậy bạn có thể duy trì address book cập nhật với số điện thoại, chức danh và phòng ban liên quan.