C# Create Formula SpreadsheetGear API Sample

Excel-Compatible Samples for .NET

Description

SpreadsheetGear has robust support for creating and calculating Excel-compatible formulas (see a list of supported Excel functions on the Features Page). To create a cell formula, set the IRange.Formula property to an Excel-compatible formula string, such as "=RAND()" as demonstrated below.

If an invalid formula is provided, IRange.Formula will throw a System.ArgumentException with the message "Invalid formula."

The IRange.Value property can also be used to set a cell formula but will not throw an exception if the provided formula string is invalid. Instead, the inputted formula is ignored.

Set Formula for Multiple Cells at the Same Time

Use a multi-cell range (e.g., cells["B3:B10"].Formula = "..."; to set all formulas in the specified range.

Strategically use absolute references (e.g., using "$" such as "$A$3") and relative references (e.g., omitting "$" such as "$A3" for the row reference) in your formula to control how references in each formula is filled down rows or across columns in the specified range. For instance, the below example demonstrates specifying the formula =SUM($A$3:$A3) for the range B3:B10. As this formula is populated into the range, the summed value for each cell will be a "running total" that sums from the first cell (using the absolute reference $A$3 in =SUM($A$3:$A3)) to the "current row" (using the relative row reference $A3 in =SUM($A$3:$A3).

Sample C# Source Code

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

// Set A1 to use the formula =RAND().
cells["A1"].Formula = "=RAND()";

// Populate multiple cells with the same formula
cells["A3:A10"].Formula = "=RAND()";

// Populate multiple cells with a series of formulas that automatically adjust relative 
// references.  In this case, we keep a "running total" of corresponding values in A3:A10 
// as we go down the column.  For instance, cell B3 will contain the formula "=SUM($A$3:A3)" 
// so sum only A3; cell B4 will contain the formula "=SUM($A$3:A4)" so sum A3 and A4, and so 
// on.
cells["B3:B10"].Formula = "=SUM($A$3:$A3)";