Databound ListObject and Paste Problem

Question

We are running into a problem with VSTO Excel spreadsheets using ListObjects and users Pasting data into the ListObject. Data pasted into the ListObject is being simultaneously lost and duplicated.

Essentially the problem is occurring when the ListObject is empty. Here are the steps to reproduce it:

1. Create a VSTO project with a ListObject (5 columns) and a Button on the spreadsheet. See the attached code below for event handlers.
2. Run the project, and type any data into the first cell in the first row. Tab to the next cell or click the next cell to the right. Do NOT use the Enter key or click any cell outside the current row.
3. Switch to another spreadsheet. Copy a 2-column by 3-row set of cells with data in them. Any data/text/numbers will work, but it is best if you can uniquely identify the rows visually.
4. Switch back to the VSTO sheet and paste into the second cell of the first row. Don't click anywhere else with the mouse. There should appear to be 3 rows in the ListObject.
5. Click the button. It should report only two rows in the DataTable. If you place a breakpoint and examine the data, only the first two rows from the ListObject are present in the DataTable.
6. Now, click the spreadsheet anywhere outside the first row of data.
7. Click the button. Now it reports 3 rows in the DataTable. If you place a breakpoint and examine the data, the new third row in the DataTable is a duplicate of the second row. Now the data visible in the ListObject is different than the data in the bound DataTable. In fact, if you change the data in the third row, the change never is reflected in the DataTable.

I understand the workaround is for the user to use the Enter key or to click outside the row before pasting. If this occurs the Paste works just fine.  It seems to us that the ListObject will not update the dataset until the user leaves the row. What I am hoping to find out is some way to force the ListObject commit the row before the paste. There are no hooks into the Paste event and there are seem to be no commands to make the ListObject push it's changes to the underlying DataTable. Unfortunaltely for this project, one of the main requirements from the user base is copy/paste functionality, as they run their enterprise primarily with Excel and copy and paste data copiously.

I posted about a different copy/paste problem previously, and this may be related, but it seemed alarming that data was lost completely in this case. Thanks for your help.

Dave

namespace PasteExperiment
{
    public partial class Sheet1
    {
        private DataSet globalData;
        private BindingSource bs;

        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
            globalData = this.CreateData();
            bs = new BindingSource(globalData, "Data");
           
            //PasteList is the name of the ListObject
            this.PasteList.DataSource = bs;
        }

        private void Sheet1_Shutdown(object sender, System.EventArgs e)
        {
        }

        private DataSet CreateData()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable("Data");
            dt.Columns.Add("First");
            dt.Columns.Add("Second");
            dt.Columns.Add("Third");
            dt.Columns.Add("Fourth");
            dt.Columns.Add("Fifth");
            ds.Tables.Add(dt);
            return ds;
        }

#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 button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show(this.globalData.Tables[0].Rows.Count.ToString());
        }
    }
}

Answer 1

Hi Dave

Thanks for highlighting the issue. Your feedback will help us make the product better. Your reproduction stesps for accurate and I have been able to reproduce the bug. I have logged it down internally. I would still adivse you to post the bug details on http://lab.msdn.microsoft.com/productfeedback/default.aspx

As for the workaround, you are correct that if you change the cell selection to some other row (or outside the ListObject) after entering  the data in the first row - the paste should not cause a problem.

Thanks once again!

-Nikhil