如何使用ImportExcel在PowerShell中调整Excel列宽

你当前使用ImportExcel模块操作Excel，仅需在原有代码基础上做两处修改即可满足需求：

1. 关闭参数中的自动列宽适配AutoSize，避免覆盖手动设置的值
2. 在关闭Excel包前添加列宽遍历设置逻辑

以下是修改后的完整可运行代码：

function Modify-Columns {

    $params = @{
        AutoSize      = $false # 关闭自动适配，改用手动设置列宽
        TableStyle    = 'Light9' 
        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"
    }

    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
        [PSCustomObject]@{
            User                      = $u.User
            "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) { ($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 

    # 列宽设置逻辑，完全匹配你的需求
    for ($col = 1; $col -le $ws.Dimension.End.Column; $col++) {
        $currentColName = $ws.Cells[1, $col].Text
        if ($currentColName -eq 'Activity') {
            $ws.Column($col).Width = 50
        } else {
            # 其余所有列包括User、Date & Time、Item统一设置为30
            $ws.Column($col).Width = 30
        }
    }

    Close-ExcelPackage $xlsx

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

如果后续需要调整其他列的宽度，仅需在循环中新增判断分支即可，比如需要把Case Name列设为40，添加如下代码即可：

elseif ($currentColName -eq 'Case Name') {
    $ws.Column($col).Width = 40
}

内容的提问来源于stack exchange，提问作者ziico