Excel listObject doesn't show updated values i

2019-09-16 18:00发布

I have an excel table created programmatically, which uses a List<Object> as a data source. I am creating this table by adding a ListObject using Tools.Worksheet AddListObject(), and then assigning listObject.DataSource with List<Object>. Table is getting created and works fine and user can edit any cell/row in a table.

I am handling edits using the WorkSheet.Change event. When a user edits a cell which belongs to a table the worksheet.Change event is fired (Verified by setting a breakpoint in the Change event handler) as expected.

I am accessing the table/listObject using target sent to Change event handler then I am using Factory.GetVstoObject(target.ListObject) to get Tools.ListObject. Everything works fine till this point.

Issue:

When I access ListObject.DataSource, I see that the values in dataSource is not updated with new value. I need this new value in workSheet.Change event to do some validation and other stuff. Why am I not seeing updated values in Change event handler? DataSource values should be updated to new values before reaching WorkSheet.Change event handler right? Is there any way to fix this issue or Is there any other way to get new values of table/listObject?

PS: After change event handler is executed and say if User edits again, I see 1st edited value in listObject.DataSource (Not 2nd edit value). I tried ListObject.Change event as well, still seeing old value.

Here's the code : This is how I am creating a new worksheet.

public static Worksheet CreateNewExcelSheet(string sheetName)
{
    var newWorksheet = (Worksheet)Globals.ThisAddIn.Application.Worksheets.Add();
    newWorksheet.Name = sheetName;
    newWorksheet.Activate();
    newWorksheet.Change += WorkSheetChange_EventHandler;
    newWorksheet.CustomProperties.Add(IsTableSelected, "false");
    return newWorksheet;
}

Code for creating listobject:

public static Tools.ListObject CreateListObject(Worksheet workSheet, Range startingPosition, string listObjectName)
{
    var vstoWorkSheetObject = GetVstoWorkSheetObject(workSheet);
    var listObject = 
    vstoWorkSheetObject.Controls.AddListObject(startingPosition, listObjectName);
    listObject.Deselected += ListObject_Deselected;
    listObject.Selected += ListObjectOnSelected;
    listObject.Change += ListObject_Change;
    listObject.DataBindings.Clear();
    listObject.TableStyle = TableStyle;
    return listObject;
}

Main Code:

private void CreateTable(string sheetName, string listObjectName)
{
    var workSheet = CreateNewExcelSheet(sheetName);
    var listObject = CreateListObject(workSheet, Globals.ThisAddIn.Application.Range["A4"], listObjectName);
    listObject.SetDataBinding(dataSourceFromServer);
    var counter = 0;
    foreach (Range cell in listObject.HeaderRowRange.Cells)
    {
        cell.Value2 = ColumnNames[counter];
        counter++;
    }
    listObject.HeaderRowRange.EntireColumn.AutoFit();
}

dataSourceFromServer is of type List<Object>

0条回答
登录 后发表回答