Hi Poi Team,
I have come up with a proposal for an implementation for this function. Please
see the attached function plus its testcase.
It works pretty nicely I would say.
Maybe you can integrate it to one of your next releases ?
Thanks,
detlef
----- Ursprüngliche Mail -----
Von: "Detlef Brendle" <[email protected]>
An: [email protected]
Gesendet: Samstag, 7. Dezember 2013 15:37:59
Betreff: Excel Function COUNTIFS available in poi ?
Hi,
is the function countifs
(http://office.microsoft.com/en-us/excel-help/countifs-function-HA010047494.aspx)
available in the latest poi (3.10-beta2)
Doesn't look like to me.
Has anybody implemented this function itself ?
Thanks,
detlef
package org.pillarone.riskanalytics.application.ui.main.action.exportimport;
import org.apache.poi.ss.formula.atp.AnalysisToolPak;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Before;
import org.junit.Test;
public class CountifsTests {
@Before
public void setup() {
if (!AnalysisToolPak.getSupportedFunctionNames().contains("COUNTIFS")) {
AnalysisToolPak.registerFunction("COUNTIFS", new Countifs());
}
}
@Test
public void callFunction() {
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("test");
Row row1 = sheet.createRow(0);
Cell cellA1 = row1.createCell(0, Cell.CELL_TYPE_FORMULA);
Cell cellB1 = row1.createCell(1, Cell.CELL_TYPE_NUMERIC);
Cell cellC1 = row1.createCell(2, Cell.CELL_TYPE_NUMERIC);
Cell cellD1 = row1.createCell(3, Cell.CELL_TYPE_NUMERIC);
cellB1.setCellValue(1);
cellC1.setCellValue(1);
cellD1.setCellValue(2);
cellA1.setCellFormula("COUNTIFS(B1:C1,1, D1:D1,2)");
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue evaluate = evaluator.evaluate(cellA1);
assert 1 == evaluate.getNumberValue();
}
@Test
public void callFunction_invalidArgs() {
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("test");
Row row1 = sheet.createRow(0);
Cell cellA1 = row1.createCell(0, Cell.CELL_TYPE_FORMULA);
cellA1.setCellFormula("COUNTIFS()");
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue evaluate = evaluator.evaluate(cellA1);
assert 15 == evaluate.getErrorValue();
cellA1.setCellFormula("COUNTIFS(A1:C1)");
evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluate = evaluator.evaluate(cellA1);
assert 15 == evaluate.getErrorValue();
cellA1.setCellFormula("COUNTIFS(A1:C1,2,2)");
evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluate = evaluator.evaluate(cellA1);
assert 15 == evaluate.getErrorValue();
}
}
package org.pillarone.riskanalytics.application.ui.main.action.exportimport;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.Countif;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
public class Countifs implements FreeRefFunction {
@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
Double result = null;
if (args.length == 0 || args.length % 2 > 0) {
return ErrorEval.VALUE_INVALID;
}
for (int i = 0; i < args.length; ) {
ValueEval firstArg = args[i];
ValueEval secondArg = args[i + 1];
i += 2;
NumberEval evaluate = (NumberEval) new Countif().evaluate(new ValueEval[]{firstArg, secondArg}, ec.getRowIndex(), ec.getColumnIndex());
if (result == null) {
result = evaluate.getNumberValue();
} else if (evaluate.getNumberValue() < result) {
result = evaluate.getNumberValue();
}
}
return new NumberEval(result == null ? 0 : result);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
