none
Excel VSTO: testing for identity of PivotTables objects RRS feed

  • Question

  • Hello,

    I am doing a lot of stuff with PivotTables in C# VSTO.
    I could not find a "nice" way for doing a simple thing.

    Assume I have a pivot chart object variable named "ch".
    Assume I have a pivot table object variable named "pt".
    These following statements always return FALSE:

    ch.PivotLayout.PivotTable == pt
    ch.PivotLayout.PivotTable.Equals(pt)
    System.Object.ReferenceEquals(ch.PivotLayout.PivotTable, pt)

    These statements return FALSE, even when I know "pt" represents the pivottable that the chart "ch" displays.
    I found a way around that problem by checking the name of the pivot table and the name of the parent worksheet.
    However, I found it very surprising that testing for equality could become a problem.

    Would you have some suggestion to solve this problem in a "nice" way, also reliable?
    Could you provide me some insight?

    Thanks,

    Michel

    Monday, September 2, 2013 9:28 AM

Answers

  • Hi Michel,

    In VSTO, we use Runtime Callable Wrapper to manipulate an Office COM object.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Microsoft.Office.Interop.Excel;
    using System.Diagnostics;
    
    namespace ExcelAddIn
    {
        public partial class CustomRibbon
        {
            private void CustomRibbon_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                var sheet = Globals.ThisAddIn.Application.ActiveSheet as Worksheet;
    
                var chart = sheet.ChartObjects(1).Chart as Chart;
    
                var tab1 = chart.PivotLayout.PivotTable;
    
                var tab2 = sheet.PivotTables(1) as PivotTable;
    
                Debug.Print(String.Format("TAB1: {0}, TAB2: {1}", tab1.GetHashCode(), tab2.GetHashCode()));
            }
        }
    }
    

    As we can see from the code above, tab1 and tab2 pointed to the same Office Object (PivotTable), but they are 2 different .Net object (see the figure below). This is the reason for these two objects are not equal.

    In a worksheet, PivotTable.Name is unique, so you are able to distinguish PivotTable by name in the same worksheet.



    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 4, 2013 5:39 AM
    Moderator

All replies

  • Hi Michel,

    In VSTO, we use Runtime Callable Wrapper to manipulate an Office COM object.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Microsoft.Office.Interop.Excel;
    using System.Diagnostics;
    
    namespace ExcelAddIn
    {
        public partial class CustomRibbon
        {
            private void CustomRibbon_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                var sheet = Globals.ThisAddIn.Application.ActiveSheet as Worksheet;
    
                var chart = sheet.ChartObjects(1).Chart as Chart;
    
                var tab1 = chart.PivotLayout.PivotTable;
    
                var tab2 = sheet.PivotTables(1) as PivotTable;
    
                Debug.Print(String.Format("TAB1: {0}, TAB2: {1}", tab1.GetHashCode(), tab2.GetHashCode()));
            }
        }
    }
    

    As we can see from the code above, tab1 and tab2 pointed to the same Office Object (PivotTable), but they are 2 different .Net object (see the figure below). This is the reason for these two objects are not equal.

    In a worksheet, PivotTable.Name is unique, so you are able to distinguish PivotTable by name in the same worksheet.



    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 4, 2013 5:39 AM
    Moderator
  • Thanks a lot, Jeffrey-Chen, for explaining so clearly the consequence of .net wrapping.

    Could you suggest me some general way to compare the identity of two COM objects (office objects) from within .net ?

    Thansk again,

    Michel

    Wednesday, September 4, 2013 9:11 AM
  • Hi Michel,

    Currently, I do not have a general way to compare the identity of two COM objects. But in this case, I suggest you to identify PivotTable by name in a worksheet.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.



    Thursday, September 5, 2013 2:23 PM
    Moderator