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;
}
finallyportion of code you need to “close”, “quit” and “release” those COM objects.