"Reload" listobject after UnList()

"Reload" listobject after UnList()

• GouletPO

  Ok, I thought if I used Remove(), the listobject was removed from he collection and I could reuse the same name. The result I get now is that the "result" from the listobjects are appended to the sheet. Is there a simple method for get rid of the "old" result Thanks for all the guidelines :)

  best regards

  
  

• Dennis van.der Stelt

  It doesen't work, all the time..
  Is there another way to fill the sheet with data, without the listobject Because Icustomer need this flow to work properly and in this way (rebind data to the sheet)!

  
  

• jose rivera

  Some mor problems withe the ListObject.

  It looks like this now:

   

  ListObject lo = this.Controls.AddListObject(this.Range["A8", "F8"], "ReportList"+_counter.ToString());

  _counter++;

  lo.DataBoundFormatSettings = FormatSettings.Width;

  lo.DataBoundFormatSettings = FormatSettings.Number;

  lo.DataBoundFormatSettings = FormatSettings.Font;

  lo.DataBoundFormatSettings = FormatSettings.Alignment;

  lo.AutoSelectRows = true;

  lo.AutoSetDataBoundColumnHeaders = true;

  lo.SetDataBinding(ds.Tables[0], "", new string[ 6 ]  { "bla", "bla2", "bla3", "bla4", "bla5", "bla6" });

  lo.Unlist();

   

  When I run this codesnippet the program hangs on the SetDataBinding(). Nothing happends, I can only see the lisobject border has been modified. But then nothing happens, the excel just hangs there.

   

  Best regards

   

  Carl

  
  

• Scuzball

  Anyone I realy need to be able to "relode" the listObject or instanisate a new one every time.

  
  

• shamrock17

  Ok, the funny thing is that it works some times but suddenly it just hangs. I will try your suggestions tomorow. Where can I report this, or send my code to

  My datatable is in differen sizes but in my tests it hasen't been larger than probably 3-50 rows with 10-15 columns.

  
  

• Mr.Nam

  If I set this.Cells.Clear(), everything works fine :)
  
  I had to create a new solution though, if a user saves the excel workbook, does it save something "behind" so the program can be corupted

  
  

• rcodyr

  Hi Carl:

  Please report any bugs you find on the MSDN Product Feedback Center. To ensure that you’re not posting a duplicate bug, be sure to use the Search option on the main page to determine whether your bug has already been discovered and posted.

  You may also submit feature requests here, too. You can help us track these requests by using this title format: "FEATURE REQUEST: <one-line request description>". You can then provide further details in the Problem Statement section of the suggestion form.

  MSDN Product Feedback Center

  http://lab.msdn.microsoft.com/productfeedback/

  Thanks!

  Mike Hernandez
  Community Program Manager
  VSTO Team

  
  

• jbond100684

  Hi Carl,

  Since you want to save the header row, you should use Unlist() only and not call the Controls.Remove as I mentioned earlier.

  If you are getting all the rows bolded, you can use formatting capabilities of the ListObject. We added 2 properties called DataBoundFormat and DataBoundFormatSettings to the ListObject that you can use to specify the formatting for the data. Whenever data gets updated in the ListObject, it applies the specified formatting to the entire ListObject range.

  Hope this helps.

  
  
  

• Lynn Roth

  Another thing you could do is to apply this formatting yourself after calling the Unlist.

  Regarding your follow up question about AddListObject throwing an exception, remember that you must specify a unique name when trying to do this every time, because you cannot have more than one controls with the same name.

  
  
  

• Stijn Sanders

  Tnx for the reply. When I do this I have lost all my layout on the listObject. I would like to set the header text on the header columns. I also dont whant all text to be bold and underlined (only the header text). How can I solve this

   

   I'm also getting an error when I'm trying to add the control a second time (a control with that name is already in the collection). I have tried this before I add the control: this.Controls.Remove("ListObjectName");

  "this" is my spreadsheet. and I get a com-error

  Best regards 

  
  

• swelsh12

  Hi Carl,

  You seem to have hit some bug here. What size is your data table

  Couple of things to note in the code snippet above

  1. The DataBoundFormatSettings is a property whose enum is a flag. So, you need to set all the values in one call like this:

  lo.DataBoundFormatSettigns = FormatSettings.Width | FormatSettings.Number | FormatSettings.Font | FormatSettings.Alignment;

  2. You should not need to set the AutoSelectRows property to true in your scenario.

  3. In the SetDataBinding method call, you dont need to create the string array, as the method accepts a params string array, so you can rewrite the method call as follows:

  lo.SetDataBinding (ds.Tables[0], "", "bla", "bla2" );

  I would like to get a repro for the bug you are hitting. Let me see how we can set this up.

  Thanks.

  
  
  

• jasin14

  Hi Carl,

  When you call Unlist on a ListObject, the control does not exist anymore as it just got converted to a range.

  In this scenario, you need to use VSTO's ability to dynamically add and delete host controls from the worksheet. To add a ListObject to Sheet1 at run time, add code similar to the following to the Sheet1 class:

  ListObject myListObject = this.Controls.AddListObject ( this.Range[ "A1", "C5" ], "myListObject" );

  myListObject.SetDataBinding ( dataSet1 );

  When you are done with the ListObject, use the Unlist() method or the Remove method to delete the ListObject.

  this.Controls.Remove ( "myListObject" );

  Hope this helps.

  
  
  

• fravelgue

  Very strange, when I have made changes in debugmode and everything "crasches". I rebuild the solution and the problem apear again. I can bind the listobject one time, then everything haults (cpu usage 100%).
  
• "Reload" listobject after UnList()