Documents for Excel .NET Edition | GCDocuments
Features / Formulas / Set Formula to Range
In This Topic
    Set Formula to Range
    In This Topic

    You can set formula to a cell range using the Formula property of the IRange interface.

    Refer to the following example code to add custom names and set formula to a range in a worksheet.

    C#
    Copy Code
    // Add custom name and set formula to range
    worksheet.Names.Add("test1", "=Sheet1!$A$1");
    worksheet.Names.Add("test2", "=Sheet1!test1*2");
    
    worksheet.Range["A1"].Value = 1;
    //C6's value is 1.
    worksheet.Range["C6"].Formula = "=test1";
    //C7's value is 3.
    worksheet.Range["C7"].Formula = "=test1 + test2";
    //C8's value is 6.283185307
    worksheet.Range["C8"].Formula = "=test2*PI()";

    Note: The value calculated by the formula is stored in a cache. Users can verify the cached value by invoking the Dirty method of the IRange interface. This method clears the cached value of the specified range and all the ranges dependent on it, or the entire workbook.

    Reference style

    GcExcel .NET supports the RIC1 reference style to allow users to perform calculations in a much easier and quicker way. To set reference style, you can use the Reference Style property of the IWorkbook interface.

    Refer to the following example code to see how reference style can be set in a workbook.

    C#
    Copy Code
    //set workbook's reference style to R1C1.
    workbook.ReferenceStyle = ReferenceStyle.R1C1;