Set Formula to Range

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!test12"); 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 = "=test2PI()";

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

Spread.Services 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;