//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];
    }
}

class Program
{
    //static C1CalcEngine _calcEngine;
    private const string Abc = "ABCDEF";

    //Method to generate data for C1CalcEngine
    public static SheetTable GetDataTable(string sheetName)
    {
        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;
    }
    
    static void Main(string[] args)
    {
        Console.WriteLine("Executing cross sheet reference sample for Excel Engine: \n");

        //Initialize C1CalcEngine instance of type ExcelEngine
        C1CalcEngine _calcEngine = new C1CalcEngine(new ExcelEngine());

        //Bind C1CalcEngine to datasource having multiple sheets
        var sheet1 = GetDataTable("Sheet1");
        var sheet2 = GetDataTable("Sheet2");
        _calcEngine.DataSource = new List<IDataSheet> { sheet1, sheet2 };

        //Assign the cross sheet reference expression to be calculated by C1CalcEngine
        _calcEngine.Expression = "=Sum(Sheet1!A3:B7) + Sum(Sheet2!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);
    }
}