基于Excel Interop的PLC数据采集应用仅能单次写入数据求助

核心问题 #

你的应用只能单次正常运行的原因集中在以下几点：

1. Excel COM对象未彻底释放：首次运行后Excel进程残留，导致模板文件被锁定，第二次采集时无法写入数据，仅能执行另存为操作。
2. SaveFileDialog结果未处理：用户选择的保存路径和文件名未更新到配置中，Properties.Settings.Default.filename仍为旧值，引发后续操作异常。
3. 代码存在语法/变量错误：未定义的excelcolumn变量、异常处理中的字符串拼接错误，导致写入逻辑失效或异常无法正确捕获。

修复步骤 #

1. 彻底释放Excel COM对象 #

必须确保所有Excel相关COM对象按逆序释放，避免进程残留。将对象初始化与释放逻辑放入try-finally块，保证异常场景下也能执行释放：

Excel.Application excel = null;
Excel.Workbook workbook = null;
Excel.Worksheet sh = null;

try
{
    // 初始化Excel对象、工作簿、工作表的逻辑
    excel = new Excel.Application();
    string filelocation = Properties.Settings.Default.templateLocation;
    workbook = excel.Workbooks.Open(filelocation);
    sh = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["MULTIPLE_SHOT_DATA"];

    // 后续的SaveFileDialog、数据采集与写入逻辑...
}
finally
{
    // 按逆序释放对象
    if (sh != null)
    {
        Marshal.FinalReleaseComObject(sh);
        sh = null;
    }
    if (workbook != null)
    {
        workbook.Close(false); // 关闭时不修改原模板
        Marshal.FinalReleaseComObject(workbook);
        workbook = null;
    }
    if (excel != null)
    {
        excel.Quit();
        Marshal.FinalReleaseComObject(excel);
        excel = null;
    }
    // 强制触发垃圾回收，确保COM对象彻底释放
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

2. 正确处理SaveFileDialog返回值 #

用户点击保存后，需更新配置中的路径与文件名，避免后续操作使用旧值：

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
if (!string.IsNullOrEmpty(Properties.Settings.Default.filedir))
{
    saveFileDialog1.InitialDirectory = Properties.Settings.Default.filedir;
}
saveFileDialog1.Filter = "Worksheet (*.xlsx)|*.xlsx|All Files (*.*)|*.*";
saveFileDialog1.Title = "Save an Excel File";

// 处理用户选择结果
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
    Properties.Settings.Default.filedir = Path.GetDirectoryName(saveFileDialog1.FileName);
    Properties.Settings.Default.filename = saveFileDialog1.FileName;
    Properties.Settings.Default.Save(); // 持久化配置
}
else
{
    // 用户取消保存，直接终止流程
    return;
}

3. 修复变量与语法错误 #

• 定义excelcolumn变量（根据业务逻辑设置列值，示例为列循环+1）；
• 修复异常处理中的字符串拼接错误：

// 写入数据时定义列变量
int excelcolumn = columnloop + 1;

// 异常处理修正字符串拼接
catch (Exception ex)
{
    AddEvent("Error, Item.Quality is " + _myItem.Quality.ToString());
}

4. 调整SaveAs参数 #

使用用户选择的文件名保存，确保原模板不被修改：

workbook.SaveAs(Properties.Settings.Default.filename, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

修正后的核心代码片段 #

Excel.Application excel = null;
Excel.Workbook workbook = null;
Excel.Worksheet sh = null;

try
{
    // Open the Excel application
    excel = new Excel.Application();
    // Open the workbook
    string filelocation = Properties.Settings.Default.templateLocation;
    workbook = excel.Workbooks.Open(filelocation);

    //Define the worksheet and open
    sh = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["MULTIPLE_SHOT_DATA"];

    //Saves as dialog control to get filename and location
    SaveFileDialog saveFileDialog1 = new SaveFileDialog();
    if (!string.IsNullOrEmpty(Properties.Settings.Default.filedir))
    {
        saveFileDialog1.InitialDirectory = Properties.Settings.Default.filedir;
    }

    saveFileDialog1.Filter = "Worksheet (*.xlsx)|*.xlsx|All Files (*.*)|*.*";
    saveFileDialog1.Title = "Save an Excel File";
    if (saveFileDialog1.ShowDialog() != DialogResult.OK)
    {
        return;
    }

    // Update settings
    Properties.Settings.Default.filedir = Path.GetDirectoryName(saveFileDialog1.FileName);
    Properties.Settings.Default.filename = saveFileDialog1.FileName;
    Properties.Settings.Default.Save();

    //Column loop should be 24 or 36 for different ball machines
    for (int columnloop = 0; columnloop < 24; columnloop++)
    {
        int inbounddata = columnloop;
        string inbounddatastring = inbounddata.ToString();
        _myItem.HWTagType = TagType.AUTO;
        _myItem.HWTagName = "Ball[" + inbounddatastring + ",0]";
        _myItem.Elements = 100;

        try
        {
            // Call Item.Read method
            _myItem.Read();

            // For atomic types, each Item.Values element represents one atomic value.
            if (!_myItem.Values[0].GetType().IsArray)
            {
                int excelcolumn = columnloop + 1; // 定义列变量，按需调整
                for (int i = 0; i < _myItem.Elements; i++)
                {
                    int fint = i + 1;
                    sh.Cells[fint, excelcolumn] = _myItem.Values[i];
                }
            }
            // For structured types (UDT, PDT, and System), each Item.Values element represents an array of bytes
            else
            {
                // 补充结构化类型处理逻辑（如有需要）
            }
        }
        catch (Exception ex)
        {
            AddEvent("Error, Item.Quality is " + _myItem.Quality.ToString());
        }
        finally
        {
            this.Cursor = Cursors.Default;
        }
    }

    //Save as and close active file
    workbook.SaveAs(Properties.Settings.Default.filename, Type.Missing, Type.Missing,
         Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
finally
{
    // Release COM objects
    if (sh != null)
    {
        Marshal.FinalReleaseComObject(sh);
    }
    if (workbook != null)
    {
        workbook.Close(false);
        Marshal.FinalReleaseComObject(workbook);
    }
    if (excel != null)
    {
        excel.Quit();
        Marshal.FinalReleaseComObject(excel);
    }
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

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