Interface IListObjects

Represents collection of IListObject in the worksheet.

Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.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");
}

Extension Methods