Shifting Excel VSTO add in to Excel DNA

[suthar monil]

Hi, 

   I already have made an excel add-in using vsto in Visual studio 2010 in .Net 4.0.

Basically my add-in requests data from a WCF service following a button click on the custom Ribbon. The ribbon many buttons on it for user interaction and fetching new data etc.

I access all the WCF services using proxy classes.I format the received data into a dictionary of objects as per my need. 

I also have to do some formatting like conditional formatting, table formatting, pivoting here and there and things like that and displaying onto new sheets.

For all these Excel related tasks I use the Excel interop and .Net libraries.

After a nightmare of VSTO deployment. I am planning to use Excel-DNA which I find very easy to deploy.

I have been reading up, on Excel DNA tutorials and stuff but, could not find out whether I will be able to use:

1.) WCF proxy classes inside Excel-DNA based add-ins, as all the connectivity with the server is maintained using WCF services.

2.) Also, whether I get all the formatting options in Excel-DNA library which I used from Interop(Interop is the main thing causing deployment problems).

If anyone knows answers to my questions, please help me out here.

Thanks and Regards,

Monil N. Suthar

[Naju Mancheril]

Proxy classes should be no problem. You can run whatever .NET code you want.

Interop will not be a problem. You will need to reference the Microsoft.Office.Interop.Excel assembly from your C# project. This will give you access to all the Excel interop types (Application, Workbook, Worksheet, Range, etc). You can then cast ExcelDnaUtil.Application (a static property provided by ExcelDna) to a Microsoft.Office.Interop.Excel.Application object. From there, you can poke the application any way you want.


Example:

        var app=(Application)ExcelDnaUtil.Application;

We switched from VSTO to ManagedXLL to ExcelDna. No problems with any of the transitions.

[suthar monil]

Thanks for your response. I understood your point, but that means I will still be dependent on Microsoft Interop assemblies. Also, this concerns with .Net versions then because my client machines will be .Net 2.0 and above and not necessarily .Net 4.0, so again that would create a problem.

[Naju Mancheril]

Oh, I understood your question as "can I use interop?" ... you can.

You have a few other options, but they require a bit more work:

(1) You can use whatever writeback support is provided by the XLL interface. What do you need to do? Write formulas and values to cell?

(2) You can use netoffice, which provides simple wrapper classes around interop objects. It used reflection to poke in values.

[suthar monil]

In simple terms, 

1.)I have created one ribbon which has buttons on it. The user will click this buttons to access data kept on servers/databases. (Interop)

2.)I need to call a WCF service which will yield some data.

3.)That data is converted into a dictionary inside the add-in for easy manipulation(no big deal in any C# code).

4.)Now, selecting the excel range which will look like a 2d array and then simply dumping my data into that excel range.(Interop)

5.)Keeping hold on that 2d excel range and adding conditional formatting and table formatting and sometimes pivoting.(Interop)

6.)Cycling through excel range after range and populating tabular and graphical representations of data like a mechanical type writer.(Interop)

All these functionality is already achieved in VSTO add-in. But trying to find similar approach in Excel-DNA because VSTO in VS 2010 creates issues while creating backward compatible add-ins. As i said my add-in is destined to run in machines having .net 2.0 and above and necessarily .net 4.0.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Naju Mancheril]

ExcelDna will help you do (1) to (3) without any Microsoft.Office.Interop.Excel (PIA) references.

(4) to (6) without PIAs will probably require lots of reflection or use of something like NetOffice. For example, NetOffice will give you an IRange which exposes properties like NumberFormat, etc.

Does this make sense?

Since some of your clients are using .NET 2.0, I guess dynamic programming is not an option.

[suthar monil]

Thanks for the response!!!. Yes, it does make sense about what you are talking. And I am definitely moving towards a good solution to this problem. 

But I am not there yet so i'll keep digging.

[Michael_F]

There is another option. The "dynamic" keyword is only supported in C# 4.0, but in VB.NET the equivalent functionality was already there in .NET 2.0. So there is the option to port that Add-In (or, at least the parts interacting with Excel directly) to VB.NET. Well, I don't know how many lines of code that means in your case, but if it were below 1000, I would probably go that route.

Greetings

- Michael -