使用PowerShell转换ODS到XLSX遇Excel打开ODS报错求助

我想用PowerShell将.ods格式文件转换为.xlsx格式，但手动用Excel打开.ods文件时，Excel提示文件损坏无法打开。编写的PowerShell脚本如下：

Function Get-FileName($initialDirectory)
{  
 [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |
 Out-Null

 $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
 $OpenFileDialog.initialDirectory = $initialDirectory
 $OpenFileDialog.filter = "All files (*.*)| *.*"
 $OpenFileDialog.ShowDialog() | Out-Null
 $OpenFileDialog.filename
} #end function Get-FileName

# *** Entry Point to Script ***

$filename = Get-FileName -initialDirectory "c:\temp\ods"
$Excel = New-Object -ComObject Excel.Application
$workbook = $Excel.Workbooks.Open($filename)
$newfilename=$filename.Replace("ods","xslx")
$workbook.SaveAs($newfilename,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault,$null,$null,$false,$false,1,1,$false,$null,$null,$false)


$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat

运行脚本时出现以下报错：

PS C:\Users\merrr1> $filename = Get-FileName -initialDirectory "c:\temp\ods"
$Excel = New-Object -ComObject Excel.Application
$workbook = $Excel.Workbooks.Open($filename)
$newfilename=$filename.Replace("ods","xslx")
$workbook.SaveAs($newfilename,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault,$null,$null,$false,$false,1,1,$false,$null,$null,$false)


$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.
At line:3 char:1
+ $workbook = $Excel.Workbooks.Open($filename)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
 
Microsoft Excel cannot access the file 'C:\Temp\xslx\CB0C3100'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
At line:5 char:1
+ $workbook.SaveAs($newfilename,[Microsoft.Office.Interop.Excel.XlFileF ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
 
xlOpenXMLWorkbook

当前使用32位Microsoft® Excel® for Microsoft 365 MSO（版本2502 Build 16.0.18526.20264），请问如何让Excel正常打开ODS文件并完成转换？

解决方法 #

• 修复ODS文件兼容性
  先用LibreOffice或OpenOffice打开目标ODS文件，选择「另存为」并保存为标准ODS格式（避免使用LibreOffice特有的扩展格式），再尝试用Excel打开。部分ODS文件因非标准格式导致Excel误判为损坏。

• 修改脚本的Excel打开参数
  Excel的Workbooks.Open支持修复模式，强制Excel尝试修复文件后打开，替换原打开代码：

  $workbook = $Excel.Workbooks.Open(
      $filename,
      0,          # UpdateLinks
      $false,     # ReadOnly
      5,          # Format
      "",         # Password
      "",         # WriteResPassword
      $true,      # IgnoreReadOnlyRecommended
      [Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows,
      "",         # Delimiter
      $true,      # Editable
      $false,     # Notify
      0,          # Converter
      $true,      # AddToMru
      $true,      # Local
      $true       # CorruptLoad（强制修复模式）
  )
  

• 修正文件名拼写与路径问题
  脚本中$newfilename=$filename.Replace("ods","xslx")存在拼写错误（xslx应为xlsx），且未确保保存目录存在，修改为：

  $newfilename = $filename.Replace(".ods", ".xlsx")
  # 确保保存目录存在
  $saveDir = Split-Path $newfilename -Parent
  if (-not (Test-Path $saveDir)) {
      New-Item -ItemType Directory -Path $saveDir | Out-Null
  }
  

• 指定正确的保存格式
  保存时直接指定XLSX对应的枚举值，避免格式混淆：

  $workbook.SaveAs($newfilename, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
  

• 清理Excel COM进程
  脚本结束后添加清理代码，避免Excel进程残留：

  $workbook.Close($false)
  $Excel.Quit()
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
  [GC]::Collect()
  [GC]::WaitForPendingFinalizers()
  

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