Tags

, , , , , , , ,


This is part #1 of a series of tutorial focusing on how to generate Excel document from C# with late binding technique. Note that my WordPress theme is not code friendly, so my apologize in advance.

Why late binding?

If you are a beginner programmer as me, you will find early binding is a lot easier to implement compared to late binding. However, you will have compatibility issue if the users of your application is non controllable. For example, if your code utilize Excel 2007 library, then your application will throw an exception if its being used on Excel 2003. By using late binding, you eliminate this problem (but your code must use only common Excel command).

Lets start!

In a late binding technique, you need to use System.Reflection class, and you need to create some objects variables to wrap the COM object. So here is my typical using section:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Windows.Forms;

And here is the objects initialization:
object xls;

Of course the initialization must be written inside a class.

Now you’re ready to create an Excel instance with the following method (but do not run it yet, because it will create memory leak)

public void create_excel_instance()
{

//create new Excel instance
Type tp;
tp = Type.GetTypeFromProgID("Excel.Application");
xls = Activator.CreateInstance(tp);

//make it visible
object[] parameter = new object[1];
parameter[0] = true;
xls.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, xls, parameter);
xls.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, xls, parameter); 

}

As mentioned above, this method will create a new Excel instance but it will stay running in the background even if you close it manually. That is because the xls variable is still hold reference to to the COM object. So we need to release it with this command:

System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);

The above command must be executed after all the tasks are completed, which means we’ll put in on the last section of our code.

Complete code

here is the complete code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Windows.Forms;

class export_excel
{
object xls;

public void create_excel_instance()
{

//create new Excel instance
Type tp;
tp = Type.GetTypeFromProgID("Excel.Application");
xls = Activator.CreateInstance(tp);

//make it visible
object[] parameter = new object[1];
parameter[0] = true;
xls.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, xls, parameter);
xls.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, xls, parameter);

//release the object
System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);

}

}

So now the class export excel is ready to use by calling the public member create_excel_instance.

Next article will focus on how to make the code nicer.

Advertisements