I have a Windows Form Application with a DataGridView where a Browse button allows the user to select an existing excel file and import the data to a datagridview.
I'm using Interop.Excel and in the btnBrowse_Click event I declare a new instance of Excel.Application, Workbook and Worksheet, I read the sheets in the selected excel file, put the list of sheets to a List and bind it to a ComboBox where the user selects the sheet they wish to load.
Next when the user selects the desired sheet in the ComboBox, the application loads the data from the selected sheet into the DataGridView.
All works well as far as the import goes, but the btnBrowse_Click event doesn't end the EXCEL process that it starts, and when the user selects the desired sheet I re-read the Excel file again to pull the data (this time from the cbxSelectSheet ComboBox_SelectedIndexChanged event), this creates a new EXCEL process and until the SelectedIndexChanded event is complete, there are two EXCEL process running. But when this event is complete it ends both the first and second EXCEL process.
Here is my code for the browse button that is clicked first.
using Excel = Microsoft.Office.Interop.Excel;
private void btnBrowse_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog folderBrowserDialog1 = new OpenFileDialog();
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
resetImportData();
txtFilePath.Clear();
FileInfo file = new FileInfo(folderBrowserDialog1.FileName);
txtFilePath.Text = file.ToString();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook excelBook = xlApp.Workbooks.Open(file.ToString());
List<String> sheets = new List<String>();
sheets.Add("<Select a Sheet>");
foreach (Excel.Worksheet wSheet in excelBook.Worksheets)
{
sheets.Add(wSheet.Name);
}
// Bind the sheet list to the cbxSheetSelect
cbxSheetSelect.DataSource = sheets;
cbxSheetSelect.Select();
excelBook.Close(false);
releaseObject(xlApp);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
And at the end I call a separate function to release the xlApp, here is the code for that.
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
Next, the following event is fired when the user makes a selection on the cbxSelectSheet combobox.
private void cbxSheetSelect_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook excelBook = xlApp.Workbooks.Open(txtFilePath.Text);
Excel.Worksheet sheet = new Excel.Worksheet();
// Find the worksheet selected in cbxSheetSelect
foreach (Excel.Worksheet tmpSheet in excelBook.Worksheets)
{
if (tmpSheet.Name == cbxSheetSelect.SelectedValue.ToString())
{
sheet = tmpSheet;
break;
}
}
// Check for null cells in Row 1 and add first row to _dtItemHeader and _dtQtyHeader tables
foreach (Excel.Range c in sheet.get_Range("A1:Z1").Cells)
{
if (c.Value != null)
{
DataRow drItem = _dtItemHeader.NewRow();
drItem["Address"] = c.Address;
drItem["Name"] = c.Value;
_dtItemHeader.Rows.Add(drItem);
DataRow drQty = _dtQtyHeader.NewRow();
drQty["Address"] = c.Address;
drQty["Name"] = c.Value;
_dtQtyHeader.Rows.Add(drQty);
}
}
excelBook.Close(false);
releaseObject(xlApp);
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
And this time when I execute releaseObject(xlApp), both EXCEL process are ended.
On a side note, if I click the browse button, select an excel file but don't make a selection on my ComboBox, the EXCEL process remains running until I close the application. The form this is on, is three deep into the application and closing just the form doesn't end the EXCEL process, I have to close the application.
Why is the btwBrowse_Click event not ending the EXCEL process when my excelBook.Close() and releaseObject() events are the same?
Also, every once in a while without any pattern, my Outlook 2010 will crash if I try to send a new email after loading the excel data.
This Outlook crash only started happening after I added the ability to import data from an excel file, it's happened on other workstations I've tried the application on, but there doesn't seem to be any patterns. I do have a custom written Outlook add-on, but this add-on is written in VB.NET and has nothing to do with Excel.
~~~~Edit~~~~
I updated my application based on Scott Chamberlain's comment and I'm getting mixed results.
This is one of the functions where I'm now using GC.Collect()
private void getExcelFileInfo()
{
Excel.Application xlApp = null;
Excel.Workbook xlBook = null;
Excel.Worksheet xlSheet = null;
try
{
// Declare new excel app, workbook and sheet
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Open(txtFilePath.Text);
xlSheet = new Excel.Worksheet();
// Collect sheets in xlBook and add them to _sheets public list
foreach (Excel.Worksheet tmpSheet in xlBook.Worksheets)
_sheets.Add(tmpSheet.Name);
if (_sheets.Count > 0)
{
cbxSheetSelect.Enabled = true;
cbxSheetSelect.DataSource = _sheets;
cbxSheetSelect.SelectedIndex = 0;
}
else
{
cbxSheetSelect.Enabled = false;
cbxSheetSelect.DataSource = null;
}
}
catch (Exception ex)
{
frmMessage msgFrm = new frmMessage("An Error has occurred...", null, ex.Message + "\n" + "\n" + "Error Code: 357", "Error");
msgFrm.ShowDialog();
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
releaseObject(xlSheet);
xlBook.Close(false);
releaseObject(xlBook);
xlApp.Quit();
releaseObject(xlApp);
}
}
This approach works perfectly on some workstation, but not on others. I tested this on 4 different workstations, all have the same version of office. On mine and an engineering workstation it worked perfectly and immediately ended the EXCEL process.
But on a virtual server session (Windows Server 2008 R2) it didn't release the EXCEL process until after the form application was closed. And the fourth workstation I tried it on was our CFO's workstation and I got the same results as on the virtual RD session, it didn't release the EXCEL process until after the form app was closed.
On the virtual RD session my user doesn't have local admin rights, but on the other three workstations I tried, the users all had local admin rights, including the CFO where it didn't end the process.
I'm getting a bit desperate at this point, any thoughts or ideas would be very welcomed.
~~~~~Second Edit~~~~~
Also, it seems when using the above approach my Outlook and a few other applications like Syteline ERP crash constantly, I'm not sure if it's GC.Collect() or releaseObject() that started causing it, but my Outlook and ERP definitely did NOT crash until I added those two to my form app. The crashes are random and I can't replicate the exact issue that causes it, but so far it only happens AFTER the user has used either the import or export feature on my form app, both of which use Interop.Excel
I tried changing it to just:
xlBook.Close(false);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
But this does not kill the EXEC.exe process until the form app is closed.
~~~~~Final Edit~~~~~
Found the answer in the linked duplicate post. I missed it on my first read through, so I'll add the code that ended up working for me on every workstation.
Basically I moved the code that does the actual Excel Reading to it's own function and I then called that function from my btnBrowse_Click event. The trick as Hans Passant pointed out in the linked answer, is WHERE I put the GC.Collect() command.
In my getExcelFileInfo() event I close and quit my xlBook and xlApp, but I put the GC.Collect() command in the finally {} secion of my btnBrowse_Click event.
private void btnBrowse_Click(object sender, EventArgs e)
{
try
{
getExcelFileInfo();
}
catch (Exception ex)
{
frmMessage msgFrm = new frmMessage("An Error has occurred...", null, ex.Message + "\n" + "\n" + "Error Code: 269", "Error");
msgFrm.ShowDialog();
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void getExcelFileInfo()
{
Excel.Application xlApp = null;
Excel.Workbook xlBook = null;
Excel.Worksheet xlSheet = null;
try
{
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Open(txtFilePath.Text);
xlSheet = new Excel.Worksheet();
// Read the excel file
}
catch (Exception ex)
{
frmMessage msgFrm = new frmMessage("An Error has occurred...", null, ex.Message + "\n" + "\n" + "Error Code: 357", "Error");
msgFrm.ShowDialog();
}
finally
{
xlBook.Close(false);
xlApp.Quit();
}
}
