You can use data binding as a way to quickly add data to your document. After binding data to a list object, you can call the Disconnect method of the ListObject so that the ListObject displays the data but is no longer bound to the data source.
To bind data to a ListObject control
-
Create a DataTable at the class level.
Dim table As DataTable = New DataTable("Employees")
DataTable table = new DataTable("Employees");
-
Add sample columns and data in the Startup event handler of Sheet1.
Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Startup
table.Columns.Add("FirstName", GetType(String))
table.Columns.Add("LastName", GetType(String))
table.Columns.Add("Title", GetType(String))
table.Rows.Add("Nancy", "Anderson", "Sales Representative")
table.Rows.Add("Robert", "Brown", "Sales Representative")
private void Sheet1_Startup(object sender, System.EventArgs e)
{
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("Title", typeof(string));
table.Rows.Add("Nancy", "Anderson", "Sales Representative");
table.Rows.Add("Robert", "Brown", "Sales Representative");
-
Call the SetDataBinding method and pass in the column names in the order they should appear. The order of the columns in the list object can differ from the order in which they appear in the DataTable.
Dim List1 As Microsoft.Office.Tools.Excel.ListObject = _
Me.Controls.AddListObject(Me.Range("A1", "B3"), "List1")
List1.AutoSetDataBoundColumnHeaders = True
List1.SetDataBinding(table, "Title", "LastName", "FirstName")
Microsoft.Office.Tools.Excel.ListObject List1 =
this.Controls.AddListObject(this.Range["A1", "B3"], "List1");
List1.AutoSetDataBoundColumnHeaders = true;
List1.SetDataBinding(table, "Title", "LastName", "FirstName");
To disconnect the ListObject control from the data source
See Also