如何使用ImportExcel調整Excel列寬

我下載了 Excel 報告，但我需要使用 PowerShell 將某些列的大小調整為不同的寬度，所以我只是想知道如何實作這一點。任何幫助或建議將不勝感激。

例如，我想將用戶、日期和時間、專案列修改為寬度大小 30，活動列修改為寬度大小 50，將其他一些列修改為寬度大小 30 等等......

function Modify-Columns {

    $params = @{
        AutoSize      = $true
        # TableName     = 'exampleTable'
        TableStyle    = 'Light9' # => Here you can chosse the Style you like the most
        BoldTopRow    = $true
        WorksheetName = "Audit Log"
        PassThru      = $true
        Path          = "C:\AuditLogSearch\$((Get-Date).AddDays(-7).ToString('yyyy-MM-dd')) _ $(Get-Date -Format "yyyy-MM-dd") Audit-Log-Records.xlsx" # => Define where to save it here!
    }

    Write-Host "Start Modifiying Column and Row using AD DisplayName and Excel Files"

    $modifiedFile = Import-Csv "C:\AuditLogSearch\Modified-Audit-Log-Records.csv"
    $actionReference = Import-Csv "C:\AuditLogSearch\Reference\eDiscovery_Activities_List.csv"

    $result = foreach ($u in $modifiedFile) {
        $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
        New-Object PsObject -Property @{
            User                      = $u.User
            # "Search Criteria"         = $u."Search Criteria"
            "Result Status"           = $u."Result Status"
            "Date & Time"             = $u."Date & Time"
            "Search Conditions"       = $u."Search Conditions"
            "SharePoint Sites"        = $u."SharePoint Sites"
            "Exchange Public Folders" = $u."Exchange Public Folders"
            "Exchange Mailboxes"      = $u."Exchange Mailboxes"
            "Case Name"               = $u."Case Name"     
            "Search Criteria"         = $u."Search Criteria"
            "Item"                    = $u."Item"
            
            "Activity"                = if (($actionReference | where-object { $_.Name -eq $u."Activity" }).Value -ne $null) { ($actionReference | where-object { $_.Name -eq $u."Activity" }).Value }
            else { $u."Activity" }
        }  | Select-object -Property User, "Date & Time", "Case Name", "Item", "Activity" , "SharePoint Sites", "Exchange Mailboxes", "Exchange Public Folders" , "Search Criteria", "Result Status"
    }
    
    $xlsx = $result | Export-Excel @params
    $ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
    $ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
    Close-ExcelPackage $xlsx

    Write-Host "Finished Modifiying Column and Row using AD DisplayName and Excel Files"
}

uj5u.com熱心網友回復：

正如您可能已經看到的-AutoSize那樣，它不是很精確，它往往會留下比需要更多的寬度。如果您需要將硬編碼值設定為您可以使用的列之一：

• $xlsx.Workbook.Worksheets['SheetName'].Column(n).width = newVal

注意：此方法需要使用-PassThru.

這是一個最小的可重現示例：

$result = [pscustomobject]@{
    Col1 = 'Test'
    Col2 = 'Test'
    Col3 = [datetime]::Now
}

$params = @{
    AutoSize      = $true
    TableStyle    = 'Light9'
    BoldTopRow    = $true
    WorksheetName = "Audit Log"
    PassThru      = $true
    Path          = './test.xlsx'
}

$xlsx = $result | Export-Excel @params
$ws = $xlsx.Workbook.Worksheets[$params.WorksheetName]

# This is how you can get the number of Columns, Index starts from 1 not 0.
$ws.Dimension.Columns           # => 3
$ws.Column(1).Width             # => Col1 current Width is 9.140625
$ws.Column(1).Width = 5         # => Updated to 5
$ws.View.ShowGridLines = $false # => Hide the GridLines
Close-ExcelPackage $xlsx

標籤：擅长 电源外壳 格式

上一篇：如何在VBA中創建n個陣列

下一篇：時間戳vba簡化