H
Hendri Adriaens
Hi,
I'm trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that I
create. The code below runs just fine and excel is closed. But there are
some commented lines:
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChanges, missing, missing,
missing, missing);
If I uncomment either one of these commands, excel won't close anymore. I
guess these lines create some COM objects that I have to release. My
questions:
1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so excel
can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up the
memory or present a memory leak?
Thank a lot in advance, best regards,
-Hendri Adriaens.
--------- The code ---------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;
public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChanges, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXCEL"))
// {
// proc.Kill();
// }
}
private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(theObject) >
0)
{
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}
}
I'm trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that I
create. The code below runs just fine and excel is closed. But there are
some commented lines:
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChanges, missing, missing,
missing, missing);
If I uncomment either one of these commands, excel won't close anymore. I
guess these lines create some COM objects that I have to release. My
questions:
1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so excel
can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up the
memory or present a memory leak?
Thank a lot in advance, best regards,
-Hendri Adriaens.
--------- The code ---------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;
public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChanges, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXCEL"))
// {
// proc.Kill();
// }
}
private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(theObject) >
0)
{
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}
}