Export (0) Print
Expand All

How to: Make String Literals Region-safe in Excel Using Reflection

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

  • Document-level projects

  • Application-level projects

Microsoft Office version

  • Excel 2003

  • Excel 2007

For more information, see Features Available by Application and Project Type.

When you use default Visual Studio Tools for Office settings, always format locale-sensitive data strings as English (United States) and Excel will display the correctly formatted data automatically. If you change the default by setting ExcelLocale1033Attribute to false and you pass string literals to Excel, the thread's current LCID is automatically used and data might be formatted incorrectly. For more information, see Formatting Data in Excel with Various Regional Settings.

If you do set ExcelLocale1033Attribute to false, you can use reflection to force specific calls to properties and methods of Excel objects to use English (United States) data format (locale ID 1033). When you do this, you can use English (United States) formatting for any strings that are passed to the property or method, and the code will work correctly in all locales.

The following examples demonstrate how to set or get a property of a NamedRange control in a document-level project for Excel, but the same concepts also apply to Excel objects in application-level projects.

To set a property in an Excel range using reflection

  1. Create a helper method that uses the InvokeMember method to set a property value of an Excel object. Include parameters for the Excel object, the property name, and the parameters of the property. In the helper method, use an InvokeMember overload that has a CultureInfo parameter, and pass the locale ID for English (United States) to this parameter.

    static object SetPropertyInternational(object target, string name, params object[] args)
    {
        return target.GetType().InvokeMember(name,
            System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.SetProperty |
            System.Reflection.BindingFlags.Public,
            null, target, args, new
            System.Globalization.CultureInfo(1033));
    }
    
  2. Create a NamedRange control on cell A5 and name it NamedRange1.

    Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
        this.Controls.AddNamedRange(this.Range["A5", missing], "NamedRange1");
    
  3. Instead of setting the Formula property of NamedRange1 directly, call the SetPropertyInternational helper method and pass in the underlying Range object for the NamedRange control, the property name and the value.

    NoteNote:

    You cannot pass in the NamedRange control directly. Instead, you must get the underlying Excel Range object by using the InnerObject property, and then pass this object to the method. For more information, see Programmatic Limitations of Host Items and Host Controls.

    SetPropertyInternational(NamedRange1.InnerObject, "Formula", "=SUM(12, 34)");
    

To retrieve a property value from an Excel range using reflection

  1. Create a helper function that uses the InvokeMember method to get a property value of an Excel object. The object, the property name, and the parameters of the property are passed in to the helper method.

    static object GetPropertyInternational(object target, string name, params object[] args)
    {
        return target.GetType().InvokeMember(name,
            System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.GetProperty |
            System.Reflection.BindingFlags.Public,
            null, target, args, new 
            System.Globalization.CultureInfo(1033));
    }
    
  2. Call the GetPropertyInternational function and pass in the NamedRange and the property name.

    NoteNote:

    The following code example assumes you have a NamedRange control named NamedRange1 on the worksheet.

    string formula = (string)
        GetPropertyInternational(NamedRange1.InnerObject, "Formula");
    
    MessageBox.Show(formula);
    

Community Additions

ADD
Show:
© 2014 Microsoft