ListObject does not update underlying DataSource Current property when AutoFilter Sort is applied RRS feed

  • Question

  • Hello,

    We are working on an Excel 2010 Add-in, developed with Visual Studio 2010, with a ListObject bound to a BindingSource which is itself bound to a List of T. 

    In the Microsoft.Office.Tools.Excel.ListObject.BeforeDoubleClick event handler of the ListObject, we would like to get the associated T object from the List, based on the parameter "Range target" that was double clicked. We were using ListObect's DataSource property, which is a BindingSource, to get to the "Current" item to get our T.

    protected void PromotionListDoubleClick(Range target, ref bool cancel)
    Promotion targetPromotion = ((BindingSource) ListObject.DataSource).Current as Promotion;

    However, this all breaks down when the user applies a sort to the table, using AutoFilter feature of Excel.  The "Current" is not updated when the sort is applied.  Only if the user manually clicks on another cell in the ListObject’s table, then "Current" property is updated.

    Is that a bug in the Excel's InterOp?  Is there a workaround for it?

    So far, we have been using some ugly code to fetch out T, based on another unique column of the table, but this method is not sustainable, and we cannot use it in a table that does not have a unique column.


    PS: Is it related to this post?

    Wednesday, September 11, 2013 3:27 PM

All replies

  • Hi,

    I reproduce the issue follow below steps:

    1. Create excel document add-in project
    2. Add listobject control in sheet1
    3. Bind listobject like below:

    private void PrepareData()
                employeeTable = new System.Data.DataTable("Employees");
                System.Data.DataColumn column = employeeTable.Columns.Add
                    ("Id", typeof(int));
                column.AllowDBNull = false;
                employeeTable.Columns.Add("FirstName", typeof(string));
                employeeTable.Columns.Add("LastName", typeof(string));
                employeeTable.Columns.Add("Age", typeof(int));
                employeeTable.Rows.Add(id, "Nancy", "Anderson", "56");
                employeeTable.Rows.Add(id, "Robert", "Brown", "44");
                BindingSource binddingSource = new BindingSource();
                binddingSource.DataSource = employeeTable;
                list1.DataSource = binddingSource;

        4.  Get current item before sort or filter

    void PromotionListDoubleClick(Microsoft.Office.Interop.Excel.Range
        Target, ref bool Cancel)
           object obj = ((BindingSource)list1.DataSource).Current;
                DataRowView drv = ((BindingSource)list1.DataSource).Current as DataRowView;
                MessageBox.Show(drv["FirstName"].ToString());        }

        5.  compare current item after sort or filter, the current was not changed.

    But BindingSource Class is used to simplify binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data source. So it is not fully compatible with ListObject Interface which belongs to Microsoft.Office.Tools.Excel namespace. To get the selected row, I suggest you to use the code below:

    (list1.ListRows[list1.SelectedIndex].Range[columnIndex] as Excel.Range).Text

    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.

    Saturday, September 14, 2013 4:46 AM
  • Thanks for the sample.

    However, your workaround only provides us with the selected row Range, not with the underlying object from the BindingSource.

    In our case the  BindingSource is a List<T>, where T could be any class. We are trying to get the T out, not the selected row in Excel.

    Any ideas?



    Friday, September 20, 2013 5:12 PM