Integrating Excel objects/methods with C#
Tony Lin
apparent ease with which one can access objects and methods in MS Office
applications. For example, I was impressed with the ease of opening Excel,
such as:
Excel._Application xlApp = new Excel.Application();
xlApp.Caption = "My Excel App";
xlApp.Visible = true;
Unfortunately, I am finding that as I go beyond this, there are a many Excel
methods and properties that don't seem to work when accessed from C#. Here
are two examples of some very basic Excel functions that don't work under
C#:
Example 1:
-------------
The following compiles and executes fine in Excel VBA:
Set xlBook = xlApp.Workbooks.Add("Workbook")
xlBook.Sheets("Sheet2").Activate
I can't get the following C# version to compile:
Excel.Workbook xlBook = xlApp.Workbooks.Add("Workbook"); // ok
xlBook.Sheets("Sheet2").Activate(); // doesn't compile
xlBook.Sheets["Sheet2"].Activate(); // doesn't compile either
Example 2:
-----------------
The following compiles and executes fine in Excel VBA:
Dim x As Integer
Set xlSheet = xlBook.ActiveSheet
x = xlSheet.Cells(2, 1)
The equivalent C# version below compiles, but throws an invalid cast
exception when executing the second line:
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.ActiveSheet; // ok
int x = (int)sLot.Cells[2,1]; // Compiles but throws invalid cast
exception
Is there another way I could have written the C# code to compile and
execute?
I can give a number of other examples of code that runs fine in Excel VBA
but where I can't their C# equivalent to either compile or execute. The
conclusion I draw is that integrating Excel into my C# application is simply
not a practical alternative. The Microsoft Excel 9.0 COM library just
doesn't work well enough to be of practical use.
Is there a book or documentation that provides a how-to on integrating Excel
with C#?
Is this do-able or am I wasting my time trying to do this? Am I approaching
this correctly?
I would like to hear from other members of this forum as to their experience
integrating VBA Excel into their C# apps.
I look forward to hearing input on this subject from other forum members.
Tony Lin
Fremont, CA
Yan-Hong Huang[MSFT]
Programming Excel in VBA and C# is somewhat different in the calling
method. C# uses automation to access Excel methods and properties. Please
refer to the following articles for code sampes in this area:
"HOWTO: Automate Microsoft Excel from Microsoft Visual C# .NET"
http://support.microsoft.com/?id=302084
"HOW TO: Handle Events for Excel by Using Visual C# .NET"
http://support.microsoft.com/?id=302815
"HOW TO: Transfer XML Data to Microsoft Excel 2002 by Using Visual C# .NET"
http://support.microsoft.com/?id=307029
"HOWTO: Create an Excel Macro Using Automation from Visual C# .NET"
http://support.microsoft.com/?id=303872
HTH.
Best regards,
yhhuang
VS.NET, Visual C++
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
Got .Net? http://www.gotdotnet.com
--------------------
!From: "Tony Lin" <to...@linfamily.org>
!Subject: Integrating Excel objects/methods with C#
!Date: Sun, 8 Jun 2003 21:25:16 -0700
!Lines: 123
!X-Priority: 3
!X-MSMail-Priority: Normal
!X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
!X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
!Message-ID: <#l4qK8jL...@TK2MSFTNGP11.phx.gbl>
!Newsgroups: microsoft.public.dotnet.languages.csharp
!NNTP-Posting-Host: 12-235-103-45.client.attbi.com 12.235.103.45
!Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:161187
!X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
!
!When I first started working in .NET, one feature that impressed me was the
!apparent ease with which one can access objects and methods in MS Office
!applications. For example, I was impressed with the ease of opening Excel,
!such as:
!
!
!
! Excel._Application xlApp = new Excel.Application();
!
! xlApp.Caption = "My Excel App";
!
! xlApp.Visible = true;
!
!
!
!Unfortunately, I am finding that as I go beyond this, there are a many
Excel
!methods and properties that don't seem to work when accessed from C#. Here
!are two examples of some very basic Excel functions that don't work under
!C#:
!
!
!
!
!
!Example 1:
!
!-------------
!
!The following compiles and executes fine in Excel VBA:
!
!
!
! Set xlBook = xlApp.Workbooks.Add("Workbook")
!
! xlBook.Sheets("Sheet2").Activate
!
!
!
!
!
!I can't get the following C# version to compile:
!
!
!
! Excel.Workbook xlBook = xlApp.Workbooks.Add("Workbook"); // ok
!
! xlBook.Sheets("Sheet2").Activate(); // doesn't compile
!
! xlBook.Sheets["Sheet2"].Activate(); // doesn't compile either
!
!
!
!
!
!
!
!Example 2:
!
!-----------------
!
!The following compiles and executes fine in Excel VBA:
!
!
!
! Dim x As Integer
!
! Set xlSheet = xlBook.ActiveSheet
!
! x = xlSheet.Cells(2, 1)
!
!
!
!The equivalent C# version below compiles, but throws an invalid cast
!exception when executing the second line:
!
!
!
! Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.ActiveSheet; // ok
!
! int x = (int)sLot.Cells[2,1]; // Compiles but throws invalid cast
!exception
!
!
!
!
!
!Is there another way I could have written the C# code to compile and
!execute?
!
!
!
!I can give a number of other examples of code that runs fine in Excel VBA
!but where I can't their C# equivalent to either compile or execute. The
!conclusion I draw is that integrating Excel into my C# application is
simply
!not a practical alternative. The Microsoft Excel 9.0 COM library just
!doesn't work well enough to be of practical use.
!
!
!
!Is there a book or documentation that provides a how-to on integrating
Excel
!with C#?
!
!
!
!Is this do-able or am I wasting my time trying to do this? Am I
approaching
!this correctly?
!
!
!
!I would like to hear from other members of this forum as to their
experience
!integrating VBA Excel into their C# apps.
!
!
!
!I look forward to hearing input on this subject from other forum members.
!
!
!
!Tony Lin
!
!Fremont, CA
!
!
!
Tony Lin
I read through the reference you suggested, "HOWTO: Automate Microsoft Excel
from Microsoft Visual C# .NET", http://support.microsoft.com/?id=302084
It is the sort of documentation I read that got me interested in trying to
integrate Excel into my C# application. Unfortunately, it is very
superficial. By that, I mean that it uses only examples of Excel objects
and methods that work in C#. It says nothing about the many Excel methods
and objects that don't work.
The fact that many objects and methods work in C# isn't the problem. The
point of my post is that many Excel objects and methods just DON'T work. I
gave two examples that don't work, and these are very, very basic Excel
operations.
For example, the referenced article shows an example of setting values into
individual cells. For example:
oSheet.Cells[1, 1] = "First Name";
Unfortunately, the opposite doesn't work as I pointed out in my original
post:
string firstName = (string)oSheet.Cells[1.1]; // throws invalid cast
exception
As I pointed out in my post, the above statement compiles but throws an
invalid cast exception on execution. Can you make this work, or am I doing
something wrong, or have I missed something?
I am open to suggestions, but the referenced article unfortunately does not
address my question.
Tony Lin
Fremont, CA
>
The reference you gave me,
"Yan-Hong Huang[MSFT]" <yhh...@online.microsoft.com> wrote in message
news:nBd2gUkL...@cpmsftngxa06.phx.gbl...
Nicholas Paldino [.NET/C# MVP]
The Excel objects actually do work, you are not using them correctly.
VB has a lot of tricks to make thngs just work, which throws people for a
loop when they want to do things outside of VB and they don't know what is
going on under the hood.
Here are the fixes for your problems:
Excel.Workbook xlBook = xlApp.Workbooks.Add("Workbook"); // ok
xlBook.Sheets("Sheet2").Activate(); // doesn't compile
xlBook.Sheets["Sheet2"].Activate(); // doesn't compile either
These dont compile because the Sheets collection returns a type of
object (which is a VARIANT in COM/Automation). You have to cast this to a
type of Excel.Worksheet and then you should be able to call the Activate
method.
int x = sLot.Cells[2,1];
Place a breakpoint on this line and check the value of sLot.Cells[2,1]
in the debugger when you get to this line. See what the type is that is
returned and it should give you a clue as to how to proceed.
One last problem. Every time that you call a property or method that
exposes another object, you are increasing the reference count on that
object. Because of this, you will have to make sure to call the static
ReleaseComObject on the Marshal class, passing in the object. Taking your
first example:
xlBook.Sheets("Sheet2").Activate(); // doesn't compile
This exposes three objects and will increase the reference count on
each. The problem with this is that these objects hold a reference to the
main application, so when you try and close your application, you will
notice Excel is running in the background still. To get around it, you have
to do this:
// Get the worksheets.
Excel.Worksheets pobjSheets = xlBook.Sheets;
// Get the worksheet.
Excel.Worksheet pobjSheet = pobjSheets["Sheet2"]
// Call activate.
pobjSheet.Activate();
// Release the objects.
Marshal.ReleaseComObject(pobjSheets);
Marshal.ReleaseComObject(pobjSheet);
Marshal.ReleaseComObject(xlBook);
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- nicholas...@exisconsulting.com
"Tony Lin" <to...@linfamily.org> wrote in message
news:OzACkxkL...@TK2MSFTNGP10.phx.gbl...
Tony Lin
Thank you so much for your excellent, excellentt advice! It answered my
questions perfectly (and restored my faith in Office/.NET integration).
What I learned here is that the invalid casting and compilation errors come
from the fact that the Excel objects are sometimes treated by .NET as a
generic _COM type, rather than as specific Excel types. I have to watch out
for this and cast to the Excel type as necessary.
Your suggestion on Activate() worked perfectly. FYI, the code I ended up
with for reading a cell was somewhat convoluted but it does work:
int x = Convert.ToInt32(((Excel.Range)xlSheet.Cells[2,1]).Value);
Once again, thanks for the advice which was perfect!
BTW, do you have any recommendations for books or web references on
.NET/Office integration?
Tony Lin
Fremont, CA
"Nicholas Paldino [.NET/C# MVP]" <nicholas...@exisconsulting.com> wrote
in message news:OpOjgjoL...@TK2MSFTNGP10.phx.gbl...