Live SpreadsheetGear API Samples

Creates Array Formulas on both a single cell and range of cells.

// Open workbook with sample data and create some local variables.
SpreadsheetGear.IWorkbook workbook = 
    SpreadsheetGear.Factory.GetWorkbook(@"Files/ArrayFormulaData.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Use IRange.ArrayFormula to enter an array formula, in this case on the range
// C2:C6, which provides a total (cost * quantity) for each line item.
cells["C2:C6"].FormulaArray = "=A2:A6*B2:B6";

// Enter a single-cell array formula to sum the total of all line items.
cells["C8"].FormulaArray = "=SUM($A$2:$A$6*$B$2:$B$6)";

// Get a reference to a single cell within the larger array formula in C2:C6.
SpreadsheetGear.IRange c3 = cells["C3"];

// Use IRange.CurrentArray to expand C3 to include the whole array formula.
SpreadsheetGear.IRange c2c6 = c3.CurrentArray;

// Slightly darken this range to make it stand out against adjacent cells.
c2c6.Interior.TintAndShade -= 0.2;