CalcEngine for .NET
Quick Start / Quick Start - Using ExcelEngine
In This Topic
    Quick Start - Using ExcelEngine
    In This Topic

    This quick start guides you through a process of creating an application that uses ExcelEngine, binds CalcEngine to data, assign expression to it and evaluate result.

    Complete the following steps to see how the result is calculated based on the assigned expression using CalcEngine.

    Create Data Source

    ExcelEngine is used to evaluate excel like expressions. Hence, the data must be in the excel sheet format. To generate this type of data, you must define a class that implements the IDataSheet interface and defines the GetValue function to access the values from the data source for expression evaluation. Therefore, here we create a class, say SheetTable, which inherits DataTable class and implements IDataSheet interface.

    C#
    Copy Code
    //Class implementing IDataSheet interface
    //used as the ExcelEngine datasource
    public class SheetTable : DataTable, IDataSheet
    {
    public string Name
    {
    get => TableName;
    set => TableName = value;
    }
    public object GetValue(int col, int row)
    {
    return Rows[row][col];
    }
    }
    

    Bind CalcEngine to Data Source

    1. Create a method, say GetDataTable, to generate data for CalcEngine. This method instantiates the class created in the last step to generate an in-memory excel like sheet and populate it with data.
      C#
      Copy Code
      public static SheetTable GetDataTable(string sheetName)
      {
      const string Abc = "ABCDEF";
      var table = new SheetTable();
      table.Name = sheetName;
      foreach (var c in Abc)
      table.Columns.Add(c.ToString(), typeof(int));
      for (int i = 0; i < 100; i++)
      table.Rows.Add(new object[] { i * 2, i * 3, i * 4, i * 5, i * 6, i * 7
      });
      return table;
      }
      
    2. Initialize an instance of the C1CalcEngine class using the following code.
      C#
      Copy Code
      //Initialize C1CalcEngine instance of type ExcelEngine
      C1CalcEngine _calcEngine = new C1CalcEngine(new ExcelEngine());
      
    3. Bind the CalcEngine to the data sheet using DataSource property of the C1CalcEngine class.
      C#
      Copy Code
      //Bind C1CalcEngine to datatable
      var sheet1 = GetDataTable("Sheet1");
      _calcEngine.DataSource = new List<IDataSheet> { sheet1 };
      

    Assign Expression and Evaluate Result

    Assign an Excel-like expression to the CalcEngine using the Expression property and invoke the TryEvaluate method to calculate the expression.

    C#
    Copy Code
    //Assign the expression to be calculated to C1CalcEngine
    _calcEngine.Expression = "=Sum(Sheet1!A3:B7)";
    //Invoke the TryEvaluate method of C1CalcEngine to calculate the expression
    var res = _calcEngine.TryEvaluate(out object result) ? result.ToString() :
    _calcEngine.GetErrors().FirstOrDefault()?.FullMessage ?? "";
    //Display the expression evaluation result
    Console.WriteLine("Result Total: " + res);