Xuất Excel trong PowerShell

Mô-đun ImportExcel có sẵn trong thư viện PowerShell. Được duy trì bởi MVP Doug Finke, mô-đun cho phép các nhà phát triển nhập và xuất dữ liệu sang Excel mà không cần cài đặt ứng dụng trên máy trạm

Mặc dù Microsoft 365 đi kèm với một số báo cáo cơ bản nhưng nhiều tổ chức sử dụng tập lệnh PowerShell để tạo báo cáo bao gồm mọi thứ từ hoạt động của người dùng đến danh sách hộp thư. Thông thường, các tập lệnh sử dụng lệnh ghép ngắn Export-CSV để xuất báo cáo sang tệp CSV. Sau này, mọi người có thể sử dụng tệp CSV để tạo báo cáo đẹp hơn bằng Excel hoặc nhập dữ liệu vào Power BI. Tôi không thể đếm số lần tôi đã trải qua thói quen này. Ở phía đầu vào, lệnh ghép ngắn Import-CSV đưa dữ liệu vào PowerShell để xử lý, chẳng hạn như danh sách các hộp thư cần kiểm tra

Xuất sang Excel

Để kiểm tra mô-đun ImportExcel, tôi đã sử dụng một số tập lệnh báo cáo mà tôi đã thảo luận ở đây trong nhiều năm. Sau khi tham khảo Câu hỏi thường gặp, tôi bắt đầu bằng cách sử dụng tập lệnh để báo cáo dung lượng lưu trữ được các trang web OneDrive for Business sử dụng để tạo báo cáo, nằm trong danh sách PowerShell có tên là $Report. Để xuất sang Excel (và bắt đầu Excel với dữ liệu được tải nếu có ứng dụng), tất cả những gì tôi cần làm là

$Report | Export-Excel

Excel tải dữ liệu sau khi áp dụng kiểu bảng mặc định (Hình 1)

Hình 1. Kiểu Excel mặc định được mô-đun ImportExcel sử dụng

Để xuất sang bảng tính đã đặt tên, hãy thêm tên của nó vào lệnh

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx

Kiểu xuất này không áp dụng bất kỳ định dạng nào, vì vậy bạn sẽ có kết quả như Hình 2

Hình 2. Xuất sang tệp bảng tính đã đặt tên sử dụng một kiểu khác

Vì Excel hỗ trợ nhiều trang tính trong một sổ làm việc nên bạn có thể thêm các trang tính mới vào sổ làm việc hiện có. Ví dụ

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -Append -WorksheetName "New data"

Một phiên bản phức tạp hơn của lệnh sẽ tạo một sổ làm việc với một trang tính được đặt tên, bảng được đặt tên, tiêu đề và một kiểu khác. Tên của các kiểu bảng có sẵn sẽ hiển thị nếu bạn di chuột qua các kiểu được hiển thị trong tùy chọn Định dạng Excel dưới dạng bảng. Hình 3 cho thấy đầu ra của lệnh này

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -WorksheetName "May 2022" -Title "OneDrive for Business Storage Consumption" -TitleBold -TableName "OneDriveStorage" -TableStyle Dark1

Hình 3. Sử dụng một kiểu khác để tạo trang tính

biểu đồ

Một trong những tính năng phổ biến nhất của Excel là khả năng tạo nhiều loại biểu đồ từ dữ liệu bảng tính. Để tạo biểu đồ trong trang tính, trước tiên chúng ta xác định loại biểu đồ sẽ tạo trong một biến, sau đó xuất dữ liệu bằng cách sử dụng định nghĩa biểu đồ. Đây là cách tôi tạo biểu đồ hình tròn (Hình 4) của dữ liệu lưu trữ OneDrive

$ExcelChart = New-ExcelChartDefinition -XRange Owner -YRange UsedGB -ChartType Pie -Title "OneDrive for Business Storage"
$Report | Export-Excel -Path c:\temp\OneDrive.xlsx -Append -WorksheetName "Pie Chart" -ExcelChartDefinition $ExcelChart -AutoNameRange -Show -Title "OneDrive for Business Storage"

hinh 4. Bao gồm một biểu đồ trong một trang tính Excel

Một số thử nghiệm và lỗi là cần thiết để chọn loại biểu đồ từ bộ được Excel hỗ trợ, nhưng nó đủ dễ để tìm ra

Tư cách thành viên nhóm

Mô-đun ImportExcel đã xử lý bất kỳ báo cáo nào tôi đã sử dụng làm đầu vào. Chẳng hạn, đầu ra từ tập lệnh Báo cáo tư cách thành viên của Microsoft 365 và Teams có thể trở nên khá lớn, nhưng ImportExcel không gặp sự cố nào. Tôi đã tạo báo cáo như trong Hình 5 bằng lệnh này

$SummaryData | Export-Excel -Path c:\Temp\TeamMembership.xlsx -WorksheetName "Groups May 2022" -Title "Microsoft 365 Groups and Teams Membership" -TitleBold -TableName "GroupsMembership" -TableStyle Medium28

Hình 5. Phiên bản Excel của báo cáo thành viên Nhóm và Nhóm Microsoft 365

Nhập từ Excel

Nhập từ Excel giống như sử dụng lệnh ghép ngắn Nhập-CSV, nhưng có thêm một số điều khiển. Trong ví dụ này, tôi xác định cột nào cần nhập và hàng bắt đầu

[array]$Data = Import-Excel -Path c:\temp\TeamMembership.xlsx -ImportColumns @(1,2,3,4,5) -StartRow 2

Sau khi được tải vào mảng, bạn có thể tương tác với dữ liệu bằng các lệnh PowerShell tiêu chuẩn. Chẳng hạn, mã này lặp qua dữ liệu để đánh dấu người dùng có chữ “đỏ” trong tên của họ

ForEach ($D in $Data) {
   If ($D.User -like "*Red*") {
      Write-Host ("{0} is a member of {1} groups..." -f $D.User, $D."Groups count" ) }
}

Cập nhật Excel

Có thêm một số quyền kiểm soát đối với cách bạn tải dữ liệu vào PowerShell thật thú vị. Có thể tương tác với dữ liệu Excel thông qua Excel thú vị hơn. Để thực hiện việc này, chúng tôi sử dụng ImportExcel để tải thông tin cho một trang tính trong bảng tính đích vào một biến, sau đó sử dụng nó để điền vào một mảng có thông tin từ các ô trong trang tính

________số 8_______

Bây giờ chúng ta có thể điều hướng qua các ô bằng cách sử dụng ký hiệu hàng/cột. Ví dụ: ô 3,1 (hàng thứ ba, cột đầu tiên) chứa thông tin này

$Data[3,1]

Style                 : OfficeOpenXml.Style.ExcelStyle
StyleName             : Normal
StyleID               : 0
Value                 : Tony Redmond
Text                  : Tony Redmond
Formula               :
FormulaR1C1           :
Hyperlink             :
Merge                 : False
AutoFilter            : False
IsRichText            : False
IsArrayFormula        : False
RichText              : {}
Comment               :
Worksheet             : Groups May 2022
FullAddress           : 'Groups May 2022'!A3
FullAddressAbsolute   : 'Groups May 2022'!$A$3
ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting
DataValidation        : OfficeOpenXml.DataValidation.RangeDataValidation
Current               :
Address               : A3
Start                 : OfficeOpenXml.ExcelCellAddress
End                   : OfficeOpenXml.ExcelCellAddress
Table                 :
IsName                : False
Rows                  : 1
Columns               : 1

Để cập nhật ô, chúng tôi viết một giá trị mới vào đó và khi hài lòng rằng giá trị đó là chính xác, hãy đóng bảng tính Excel để lưu dữ liệu

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx

0

Chức năng Lưu tự động bình thường của Excel không hoạt động, ngay cả khi vị trí bảng tính nằm trên OneDrive for Business hoặc site SharePoint Online, vì vậy, điều quan trọng cần nhớ là thực hiện các thay đổi rồi lưu vào cam kết, giống như khi làm việc với các tệp được lưu trữ trên ổ đĩa cục bộ

Bộ công cụ đáng giá

Tôi thích mô-đun ImportExcel và nghĩ rằng nó mang lại nhiều chức năng hữu ích cho bảng. Đây là một nâng cấp dễ dàng để áp dụng định dạng cho các báo cáo CSV được tạo bởi các tập lệnh và chắc chắn là điều đáng xem xét, đặc biệt nếu người tiêu dùng dự định của một báo cáo như biểu đồ

---

Tìm hiểu cách khai thác dữ liệu có sẵn cho quản trị viên bên thuê Microsoft 365 thông qua Sách điện tử Office 365 dành cho Chuyên gia CNTT. Chúng tôi thích tìm hiểu cách mọi thứ hoạt động

PowerShell có thể ghi vào Excel không?

Làm cách nào để chuyển đổi CSV sang Excel trong PowerShell?

Làm cách nào để lấy dữ liệu từ Excel bằng PowerShell?