none
VSTO C# datatable on worksheet. Ribbon sort not working correctly RRS feed

  • Question

  • Hello,

    I have a VSTO addin project written in C# that outputs a datatable to Excel. Columns have autofilters. With no filter applied sorting the data through usage of the sort function of the auto-filter options gives the correct result. However when I use to button in the Ribbon or record a macro on the first scenario the datatable gets out of sync with the worksheet. This means: if 1 change a cell in another column it will also change in the same column on a different row.

    Does anybody know what the problem is here? I tested this behaviour with the TFS excel plugin but have found no such behaviour.

    I have a small independent test-project set up that will reproduce this behaviour.

    Regards,

    Jeroen

     

     

    Thursday, April 7, 2011 9:03 AM

All replies

  • Hello Jeroen,

    Thanks for posting. I read your post carefully, but I think your question is unclear for me so that currently I cannot give much helpful information on this. To provide further assistance, would you mind sharing the test-project/reproducing steps with us? So I could use it on my side to reproduce this issue and do further research.

    I am looking forward to your reply. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, April 8, 2011 4:01 AM
  • Hi Bessie,

    I have a sample project set up. I can send this to you. I just need an email address. Included with the email I will provide you with the steps to reproduce our problem.

    Regards,

    Jeroen

    Monday, April 11, 2011 12:50 PM
  • Hello again Jeroen,

    You could send the sample project to me via v-beszha at microsoft dot com. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 12, 2011 8:13 AM
  • Hi Jeroen,
    I tested according your steps:
    1. Run project from VS2010
    2.Sort column ‘C’ descending using the auto-filter sort option
    3.Change a name in column ‘B’. Results in expected, normal behavior
    4.Now sort ascending in column ‘C’. Using the ribbon sort button located under the data tab (Excel 2010)
    5.
    6. Change a value in column ‘B’
    Now if you look another value in column ‘B’ on a different row will also change.
    This demonstrates the problem. Currently this is a show-stopper for the project
    and releasing it to production

    But I failed to reproduce your problem. What do you mean about changing a name in Column 'B' and cahnge a value in column 'B'? I am not very clear about this? What's the data in Column B?
    I will do further research with your detailed information.
     
    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 20, 2011 8:20 AM
  • Just change a value in one of the cells in a different column than the the one your sorting. You will see that in the same column another cell's value will also change.

    Wednesday, April 20, 2011 11:53 AM
  • Hi KeesFan,

    I still couldn't reproduce your problem. So, could you please upload the test project and screen recorder on the skydrive and share the link with me? 

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, April 21, 2011 6:05 AM
  • Hi,

    I've recorded my screen showing the sorting issue in context menu compared to Ribbon. We are working with a DataTable in Excel, but undoubtfully already noticed that.

    http://cid-9b7ca1ec51559910.office.live.com/self.aspx/.Documents/ExcelSorting.avi

    Best regards,

    Jeroen

    Thursday, April 21, 2011 8:19 AM
  • Hi KeesFan, 

    I still couldn't reproduce your problem after reading the video. I think it may related with your project. Could you share me a copy of your project on the skydrive?

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, April 22, 2011 5:33 AM
  • I can not imagine. This is going wrong on every single workstation I tried. Can you make a screen capture of what you're doing. Im guessing you're doing something wrong.
    Friday, April 22, 2011 7:59 AM
  • Hi KeesFan,

    Here is the screen recoder of mine:

    http://cid-c7d080162e80a07a.office.live.com/self.aspx/Screen%20Records/ScreenShotColumnTable.avi?sa=711191968

    Please check whether I have missed something.

    Best Regards,

     

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 25, 2011 9:07 AM
  • Hi Bruce, Did you use my testproject & ran it from VS 2010? If not I've uploaded my version again to SkyDrive: http://cid-9b7ca1ec51559910.office.live.com/self.aspx/.Documents/ExcelSortTestAddIn.zip Best regards,
    Tuesday, April 26, 2011 6:32 AM
  • Hi KeesFan,

    I have reproduced your problem. However, the problem can't be reproduced if you always select the column header. You can take a try.

    Besides, if I use my manually insert the data to Excel, the problem doesn't happen.

    I am not sure whether this is a issue and I will do further research about the problem.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, April 27, 2011 11:50 AM
  •  Hello KeesFan,

    I downloaded your Add-in and ran it repeatedly until I saw the issue.  Then I copied your code into a new VSTO Excel 2010 Add-in (ExcelAddin2) and disabled your add-in.

    Initially I did not reproduce the problem in debug mode.  I tested repeatedly while the code was idle with inconsistent results. I changed contents of Column2 (name) several times, and after each change I sorted A-Z and Z-A on column 3 repetitively without seeing the issue. I changed the name in Column B Row 2 and instantly the new value (name) appeared in C2R2 as expected, but also in C2R3 - demonstrating the issue.

    The next test was different; not until I changed the name multiple times, interspersed with sorting on Column3, did the issue appear at the end of a sort. I could not identify why the issue happened this time as being different from the sequence of events for earlier tests.

    Recommendation: Add an event handler to the Add-in for the Microsoft.Office.Interop.Excel.DocEvents.ChangeEventHandler to monitor the contents of the column2 array and Column3 array and build a log of changes, and trigger a dump when the eventhandler code shows the issue so you can see the call stack leading up to the occurrence of the issue.  You’ll need to install Debugging Tools for Windows and get a hang dump (even though your code hasn’t hung) in order to catch the call stack on that test instead of every time you exercise the test.

    See the following KnowledgeBase article:
    286350  How to use ADPlus to troubleshoot "hangs" and "crashes"
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;286350

    There is a link in that article to “Download Debugging Tools from the Windows SDK”

    If you still are unable to determine the cause of the issue your question is for advisory support and falls into the paid support category which requires a more in-depth level of support.  Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    If the Support Engineer who works with you needs to trace the flow of code through your code and that of Excel he will ask you to install a tool that traces the code over as many repetitions as it takes to catch the issue.

    If the support engineer determines that the issue is the result of a bug in Excel the service request will be a no-charge case and you won't be charged.

    Chris Jensen
    Senior Technical Support Lead

    Monday, May 2, 2011 6:54 PM
    Moderator