Hey Everyone,
I have a project in which im' trying to populate an excel file i've opened with data from a datatable.
I can accomplish this but where I get hung up is in releasing resources and making sure the instance of Excel.EXE closes properly upon the function finishing.
I've narrowed down the problem to when I populate excel cells with data from the datatable.
I do so in a foreach loop
void saveExcelFile()
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlWorkSheet = null;
object missingVal = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(filename, 0, false, 5, "", "", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,false,0,true,1,0);
xlSheets = xlWorkBook.Worksheets;
xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item("Summary");
int i = 13;
foreach(DataRow in dt.rows)
{
if(i <=32)
{
xlWorkSheet.Cells[i, 2] = row["price"];
xlWorkSheet.Cells[i, 3] = row["width"];
xlWorkSheet.Cells[i, 4] = row["depth"];
xlWorkSheet.Cells[i, 5] = row["height"];
}
i++;
}
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(filename2);
xlApp.DisplayAlerts = true;
if (xlApp != null)
{
xlWorkBook.Close(false, missingVal, missingVal);
xlApp.Quit();
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp) != 0) { }
if (xlSheets != null)
{
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheets) != 0) { }
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet) != 0) { }
}
if (xlWorkBook != null)
{
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook) != 0) { }
}
if (xlApp != null)
{
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp) != 0) { }
}
}
xlSheets = null;
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
If I comment out my for loop, the instances of my workbook, application and worksheet all get released properly.
I believe I'm creating com objects here in this loop that don't get released properly, but don't know how to best rectify the situation when using the Cell reference. I've seen examples on this site and others but none using a loop.
Any guidance would be appreciated!
Thanks!
Ethan
**Added rest of function code ~ Thanks