locked
Excel get selected shape c#/vsto RRS feed

  • Question

  • Hi,

    I'm trying to get the shape a user has selected and change its colour. It seems to not be recognising the shape when i try to cast. If i have the selection (application.selection):

    Excel.Shape = selection as Excel.Shape;

    if (shape != null)

    {

    // never enters here

    shape.Fill.ForeColor.RGB = rgb

    }


    Is it possible to get the selected shape in excel? Similar code in powerpoint works fine

    Wednesday, November 23, 2011 12:52 AM

Answers

  • Nicholas,

    I pointed out that Rectangle and Oval are just exampples. Here is the code returning the type name of the selected object:

    using System;
    using System.Runtime.InteropServices;
    using ComTypes = System.Runtime.InteropServices.ComTypes;
    
    namespace ComUtils
    {
        public class ComHelper
        {
            /// <summary>
            /// Returns a string value representing the type name of the specified COM object.
            /// </summary>
            /// <param name="comObj">A COM object that type name of which to return.</param>
            /// <returns>A string containing the type name.</returns>
            public static string GetTypeName(object comObj)
            {
    
                if (comObj == null)
                    return String.Empty;
    
                if (!Marshal.IsComObject(comObj))
                    //The specified object is not a COM object
                    return String.Empty;
    
                IDispatch dispatch = comObj as IDispatch;
                if (dispatch == null)
                    //The specified COM object doesn't support getting type information
                    return String.Empty;
    
                ComTypes.ITypeInfo typeInfo = null;
                try
                {
                    try
                    {
                        // obtain the ITypeInfo interface from the object
                        dispatch.GetTypeInfo(0, 0, out typeInfo);
                    }
                    catch (Exception ex)
                    {
                        //Cannot get the ITypeInfo interface for the specified COM object
                        return String.Empty;
                    }
    
                    string typeName = "";
                    string documentation, helpFile;
                    int helpContext = -1;
    
                    try
                    {
                        //retrieves the documentation string for the specified type description 
                        typeInfo.GetDocumentation(-1, out typeName, out documentation, out helpContext, out helpFile);
                    }
                    catch (Exception ex)
                    {
                        // Cannot extract ITypeInfo information
                        return String.Empty;
                    }
    
                    return typeName;
                }
                catch (Exception ex)
                {
                    // Unexpected error
                    return String.Empty;
    
                }
                finally
                {
                    if (typeInfo != null) Marshal.ReleaseComObject(typeInfo);
                }
            }
        }
    
        /// <summary>
        /// Exposes objects, methods and properties to programming tools and other applications that support Automation.
        /// </summary>
        [ComImport()]
        [Guid("00020400-0000-0000-C000-000000000046")]
        [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
        interface IDispatch
        {
            [PreserveSig]
            int GetTypeInfoCount(out int Count);
    
            [PreserveSig]
            int GetTypeInfo
                (
                    [MarshalAs(UnmanagedType.U4)] int iTInfo,
                    [MarshalAs(UnmanagedType.U4)] int lcid,
                    out ComTypes.ITypeInfo typeInfo
                );
    
            [PreserveSig]
            int GetIDsOfNames
                (
                    ref Guid riid,
                    [MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPWStr)] string[] rgsNames,
                    int cNames,
                    int lcid,
                    [MarshalAs(UnmanagedType.LPArray)] int[] rgDispId
                );
    
            [PreserveSig]
            int Invoke
                (
                    int dispIdMember,
                    ref Guid riid,
                    uint lcid,
                    ushort wFlags,
                    ref ComTypes.DISPPARAMS pDispParams,
                    out object pVarResult,
                    ref ComTypes.EXCEPINFO pExcepInfo,
                    IntPtr[] pArgErr
                );
        }
    }
    
    

    Use this code to get the type name of the selected object (instead of "__ComObject") and modify your code accordingly.  Hope you understand the idea.

     

     


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Wednesday, December 7, 2011 10:22 AM
    Wednesday, November 23, 2011 4:40 PM
  • :) Which of the procedures is mine?

    In fact, using my latest ComHelper class, you can find that when you select several shapes simultaneously, you get an object the type of which is DrawingObjects. And its ShapeRange property for that object returns selected shapes, not just one shape.

     


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Wednesday, December 7, 2011 10:22 AM
    Wednesday, November 23, 2011 4:45 PM

All replies

  • Hello,
     
    In your code you need to check if the selection is Excel.Rectangle or Excel.Oval and then get the Shape object using Rectangle.ShapeRange[1] or Oval.ShapeRange[1]. Note that Oval and Rectangle are just examples.

    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, November 23, 2011 10:50 AM
  • To add to Andrei's comment, elbow and curved connectors are also Excel.Rectangle selections, so you should check that shape.Connector = Office.MsoTriState == msoFalse.

    BTW, the Selection objects that do cast to Excel.Shape are the Form Controls that can be inserted onto the worksheet. It must be a legacy reference.

    ----

    Nicholas Hebb

    Wednesday, November 23, 2011 12:46 PM
  • Nice to meet you again, Nicholas!

    And thank you very much for the helpful comment.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, November 23, 2011 1:00 PM
  • It can be done, but it is not easy to figure out.  If yo are getting the Selected Sahpe you must first get the name of the shape and then from the name you can get the actual shape object.  which means you need to get the selected worksheet first.

     

    Make sure you add the office.dll which is part of the PIA.  Yo need to browse for the file office.dll if it is not in your Net Reference libraries.

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Core;
    using System.Reflection;
    using System.Drawing;
    
    
    
    
    
    namespace ConsoleApplication1
    {
        
        class Program
        {
    
            static void Main(string[] args)
            {
    
                var excelApp = new Excel.Application();
                string FName = @"c:\temp\book1.xls";
                
    
                excelApp.Visible = true;
    
                Excel.Workbook excelbk = excelApp.Workbooks._Open(FName,
                   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing);
    
                Excel._Worksheet excelSht = (Excel._Worksheet)excelApp.ActiveSheet;
    
                object selectedObj = (object)excelApp.Selection;
                object objectType = selectedObj.GetType().InvokeMember("Name", BindingFlags.Default | BindingFlags.GetProperty, null, selectedObj, null);
                string stu = objectType.ToString();
    
                Excel.Shape shape = (Excel.Shape)excelSht.Shapes.Item(stu);
    
              
                
                if (shape != null)
                {
                    
                    int Mycolor = System.Drawing.Color.Aquamarine.ToArgb() & 0xFFFFFF;
                    shape.Fill.ForeColor.RGB = Mycolor;
    
                }
            }
        }
    }
    
    

     


    jdweng
    Wednesday, November 23, 2011 2:41 PM
  • @Andrei: Hi!

     

    @jdweng:

    I've used both approaches (Andrei's and yours), and they both seem to work fine. I've implemented both as a trial in my upcoming application, and haven't seen any performance difference. The sample code is below if anyone is interested. If you spot any errors, I'd appreciate the feedback. It assumes a reference to the Excel Application object (ExcelApp in this case), uses the two-dot rule for COM object referencing, and releases all COM objects.

     

    private void SetShapeFill(int rgb)
    {
        Excel.Shape shape = GetSelectedShape();
        if (shape != null)
        {
            Excel.FillFormat fill = shape.Fill;
            fill.ForeColor.RGB = rgb;
            Marshal.ReleaseComObject(fill);
        }
    }
    
    private void SetShapeFill2(int rgb)
    {
        Excel.Shape shape = GetSelectedShape2();
        if (shape != null)
        {
            Excel.FillFormat fill = shape.Fill;
            fill.ForeColor.RGB = rgb;
            Marshal.ReleaseComObject(fill);
        }
    }
    
    private Excel.Shape GetSelectedShape()
    {
        Excel.Shape shape = null;
        try
        {
            object selection = ExcelApp.Selection;
            if ((selection as Excel.Rectangle) != null || (selection as Excel.Oval) != null)
            {
                string name = selection.GetType().InvokeMember("Name", System.Reflection.BindingFlags.GetProperty, null, selection, null).ToString();
                Excel.Worksheet sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
                if (sheet != null)
                {
                    Excel.Shapes shapes = sheet.Shapes;
                    shape = shapes.Item(name);
    
                    if (shape != null && shape.Connector == Office.MsoTriState.msoTrue)
                    {
                        Marshal.ReleaseComObject(shape);
                        shape = null;
                    }
                    Marshal.ReleaseComObject(shapes);
                    Marshal.ReleaseComObject(sheet);
                }
                Marshal.ReleaseComObject(selection);
            }
        }
        catch
        {
            // ignore and return null
        }
        return shape;
    }
    
    
    private Excel.Shape GetSelectedShape2()
    {
        Excel.Shape shape = null;
        try
        {
            object selection = ExcelApp.Selection;
            if ((selection as Excel.Rectangle) != null)
            {
                Excel.ShapeRange shapeRange = ((Excel.Rectangle)selection).ShapeRange;
                shape = shapeRange.Item(1);
                Marshal.ReleaseComObject(shapeRange);
            }
            else if ((selection as Excel.Oval) != null)
            {
                Excel.ShapeRange shapeRange = ((Excel.Oval)selection).ShapeRange;
                shape = shapeRange.Item(1);
                Marshal.ReleaseComObject(shapeRange);
            }
    
            if (shape != null && shape.Connector == Office.MsoTriState.msoTrue)
            {
                Marshal.ReleaseComObject(shape);
                shape = null;
            }
    
            Marshal.ReleaseComObject(selection);
        }
        catch
        {
            //ignore and return null
        }
    
        return shape;
    }
    

     


    Nicholas Hebb

    BreezeTree Software


    Wednesday, November 23, 2011 4:14 PM
  • Have you tried Andrei's approach when there is more than one shape on the worksheet?  I beleive Andrei's code will only work with  the first shape on the worksheet.  My code will work with any shape on the worksheet.
    jdweng
    Wednesday, November 23, 2011 4:20 PM
  • Nicholas,

    I pointed out that Rectangle and Oval are just exampples. Here is the code returning the type name of the selected object:

    using System;
    using System.Runtime.InteropServices;
    using ComTypes = System.Runtime.InteropServices.ComTypes;
    
    namespace ComUtils
    {
        public class ComHelper
        {
            /// <summary>
            /// Returns a string value representing the type name of the specified COM object.
            /// </summary>
            /// <param name="comObj">A COM object that type name of which to return.</param>
            /// <returns>A string containing the type name.</returns>
            public static string GetTypeName(object comObj)
            {
    
                if (comObj == null)
                    return String.Empty;
    
                if (!Marshal.IsComObject(comObj))
                    //The specified object is not a COM object
                    return String.Empty;
    
                IDispatch dispatch = comObj as IDispatch;
                if (dispatch == null)
                    //The specified COM object doesn't support getting type information
                    return String.Empty;
    
                ComTypes.ITypeInfo typeInfo = null;
                try
                {
                    try
                    {
                        // obtain the ITypeInfo interface from the object
                        dispatch.GetTypeInfo(0, 0, out typeInfo);
                    }
                    catch (Exception ex)
                    {
                        //Cannot get the ITypeInfo interface for the specified COM object
                        return String.Empty;
                    }
    
                    string typeName = "";
                    string documentation, helpFile;
                    int helpContext = -1;
    
                    try
                    {
                        //retrieves the documentation string for the specified type description 
                        typeInfo.GetDocumentation(-1, out typeName, out documentation, out helpContext, out helpFile);
                    }
                    catch (Exception ex)
                    {
                        // Cannot extract ITypeInfo information
                        return String.Empty;
                    }
    
                    return typeName;
                }
                catch (Exception ex)
                {
                    // Unexpected error
                    return String.Empty;
    
                }
                finally
                {
                    if (typeInfo != null) Marshal.ReleaseComObject(typeInfo);
                }
            }
        }
    
        /// <summary>
        /// Exposes objects, methods and properties to programming tools and other applications that support Automation.
        /// </summary>
        [ComImport()]
        [Guid("00020400-0000-0000-C000-000000000046")]
        [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
        interface IDispatch
        {
            [PreserveSig]
            int GetTypeInfoCount(out int Count);
    
            [PreserveSig]
            int GetTypeInfo
                (
                    [MarshalAs(UnmanagedType.U4)] int iTInfo,
                    [MarshalAs(UnmanagedType.U4)] int lcid,
                    out ComTypes.ITypeInfo typeInfo
                );
    
            [PreserveSig]
            int GetIDsOfNames
                (
                    ref Guid riid,
                    [MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPWStr)] string[] rgsNames,
                    int cNames,
                    int lcid,
                    [MarshalAs(UnmanagedType.LPArray)] int[] rgDispId
                );
    
            [PreserveSig]
            int Invoke
                (
                    int dispIdMember,
                    ref Guid riid,
                    uint lcid,
                    ushort wFlags,
                    ref ComTypes.DISPPARAMS pDispParams,
                    out object pVarResult,
                    ref ComTypes.EXCEPINFO pExcepInfo,
                    IntPtr[] pArgErr
                );
        }
    }
    
    

    Use this code to get the type name of the selected object (instead of "__ComObject") and modify your code accordingly.  Hope you understand the idea.

     

     


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Wednesday, December 7, 2011 10:22 AM
    Wednesday, November 23, 2011 4:40 PM
  • :) Which of the procedures is mine?

    In fact, using my latest ComHelper class, you can find that when you select several shapes simultaneously, you get an object the type of which is DrawingObjects. And its ShapeRange property for that object returns selected shapes, not just one shape.

     


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Wednesday, December 7, 2011 10:22 AM
    Wednesday, November 23, 2011 4:45 PM
  • @Andrei: That's very helpful. Currently, I have different methods set up to detect Rectangle, Oval, Drawing, DrawingObjects, GroupObject, Line, and TextBox selections - checking each for null then trying the next. This code will simplify things greatly.

    Have you ever thought about setting up a code repository / wiki on add-in-express.com? Not only would it help your customers, but it would be great for SEO as well. It's just a thought ...

    Wednesday, November 23, 2011 4:59 PM
  • This is more than just simplification: I believe this is the only approach in some situations. Now I try to find a confirmation of this thought. I'm going to publish a blog on this. Maybe, next week.

    As to code repository, it is a good idea. Thank you for sharing :)


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, November 23, 2011 5:12 PM
  • Thanks very much all, this has been a great help!!

    Just one question, is there a different thing that needs to be done if the selected shape is inside a chart? It doesnt seem to colour those ones. Its not a huge deal as I can just cut the shape and paste it outside the chart, colour it then put it back in the chart, but if its possible to colour it while inside the chart it'd be a lot easier for users.

    Thank you.

    Wednesday, November 23, 2011 10:12 PM
  • With a chart you need to use the Activate method before making any changes to the chart.  I usually record a macro in excel to help me write my own macros.  thei is the code I got from the macro

        With Selection.Interior
            .ColorIndex = 43
            .PatternColorIndex = 1
            .Pattern = 1
        End With


    jdweng
    Thursday, November 24, 2011 7:08 AM