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.

//ExcelEngine データソースとして使用される
//IDataSheet インターフェイスを実装するクラス。
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#	コードのコピー
   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#	コードのコピー
   //ExcelEngine 型の C1CalcEngine インスタンスを初期化します。 C1CalcEngine _calcEngine = new C1CalcEngine(new ExcelEngine());

3. Bind the CalcEngine to the data sheet using DataSource property of the C1CalcEngine class.
   

   C#	コードのコピー
   //データテーブルにC1CalcEngine を連結します。 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.

//計算する式を C1CalcEngine に代入します。
_calcEngine.Expression = "=Sum(Sheet1!A3:B7)";
//C1CalcEngine の TryEvaluate メソッドを呼び出して式を計算します。
var res = _calcEngine.TryEvaluate(out object result) ? result.ToString() :
_calcEngine.GetErrors().FirstOrDefault()?.FullMessage ?? "";
//式の評価結果を表示します。
Console.WriteLine("Result Total: " + res);