Ô định dạng excel xuất khẩu 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 đầu vào 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

Nội dung chính Hiển thị

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 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 lệnh của tập tin sử dụng lệnh ghép rút gọ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. Ở đầu vào, lệnh rút ngắn dữ liệu Import-CSV đã cung cấp 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 tin 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, sách của 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 xuống 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. Type Excel default is module ImportExcel used

Để xuất 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 cho 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 bảng tính toán đã đặt tên sử dụng một kiểu khác

Vì Excel hỗ trợ nhiều trang tính trong một cửa sổ làm việc nên bạn có thể thêm các trang tính mới vào cửa 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 ra một cửa 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 loại bảng có sẵn sẽ hiển thị nếu bạn di chuột qua các loại đượ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 tượng

Một trong những tính năng biến phổ biến nhất của Excel là khả năng tạo nhiều loại biểu thức từ bảng tính dữ liệu. Để tạo biểu đồ trong trang tính, trước tiên chúng tôi xác định loại biểu đồ sẽ được 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 tượng 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"

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

Một số thử nghiệm và lỗi cần thiết để chọn loại biểu đồ từ bộ được Excel hỗ trợ, nhưng nó đủ dễ dàng để 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 để bắt đầu. Không giới hạn, bắt đầu từ tập lệnh Báo cáo tư cách thành viên của Microsoft 365 và các nhóm 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 rút 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 tiêu chuẩn lệnh PowerShell. Không giới hạn, mã này lặp lại thông 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" ] }
}

Update Excel

Có thêm một số quyền kiểm tra đối với cách bạn tải dữ liệu vào PowerShell thực sự thú vị. There can interable with Excel data information through Excel thú vị hơn. Để thực hiện công 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

________số 8

Để 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 trang 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,

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

Tôi thích mô-đun ImportExcel và cho rằng nó mang lại nhiều chức năng hữu ích cho bảng. Đây là một sự nâng cao dễ dàng để áp dụng định 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 dùng sử dụng dự báo 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 khám phá mọi thứ hoạt động