Powershell调用Excel COM对象SaveAs处理不同XLSX文件输出结果不一致

工作中需要处理不同客户提供的XLSX数据文件，需通过SSIS自动化将数据导入SQL，经测试最优方案是使用SSIS项目/包加载管道分隔格式的数据。
目前处理的两个客户的XLSX文件均仅包含1张存有待导入数据的工作表，导入前都需要完成预处理：

• 客户1的A列数据内嵌了需移除的换行符（LF）
• 客户2的有效数据中存在约1600行空行

问题复现 #

首先为客户1开发了PowerShell脚本：通过PowerShell调用Excel COM对象打开工作表，选中整列A，遍历A列单元格移除LF字符后，执行如下命令保存：

$<ComObjectWorsheetVariable>.SaveAs(($<path\filename.csv'),6)

个人工作站的CSV默认输出为管道分隔格式，该脚本输出完全符合预期。

之后以客户1的脚本为框架，仅修改了输入输出路径、文件名，新增了删除多余空行的逻辑，SaveAs命令和之前完全一致：

$<ComObjectWorsheetVariable>.SaveAs(($<path\filename.csv'),6)

但本次输出为引号限定的逗号分隔格式，每行末尾的CRLF前还多出了一个逗号。排查后确认空行删除逻辑不影响该结果：调试时在工作表打开后就手动执行和客户1场景完全相同的SaveAs命令，依旧得到引号限定的逗号分隔输出，同时排除了Excel版本差异的影响，两个文件都是XLSX格式。

补充排查进展 #

已找到部分问题的原因：之前是调用workbook对象执行SaveAs，调整后已经能得到预期的管道分隔文件，但仍存在异常：其中一个客户的文件输出符合预期，管道分隔、最后一列末尾直接跟CRLF；另一个客户的文件虽然已经是管道分隔输出，但每行CRLF前的最后一列位置仍有多余的末尾管道符。

第一个问题（SaveAs输出逗号分隔带引号文件）的原因 #

该差异和文件格式无关，核心原因是Excel的SaveAs方法使用CSV格式（参数值6）时，分隔符、文本限定符的规则完全继承当前系统的区域设置，以及Excel对文件内容的自动适配规则：

1. 你工作站默认CSV是管道分隔，是因为Windows区域设置的列表分隔符被修改为了|，但客户2的文件中存在单元格内容包含管道符的情况，Excel会自动回退到逗号分隔，同时给包含特殊字符的内容加双引号限定，避免分隔错乱
2. 行末多逗号的原因是客户2的文件中存在超出实际有效数据列的空白列被Excel识别为「已使用列」，导出时会给这些空白列也输出对应的分隔符，就会出现行末多逗号的情况

第二个问题（行末多管道符）的原因 #

这个问题确实和XLSX源文件本身的特性直接相关，根源是Excel的UsedRange（已使用区域）识别规则：

• 输出正常的客户文件中，所有行的最后一个有内容的单元格都在同一列，UsedRange的列数和实际有效列数完全一致，导出时不会多输出分隔符
• 输出带末尾管道符的客户文件中，要么是某几行曾经在更靠右的列输入过内容（哪怕后来删除了内容，格式残留也会被Excel计入UsedRange），要么是存在整列设置了格式但没有内容的情况，Excel导出时会按照识别到的最大UsedRange列数输出，最后一列如果没有内容，就会在末尾多出一个管道分隔符

修复方案 #

• 导出前显式重置工作表的UsedRange：执行$worksheet.UsedRange后再调用$worksheet.UsedRange.Calculate，强制Excel重新计算有效区域，排除空白列的影响
• 如果还是出现末尾分隔符，可以在PowerShell导出后加一行文本处理逻辑，直接替换掉行末的管道符：(Get-Content 输出文件路径) -replace '\|$', '' | Set-Content 输出文件路径
• 避免依赖系统区域设置，导出CSV时直接指定分隔符参数，或者改用EPPlus等不需要依赖Excel COM的库处理XLSX，更适合自动化场景

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