CODEWITHSUNDEEP
c#excelvstocultureinfoinvokemember
tjsmith
tjsmith

Reputation: 749

Culture specific property in Excel via C# using InvokeMember

When we call the Excel object model via COM in C#, it always uses the en-US culture (this is a change that was made some years ago so that addins should work on all machines regardless of regional settings).

I would like to call the Excel object model with a specific culture and the way to do this according to the Excel documentation is using InvokeMember and passing the cultureInfo parameter. https://msdn.microsoft.com/en-us/library/bb157877.aspx

However, whenever I try this with a locale other than en-US, I get a System.Reflection.TargetInvocationException.

So with the example code below, the call will work with the en-US culture, but throw an exception with the fr-CA culture. Note that changing the current culture in the regional settings does not affect whether an exception is thrown or not. Though it will determine if a string is interpreted as a number when using the Value2 property according to the regional settings.

I based the code off of this example, which seemed to be working when it was written: https://social.msdn.microsoft.com/Forums/office/en-US/2aee0a8a-aaff-48a8-9364-edf1e3fbb9b4/setting-rangevalue2-behavior-changed-between-net-20-and-net-40-for-international-versions-of?forum=exceldev

Does anyone know what changes I can make to call the Range.Value2 with a specific culture other than en-US?

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        try
        {
            var excelApp = Globals.ThisAddIn.Application;
            var workbook = excelApp.Workbooks.Add();
            var worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            var rangeA1 = worksheet.Range["A1"];
            var rangeB1 = worksheet.Range["B1"];
            string num1English = "1.1";
            string num1French = "1,1";

            rangeA1.GetType().InvokeMember("Value2", BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, null,
                                            rangeA1, new object[] { num1English }, new CultureInfo("en-US"));

            rangeB1.GetType().InvokeMember("Value2", BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, null,
                                            rangeB1, new object[] { num1French }, new CultureInfo("fr-CA"));
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString() + "\n\n" + ex.StackTrace);
        }
    }

Upvotes: 2

Views: 1327

Answers (2)

Tommaso Ercole
Tommaso Ercole

Reputation: 592

You misread the documentation. It says to pas the locale id, not the CultureInfo. This is also logical, Excel is not written using the .NET Framework and the COM technology existed far earlier and has a wider spectrum of usages than .NET itself, so that parameter should work across different programming languages. What you should pass is the LCID property of the CultureInfo instance.

Upvotes: 0

Andrei Smolin - Add-in Express
Andrei Smolin - Add-in Express

Reputation: 459

Find an example in HowTo: Fix “Old format or invalid type library” error (0x80028018). You can use any VB.NET to C# code converter available online.

Upvotes: 0

Related Questions