PowerShell写入Excel遇类型转换错误：String无法转为Int32

问题现象 #

使用PowerShell通过COM对象向Excel写入数据时，仅写入数字时代码正常运行，但写入字符串类型变量时会报错：Unable to cast object of type 'System.String' to type 'System.Int32'。尝试通过判断值类型并转换为字符串后，问题仍未解决。

错误原因 #

Excel的COM对象会根据单元格已有内容或默认格式自动推断数据类型。当同一列中先写入了数字（单元格默认格式为数字），后续写入字符串时，Excel会尝试将字符串强制转换为数字类型，从而触发类型转换失败的错误。此外，原代码中的类型判断逻辑未提前处理单元格格式问题，导致字符串写入时仍被Excel按数字类型解析。

解决方案 #

方法1：提前设置单元格格式为文本 #

在写入数据前，将目标列或所有列的格式设置为文本，避免Excel自动转换类型：

# 提前将所有列设置为文本格式
for ($col = 1; $col -le $columnCount; $col++) {
    $worksheet.Columns.Item($col).NumberFormat = "@"
}

方法2：优化赋值逻辑，显式处理类型 #

在写入数据时，针对非数字值先设置单元格格式为文本，再赋值：

for ($row = 1; $row -le $rowCount; $row++) {
    for ($col = 1; $col -le $columnCount; $col++) {
        $value = $values[$row - 1][$col - 1]
        $cell = $worksheet.Cells.Item($row, $col)
        
        if ($value -is [int]) {
            $cell.Value2 = $value
        }
        else {
            $cell.NumberFormat = "@"
            $cell.Value2 = $value.ToString()
        }
    }
}

方法3：使用Text属性直接赋值 #

直接使用单元格的Text属性写入内容，强制以文本形式存储：

for ($row = 1; $row -le $rowCount; $row++) {
    for ($col = 1; $col -le $columnCount; $col++) {
        $value = $values[$row - 1][$col - 1]
        $cell = $worksheet.Cells.Item($row, $col)
        $cell.Text = $value.ToString()
    }
}

完整修正代码 #

$excelFilePath = "C:\Users\Test\test.xlsx"

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.Worksheets.Item(1)

$Column = $Worksheet.Columns.Item(2)
$Column.Interior.ColorIndex = 6

$columnCount = 5
$rowCount = 13
$four = "four"

$values = @(
    @(1, 'two', 3, $four, 5),
    @(6, 7, 8, 9, 10),
    @(11, 12, 13, 14, 15),
    @(16, 17, 18, 19, 20),
    @(21, 22, 23, 24, 25),
    @(26, 27, 28, 29, 30),
    @(31, 32, 33, 34, 35),
    @(36, 37, 38, 39, 40),
    @(41, 42, 43, 44, 45),
    @(46, 47, 48, 49, 50),
    @(51, 52, 53, 54, 55),
    @(56, 57, 58, 59, 60),
    @(61, 62, 63, 64, 65)
)

# 提前设置所有列为文本格式，避免类型转换错误
for ($col = 1; $col -le $columnCount; $col++) {
    $worksheet.Columns.Item($col).NumberFormat = "@"
}

# 写入数据
for ($row = 1; $row -le $rowCount; $row++) {
    for ($col = 1; $col -le $columnCount; $col++) {
        $value = $values[$row - 1][$col - 1]
        $cell = $worksheet.Cells.Item($row, $col)
        $cell.Value2 = $value
    }
}

$Column = $Worksheet.Columns.Item(2) 
$Column.Interior.ColorIndex = 6 

$Workbook.Save()
$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
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

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