Excel Interop调用Workbooks.Open打开CSV时忽略指定分隔符问题

当Workbooks.Open方法的Format参数指定为Excel.XlFileFormat.xlCSV时，Excel会优先读取系统区域设置中的默认列表分隔符（绝大多数系统默认是逗号），你传入的第9位Delimiter参数会被直接忽略，只有当Format参数设置为文本格式（xlText）时，自定义分隔符参数才会生效。

方案1：调整Open方法参数 #

将Format参数改为Excel.XlFileFormat.xlText，保持分号分隔符参数即可生效，修正后代码如下：

Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbooks workbooks = app.Workbooks;
// 核心修改：将xlCSV改为xlText
Excel.Workbook wb = app.Workbooks.Open(sourceFile, 
                                       Type.Missing,
                                       Type.Missing,
                                       Excel.XlFileFormat.xlText,   // 此处修改为文本格式
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       ";",          // 分号分隔符正常生效
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing);
wb.SaveAs(newXLSXPath, Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close();
app.Quit();
// 补充：释放COM对象避免残留Excel后台进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

方案2：临时修改Excel的列表分隔符配置 #

如果必须使用xlCSV格式打开，可以临时修改Excel实例的分隔符配置，操作完成后还原即可：

Excel.Application app = new Excel.Application();
// 临时禁用系统分隔符，指定列表分隔符为分号
bool originalUseSystem = app.UseSystemSeparators;
string originalSeparator = app.ListSeparator;
app.UseSystemSeparators = false;
app.ListSeparator = ";";

app.Visible = true;
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook wb = app.Workbooks.Open(sourceFile, 
                                       Type.Missing,
                                       Type.Missing,
                                       Excel.XlFileFormat.xlCSV,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing);
wb.SaveAs(newXLSXPath, Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close();
// 还原原配置
app.UseSystemSeparators = originalUseSystem;
app.ListSeparator = originalSeparator;
app.Quit();
// 释放COM对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

方案3：使用专门的OpenText方法处理分隔符文件 #

Workbooks.OpenText是专为分隔符文本文件设计的导入方法，分隔符配置更稳定，不会受区域设置影响：

Excel.Application app = new Excel.Application();
app.Workbooks.OpenText(
    Filename: sourceFile,
    Origin: XlPlatform.xlWindows,
    StartRow: 1,
    DataType: XlTextParsingType.xlDelimited,
    TextQualifier: XlTextQualifier.xlTextQualifierDoubleQuote,
    Semicolon: true, // 直接指定启用分号作为分隔符
    Comma: false
);
Excel.Workbook wb = app.ActiveWorkbook;
wb.SaveAs(newXLSXPath, Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close();
app.Quit();
// 释放COM对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

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