PowerShell脚本无法替换Excel单元格换行符为逗号求解决

我编写了一段PowerShell脚本，用于检测Excel工作表（design sheet）中单元格内以换行符分隔的值，将换行符替换为逗号后更新文件。脚本执行无报错，但始终输出“未对Excel文件进行任何更改”，文件也未更新，尝试多种方法仍未解决，请求帮助排查修复。

脚本代码：

# Load the Excel file
$designSheet = "/home/lokeshdir/NSG_Rules.xlsx"
$excel = Import-Excel -Path $designSheet

# Flag to track changes
$changesMade = $false

# Iterate through each cell and replace line breaks with commas if present
foreach ($row in $excel) {
    foreach ($property in $row.psobject.Properties) {
        # Check if the cell content has line breaks and replace them with commas
        if ($property.Value -match "`r`n") {
            $property.Value = $property.Value -replace "`r`n", ","
            $changesMade = $true  # Set flag indicating changes
        }
    }
}

# If changes were made, save the updated Excel file
if ($changesMade) {
    $excel | Export-Excel -Path $designSheet -Show
    Write-Host "Excel file updated successfully."
} else {
    Write-Host "No changes made to the Excel file."
}

执行输出：

PS /home/lokeshdir> ./NSGRulesadd.ps1
No changes made to the Excel file.
PS /home/lokeshdir>

• 覆盖所有换行符类型：Excel单元格内的换行可能是单独的n（LF）而非r`n（CRLF），修改匹配和替换逻辑，兼容所有换行格式：

  if ($property.Value -match "`r?`n|`n") {
      $property.Value = $property.Value -replace "`r?`n|`n", ","
      $changesMade = $true
  }
  

  也可以用正则表达式[\r\n]+匹配任意连续换行符，替换为单个逗号。

• 指定目标工作表：当前脚本默认读取第一个工作表，若目标是“design sheet”，需在导入时明确指定：

  $excel = Import-Excel -Path $designSheet -WorksheetName "design sheet"
  

• 添加调试输出验证内容：在循环中加入调试代码，确认读取到的单元格值是否包含换行符，以及换行符的实际类型：

  foreach ($row in $excel) {
      foreach ($property in $row.psobject.Properties) {
          Write-Host "属性名：$($property.Name)，值：$($property.Value)"
          if ($property.Value) {
              # 输出值的字节编码，查看换行符对应的字节
              $bytes = [System.Text.Encoding]::UTF8.GetBytes($property.Value)
              Write-Host "字节内容：$bytes"
          }
          if ($property.Value -match "`r`n|`n") {
              $property.Value = $property.Value -replace "`r`n|`n", ","
              $changesMade = $true
          }
      }
  }
  

• 改用COM对象直接操作Excel：如果Import-Excel模块自动处理了换行符导致读取丢失，可直接调用Excel COM对象操作（需系统安装Excel，Linux环境可替换为LibreOffice的对应接口）：

  $excelApp = New-Object -ComObject Excel.Application
  $excelApp.Visible = $false
  $workbook = $excelApp.Workbooks.Open("/home/lokeshdir/NSG_Rules.xlsx")
  $worksheet = $workbook.Worksheets.Item("design sheet")
  $usedRange = $worksheet.UsedRange
  
  $changesMade = $false
  foreach ($row in $usedRange.Rows) {
      foreach ($cell in $row.Cells) {
          if ($cell.Value2 -match "`r?`n") {
              $cell.Value2 = $cell.Value2 -replace "`r?`n", ","
              $changesMade = $true
          }
      }
  }
  
  if ($changesMade) {
      $workbook.Save()
      Write-Host "Excel file updated successfully."
  } else {
      Write-Host "No changes made to the Excel file."
  }
  
  $workbook.Close()
  $excelApp.Quit()
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp) | Out-Null
  

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