Accessing Excel sheet using Excel DNA

[pb19...@gmail.com]

Hi all, 

I am newbie for excel related development. Apologies in advance for any irrelevant question/topic. 

I want to access excel objects from c#. So i can populate data to api.   

Basically i want to read images, charts , cells ...... all the excel objects from .net. So i can send data retrieved from excel to the corresponding API. I heard about EXCEL DNA, i implemented it for UDF. I can successfully call UDF from VBA but i want to retrieve images, charts, graphs from the excel sheet.

Please help me out in this. If you have any other way to achieve my goals, i am open to it. I just want a guidance or a correct path on which i can progress.           

[Govert van Drimmelen]

Hi,

You have full access to the Excel COM object model from your Excel-DNA add-in.

The first step is to add a reference to the interop assemblies:
* Microsoft.Office.Interop.Excel.dll
* Office.dll

You can also install the NuGet package "ExcelDna.Interop", which will install and reference the Excel 2010 version of the interop assemblies (which will work for Excel versions 2010 and later).

If you're using .NET 4, the 'Embed Interop Types' options which is true by default, will allow this to work without have to redistribute extra files.

Next you need to get hold of the correct Application object (for the Excel instance where your add-in is running).
This you do with a call to ExcelDnaUtil.Application - this returns an object which is the COM Application object.

From there you use the COM object model as you would from VBA.

I paste a small example below.
Please ask if anything is unclear.

Regards,
Govert



using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;

public class TestCommands
{
[ExcelCommand(ShortCut = "^D")] // Ctrl+Shift+D
public static void AddDiamond()
{
Application xlApp = (Application)ExcelDnaUtil.Application;
string version = xlApp.Version;

Worksheet ws = xlApp.ActiveSheet as Worksheet; // Need to change type - it might be a Chart, then ws will be null
Shape diamond = ws.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeDiamond, 10, 10, 100, 100);
diamond.Fill.BackColor.RGB = (int)XlRgbColor.rgbSlateBlue;
}
}



________________________________________
From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of pb19...@gmail.com [pb19...@gmail.com]
Sent: 27 March 2016 01:38 PM
To: Excel-DNA
Subject: [ExcelDna] Accessing Excel sheet using Excel DNA

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com<mailto:exceldna+u...@googlegroups.com>.
To post to this group, send email to exce...@googlegroups.com<mailto:exce...@googlegroups.com>.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[pb19...@gmail.com]

Thanks Govert,

I have been following you since i've started using excel dna. I managed to access excel sheet using com .

But there are few issues in accessing handlers.

Example : -    

 public class Class1 : IExcelAddIn

{

Microsoft.Office.Interop.Excel.Application xlapp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;

      //  int eventCounter = 0;

        Workbook Wb_ ;

        public void AutoOpen()

        {

                        xlapp.WorkbookActivate += xlapp_WorkbookActivate;

        }

        void xlapp_WorkbookActivate(Workbook Wb)

        {

            Wb.SheetChange += Wb_SheetChange;

            System.Windows.Forms.MessageBox.Show("in activate");

            Microsoft.Office.Interop.Excel.Worksheet ws = Wb.ActiveSheet;

        }

        void Wb_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)

        {

           // something

        }

}

As you can see, i am using sheetchange event handler here. But this event only fires twice or once . Not every time when i change anything in sheet.

Reply soon. 

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com<mailto:exceldna+unsubscribe@googlegroups.com>.

[Govert van Drimmelen]

Hi,

 

Perhaps your COM objects are being garbage collected, and then the events don’t fire anymore.

 

You might like to keep a list of Workbook and Worksheet objects that you are watching.

 

-Govert

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com<mailto:exceldna+u...@googlegroups.com>.

To post to this group, send email to exce...@googlegroups.com<mailto:exce...@googlegroups.com>.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--

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

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.

[pb19...@gmail.com]

Thanks Govert,

If possible, could you please help me out in that. The above pasted code is the one i am using. OR you can guide me for that. 

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com<mailto:exceldna+unsubscribe@googlegroups.com>.

To post to this group, send email to exce...@googlegroups.com<mailto:exce...@googlegroups.com>.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[pb19...@gmail.com]

I got the solution for the garbage collector problem. Thanks for your help.

[pb19...@gmail.com]

Hi Govert,

Can we create an user control in excel dna as we can in vsto ?

[ux.le...@gmail.com]

Hi,

Can you share this solution, I'm new ExcelDNA and this is the same problem I am having.

[Leigh Tilley]

Hi

I'm busy building a nice xll using ExcelDNA.

I have just stumbled across what appears to be an infamous problem; the Ctrl+Z / undo problem!

My custom function calls a remote cube and builds MDX, gets data back and returns a 2D array to Excel. All working nicely.

In testing I noticed though that if Ctrl+Z is pressed it ignores the last action and wipes my array function.

From reading around this is to do with the Undo history being wiped due to the Com object model being used; v odd!!!?!

I stumbled across this thread trying to get a handle to the Application to see if I could reach the undo stack somehow....

I tried

using Microsoft.Office.Interop.Excel;

using ExcelDna.Integration;

But this line:

Application xlApp = (Application)ExcelDnaUtil.Application;

Application is underlined red / not found.

Any advice please?

Thanks

Leigh Tilley | tilleytech.com

[Govert van Drimmelen]

Hi Leigh,

 

Yes – Excel will clear the Undo stack when you manipulate the sheet from a macro.

I don’t know of a any to avoid that.

 

It sounds like your 2D array return would work very well with the new “Dyanmic Array” feature of Excel.

 

For older Excel versions my recommendation is the ArrayResizer approach (https://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/) but that has the disadvantage of clearing the undo stack among other problems.

 

For using ExcelDnaUtil.Application you need to reference the Excel Primary Interop Assemblies (PIAs).

You can either add these as references to your project, or install the “ExcelDna.Interop” package from NuGet, which add the PIAs from Excel 2010 to your project.

 

-Govert

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/34e05c3d-9a16-4b93-99b4-7edb529c04c4%40googlegroups.com.

[Leigh Tilley]

Hi

Thanks for your prompt reply. I had installed the Nuget package but it still doesn't recognise Applicaition. 

I shall check today with fresh eyes!

I use this to return stuff to Excel and it works well (result being the 2D array): 

return XlCall.Excel(XlCall.xlUDF, "Resize", result);

To unsubscribe from this group and stop receiving emails from it, send an email to exce...@googlegroups.com.

[Leigh Tilley]

Hey

I checked my using section and I had this last night:

using ExcelDna.IntelliSense;

using Microsoft.Office.Interop.Excel;

using ExcelDna.Integration;

I changed the MS one to:

using ExcelDna.ComInterop;

as this is the only Interop one visible.

I will look for the one you mention

[Govert van Drimmelen]

Hi Leigh,

 

The “ExcelDna.ComInterop” namespace is not related to what you want to do.

 

You need to install a package from NuGet, called “ExcelDna.Interop”.

After installing this, you should see three extra References in your project:

 

• Microsoft.Office.Interop.Excel
• Microsoft.Vbe.Interop
• Office

 

 

 

Do you see this?

 

After that you should be able to do

    using Microsoft.Office.Interop.Excel;

 

in your code.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Leigh Tilley

Sent: 4 December 2019 12:33
To: Excel-DNA <exce...@googlegroups.com>

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/bebd59ba-b37f-45c4-8c80-aa986bd205f6%40googlegroups.com.

[Leigh Tilley]

Hey

I realised I'd installed the Interop.dao nuget, so I went and found the main PIA one (see here; 14.0.1.

What's the correct using package to use to get to Application please? Oh I only just realised you're the author too; nice wor mate :).

[Leigh Tilley]

Hey

Is OK I had to add:

using Microsoft.Office.Interop.Excel;

back in.

No errors now. I can see the Application object etc.