使用ImportExcel转换后设置Excel左水平对齐求助

你可以通过Export-Excel的-PassThru参数获取ExcelPackage对象，然后对工作表的已使用范围设置水平左对齐，具体修改后的脚本如下：

$archiveFolderPath = Join-Path $PSScriptRoot "archive"
if (-not (Test-Path $archiveFolderPath)) { New-Item -ItemType Directory -Path $archiveFolderPath | Out-Null }

Get-ChildItem $PSScriptRoot "*.txt" | ForEach-Object {
    $excelFilePath = Join-Path $PSScriptRoot "$($_.BaseName).xlsx"

    # 导出数据并保留ExcelPackage对象
    $excelPackage = Get-Content $_.FullName | Where-Object { $_ -match '^\|.*\|$' } | ForEach-Object { $_.TrimStart('|') } |
    ConvertFrom-Csv -Delimiter "|" | Export-Excel $excelFilePath -AutoSize -AutoFilter -BoldTopRow -PassThru

    # 获取第一个工作表
    $worksheet = $excelPackage.Workbook.Worksheets[0]
    # 设置整个已使用范围的水平对齐为左对齐
    $worksheet.Cells[$worksheet.Dimension.Address].Style.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Left

    # 保存并释放资源
    $excelPackage.Save()
    $excelPackage.Dispose()

    $timestamp = Get-Date -Format 'yyyyMMdd-HHmm'
    $archiveFileName = "{0}_{1}.txt" -f $_.BaseName, $timestamp
    Move-Item -Path $_.FullName -Destination (Join-Path $archiveFolderPath $archiveFileName)

    Write-Host "Txt file '$($_.FullName)' converted to Excel and moved to the archive as '$archiveFileName'."
}

Read-Host "Press enter to close."

修改说明： #

• 添加-PassThru参数让Export-Excel返回ExcelPackage对象，而非直接保存关闭文件
• 通过$excelPackage.Workbook.Worksheets[0]获取生成的默认工作表
• 使用$worksheet.Dimension.Address定位工作表中实际有数据的范围，避免无效设置空单元格
• 调用Style.HorizontalAlignment设置左对齐，枚举值[OfficeOpenXml.Style.ExcelHorizontalAlignment]::Left是ImportExcel依赖的EPPlus库标准枚举
• 必须调用Save()保存修改，再用Dispose()释放资源，防止文件被占用

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