Interface IListObjectColumn

Represents a column in the table.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IListObjectColumn

Properties

CalculatedFormula

Gets or sets the calculated formula value.

Declaration

string CalculatedFormula { get; set; }

Property Value

Type
System.String

Examples

If we want to set a column to hold results of calculation using data from some other columns in a table then we can make use of CalculatedFormula property. Here for example, we set a formula to third column to hold the sum of first and second column's data.

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;
    worksheet["D1"].Text = "Sum";

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

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

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

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

    //Set formula
    table1.Columns[3].CalculatedFormula = "=sum([@" + table1.Columns[1].Name + "]+[@" + table1.Columns[2].Name + "])";

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

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

Id

Gets column id of current column. Read-only.

Declaration

int Id { get; }

Property Value

Type
System.Int32

Examples

The following code illustrates how the Id of a particular column can be accessed using the Id 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["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;

    //Print Column ID
    Console.WriteLine("Column ID " + table1.Columns[0].Id);

    //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();
    Console.ReadKey();
}

Index

Gets column index of current column. Read-only.

Declaration

int Index { get; }

Property Value

Type
System.Int32

Examples

The following code illustrates how the index of a particular column can be accessed using Index 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["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;

    //Print Column's Index
    Console.WriteLine("Column Index " + table1.Columns[0].Index);

    //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();
    Console.ReadKey();
}

Name

Gets or sets the name of the column.

Declaration

string Name { get; set; }

Property Value

Type
System.String

Examples

If table was created using data in worksheet then Column name will be set automatically if it is available within the range. We can get or set column name using Name property. Here for example, We create table using the data in the range "C1:E7" and print first column's name, id and index using Name, Id and Index respectively 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["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;

    //Print Column Name, ID, Index
    Console.WriteLine("Column Name " + table1.Columns[0].Name);
    Console.WriteLine("Column ID " + table1.Columns[0].Id);
    Console.WriteLine("Column Index " + table1.Columns[0].Index);

    //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();
    Console.ReadKey();
}

QueryTableFieldId

Gets query table field id associated with this column. Read-only.

Declaration

int QueryTableFieldId { get; }

Property Value

Type
System.Int32

Examples

If table is added using AddEx(ExcelListObjectSourceType, IConnection, IRange) method then query is used to fetch data for table. Here for example, We create table with data queried from MS Access DB file. The created table's field id can be accessed using QueryTableFieldId 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];

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

    //Get Query table field ID
    Console.WriteLine(table.Columns[1].QueryTableFieldId);

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

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

TotalsCalculation

Gets or sets the function used for totals calculation.

Declaration

ExcelTotalsCalculation TotalsCalculation { get; set; }

Property Value

Type
ExcelTotalsCalculation

Examples

TotalsCalculation is used to set formula in the last row for the column specified. Here for example, We set ShowTotals to "True" to enable totals and set Sum for second and third columns to get sum of values in the respective columns.

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

TotalsRowLabel

Gets or sets the label for the totals row.

Declaration

string TotalsRowLabel { get; set; }

Property Value

Type
System.String

Examples

TotalsRowLabel property is used to set text in the last row of the column specfied. This will be displayed only if the ShowTotals property is set to "true". Here for example, We set a string "Total" to the first column's TotalsRowLabel property to indicate that it's adjacent cell holds sum of the values of it's 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["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();
}