0

Link to error message

I am writing a program to take some measurements and store them in an Excel file. I have a 'Do Test' button that, when clicked, creates an excel sheet, does a test, stores the results in a dataGridView, and then exports the dataGridView to Excel. This works perfectly the first time, but when I click the button again for a second test, a new Excel sheet is created (rather than editing the existing one), and the program crashes. I want the program to just add another few rows of data to the existing spreadsheet without overwriting the whole document.

Here's my UPDATED code. The first button click calls CreateExcel() and WriteToNewExcel(), and the second click just calls 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();
            }
        }

    }

But then I got this error: System.NullReferenceException: 'Object reference not set to an instance of an object. xlApp was null.' on the first click of the test button.

Edit 2: This is my code for the button click:

           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;
    }
28
  • Does this answer your question? Appending data to existing Excel file using C# Commented Jul 30, 2021 at 9:24
  • Thank you - I seem to have a similar problem to this other asker but I cannot figure out what bit of code he used to fix it! Commented Jul 30, 2021 at 9:56
  • 1
    I bet there are MANY Excel apps running… check the task manager after running the code a few times. Minimum, you should “wrap” all the Excel code in a try/catch/finally construct. In the finally portion of code you need to “close”, “quit” and “release” those COM objects. Commented Jul 30, 2021 at 11:05
  • 1
    Thanks for this - does this mean that I have to close the entire Excel application every time I want to edit it? Commented Jul 30, 2021 at 11:11
  • 1
    I don't think CreateExcel() has been called again, unless my button function is incorrect? I've added it above Commented Jul 30, 2021 at 12:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.