Excel API Library for Java - Sample Browser | Document Solutions | MyConditionalSumFunction
[
フレーム]
src="bundle.js">
This sample demonstrates how to implement a custom function to calculate sum of those cells which are filled in Red background color.
// Create a new workbook
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1:A10").setValue(new Object[][] {
{1 },
{2 },
{3 },
{4 },
{5 },
{6 },
{7 },
{8 },
{9 },
{10 }});
IFormatCondition cellValueRule = (IFormatCondition)worksheet.getRange("A1:A10").getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5, null);
cellValueRule.getInterior().setColor(Color.GetRed());
//Sum cells value which display format interior color are red.
worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");
//Range["C1"]'s value is 40.
Object result = worksheet.getRange("C1").getValue();
/* Implementation of MyAddFunctionX
class MyConditionalSumFunctionX extends CustomFunction {
public MyConditionalSumFunctionX() {
super("MyConditionalSum", FunctionValueType.Number, CreateParameters());
}
private static Parameter[] CreateParameters() {
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++) { parameters[i] = new Parameter(FunctionValueType.Object, true); } return parameters; } @Override public Object evaluate(Object[] arguments, ICalcContext context) { double sum = 0d; for (Object argument : arguments) { Iterable iterator = toIterable(argument);
for (Object item : iterator) {
if (item instanceof CalcError) {
return item;
} else if (item instanceof Double) {
sum += (double) item;
}
}
}
return sum;
}
private static Iterable toIterable(Object obj) {
if (obj instanceof Iterable) {
return (Iterable) obj;
} else if (obj instanceof Object[][]) {
List list = new ArrayList();
Object[][] array = (Object[][]) obj;
for (int i = 0; i < array.length; i++) { for (int j = 0; j < array[i].length; j++) { list.add(array[i][j]); } } return list; } else if (obj instanceof CalcReference) { List list = new ArrayList();
CalcReference reference = (CalcReference) obj;
for (IRange range : reference.getRanges()) {
int rowCount = range.getRows().getCount();
int colCount = range.getColumns().getCount();
for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { if (range.getCells().get(i, j).getDisplayFormat().getInterior().getColor().equals(Color.getRed())) { list.add(range.getCells().get(i, j).getValue()); } } } } return list; } else { List list = new ArrayList();
list.add(obj);
return list;
}
}
}
*/
// Create a new workbook
var workbook = Workbook()
Workbook.AddCustomFunction(MyConditionalSumFunctionX())
val worksheet = workbook.activeSheet
worksheet.getRange("A1:A10").value = arrayOf(arrayOf
(1), arrayOf(2), arrayOf(3), arrayOf(4), arrayOf(5), arrayOf(6), arrayOf(7), arrayOf(8), arrayOf(9), arrayOf(10))
val cellValueRule = worksheet.getRange("A1:A10").formatConditions.add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5, null) as IFormatCondition
cellValueRule.interior.color = Color.GetRed()
//Sum cells value which display format interior color are red.
worksheet.getRange("C1").formula = "=MyConditionalSum(A1:A10)"
//Range["C1"]'s value is 40.
val result = worksheet.getRange("C1").value
/* Implementation of MyAddFunctionX
class MyConditionalSumFunctionX extends CustomFunction {
public MyConditionalSumFunctionX() {
super("MyConditionalSum", FunctionValueType.Number, CreateParameters());
}
private static Parameter[] CreateParameters() {
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++) { parameters[i] = new Parameter(FunctionValueType.Object, true); } return parameters; } @Override public Object evaluate(Object[] arguments, ICalcContext context) { double sum = 0d; for (Object argument : arguments) { Iterable iterator = toIterable(argument);
for (Object item : iterator) {
if (item instanceof CalcError) {
return item;
} else if (item instanceof Double) {
sum += (double) item;
}
}
}
return sum;
}
private static Iterable toIterable(Object obj) {
if (obj instanceof Iterable) {
return (Iterable) obj;
} else if (obj instanceof Object[][]) {
List list = new ArrayList();
Object[][] array = (Object[][]) obj;
for (int i = 0; i < array.length; i++) { for (int j = 0; j < array[i].length; j++) { list.add(array[i][j]); } } return list; } else if (obj instanceof CalcReference) { List list = new ArrayList();
CalcReference reference = (CalcReference) obj;
for (IRange range : reference.getRanges()) {
int rowCount = range.getRows().getCount();
int colCount = range.getColumns().getCount();
for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { if (range.getCells().get(i, j).getDisplayFormat().getInterior().getColor().equals(Color.getRed())) { list.add(range.getCells().get(i, j).getValue()); } } } } return list; } else { List list = new ArrayList();
list.add(obj);
return list;
}
}
}
*/