在调用Workbooks.Add()时使用Powershell自动化Excel Linq的异常

# 引用Excel COM组件
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

# 引用Linq库
Add-Type -AssemblyName System.Core

# 创建Excel工作簿
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Add()

# 创建一个数组，用于存储数据
$data = @(
    @{ Name = "John"; Age = 25 }
    @{ Name = "Mary"; Age = 30 }
    @{ Name = "Bob"; Age = 40 }
)

# 将数据转换为DataTable
$table = New-Object System.Data.DataTable
$table.Columns.Add("Name", [string]) | Out-Null
$table.Columns.Add("Age", [int]) | Out-Null

foreach ($item in $data) {
    $row = $table.NewRow()
    $row["Name"] = $item.Name
    $row["Age"] = $item.Age
    $table.Rows.Add($row)
}

# 将DataTable写入Excel工作表
$worksheet = $workbook.Worksheets.Item(1)
$table | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | % {
    $worksheet.Cells.Item($worksheet.Cells.Rows.Count, 1).End(-4162).Offset(1, 0).Value2 = $_
}

# 保存Excel工作簿
$workbook.SaveAs("C:\Temp\test.xlsx")

# 关闭Excel工作簿并退出应用程序
$workbook.Close()
$excel.Quit()

# 释放COM对象
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# 清除COM对象
Remove-Variable -Name excel, workbook, worksheet