Interface IListObject

Represents a table on a worksheet.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Portable.dll

Syntax

public interface IListObject

Remarks

The ListObject object is a member of the IListObjects collection. The IListObjects collection contains all the list objects on a worksheet. Use the ListObjects property of the IWorksheet to return a IListObjects collection.

Properties

AlternativeText

Gets or sets the alternative text title.

Declaration

string AlternativeText { get; set; }

Property Value

Type
System.String

Examples

The following code illustrates how to set the alternative text title for the table.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].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["D2:E8"].CellStyleName = "CurrencyFormat";

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

            //Set the alternative text title
            table1.AlternativeText = "Title";

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

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

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

AutoFilters

Gets the IAutoFilters collection in the table. Read-only.

Declaration

IAutoFilters AutoFilters { get; }

Property Value

Type
IAutoFilters

Examples

The following code illustrates how IAutoFilters collection in table can be accessed.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

            IAutoFilters filters = table1.AutoFilters;
            IAutoFilter filter = filters[0];
            filter.AddTextFilter("Bon app");

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

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

BuiltInTableStyle

Gets or sets the built-in style for the table.

Declaration

TableBuiltInStyles BuiltInTableStyle { get; set; }

Property Value

Type
TableBuiltInStyles

Examples

To customize the table layout built-in styles from TableBuiltInStyles enumeration can be applied to tables. Here for example, we set TableStyleMedium9 to BuiltInTableStyle property.

        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();
        }

Columns

Gets collection of columns in the table. Read-only.

Declaration

IList<IListObjectColumn> Columns { get; }

Property Value

Type
System.Collections.Generic.IList<IListObjectColumn>

Examples

The following code illustrates how to access Columns property of the table.

        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();
        }

DisplayName

Gets or sets the display name for the table.

Declaration

string DisplayName { get; set; }

Property Value

Type
System.String

Examples

If the DisplayName of the table is not set it will be set using the value from Name property. Here for example, we get the DisplayName and print it to console.

        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["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["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;

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

            //Get Table display name
            Console.WriteLine(table1.DisplayName);

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

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//Table1

Index

Gets index of the table in a worksheet. Read-only.

Declaration

int Index { get; }

Property Value

Type
System.Int32

Examples

The following code illustrates how to access the index of the table.

        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;

            //Print Table index
            Console.WriteLine(table1.Index);

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

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//2

Location

Gets or sets the location of the table in a worksheet.

Declaration

IRange Location { get; set; }

Property Value

Type
IRange

Examples

The following code illustrates how table range can be set and accessed.

        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["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["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;

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

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

            //Print Table Range
            Console.WriteLine(table1.Location.AddressLocal.ToString());

            //Add Data
            worksheet["A7"].Text = "Ernst Handel";
            worksheet["B7"].Number = 943.89;
            worksheet["C7"].Number = 349.6;

            //Set Table Range
            table1.Location = worksheet.Range["A1:C7"];

            //Get Table Range
            Console.WriteLine(table1.Location.AddressLocal.ToString());

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

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

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

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//A1:C6
//A1:C7

Name

Gets or sets the name of the table.

Declaration

string Name { get; set; }

Property Value

Type
System.String

Examples

Following code illustrates how to access Name property of the table.

        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["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["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;

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

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

            //Get Table Name
            Console.WriteLine(table1.Name);

            //Set Table Name
            table1.Name = "Products";

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

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

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

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//Table1

QueryTable

Gets the query table object bound with the table.

Declaration

QueryTableImpl QueryTable { get; }

Property Value

Type
QueryTableImpl

Examples

Following code illustrates how to access the Query table.

        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();
        }

ShowAutoFilter

Gets or sets Boolean to indicate whether the AutoFilter will be displayed. Default value is TRUE.

Declaration

bool ShowAutoFilter { get; set; }

Property Value

Type
System.Boolean

Examples

By default ShowAutoFilter property is set to "true". If it is set to "false" after applying filter within table, then it clears the filter applied. Here for example, we set a text filter within the table and set "false" to ShowAutoFilter property to clear the applied text filter.

        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:C7"].CellStyleName = "CurrencyFormat";

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

            //Add filter
            IAutoFilters filters = table.AutoFilters;
            IAutoFilter filter = filters[0];
            filter.AddTextFilter("Ernst Handel");

            //Clear filter
            table.ShowAutoFilter = false;

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

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

ShowFirstColumn

Gets or sets a Boolean value indicating whether first column format is present.

Declaration

bool ShowFirstColumn { get; set; }

Property Value

Type
System.Boolean

Examples

If table's BuiltInTableStyle property is set, then the table's first column format can be enabled by setting "true" to ShowFirstColumn property. By default it is "false". Here for example, we set ShowFirstColumn to "true" to enable the format for the first column.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

            //Set Column Visiblity
            table1.ShowFirstColumn = true;
            table1.ShowLastColumn = true;

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

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

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

ShowHeaderRow

Gets or sets a Boolean value indicating whether to hide/display header row.

Declaration

bool ShowHeaderRow { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to hide the header row of the table.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

            //Set Header Visiblity
            table1.ShowHeaderRow = false;

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

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

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

ShowLastColumn

Gets or sets a Boolean value indicating whether last column format is present.

Declaration

bool ShowLastColumn { get; set; }

Property Value

Type
System.Boolean

Examples

If table's BuiltInTableStyle property is set, then the table's last column format can be enabled by setting "true" to ShowLastColumn property. By default it is "false". Here for example, we set ShowLastColumn to "true" to enable the format for the last column.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

            //Set Column Visiblity
            table1.ShowFirstColumn = true;
            table1.ShowLastColumn = true;

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

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

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

ShowTableStyleColumnStripes

Gets or sets a Boolean value indicating whether column stripes should be present.

Declaration

bool ShowTableStyleColumnStripes { get; set; }

Property Value

Type
System.Boolean

Remarks

Similar to ShowTableStyleRowStripes and ShowTableStyleColumnStripes, ShowHeaderRow and ShowTotals properties are used to alter first and last rows of the table.

Examples

By default ShowTableStyleColumnStripes is set to "False", So formatting is applied only to table rows. Here for example, We set ShowTableStyleColumnStripes to "True" and ShowTableStyleRowStripes to "False" to change the default formatting of the table.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

            //Set Row Column Stripe Visiblity
            table1.ShowTableStyleRowStripes = false;
            table1.ShowTableStyleColumnStripes = true;

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

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

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

ShowTableStyleRowStripes

Gets or sets a Boolean value indicating whether row stripes should be present.

Declaration

bool ShowTableStyleRowStripes { get; set; }

Property Value

Type
System.Boolean

Examples

ShowTableStyleRowStripes property is set to "True" by default. This allows table to show alternate shades of color present in the table style. Here for example, We set ShowTableStyleRowStripes to "False" and set ShowTableStyleColumnStripes to "True" to enable table formatting in columns only.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].Number = 349.6;

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

            //Set Row Column Stripe Visiblity
            table1.ShowTableStyleRowStripes = false;
            table1.ShowTableStyleColumnStripes = true;

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

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

            //Apply number format
            worksheet["D2:E8"].CellStyleName = "CurrencyFormat";

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

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

ShowTotals

Gets or sets a Boolean value indicating whether the Total row is visible.

Declaration

bool ShowTotals { get; set; }

Property Value

Type
System.Boolean

Examples

By default ShowTotals property is set to "False". To add an additional row to display the sum or average of values in the column in the table ShowTotals has to be set to "True". Here for example, We enable it by setting it to "True" and show the sum of values in second and third columns in the totals row.

        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();
        }

Summary

Gets or sets the alternative text description.

Declaration

string Summary { get; set; }

Property Value

Type
System.String

Examples

The following code illustrates how to set the alternative text description for the table.

        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["C1"].Text = "Products";
            worksheet["C2"].Text = "Alfreds Futterkiste";
            worksheet["C3"].Text = "Antonio Moreno Taqueria";
            worksheet["C4"].Text = "Around the Horn";
            worksheet["C5"].Text = "Bon app";
            worksheet["C6"].Text = "Eastern Connection";
            worksheet["C7"].Text = "Ernst Handel";
            worksheet["D1"].Text = "Qtr1";
            worksheet["D2"].Number = 744.6;
            worksheet["D3"].Number = 5079.6;
            worksheet["D4"].Number = 1267.5;
            worksheet["D5"].Number = 1418;
            worksheet["D6"].Number = 4728;
            worksheet["D7"].Number = 943.89;
            worksheet["E1"].Text = "Qtr2";
            worksheet["E2"].Number = 162.56;
            worksheet["E3"].Number = 1249.2;
            worksheet["E4"].Number = 1062.5;
            worksheet["E5"].Number = 756;
            worksheet["E6"].Number = 4547.92;
            worksheet["E7"].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["D2:E8"].CellStyleName = "CurrencyFormat";

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

            //Set the alternative text description
            table1.Summary = "Description";

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

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

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

TableStyleName

Gets and sets the table style name.

Declaration

string TableStyleName { get; set; }

Property Value

Type
System.String

TableType

Gets the type or source of the table.

Declaration

ExcelTableType TableType { get; }

Property Value

Type
ExcelTableType

Examples

The following code illustrates how the table type can be accessed.

        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"]);

            //Print table type to Console
            Console.WriteLine(table1.TableType.ToString());

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

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//worksheet

TotalsRowCount

Gets number of totals rows in the table. Read-only.

Declaration

int TotalsRowCount { get; }

Property Value

Type
System.Int32

Examples

The following code illustrates how to get the totals rows count.

        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;

            //Apply AutoFit
            worksheet["A1:D8"].AutofitColumns();

            //Check totals row count
            Console.WriteLine(table1.TotalsRowCount);

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//0

Worksheet

Gets the parent worksheet object. Read-only.

Declaration

IWorksheet Worksheet { get; }

Property Value

Type
IWorksheet

Examples

The following code illustrates how the parent worksheet can be accessed.

        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];

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

            //Get parent worksheet's name
            Console.WriteLine(table1.Worksheet.Name);

            //Save and Dispose
            workbook.SaveAs("Tables.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//Sheet1

Methods

add_OnRefreshConnection(RefreshConnectionEventHandler)

Declaration

void add_OnRefreshConnection(RefreshConnectionEventHandler value)

Parameters

Type	Name	Description
RefreshConnectionEventHandler	value

Refresh()

Refreshes the table data in the worksheet.

Declaration

void Refresh()

Remarks

This method works only on the Windows platform.

Examples

Following code illustrates how to refresh the table.

{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];
            //Database path
            string dataPath = Path.GetFullPath("../../../Data/access-sakila.mdb");
            //Connection string for DataSource
            string ConnectionStringOledb = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + dataPath;
            //Adding a connection to the workbook
            IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionStringOledb, "", ExcelCommandType.Sql);
            //Adding a QueryTable to sheet object
            worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
            //Command text for the connection
            worksheet.ListObjects[0].QueryTable.CommandText = "SELECT * FROM actor";
            //The query performs asynchronous action
            worksheet.ListObjects[0].QueryTable.BackgroundQuery = true;
            //The query table is refreshed when the workbook is opened
            worksheet.ListObjects[0].QueryTable.RefreshOnFileOpen = true;
            //Represents the connection description
            Connection.Description = "Sample Connection";
            //Bind the GetDataTable method to OnRefreshConnection.
            worksheet.ListObjects[0].OnRefreshConnection += GetOledbDataTable;
            //Import data to the sheet from the database
            worksheet.ListObjects[0].Refresh();
            FileStream stream = new FileStream("output.xlsx", FileMode.Create, FileAccess.Write);
            workbook.SaveAs(stream);
     }
}

void GetOleDBDataTable(object sender, RefreshConnectionEventArgs e)
{
   if (sender == null)
   {
       return;
   }
   OleDbConnection ole_connection = new OleDbConnection();
   ole_connection.ConnectionString = e.ConnectionString;
   OleDbCommand command = new OleDbCommand();
   command.Connection = ole_connection;
   command.CommandText = e.Query;
   DataTable table = new DataTable();
   OleDbDataAdapter Adapter = new OleDbDataAdapter(command);
   Adapter.Fill(table);
   ole_connection.Dispose();
   Adapter.Dispose();
   command.Dispose();
   e.Data = table;
}

remove_OnRefreshConnection(RefreshConnectionEventHandler)

Declaration

void remove_OnRefreshConnection(RefreshConnectionEventHandler value)

Parameters

Type	Name	Description
RefreshConnectionEventHandler	value

Events

OnRefreshConnection

An event that triggers when refreshing list object in the worksheet.

Declaration

event RefreshConnectionEventHandler OnRefreshConnection

Event Type

Type
RefreshConnectionEventHandler

Remarks

This event works only on the Windows platform.

Examples

Following code illustrates how to refresh the table.

{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];
            //Database path
            string dataPath = Path.GetFullPath("../../../Data/access-sakila.mdb");
            //Connection string for DataSource
            string ConnectionStringOledb = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + dataPath;
            //Adding a connection to the workbook
            IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionStringOledb, "", ExcelCommandType.Sql);
            //Adding a QueryTable to sheet object
            worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
            //Command text for the connection
            worksheet.ListObjects[0].QueryTable.CommandText = "SELECT * FROM actor";
            //The query performs asynchronous action
            worksheet.ListObjects[0].QueryTable.BackgroundQuery = true;
            //The query table is refreshed when the workbook is opened
            worksheet.ListObjects[0].QueryTable.RefreshOnFileOpen = true;
            //Represents the connection description
            Connection.Description = "Sample Connection";
            //Bind the GetDataTable method to OnRefreshConnection.
            worksheet.ListObjects[0].OnRefreshConnection += GetOledbDataTable;
            //Import data to the sheet from the database
            worksheet.ListObjects[0].Refresh();
            FileStream stream = new FileStream("output.xlsx", FileMode.Create, FileAccess.Write);
            workbook.SaveAs(stream);
     }
}

void GetOleDBDataTable(object sender, RefreshConnectionEventArgs e)
{
   if (sender == null)
   {
       return;
   }
   OleDbConnection ole_connection = new OleDbConnection();
   ole_connection.ConnectionString = e.ConnectionString;
   OleDbCommand command = new OleDbCommand();
   command.Connection = ole_connection;
   command.CommandText = e.Query;
   DataTable table = new DataTable();
   OleDbDataAdapter Adapter = new OleDbDataAdapter(command);
   Adapter.Fill(table);
   ole_connection.Dispose();
   Adapter.Dispose();
   command.Dispose();
   e.Data = table;
}