PowerShell脚本报错：[System.__ComObject]无LoadFromDataTable方法

运行ConvertCSVToXLS6.ps1脚本并传入InputFile和OutputFile参数后，出现如下报错：

Method invocation failed because [System.__ComObject] does not contain a method named 'LoadFromDataTable'

报错定位在脚本第20行：

$range = $worksheet.Range("A1").LoadFromDataTable($data, $true)

完整错误信息：

+ CategoryInfo          : InvalidOperation: (LoadFromDataTable:String) [], RuntimeException
  + FullyQualifiedErrorId : MethodNotFound

原脚本代码：

param (
    [Parameter(Mandatory=$true)]
    [string]$InputFile,

    [Parameter(Mandatory=$true)]
    [string]$OutputFile
)

# Load the CSV data
$data = Import-Csv $InputFile

# Create a new Excel workbook
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Add()

# Get the first worksheet
$worksheet = $workbook.Worksheets.Item(1)

# Write the CSV data to the worksheet
$range = $worksheet.Range("A1").LoadFromDataTable($data, $true)

# Format column 9 to retain leading zeros
$range = $worksheet.Range("I:I")
$range.NumberFormat = "000000000"

# Save the workbook as XLS format
$workbook.SaveAs($OutputFile, -4143)

# Close the workbook and quit Excel
$workbook.Close()
$excel.Quit()

原因 #

LoadFromDataTable是EPPlus等第三方Excel处理库的专属方法，原生Excel COM对象（通过New-Object -ComObject Excel.Application创建的对象）并不支持这个方法，因此触发方法未找到的错误。

解决方案 #

改用原生Excel COM支持的方式逐行写入数据，同时针对第9列设置文本格式以保留前导零。以下是修改后的完整脚本：

param (
    [Parameter(Mandatory=$true)]
    [string]$InputFile,

    [Parameter(Mandatory=$true)]
    [string]$OutputFile
)

# 加载CSV数据
$data = Import-Csv $InputFile

# 创建Excel实例并后台运行
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.Worksheets.Item(1)

# 写入表头
$headers = $data[0].PSObject.Properties.Name
for ($i = 0; $i -lt $headers.Count; $i++) {
    $worksheet.Cells(1, $i + 1).Value = $headers[$i]
}

# 逐行写入数据，处理第9列前导零
$rowIndex = 2
foreach ($row in $data) {
    $rowValues = $row.PSObject.Properties.Value
    for ($colIndex = 0; $colIndex -lt $rowValues.Count; $colIndex++) {
        $currentCol = $colIndex + 1
        # 针对第9列（Excel的I列）设置文本格式
        if ($currentCol -eq 9) {
            $worksheet.Cells($rowIndex, $currentCol).NumberFormat = "@"
            $worksheet.Cells($rowIndex, $currentCol).Value = $rowValues[$colIndex]
        } else {
            $worksheet.Cells($rowIndex, $currentCol).Value = $rowValues[$colIndex]
        }
    }
    $rowIndex++
}

# 保存文件并清理资源
$workbook.SaveAs($OutputFile, -4143) # -4143对应XLS格式
$workbook.Close()
$excel.Quit()

# 释放COM对象，避免Excel进程残留
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

关键修改说明 #

1. 替换数据写入逻辑：移除LoadFromDataTable调用，通过循环手动写入表头和每一行数据，这是原生Excel COM支持的标准方式。
2. 保留第9列前导零：在写入第9列数据前，先将单元格格式设置为文本（@是Excel文本格式代码），确保前导零不会被自动去除。
3. 添加COM对象释放：脚本末尾增加了COM对象释放和垃圾回收逻辑，避免Excel进程在后台残留占用资源。

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