Updating BindingList data source bound to a VSTO ListObject may cause sequencing and formatting issues.
Issue Description:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Consider a scenario where a VSTO ListObject is bound to a BindingList data source. Let the data source contain values like {{1,5},{3,4},{5,6},{7,8}}. Below is the screen shot of ListObject bound to this data source.
Before Insert:
Now let’s say there is a requirement to insert new element at a particular index into the ListObject. The solution is to update the data source, and ListObject should automatically reflect those changes.
Lets insert a new element {100,100} at 2nd index into the data source. Now the data source become {{1,5},{3,4},{100,100},{5,6},{7,8}} (considering index start at 0). Now we expect the ListObject to reflect this change. List Object does insert the new element {100,100}, but at the last index as shown below
After Insert: 
(Issue 1)
If we try to re-bind the ListObject (ListObject.SetDataBinding) , it appears to work, but introduces formatting issues as shown below:
Before Insert: 
After Insert and rebind: 
(Issue 2)
You will observe that the formatting which was applied to {5,6} has moved to {100,100} which is not the expected result.
To workaround this, we will need to disconnect the ListObject from data source, update both ListObject and data source (BindingList) and then rebind the ListObject with data source.
Below are the steps to reproduce and workaround the issue:
1) Create a new Excel VSTO Workbook project using Visual Studio 2008 (C# language is used here).
2) Add a button and a ListObject control to sheet1. Name the button as “Button1” and the ListObject as “list1”.
3) Go to sheet1 code view and replace the existing code with the following:
public partial class Sheet1
{
BindingList<Box> boxList;
private void Sheet1_Startup(object sender, System.EventArgs e)
{
boxList = CreateTestData();
BindListObject();
}
private void Sheet1_Shutdown(object sender, System.EventArgs e)
{
}
private BindingList<Box> CreateTestData()
{
BindingList<Box> boxList1 = new BindingList<Box>();
boxList1.AllowEdit = true;
boxList1.AllowNew = true;
boxList1.AllowRemove = true;
boxList1.Add(new Box(1, 5));
boxList1.Add(new Box(3, 4));
boxList1.Add(new Box(5, 6));
boxList1.Add(new Box(7, 8));
return boxList1;
}
#region VSTO Designer generated code
///<summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///</summary>
private void InternalStartup()
{
this.button1.Click += new System.EventHandler(this.button1_Click);
this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);
this.Startup += new System.EventHandler(this.Sheet1_Startup);
}
#endregion
private void BindListObject()
{
list1.SetDataBinding(boxList, null, null);
}
private void button1_Click(object sender, EventArgs e)
{
boxList.Insert(2, new Box(100, 100));
}
}
public class Box
{
public int _width, _height;
public int width
{
get { return _width; }
set
{
_width = value;
}
}
public int height
{
get { return _height; }
set
{
_height = value;
}
}
public Box(int width, int height)
{
this.width = width;
this.height = height;
}
}
4) Build and run the project.
5) Click on Button1. You will observe that a new row (100,100) has been inserted at the end of the ListObject. However we inserted the new row at 2nd place as per the button1_click handler code. Issue1 mentioned above is reproduced.
6) Now close the worksheet and stop debugging (if in debug mode).
7) Update the Button1 event handler with the following code to rebind it after updating the data source.
private void button1_Click(object sender, EventArgs e)
{
boxList.Insert(2, new Box(100, 100));
BindListObject();
}
8) Build and run the project.
9) Apply “Fill Color” to the third row (5,6). Click on “Button1.”
10) You will observe that formatting placed on row (5,6) is not there and has now been applied to newly inserted row which is at third place. This reproduces the Issue 2 discussed above.
11) Update the Button1 event handler with the following code to disconnect the ListObject from data source, update both ListObject and data source (BindingList), and then rebind the ListObject with data source.
private void button1_Click(object sender, EventArgs e)
{
// Disconnect
list1.Disconnect();
//Update ListObject
Excel.ListRow lr = list1.ListRows.Add(2);
lr.Range.Cells[1, 1] = "100";
lr.Range.Cells[1, 2] = "100";
//Update Data source
boxList.Insert(2, new Box(100, 100));
//Rebind
BindListObject();
}
12) Build and run the project. Observe the results and they should look good J.
NOTE: Above issues are not observed when the ListObject is bound to a DataSet. These are only applicable when the ListObject is bound to BindingList data source.