Interface IListObjects

Represents collection of IListObject in the worksheet.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IListObjects : IList<IListObject>, ICollection<IListObject>, IEnumerable<IListObject>, IEnumerable

Methods

AddEx(ExcelListObjectSourceType, IConnection, IRange)

Creates a query table with specified connection and its source type. Adds it to the IListObjects collection.

Declaration

IListObject AddEx(ExcelListObjectSourceType type, IConnection connection, IRange Destination)

Parameters

Type	Name	Description
ExcelListObjectSourceType	type	Represents connection source type.
IConnection	connection	Represents the connection of the table.
IRange	Destination	Represents destination Range for the query table.

Returns

Type	Description
IListObject	Returns a IListObject with the specified connections.

Remarks

To know more about importing data to table refer Create a Table from External Connection

Examples

The following code illustrates how a table can be created using data from external connection.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Create worksheet
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook= application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Set Connection string for DataSource
    string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";

    //Add a connection to the workbook
    IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);

    //Add a QueryTable to sheet object
    IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);

    //Set Command Text for the Connection
    table.QueryTable.CommandText = "Select * from tbltest";

    //Set connection description
    Connection.Description = "Sample Connection";

    // Import data to the sheet from the database
    table.Refresh();

    //Apply AutoFit
    worksheet.UsedRange.AutofitColumns();

    //Save and Dispose
    workbook.SaveAs("Tables.xlsx");
    workbook.Close();
}

Create(String, IRange)

Creates a table with specified name and its data range. Adds it to the IListObjects collection.

Declaration

IListObject Create(string name, IRange range)

Parameters

Type	Name	Description
System.String	name	Represents the name of the table.
IRange	range	Represents the data range of the table.

Returns

Type	Description
IListObject	Returns a IListObject with the specified name and range.

Remarks

To know more about tables refer Working with Tables

Examples

To create table using data from external sources AddEx(ExcelListObjectSourceType, IConnection, IRange) method can be used. The following code illustrates how a table can be created in a worksheet using data in the worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Create worksheet
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook= application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0]; 

    //Load data
    worksheet["A1"].Text = "Products";
    worksheet["A2"].Text = "Alfreds Futterkiste";
    worksheet["A3"].Text = "Antonio Moreno Taqueria";
    worksheet["A4"].Text = "Around the Horn";
    worksheet["A5"].Text = "Bon app";
    worksheet["A6"].Text = "Eastern Connection";
    worksheet["A7"].Text = "Ernst Handel";
    worksheet["B1"].Text = "Qtr1";
    worksheet["B2"].Number = 744.6;
    worksheet["B3"].Number = 5079.6;
    worksheet["B4"].Number = 1267.5;
    worksheet["B5"].Number = 1418;
    worksheet["B6"].Number = 4728;
    worksheet["B7"].Number = 943.89;
    worksheet["C1"].Text = "Qtr2";
    worksheet["C2"].Number = 162.56;
    worksheet["C3"].Number = 1249.2;
    worksheet["C4"].Number = 1062.5;
    worksheet["C5"].Number = 756;
    worksheet["C6"].Number = 4547.92;
    worksheet["C7"].Number = 349.6;

    //Create style for table number format
    IStyle style1 = workbook.Styles.Add("CurrencyFormat");
    style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";

    //Apply number format
    worksheet["B2:C8"].CellStyleName = "CurrencyFormat";

    //Create table
    IListObject table1 = worksheet.ListObjects.Create("Table1", worksheet["A1:C7"]);

    //Apply builtin style
    table1.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium9;

    //Total row
    table1.ShowTotals = true;
    table1.Columns[0].TotalsRowLabel = "Total";
    table1.Columns[1].TotalsCalculation = ExcelTotalsCalculation.Sum;
    table1.Columns[2].TotalsCalculation = ExcelTotalsCalculation.Sum;

    //Apply AutoFit
    worksheet.UsedRange.AutofitColumns();

    //Save and Dispose
    workbook.SaveAs("Tables.xlsx");
    workbook.Close();
}

Remove(IListObject)

Removes a table from the worksheet.

Declaration

bool Remove(IListObject listObject)

Parameters

Type	Name	Description
IListObject	listObject	ListObject to be removed

Returns

Type	Description
System.Boolean	true if table is successfully removed; otherwise, false.

Examples

Following code illustrates how to remove a table from the worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
    IWorksheet worksheet = workbook.Worksheets["Database"];

    IListObjects listObjects = worksheet.ListObjects;

    IListObject table = listObjects[0];

    listObjects.Remove(table);

    workbook.SaveAs("output.xlsx");
}

RemoveAt(Int32)

Removes a table from the worksheet at the specified index.

Declaration

void RemoveAt(int index)

Parameters

Type	Name	Description
System.Int32	index	Index of the ListObject to be removed

Examples

Following code illustrates how to remove a table from the worksheet from using specified index.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
    IWorksheet worksheet = workbook.Worksheets["Database"];

    IListObjects listObjects = worksheet.ListObjects;

    listObjects.RemoveAt(0);

    workbook.SaveAs("output.xlsx");
}