R
rhett
howdy folks,
first off, I'd like to not that I have browsed the forums and thanks to
what I've found there I'm 80% through this problem, but the last part
seems to be a profound obstacle and any help you could provide would be
much appreciated.
As my title indicates, I'm devloping a C# application which will need
to opne up an excel template...manipulate and save it, and then quit.
The problem I'm having is that the instance of EXCEL.EXE remains open
even when I release all of the objects I manipulate, null them, and run
GC.Collect(). Many thanks to all of you who have posted on this topic
before or I wouldn't have even gotten as far as releasing/collecting
etc.
Here is a sample of code that exhibits the problem.
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(instring, 0, false,
5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0,
true);
Excel.Worksheet ws = (Excel.Worksheet)xlApp.Sheets[1];
for (i = 0; i<items.Count; i++)
{
//insert a column
Excel.Range rng = (Excel.Range)ws.Cells[3, 4];
Excel.Range column = rng.EntireColumn;
column.Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
System.Runtime.InteropServices.Marshal.ReleaseComObject(column);
column = null;
//delete superfluous cells
rng = ws.get_Range("D5", "D11");
rng.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
rng = ws.get_Range("D41", "D55");
rng.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
//adjust formatting of inserted column
rng = ws.get_Range("D14", "D16");
rng.Merge(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
rng = null;
}
excelWorkbook.SaveAs("c:\\Program
Files\\rpm\\incentive\\grouptemplates\\" + Companion_file_name +
".xlt", Excel.XlFileFormat.xlTemplate,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
excelWorkbook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
The previous code will successfully remove the instance if I don't
execute the
rng.Merge(Type.Missing);
line. Otherwise the Excel instance remains.
Is there a way I can find out what COM objects are open so I know which
ones I am implicitly opening and not releasing? Is there a problem
with reusing the range object (I've tried nulling it before assigning a
different value to it, but with no luck). Are there other actions I
should be taking? Many thanks for any help anyone can give,
-rhett
first off, I'd like to not that I have browsed the forums and thanks to
what I've found there I'm 80% through this problem, but the last part
seems to be a profound obstacle and any help you could provide would be
much appreciated.
As my title indicates, I'm devloping a C# application which will need
to opne up an excel template...manipulate and save it, and then quit.
The problem I'm having is that the instance of EXCEL.EXE remains open
even when I release all of the objects I manipulate, null them, and run
GC.Collect(). Many thanks to all of you who have posted on this topic
before or I wouldn't have even gotten as far as releasing/collecting
etc.
Here is a sample of code that exhibits the problem.
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(instring, 0, false,
5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0,
true);
Excel.Worksheet ws = (Excel.Worksheet)xlApp.Sheets[1];
for (i = 0; i<items.Count; i++)
{
//insert a column
Excel.Range rng = (Excel.Range)ws.Cells[3, 4];
Excel.Range column = rng.EntireColumn;
column.Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
System.Runtime.InteropServices.Marshal.ReleaseComObject(column);
column = null;
//delete superfluous cells
rng = ws.get_Range("D5", "D11");
rng.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
rng = ws.get_Range("D41", "D55");
rng.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
//adjust formatting of inserted column
rng = ws.get_Range("D14", "D16");
rng.Merge(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
rng = null;
}
excelWorkbook.SaveAs("c:\\Program
Files\\rpm\\incentive\\grouptemplates\\" + Companion_file_name +
".xlt", Excel.XlFileFormat.xlTemplate,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
excelWorkbook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
The previous code will successfully remove the instance if I don't
execute the
rng.Merge(Type.Missing);
line. Otherwise the Excel instance remains.
Is there a way I can find out what COM objects are open so I know which
ones I am implicitly opening and not releasing? Is there a problem
with reusing the range object (I've tried nulling it before assigning a
different value to it, but with no luck). Are there other actions I
should be taking? Many thanks for any help anyone can give,
-rhett