使用Import-Excel模块的PowerShell数据转置问题求助

原代码关键错误分析 #

1. 循环语法与逻辑错误：
   • 循环增量$++是无效语法，正确写法为$i++
   • 循环条件$i -ge $count逻辑颠倒：要从第4列（D列）遍历到目标列，应该用$i -le $count，否则循环直接跳过，根本不会执行单元格赋值操作
2. 公式方案列宽问题：
   • 原Export-Excel的-AutoSize仅对初始导出的列生效，后续通过公式新增的列未自动调整宽度，导致列宽为0被隐藏，需要在公式写入后重新执行自动列宽调整

修复后的完整代码 #

# 删除旧文件
Remove-Item -Path $prnfn -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
Write-Host -ForegroundColor Green "Create base Excel file"

# 导出基础数据，保留工作簿对象
$xl = $file | Export-Excel -WorkSheetName $wsn -ClearSheet -PassThru -Path $prnfn -StartRow 2 -FreezePane 3,3
Write-Host -ForegroundColor Green "Assign Worksheet name"
$sheet = $xl.Workbook.Worksheets[$wsn]

Write-Host -ForegroundColor Green "Transpose vertical data to horizontal"
# 计算需要转置的列数：A、B列第3-8行共6行，对应D-I列（第4-9列），所以$count应为9
$count = 9
# 修复循环逻辑：从第4列（D）到第9列（I）遍历
for ($i = 4; $i -le $count; $i++) {
    # 对应A列第(i-1)行 → 第1行第i列
    $Sheet.Cells[1, $i].Value = $Sheet.Cells[$i - 1, 1].Value
    # 对应B列第(i-1)行 → 第2行第i列
    $Sheet.Cells[2, $i].Value = $Sheet.Cells[$i - 1, 2].Value
}

Write-Host -ForegroundColor Green "Set formats"
# 设置A列对齐方式
Set-Format -Worksheet $sheet -Range "A:A" -HorizontalAlignment Left
# 设置数值列格式
$rng = "C:" + $c[$count]
Set-Format -Worksheet $sheet -Range $rng -NumberFormat '#,##0'

# 若使用公式方案，替换循环部分为以下代码，并取消注释：
<#
Write-Host -ForegroundColor Green "Use formula to transpose data"
$rng = "D1:" + $c[$count] + "1"
Set-ExcelRange -Worksheet $sheet -Range $rng -Formula '=INDIRECT("A"&COLUMN()-1)' -HorizontalAlignment Left
$rng = "D2:" + $c[$count] + "2"
Set-ExcelRange -Worksheet $sheet -Range $rng -Formula '=INDIRECT("B"&COLUMN()-1)' -HorizontalAlignment Left
#>

# 重新执行自动列宽调整，解决公式方案的列宽问题
$xl | Export-Excel -WorkSheetName $wsn -ClearSheet -PassThru -AutoSize -NoHeader

Write-Host -ForegroundColor Green "Complete Excel process"
# 保存并关闭工作簿
Close-ExcelPackage $xl -Calculate

关键修改说明 #

• 循环修复：修正循环增量与条件，确保A3-A8的数据被转置到D1-I1，B3-B8转置到D2-I2
• 列宽处理：在完成所有数据写入（无论是直接赋值还是公式）后，重新执行Export-Excel -AutoSize，确保所有列都能自动调整到合适宽度
• 数据保存：确保所有修改都执行后，调用Close-ExcelPackage完成保存，-Calculate参数会确保公式计算完成后再保存

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