3

I want to explore this path of working with Excel dynamically.

I want to use Excel in my C# application without including dlls and stuff. I would just check first if the required Excel version is installed and run it.

First I want to get all the types I need but I can't get hold of them:

// works
Type typeExcel = Type.GetTypeFromProgID("Excel.Application");
object excel = Activator.CreateInstance(typeExcel);
object workbooks = typeExcel.InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);

// this doesn't work, returns null
Type typeWorkbooks = Type.GetTypeFromProgID("Excel.Workbooks");

Without the correct types I can't invoke members. So what am I doing wrong ? How do I load all types I need and know that they are there ? My current Excel version is 2003.

Reasons for this: If I include COM Libraries that are not installed on the target system my application wont start. If I load them dynamically I can check for their existence and notify the user about missing functionality.
Bitterblue
  • 13,162
  • 17
  • 86
  • 124
  • What are you trying to accomplish exactly? If you want to rely on interop (and thus not having to use DLLs but having to add the given reference to your project and target your development to the specifically-referred Office version(s)), you don't need to do that. Here you have a start guide: http://support.microsoft.com/kb/302084/en-us – varocarbas Sep 26 '13 at 08:37
  • PS: also bear in mind that Office 2003 is pretty old and has "some peculiarities". For example, the "Redistributable Primary Interop Assemblies" have to be installed on the computer where you want to use the application (http://www.microsoft.com/en-us/download/details.aspx?id=20923). This is also required with Office 2007 (its specific package); but not anymore since Office 2010. – varocarbas Sep 26 '13 at 08:40
  • @varocarbas I'll ask my boss later if I can work with Office 2010. But for now I'm stuck with 2003. – Bitterblue Sep 26 '13 at 08:47
  • OK. These are the "classic" interop rules. Apparently GSerg proposes some alternative (fitting within what was written in your original code) about which I am not too sure. – varocarbas Sep 26 '13 at 08:52
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Sep 26 '13 at 08:54
  • Usually, you have to add a reference to the given COM (Excel) Object, which is the one of the Office version installed on the computer where you are building your application. This allows you to have full access to the given Interop namespace (Microsoft.Office.Interop.Excel). As said, this interop part has also to be installed in the target machine (redistributable upto version 2007 or automatically with Office since then). What you are proposing is skipping all this and accessing directly the Excel version in the given computer. Never done that and thus no idea about its actual reliability. – varocarbas Sep 26 '13 at 09:04

2 Answers2

7

Use dynamic.

Type typeExcel = Type.GetTypeFromProgID("Excel.Application");

dynamic excel = Activator.CreateInstance(typeExcel);
excel.Visible = true;

dynamic workbooks = excel.Workbooks;
workbooks.Add();
workbooks.Add();

Also see this answer.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • What is exactly dynamic? and what is its relationship with interop and the difference Office versions? – varocarbas Sep 26 '13 at 08:50
  • @varocarbas `dynamic` is a [C# feature](http://stackoverflow.com/q/961581/11683). – GSerg Sep 26 '13 at 08:52
  • Thanks for the clarification, very instructive. Still not sure about the exact applicability of all this to Office/interop, but certainly interesting anyway. – varocarbas Sep 26 '13 at 08:55
  • Can you provide more code to set an actual cell ? There are some methods missing like `workbook.get_Item(...)` and `sheet.get_Range(...)`. – Bitterblue Sep 26 '13 at 09:27
  • @mini-me I am afraid that you haven't understood what you were requesting: you are asking for what the aformentioned Interop namespace contains; to access it, you need to follow the steps I explained via comments. Dynamic (as perfectly explained in the link above) is just like "Object", that is, it does not know anything about the given contents (methods, properties, etc.). On top of that, I haven't ever tested this approach and thus I am not even sure about its actual reliability and version-to-version compatibility. – varocarbas Sep 26 '13 at 09:31
  • I asked GSerg. Sorry if you misunderstood me. But I fixed it and my tests were successful with GSerg's suggested way. See my reasons above why I don't want to include the libraries at design time. – Bitterblue Sep 26 '13 at 09:40
  • @mini-me I know that you asked GSerg, but as far as this is not just for you but for any future reader, I wanted to make the point clear. From your question, I thought that you weren't understanding things exactly. If this wasn't the case, I am happy that you got your problem solved but will let my comment anyway for future readers to help them understand that this solution delivers a black box (whose reliability is unknown to me at the moment) with no kind of visual help while programming (what interop provides). – varocarbas Sep 26 '13 at 09:51
2

If I include COM Libraries that are not installed on the target system my application wont start.

The cure you are looking for is considerably worse than the problem. There isn't anything that pretty about late-binding Office code as you are attempting in your snippet. The dynamic keyword supported in C# version 4 certainly makes the syntax a lot friendlier. There's however nothing friendly about it at code-writing time, you won't get any IntelliSense. And nothing friendly at run-time, the mistakes you make at coding-time will cause exceptions and late-binding has considerable overhead.

There are simple counter-measures to ensure the interop assemblies are available:

  • Ask your user to install the Office Primary Interop Assemblies (PIAs).

  • It is quite rare to actually need a PIA, it is only necessary when you expose an Office type in your own public methods and use it in another assembly. Select the Microsoft.Office.Interop assemblies in your Reference node and set their Copy Local property to true. Rebuild and you'll get those assemblies in your build directory. Copy them along with your own executables to the client machine.

  • VS2010 and up have the Golden Solution to this deployment detail. Select the interop assembly in your Reference node and set the Embed Interop Types property to True. The interop declarations will now be merged into your own executable and you don't have to deploy the interop assemblies or PIA anymore.

Since you accepted the dynamic solution, available on VS2010 and up, the last bullet is the one you want.

Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536