Live SpreadsheetGear API Samples

Use ISheet.EvaluateValue(...) and EvaluateRange(...) to evaluate Excel-compatible formulas and expressions without ever entering them into cells on a worksheet.

// Create a workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;
object val;

// Populate worksheet cells with some values.
cells["A1"].Value = 1;
cells["A2"].Value = 2;
cells["A3"].Value = 3;
cells["A4"].Value = 4;
cells["A5"].Value = 5;

// Use EvaluateValue(...) to return the results of Excel-compatible expressions, such as the function SUM.
{
    val = worksheet.EvaluateValue("SUM(1,2,3)");
    Console.WriteLine(val); // OUTPUT: 6

    // Or reference cells on the sheet for which EvaluateValue(...) was called.
    val = worksheet.EvaluateValue("SUM(A1:A5)");
    Console.WriteLine(val); // OUTPUT: 15

    // Some Excel functions use the currently active cell to return certain results.
    cells["C1"].Select();
    val = worksheet.EvaluateValue("COLUMN()");
    Console.WriteLine(val); // OUTPUT: 3
    cells["E1"].Select();
    val = worksheet.EvaluateValue("COLUMN()");
    Console.WriteLine(val); // OUTPUT: 6
}

// Use EvaluateRange(...) to return an IRange from expressions that evaluate to ranges.
{
    IRange range = worksheet.EvaluateRange("A1");
    Console.WriteLine(range.Address); // $A$1
    range = worksheet.EvaluateRange("A1:D10");
    Console.WriteLine(range.Address); // $A$1:$D$10

    // References to other worksheets (or entirely different workbooks in the same workbook set) can also be included.
    workbook.Worksheets.Add(); // Sheet2
    range = worksheet.EvaluateRange("Sheet2!A1:A3");
    Console.WriteLine($"{range.Worksheet.Name}!{range.Address}"); // Sheet2!$A$1:$A$3

    // Get currently-active cell or variants thereof.
    worksheet.Cells["C1"].Select();
    range = worksheet.EvaluateRange("INDIRECT(ADDRESS(ROW(),COLUMN()))");
    Console.WriteLine(range.Address); // $C$1
    worksheet.Cells["A1"].Select();
    range = worksheet.EvaluateRange("INDIRECT(ADDRESS(ROW()+1,COLUMN()+1))");
    Console.WriteLine(range.Address); // $B$2
}