Returning a Range of Values using ICustomFunction

The ICustomFunction is deprecated since the release of Aspose.Cells for Java 20.8. Please use the AbstractCalculationEngine class. The use of the AbstractCalculationEngine class is described in the following article.

Returning a Range of Values using AbstractCalculationEngine.

Aspose.Cells provides ICustomFunction interface which is used to implement user-defined or custom functions that are not supported by Microsoft Excel as built-in functions.

Mostly when you implement the ICustomFunction interface method, you need to return a single cell value. But sometimes, you need to return a range of values. This article will explain how to return the range of values from ICustomFunction.

Returning a Range of Values using ICustomFunction

The following code implements ICustomFunction and returns the range of values via its method. Please check the output excel file and pdf generated with the code for your reference.

Create a class with a function CalculateCustomFunction. This class implements ICustomFunction.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
import java.util.ArrayList;

import com.aspose.cells.AbstractCalculationEngine;
import com.aspose.cells.CalculationData;
import com.aspose.cells.DateTime;

public class CustomFunctionStaticValue extends AbstractCalculationEngine {
	@Override
	public void calculate(CalculationData calculationData) {
		calculationData.setCalculatedValue(new Object[][] { new Object[] { new DateTime(2015, 6, 12, 10, 6, 30), 2 },
				new Object[] { 3.0, "Test" } });
	}
}

Now use the above function into your program.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(ReturningRangeOfValues.class);
Workbook wb = new Workbook();
Cells cells = wb.getWorksheets().get(0).getCells();

Cell cell = cells.get(0, 0);
cell.setArrayFormula("=MYFUNC()", 2, 2);

Style style = cell.getStyle();
style.setNumber(14);
cell.setStyle(style);

CalculationOptions copt = new CalculationOptions();
copt.setCustomFunction(new CustomFunctionStaticValue());
wb.calculateFormula(copt);

// Save to xlsx by setting the calc mode to manual
wb.getSettings().getFormulaSettings().setCalculationMode(CalcModeType.MANUAL);
wb.save(dataDir + "output.xlsx");

// Save to pdf
wb.save(dataDir + "output.pdf");