c# - 写入工作表导致 "name already taken"

Question

我正在学习如何使用 Interop.Excel。测试 Winforms 程序读取现有 Excel 文件，检查名称“Added_by_program”的选项卡是否存在，如果存在则删除工作表，并创建一个名为“Added_by_program”的新工作表。如果我不尝试写入新工作表，则程序会一遍又一遍地完美运行。当我尝试写入时遇到问题。如果原始文件中不存在工作表，则程序会完美运行一次，并正确写入新创建的工作表。但在随后的运行中，我得到:

"System.Runtime.InteropServices.COMException: 'That name is already taken. Try a different one.'"

对于尝试命名新工作表的行。我必须手动终止打开的 Excel 实例。我错过了什么？

代码(不相关的行去掉)

using System;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace excelReadWrite
{

public partial class Form1 : Form
{
    string readFolder = myPath;

    string inFileName = @"Aram test excel file.xlsx";
    string newSheetName = "Added_by_program";
    Range rawRange = null;
    Range pasteRange = null;
    int rawCols = 0;
    int rawRows = 0;
    int iInSheet = 0;
    int iNewSheet = 0;
    int nInSheets = 0;     
    bool foundRawSheet = false;
    bool foundNewSheet = false;
    Worksheet worksheet = null;

    public Form1()
    {
        InitializeComponent();
    }

    private void start_Button_Click(object sender, EventArgs e)
    {
        string inFile = myPath+ inFileName;
        int nSheets = 0;
        string sheetNames = "";

        // Open Excel workbook to read
        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();

        Workbook workbook = xl.Workbooks.Open(inFile);

        // Count worksheets in opened Excel file
        nSheets = workbook.Worksheets.Count;
        nSheets_TextBox.Text = nSheets.ToString();

        nInSheets = 0;
        foreach (Worksheet worksheet in workbook.Worksheets)
            ++nInSheets;

            //foreach (Worksheet worksheet in workbook.Worksheets)
            for (int iSheet = nInSheets; iSheet >= 1; --iSheet)
        {
            worksheet = workbook.Worksheets[iSheet];
            sheetNames  += " " + worksheet.Name;

            // The program is going to add a worksheet. If it already exists, delete it before adding it.
            if (string.Equals(worksheet.Name, newSheetName))
            {
                workbook.Worksheets[iSheet].Delete();
            }
        }


        // Add a new sheet and name it
        if (foundRawSheet)
        {
         newWorksheet = workbook.Worksheets.Add();

         newWorksheet.Name = newSheetName;
// THE NEXT LINE IS THE PROBLEM LINE
// "Written" WILL BE WRITTEN TO A1:C3 WHEN THE SHEET IS CREATED, BUT THIS LINE
// CAUSES THE ERROR IN SUBSEQUENT RUNS 
// IF I COMMENT IT OUT, THE PROGRAM RUNS FINE, REPEATEDLY
         newWorksheet.Range["A1", "C3"].Value2 = "Written";

        workbook.Save();
        workbook.Close();
        xl.Quit();
    }


}
}

Answer 1

你设置了xl.DisplayAlerts=false ?

如果没有，删除包含现有数据的工作表将导致显示确认对话框。 .

如果 Excel 应用程序可见，Worksheet.Delete将阻塞直到对话框被确认。

如果 Excel 应用程序不可见，您的代码将继续执行(对话框已有效取消 --> 未确认删除)，但不会删除工作表。