Jerry Nixon @Work: Use VSTO 3 in Excel 2007 to add a data bound Table (ListObject)

Jerry Nixon on Windows

Wednesday, November 19, 2008

Use VSTO 3 in Excel 2007 to add a data bound Table (ListObject)

The ListObject in Excel is a lot like a grid in WinForms. You can data bind to it. You can sort it, You can filter it. You can style it. You can do a lot. To create on one the fly (which is how I would recommend doing it - opposed to creating one in the designer) just use my sample as a guide. I'll put the new ListObject on Sheet1 in cell B:2. I'm using the Northwind Employees table in my sample.
 

 // get the data

var _Source = Database.Easy.Employees();

// create list

Microsoft.Office.Tools.Excel.ListObject _ListObject;
_ListObject = Globals.Sheet1.Controls.AddListObject(
Globals.Sheet1.Range["B2", System.Type.Missing], "SalesListObject");

// bind data

_ListObject.SetDataBinding(_Source, string.Empty,
new string[] { "FirstName", "LastName", "Title" });
_ListObject.AutoSetDataBoundColumnHeaders = true;

// expand the cols

Globals.Sheet1.Range["B:B", System.Type.Missing].EntireColumn.AutoFit();
Globals.Sheet1.Range["C:C", System.Type.Missing].EntireColumn.AutoFit();
Globals.Sheet1.Range["D:D", System.Type.Missing].EntireColumn.AutoFit();