Strange TargetInvocationException

Jul 28, 2011 at 11:50 PM

VS2008, .NET35, C#, ExcelDNA, NetOffice

in AutoOpen()

I add 3 handlers to Excel App events

                LateBindingApi.Core.Factory.Initialize();
                XLApp = new Excel.Application(null, ExcelDnaUtil.Application);
                XLApp.SheetSelectionChangeEvent += ApplicationSheetSelectionChange;
                XLApp.SheetDeactivateEvent += SheetDeactivatedEvent;
                XLApp.SheetCalculateEvent += ApplicationSheetCalculate; 

When I open a xls file in Excel 2007, it takes 20-30 seconds. I am not sure why

I track down in VS, it seems SheetCalculateEvent is called 3 times and every time an exception is thrown

This is ApplicationSheetCalculate(),


        void ApplicationSheetCalculate(object sh)
        {
            var rng = Helper.InvokeExcelMethodOnUSCulture(XLApp, "Selection") as Range;
            if (rng == null) return;
            var formula = rng.GetValue("Formula") as string;
            if(string.IsNullOrEmpty(formula)) return;
             .....do something....
        }

 

Helper class has

        public static object InvokeExcelMethodOnUSCulture(Application app, string memberName)
        {
            var excelCulture = new CultureInfo(1033);
            var ret = app.GetType().InvokeMember(memberName,
                BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, app, null, excelCulture);
            return ret;
        }

        public static object GetValue(this Range range, string propertyName)
        {
            try
            {
                var excelCulture = new CultureInfo(1033);
                var ret = range.GetType().InvokeMember(propertyName,
                    BindingFlags.GetProperty, null, range, null, excelCulture);   //throw TargetInvokeException!?
                return ret;
            }
            catch (Exception ex)
            {
                Helper.LogError(ex.StackTrace);
                return null;
            }
        }

 

Another issue is: when users copy/paste entire sheet, Excel hangs for 2-3 minutes even if users do not use any functions in my AddIn.

I bet it is related to the event handler. but I can't figure out why

 

thanks

 

Coordinator
Jul 29, 2011 at 12:47 AM

Hello;
try the follows please:

void Foo()
{
   excelApplication.SheetCalculateEvent += new Excel.Application_SheetCalculateEventHandler(excelApplication_SheetCalculateEvent); 
}

void excelApplication_SheetCalculateEvent(COMObject Sh) 
{ 
}



What are doing in the helper class? the range object is not the native COMProxy but you have access to them: range.UnderlyingObject.
anyway, its not necessary to call latebind. netoffice do that for you. if you want to change the given thread culture then use

LateBindingApi.Core.Settings.ThreadCulture
if you still want to use latebind calls try the static class Invoker from NetOffice



http://netoffice.codeplex.com/wikipage?title=Tec_Documentation_English




http://netoffice.codeplex.com/wikipage?title=Tec_Faq_English




Jul 29, 2011 at 5:07 PM

Thanks, Sebastian.

No, it does not work.

when I use UnderlyingObject as below,  it gives an exception saying "Method 'System.__ComObject.Formula' not found."

(I do not want to change culture on current thread, I simply want to use en-us for current method.  )

        public static object GetValue(this Range range, string propertyName)
        {
            try
            {
                var excelCulture = new CultureInfo(1033);
                var ret = range.UnderlyingObject.GetType().InvokeMember(propertyName,
                    BindingFlags.GetProperty, null, range, null, excelCulture);
                return ret;
            }
            catch (Exception ex)
            {
                Helper.LogError(ex.StackTrace);
                return null;
            }
        }

Coordinator
Jul 29, 2011 at 5:17 PM
Edited Jul 29, 2011 at 5:27 PM

you dont change the current thread with Settings.ThreadCulture
take a look at core of netoffice

public static object PropertyGet(object comObject, string name)
{
            try
            {
                object returnValue = comObject.GetType().InvokeMember(name, BindingFlags.GetProperty, null, comObject, null, Settings.ThreadCulture);
                return returnValue;
            }
            catch (Exception throwedException)
            {
                throw new System.Runtime.InteropServices.COMException("", throwedException);
            }
}

you see Settings.ThreadCulture was given in latebinded call.
i'm not sure whats the problem with your latebind call. i take a look in MS OLE Viewer at home.

Jul 29, 2011 at 6:01 PM

Sorry, I did not get you. This is what I tried.

I copy the PropertyGet method and use it like this

       void ApplicationSheetCalculate(COMObject sh)
        {
            var rng = Helper.InvokeExcelMethodOnUSCulture(XLApp, "Selection") as Range;
            if (rng == null) return;
              var formula = Helper.PropertyGet(rng.UnderlyingObject, "Formula") as string;  //throw outofmemory exception
              var formula2 = Helper.PropertyGet(rng, "Formula") as string; //throw TargetInvocationException
            if(string.IsNullOrEmpty(formula)) return;
             .....do something....
        }

I did not use COMObject sh b/c it is Worksheet, I want to get current selected cells on the sheet, I could not find a way to do it through WorkSheet so I use Application.Selection

Coordinator
Jul 29, 2011 at 6:40 PM

i have still no idea why you want using native latebinding here but the follwing code runs fine in my test

 

// Initialize Api COMObject Support
LateBindingApi.Core.
Factory.Initialize();

 
// start excel and turn off msg boxes

Excel.
Application excelApplication = new Excel.Application();

excelApplication.DisplayAlerts = false; 
// add a new workbook
Excel.
Workbook workBook = excelApplication.Workbooks.Add();

Excel.
Worksheet sheet = workBook.Worksheets[1] as Excel.Worksheet;

Excel.
Range range = sheet.Cells[1, 1];

var excelCulture = new System.Globalization.CultureInfo(1033);

var ret = range.UnderlyingObject.GetType().InvokeMember("Formula",
  BindingFlags.GetProperty, null, range.UnderlyingObject, new object[0], null);

Jul 29, 2011 at 7:25 PM
Edited Jul 29, 2011 at 7:33 PM

thanks, I try your code, works fine for me.

but my code still gives me an exception even if I change it to use

var ret = range.UnderlyingObject.GetType().InvokeMember("Formula",  BindingFlags.GetProperty, null, range.UnderlyingObject, new object[0], null);


Before I switch to NetOffice,  if I have French version Excel and I use range.Formula, an exception will be thrown. but InvokeMemeber works b/c I can specify it run on en-us

Now I switch to NetOffice, if I use Range.Formula directly in French version Excel, I will get an Exception, too.  That's why I want to use InvokeMemember

I want to get current selected cell, based on its formula,  disable/enable certain items in GUI like menus, buttons, ribbons, etc. 

thanks

Coordinator
Jul 29, 2011 at 8:16 PM

okay you got an exception while using range.Formula in NetOffice. this is a new information for me.
please gimme more informations about your environment. visual studio, windows, office 32 or 64 bit ..
are you sure a french excel its the reason?