C# .Net – Excel Com Issue solved


Today I ran into an issue calling Excel via COM from C# .Net.

We had a situation where calling the com code worked fine on some machines and crashed on others.

MSexcel.Range cell = getTableCell( rowIndex, columnIndex );
cell.Value2 = "Test";

The call to set property Value2 would on some machines throw the following exception:

0x800A03EC at System.RuntimeType.ForwardCallToInvokeMember( String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) 

I then tried to get Value2 from the cell and that threw the following exception:

“Old format or invalid type library”. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD)) at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) 

After a lot of searching I came across an article which solves the issue.

The problem seems to be when the installed version of Excel has a different locale to that of the current user.

I have created a wrapper class that can be used to wrap any of the Excel objects so that you can safely call the properties and methods.

using System.Text;
using MSexcel = Microsoft.Office.Interop.Excel;
using System.Globalization;
using System.Reflection;
namespace ExcelWrapper {
    public class PExcelOb {
        object officeObject;
        Type type;
        public static CultureInfo CultureInfo = null;
        public static void SetUpCulture( MSexcel.Application app ) {
            int culture = app.LanguageSettings.get_LanguageID(
                Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI );
            CultureInfo = new CultureInfo( culture );
        }
        public PExcelOb( object officeObject ) {
            if( CultureInfo == null ) {
                throw new Exception( "SetUpCulture has not been called" );
            }
            this.officeObject = officeObject;
            type = officeObject.GetType();
        }
        public object Invoke( string method, params object[] paramters ) {
            object results = null;
            results = type.InvokeMember(
                method, BindingFlags.InvokeMethod, null,
                officeObject, paramters, CultureInfo );
            return results;
        }
        public object this[string name] {
            get {
                return type.InvokeMember(
                    name, BindingFlags.GetProperty, null,
                    officeObject, null, CultureInfo );
            }
            set {
                object[] parameters = new object[1];
                parameters[0] = value;
                type.InvokeMember(
                    name, BindingFlags.SetProperty, null,
                    officeObject, parameters, CultureInfo );
            }
        }
    }
}

To use the wrapper you need to first setup the Culture such:

PExcelOb.SetUpCulture( Application );

Where Application is the Excel application object.

e.g.

MSexcel.Application 

Then create an instance of the wrapper around the Excel object.

Is replaced with the following:

PExcelOb cell_Safe = new PExcelOb( cell );
cell_Safe["Value2"] = "Test";

You can call a method such:

PExcelOb activeSheet_Safe = newPExcelOb( activeSheet );
activeSheet_Safe.Invoke( "Paste", null );
Advertisements

Tags: , , , , , , , ,

One Response to “C# .Net – Excel Com Issue solved”

  1. Choirul Dimyati Says:

    I also experience same problem, that was coloum name (I was exporting report from delphi to excell) it;s occur when coloum name is AA, then I changed when j(integer)>26 then I add ‘a’ in the front of coloum name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: