Hướng dẫn powershell excel formatting - định dạng excel powershell

Hello,

Tôi đang tự động hóa một số tiền xử lý cần thiết trong Excel thông qua PowerShell, nhưng đã nhận thấy rằng định dạng không hoạt động khi một hàng bị ẩn.

Tôi đã tìm kiếm trong 4 ngày qua, con số này, nhưng tôi là một thằng ngốc, vì vậy tôi xin lỗi nếu đây là một sửa chữa dễ dàng.

Đây là cách bảng tính Excel được trao cho chúng tôi và bạn có thể thấy rằng các hàng 4, 7, 8 [và nhiều hơn nữa] được ẩn:

Sau khi chạy chương trình PowerShell của tôi, đây là đầu ra trong CSV, bạn có thể thấy các hàng ẩn không được định dạng:

Phải có một cách để định dạng tất cả các ô trong cột? Đây là mã PowerShell của tôi để tham khảo:

 $objExcel = New-Object -ComObject Excel.Application
 $objExcel.Visible = $false
 $objExcel.DisplayAlerts = $false
 $objExcel.AskToUpdateLinks = $false
     
 $file = Get-ChildItem -Path "C:\................\*.xlsx"
 $wb = $objExcel.Workbooks.Open[$file.Fullname]
 $sheet = $wb.Worksheets.Item["Details"]
    
 $sheet.Cells.Item[1, 1].EntireRow.Delete[]
 $sheet.Cells.Item[1, 1].EntireRow.Replace["`n", " ", [Microsoft.Office.Interop.Excel.XlLookAt]::xlPart]
    
 $sheet.Range["U:U"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["V:V"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AC:AC"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AD:AD"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AE:AE"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AF:AF"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AG:AG"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AH:AH"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AI:AI"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AJ:AJ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AO:AO"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AP:AP"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AQ:AQ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AR:AR"].EntireColumn.NumberFormat = "0.0000"
 $sheet.Range["AS:AS"].EntireColumn.NumberFormat = "0.0000"
 $sheet.Range["AT:AT"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AU:AU"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AV:AV"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["AZ:AZ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BM:BM"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BN:BN"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BO:BO"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BP:BP"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BQ:BQ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BR:BR"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["BZ:BZ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CB:CB"].EntireColumn.NumberFormat = "0.0000"
 $sheet.Range["CG:CG"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CH:CH"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CI:CI"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CJ:CJ"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CK:CK"].EntireColumn.NumberFormat = "0.00"
 $sheet.Range["CL:CL"].EntireColumn.NumberFormat = "0.00"
    
 $sheet.SaveAs["C:\................\" + "_converted_file" + ".csv", 6]
     
 $wb.Close[]
 $objExcel.Quit[]
 [System.Runtime.Interopservices.Marshal]::ReleaseComObject[$objExcel]

Cảm ơn bạn!

Xldash

Border weight

XLHAIRLINE
{
$name = $user.displayname
$phone = $user.ipphone
$tele = $user.telephonenumber
if [$name -like "ctu"]
{

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

xlthin

xlthick
Any advice would be greatly appreciated

Làm cho phông chữ đậm và điều chỉnh màu nền [sử dụng thuộc tính bên trong.ColorIndex của mỗi ô] thực sự giúp làm cho các tiêu đề xuất hiện nổi bật hơn trên bảng tính.

Bây giờ là lúc để thêm dữ liệu vào bảng tính!

Sẽ có một số mã lại mã khi tôi sẽ thực hiện cùng một truy vấn và cùng loại đầu vào của các giá trị trong mỗi ô cho Excel. Sự khác biệt là cách tôi định dạng các ô trong Excel để cho nó một cái nhìn đẹp hơn.

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True

#Add a workbook
$workbook = $excel.Workbooks.Add[]

#Remove other worksheets
1..2 | ForEach {
    $Workbook.worksheets.item[2].Delete[]
}

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item[1]
$serverInfoSheet.Name = 'DiskInformation'
$serverInfoSheet.Activate[] | Out-Null

Không có gì mới ở đây khi tôi kết nối với đối tượng Excel COM và sau đó xóa tất cả các sổ làm việc của tôi.

Thay vì trước tiên tạo các tiêu đề của tôi cho dữ liệu cho mỗi ổ đĩa, tôi muốn thêm một tiêu đề để thông báo loại báo cáo này là gì.

#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$serverInfoSheet.Cells.Item[$row,$column]= 'Disk Space Information'

OK, vì vậy tôi có thể thực sự gọi đây là một tiêu đề bằng mọi cách. Chúng ta cần định dạng điều này và cung cấp cho một cái nhìn tiêu đề hơn. Tôi sẽ sử dụng thuộc tính phông chữ của đối tượng ô để làm cho nó trông đẹp hơn rất nhiều.

$serverInfoSheet.Cells.Item[$row,$column].Font.Size = 18
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$serverInfoSheet.Cells.Item[$row,$column].Font.Name = "Cambria"
$serverInfoSheet.Cells.Item[$row,$column].Font.ThemeFont = 1
$serverInfoSheet.Cells.Item[$row,$column].Font.ThemeColor = 4
$serverInfoSheet.Cells.Item[$row,$column].Font.ColorIndex = 55
$serverInfoSheet.Cells.Item[$row,$column].Font.Color = 8210719

Bây giờ điều đó giống như nó! Điều này mang lại cho nó một chút pop và tốt hơn tách chính nó với phần còn lại của dữ liệu trong bảng tính. Ở đây tôi đã tạo ra phông chữ đậm, thay đổi loại phông chữ thành Cambria cũng như cập nhật một số màu trên phông chữ. Đối với những người chú ý, điều này giống như đặt kiểu theo tiêu đề, cũng có thể được thực hiện bằng mã sau:

$range = $serverInfoSheet.Range["a1","h2"]
$range.Style = 'Title'

Tuy nhiên, chỉ có một điều nữa mà tôi muốn làm để làm sạch nó một chút.

$range = $serverInfoSheet.Range["a1","g2"]
$range.Merge[] | Out-Null
$range.VerticalAlignment = -4160

Những gì tôi đã làm là tôi đã chọn một hàng phụ cũng như chọn nhiều cột như tôi biết tôi sẽ sử dụng với các tiêu đề và dữ liệu của mình. Khi tôi có điều đó, tôi đã hợp nhất tất cả mọi thứ và sau đó đặt phần thẳng đứng để căn chỉnh ở trên cùng. Vậy làm thế nào trên thế giới tôi đã biết cách sử dụng giá trị chính xác cho sự liên kết hàng đầu khi nó không dễ đọc [-4160]?

Lần đầu tiên tôi chạy lệnh sau để tìm ra loại enum mà tôi cần phải làm việc với:

$range | Get-Member -Name verticalalignment

& nbsp; & nbsp; Typename: System .__ comobject#{00020846-0000-0000-c000-0000000046}

Tên & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; Định nghĩa thành viên & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; ; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; Ốp- & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; Ăn, & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; Thuộc tính dọc & nbsp; & nbsp; Biến thể dọc.
—-              ———- ———-                              
VerticalAlignment Property   Variant VerticalAlignment [] {get} {set}

Tiếp theo tôi đã đi ra trang web MSDN cho đối tượng Excel COM và tìm kiếm sự liệt kê của loại này. Thật không may, tôi không thể có được các giá trị liên quan đến loại căn chỉnh. Giải pháp là làm việc với enum bằng cách sử dụng mã sau:

[Enum]::getvalues[[Microsoft.Office.Interop.Excel.XLVAlign]] | 
select @{n="Name";e={"$_"}},value__ 

Dọc

Bây giờ tôi biết chính xác giá trị nào là cần thiết để có được sự liên kết đúng! Kỹ thuật này sẽ được sử dụng một số lần trong bài viết này để nhanh chóng tìm ra giá trị chính xác cần thiết để định cấu hình một thuộc tính cụ thể.

Tiếp theo, chúng tôi có thể thêm các tiêu đề cho mỗi bộ dữ liệu, nhưng lần này với một vài bổ sung để thực sự để nó nổi bật như một tiêu đề.

#Increment row for next set of data
$row++;$row++

#Save the initial row so it can be used later to create a border
$initalRow = $row

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item[$row,$column]= 'Computername'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'DeviceID'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'VolumeName'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'TotalSize[GB]'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'UsedSpace[GB]'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'FreeSpace[GB]'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item[$row,$column]= 'PercentFree'
$serverInfoSheet.Cells.Item[$row,$column].Interior.ColorIndex =48
$serverInfoSheet.Cells.Item[$row,$column].Font.Bold=$True

Làm cho phông chữ đậm và điều chỉnh màu nền [sử dụng thuộc tính bên trong.ColorIndex của mỗi ô] thực sự giúp làm cho các tiêu đề xuất hiện nổi bật hơn trên bảng tính.

Bây giờ là lúc để thêm dữ liệu vào bảng tính!

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

Tôi đang sử dụng PowerShell v3 để lấy dữ liệu với get-ciminstance; Đối với v2, bạn vẫn nên sử dụng get-wmiObject.

Một số thay đổi lớn ở đây. Thay vì chỉ có văn bản cũ đơn giản không có màu. Tôi quyết định theo dõi những ổ đĩa nào có thể gặp vấn đề với không gian. Ok, tôi biết rằng đây không phải là đại diện tốt nhất của việc hết không gian, nhưng như bạn có thể thấy trong mã, tôi có thể đặt ngưỡng dựa trên không gian trống và phần trăm miễn phí để xác định nếu không gian đĩa quan trọng hoặc Một cảnh báo và làm nổi bật toàn bộ hàng phù hợp. Tôi cũng mã cứng phạm vi của hàng vì nó sẽ không bao giờ thay đổi để làm cho nó dễ dàng hơn để làm nổi bật hàng dễ dàng hơn. Điều này giúp dễ dàng hơn bây giờ để nhanh chóng định vị các ổ đĩa cần một số sự chú ý và giải quyết vấn đề không gian.Critical or a Warning and highlight the entire row accordingly. I also hard code the range of the row as it will never change to make it easier to highlight the row. This makes it easier now to quickly locate drives that need some attention and resolve the space issue.

Chúng tôi chưa hoàn thành! Tôi muốn thêm một số biên giới vào điều này để cung cấp cho nó một cái nhìn sạch hơn.

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

Làm thế nào tôi biết sử dụng chỉ số biên giới nào để làm cho biên giới trông như thế này? Bằng cách sử dụng cùng một thủ thuật mà tôi đã sử dụng với thuộc tính dọc.

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

Chỉ số biên giới

Bây giờ là lúc để thêm dữ liệu vào bảng tính!

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

Kỹ năng

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

Border weight

xllinestylenone

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

xldouble

 }
 else {
     "$name,$phone,$tele" | Out-File -FilePath '\\corp\fileshare\IT\Reports\test.csv' -Encoding ascii -Append 
     Write-Host $name $phone
 }

XLDOT