Set Formula to Range | Documents for Excel, Java Edition Documentation

Set Formula to Range

In This Topic

In GcExcel Java, users can set formula to a cell range using the setFormula method of the IRange interface.

In order to add custom names and set formula to a range in a worksheet, refer to the following example code.

Java	Copy Code
// Add custom name and set formula to range worksheet.getNames().add("test1", "=Sheet1!$A$1"); worksheet.getNames().add("test2", "=Sheet1!test12"); worksheet.getRange("A1").setValue(1); // C6's value is 1. worksheet.getRange("C6").setFormula("=test1"); // C7's value is 3. worksheet.getRange("C7").setFormula("=test1 + test2"); // C8's value is 6.283185307 worksheet.getRange("C8").setFormula("=test2PI()");

Java

Copy Code

// Add custom name and set formula to range 
worksheet.getNames().add("test1", "=Sheet1!$A$1");
worksheet.getNames().add("test2", "=Sheet1!test1*2");
worksheet.getRange("A1").setValue(1);

// C6's value is 1.
worksheet.getRange("C6").setFormula("=test1");
        
// C7's value is 3.
worksheet.getRange("C7").setFormula("=test1 + test2");
        
// C8's value is 6.283185307
worksheet.getRange("C8").setFormula("=test2*PI()");

Note: Formula values are stored in a cache. Users can verify the cached value by invoking the Dirty method of the IRange interface. This method eliminates the cached value of the specified range and all the ranges dependent on it, or the entire workbook.

Reference style

GcExcel Java supports the RIC1 reference style in order to enable users to execute calculations easily and quickly. To set reference style, you can use the setReferenceStyle method of the IWorkbook interface.

In order to see how reference style can be set in a workbook, refer to the following example code.

Java	Copy Code
// set workbook's reference style to R1C1. workbook.setReferenceStyle(ReferenceStyle.R1C1);

Product Support Forum | Print this page