Interface IListObject
Represents a table on a worksheet.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Base.dll
Syntax
public interface IListObjectRemarks
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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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
//Table1Index
Gets index of the table in a worksheet. Read-only.
Declaration
int Index { get; }Property Value
| Type | Description |
|---|---|
| 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
//2Location
Gets or sets the location of the table in a worksheet.
Declaration
IRange Location { get; set; }Property Value
| Type | Description |
|---|---|
| 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:C7Name
Gets or sets the name of the table.
Declaration
string Name { get; set; }Property Value
| Type | Description |
|---|---|
| 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
//Table1QueryTable
Gets the query table object bound with the table.
Declaration
QueryTableImpl QueryTable { get; }Property Value
| Type | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| 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 | Description |
|---|---|
| System.String |
TableType
Gets the type or source of the table.
Declaration
ExcelTableType TableType { get; }Property Value
| Type | Description |
|---|---|
| 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
//worksheetTotalsRowCount
Gets number of totals rows in the table. Read-only.
Declaration
int TotalsRowCount { get; }Property Value
| Type | Description |
|---|---|
| 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
//0Worksheet
Gets the parent worksheet object. Read-only.
Declaration
IWorksheet Worksheet { get; }Property Value
| Type | Description |
|---|---|
| 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
//Sheet1Methods
Refresh()
Updates the table's data range which is similar to refreshing table in Excel.
Declaration
void Refresh()Remarks
This method is only supported in Windows Forms, Windows Presentation Foundation, Web Forms and MVC platforms.
Examples
Following code illustrates how to refresh 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];
//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();
}