VSTO Excel: how to create custom column mapping using ListObject?

Question

I have a model like this:

public class Instrument { public string Id { get; set; } public string Name { get; set; } // About 100 other properties } 

And I am retrieving Instrument instances from the EDM Framework Entity Framework.

Now I would like to make a custom mapping to bind this dataset to an Excel ListObject element. By default, ListObject will display each column in an Excel worksheet, but I only want to show some properties in a specific order.

 ExcelTools.ListObject instrumentsTable = this.Controls.AddListObject(tableStart, tableName); instrumentsTable.DataSource = myEDM.Instruments; // Custom mapping code... 

I tried using the SetDataBinding method, but it selected a data binding exception.

Can anyone help me achieve this?

Thanks.

Answer 1

I do not know if this is useful, but I created a test project. The test project works without problems.

First I declare a test class:

 public class Instrument { public string Id { get; set; } public string Name { get; set; } public string Property1 { get; set; } public string Property2 { get; set; } } 

I have a getInstruments function that returns a list.

Then I bind this to the sheet using this code:

  List<Instrument> list = getInstruments(); ListObject instrumentsTable = Controls.AddListObject(Range["A1", "B4"], "list1"); string[] mappedColumns = { "Name", "Property1" }; instrumentsTable.SetDataBinding(list, string.Empty, mappedColumns); 

A sheet with 4 fixtures is displayed, displaying the name and property1 in columns A and B.

I also tried this using the Entity framework, connecting the sheet to one of my databases and not having any problems with this.

If you still have problems, provide additional information about the exception that you get when using SetDataBinding.