使用ImportExcel模块为每个工作表创建数据透视表时遇到文件损坏问题

错误根因 #

• 调用New-PivotTableDefinition时未配置任何透视维度字段，Excel不支持无行、列、值字段的空透视表，打开时就会触发不可读错误
• 透视表名称直接使用纯数字的循环变量$i，不符合Excel对象命名规则：名称不能以数字、特殊符号开头
• 循环内多次写入同一个Excel文件未加-Append参数，每次执行都会覆盖之前生成的内容，导致第一个工作表被损坏

修正代码 #

function createDT()
{
    ### 创建新的DataTable ###
    $tempTable = New-Object System.Data.DataTable
   
    ## 为DataTable创建列 ##
    $query = New-Object System.Data.DataColumn("query")
    $sheetName = New-Object System.Data.DataColumn("sheetName")
    $format = New-Object System.Data.DataColumn("format")
    $pivotRow = New-Object System.Data.DataColumn("pivotRow")
    $pivotData = New-Object System.Data.DataColumn("pivotData")
           
    ### 为DataTable添加列 ###
    $tempTable.columns.Add($query)
    $tempTable.columns.Add($sheetName)
    $tempTable.columns.Add($format)
    $tempTable.columns.Add($pivotRow)
    $tempTable.columns.Add($pivotData)
       
    return ,$tempTable
}

### 创建新的DataTable ###
[System.Data.DataTable]$dTable = createDT

### 为DataTable添加行 ###
$row = $dTable.NewRow()
$row["query"] = "SELECT * FROM exampletable"
$row["sheetName"] = "exampletable"
$row["format"] = "Medium2"
$row["pivotRow"] = "row"
$row["pivotData"] = "data"
$dTable.rows.Add($row)
### 为DataTable添加行 ###
$row = $dTable.NewRow()
$row["query"] = "SELECT * FROM exampletable2"
$row["sheetName"] = "exampletable2"
$row["format"] = "Medium2"
$row["pivotRow"] = "row"
$row["pivotData"] = "data"
$dTable.rows.Add($row)

 ### DataTable的行数 ###
 $dtLength = $dTable.Rows.Count

for ($i = 0; $i -lt $dtLength; $i++)
{
    # 修正透视表命名规则，补充必要的行、值字段
    $pvTable = New-PivotTableDefinition -PivotTableName "Pivot_$($dTable.sheetName[$i])" `
        -SourceWorksheet $dTable.sheetName[$i] `
        -PivotRows $dTable.pivotRow[$i] `
        -PivotValues @{$dTable.pivotData[$i] = 'Count'}

    # 添加-Append参数避免覆盖之前的工作表
    Invoke-SQLCmd -query $dTable.query[$i] -database $dbname -serverinstance $ServerName | 
        Export-Excel -workSheetName $dTable.sheetName[$i] -TableStyle $dTable.format[$i] `
        -BoldTopRow -path $destination -AutoSize -AutoFilter -PivotTableDefinition $pvTable -Append
}

补充说明 #

如果不需要固定统计逻辑，仅做验证可以将PivotValues设置为任意存在的字段，Excel可以正常加载透视表后再手动调整维度配置。

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