c# - 在 C# 中将数据添加到现有 Excel 文件

Question

链接到错误消息

我正在编写一个程序来进行一些测量并将它们存储在 Excel 文件中。我有一个“执行测试”按钮，单击该按钮时，会创建一个 Excel 工作表，进行测试，将结果存储在 dataGridView 中，然后将 dataGridView 导出到 Excel。这第一次完美运行，但是当我再次单击该按钮进行第二次测试时，会创建一个新的 Excel 工作表（而不是编辑现有的工作表），并且程序崩溃。我希望程序只在现有电子表格中添加另外几行数据，而不覆盖整个文档。

这是我的更新代码。第一次单击按钮调用 CreateExcel() 和 WriteToNewExcel()，第二次单击只调用 WriteToExistingExcel()。

     private void CreateExcel()
    {
        try
        {
            //Create excel sheet
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = true;
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //Create headings
            xlWorkSheet.Cells[1, 1] = "Bandwidth (Hz)";
            xlWorkSheet.Cells[1, 2] = "Centre of marker search range (Hz)";
            xlWorkSheet.Cells[1, 3] = "Qf              ";
            xlWorkSheet.Cells[1, 4] = "Loss (dB)             ";
            xlWorkSheet.Cells[1, 5] = "Shape Factor";
            xlWorkSheet.Cells[1, 5].EntireRow.Font.Bold = true;

            //Autofit column widths
            Cellrange = xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 5]];
            Cellrange.EntireColumn.AutoFit();

            //Save excel sheet
            xlWorkBook.SaveAs("S:\\User Shares\\Lucy\\Lucy's Files\\Anechoic Chamber\\Anechoic Program\\Excel File\\Data",
                Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, false, Excel.XlSaveAsAccessMode.xlExclusive,
                misValue, misValue, misValue, misValue, misValue);
        }
        catch (Exception)
        {
            MessageBox.Show("Caught Error in editing Excel");
        }
    }

    //Write to excel function

    private void WriteToNewExcel()
    {
        try
        {
            //Add heading
            int TestNumber = 1;
            int RowNumber = 2;
            xlWorkSheet.Cells[RowNumber, 1] = "Test " + TestNumber;
            xlWorkSheet.Range[xlWorkSheet.Cells[2, 1], xlWorkSheet.Cells[2, 5]].Merge();
            xlWorkSheet.Cells[2, 5].EntireRow.Font.Italic = true;


            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    xlWorkSheet.Cells[i + 3, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

            ++TestNumber;
            RowNumber = (TestNumber - 1) * 13;
        }
        catch (Exception)
        {
            MessageBox.Show("Caught Error in editing Excel");
        }
        finally
        {
            if (xlWorkSheet != null)
            {
                Marshal.ReleaseComObject(xlWorkSheet);
            }
            if (xlWorkBook != null)
            {
                Marshal.ReleaseComObject(xlWorkBook);
            }
            if (xlApp != null)
            {
                Marshal.ReleaseComObject(xlApp);
                xlApp.Quit();
            }
        }
    }

    private void WriteToExistingExcel()
    {
        try
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open("S:\\User Shares\\Lucy\\Lucy's Files\\Anechoic Chamber\\Anechoic Program\\Excel File\\Data",
            misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //Add heading
            int TestNumber = 2;
            int RowNumber = 13;
            xlWorkSheet.Cells[RowNumber, 1] = "Test " + TestNumber;
            xlWorkSheet.Range[xlWorkSheet.Cells[RowNumber, 1], xlWorkSheet.Cells[RowNumber, 5]].Merge();


            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    xlWorkSheet.Cells[i + 16, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

            ++TestNumber;
            RowNumber = RowNumber + 13;
        }
        catch (Exception)
        {
            MessageBox.Show("Caught Error in editing Excel");
        }
        finally
        {
            if (xlWorkSheet != null)
            {
                Marshal.ReleaseComObject(xlWorkSheet);
            }
            if (xlWorkBook != null)
            {
                Marshal.ReleaseComObject(xlWorkBook);
            }
            if (xlApp != null)
            {
                Marshal.ReleaseComObject(xlApp);
                xlApp.Quit();
            }
        }

    }

但后来我得到了这个错误：System.NullReferenceException：'对象引用未设置为对象的实例。xlApp 为空。在第一次单击测试按钮时。

编辑2：这是我的按钮点击代码：

           private void button_StartTest_Click(object sender, EventArgs e)
    {
        if (NumberOfClick == 1)
        {
            CreateExcel();

            MoveAndMeasure();
            WriteToNewExcel();
            KillAllGroups();
            InitializeAllGroups();
            HomeAllGroups();
        }
        else
        {
            MoveAndMeasure();
            WriteToExistingExcel();
            KillAllGroups();
            InitializeAllGroups();
            HomeAllGroups();
        }
        ++NumberOfClick;
    }

标签： c#excelexcel-interop