Interface IListObject
Represents a table on a worksheet.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.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;
}