menu

Xamarin.Forms

  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IListObjectColumn - Xamarin.Forms API Reference | Syncfusion

    Show / Hide Table of Contents

    Interface IListObjectColumn

    Represents a column in the table.

    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.Portable.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();
    }
    Back to top Generated by DocFX
    Copyright © 2001 - 2025 Syncfusion Inc. All Rights Reserved