Set Formula to Range | Document Solutions for Excel, Java Edition

Set Formula to Range

In This Topic

In DsExcel 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. For more information on how to add custom names, see Defined Names.

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

DsExcel 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);

Defer the Update of Dirty State for Formula Cells

The value calculated by a formula is stored in cache first and the cached result is returned upon retrieving the cell value. When a worksheet contains huge amount of data which depends on the result of formulas and the value of a cell is changed, all the formula cells are recalculated and the cached values are stored again which could degrade the performance of worksheet.

Hence, DsExcel provides setDeferUpdateDirtyState method in Workbook class, which when set to true does not update the dirty state of formula cells immediately when the value of a cell is changed.

Refer to the following example code to defer the update of dirty state for formula cells.

Java	Copy Code
Workbook wb = new Workbook(); wb.open("formulas.xlsx"); //Defer the update of dirty cell state wb.setDeferUpdateDirtyState(true); for (int i = 0; i < 1000; i++) { wb.getWorksheets().get(0).getRange(i, 0).setValue(i); } //Resume the update of dirty cell state wb.setDeferUpdateDirtyState(false);

Java

Copy Code

Workbook wb = new Workbook();
wb.open("formulas.xlsx");
//Defer the update of dirty cell state
wb.setDeferUpdateDirtyState(true);
for (int i = 0; i < 1000; i++)
{
    wb.getWorksheets().get(0).getRange(i, 0).setValue(i);
}
//Resume the update of dirty cell state
wb.setDeferUpdateDirtyState(false);

Limitation

When Workbook.DeferUpdateDirtyState is set to True, DsExcel does not update the dirty state of formula cells immediately. At this point the referred ranges for other features (such as chart etc.) won't be dirty, so their caches would not be updated. If you retrieve the state of such features, they may not be correct at that particular point of time.