Cross Sheet Reference

When the information is spread across several sheets, it becomes difficult to work with such data together. This is where cross-sheet referencing can be helpful. In cross-sheet referencing, a cell reference includes a reference to a cell in another sheet. It can be used in a formula wherein the name of the target worksheet is entered followed by an exclamation (!) before the cell reference or cell range reference.

The following code implements cross-sheet referencing.

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

Cross Sheet Reference

See Also