import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
import org.junit.jupiter.api.Test;

import static org.assertj.core.api.Assertions.assertThat;

// https://bz.apache.org/bugzilla/show_bug.cgi?id=65059
class SumproductSumifsWrongEvalutationTest {

    @Test
    void formulaEvaluate_should_provide_correct_result_on_array_formula() {

        // given
        XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        String sheetName = "Sheet1";
        Sheet sheet = workbook.createSheet(sheetName);


        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        Row row3 = sheet.createRow(2);


        Cell cellB1 = row1.createCell(1);
        Cell cellB2 = row2.createCell(1);
        Cell cellB3 = row3.createCell(1);

        Cell cellC1 = row1.createCell(2);
        Cell cellC2 = row2.createCell(2);
        Cell cellC3 = row3.createCell(2);

        Cell cellD1 = row1.createCell(3);
        Cell cellD2 = row2.createCell(3);
        Cell cellD3 = row3.createCell(3);

        Cell cellA1 = row1.createCell(0);


        int val1 = 1;
        int val2 = 2;
        int val3 = 3;
        int expectedCriteria = 1;
        int actualCriteria = 1;

        cellB1.setCellValue(val1);
        cellB2.setCellValue(val2);
        cellB3.setCellValue(val3);

        cellC1.setCellValue(expectedCriteria);
        cellC2.setCellValue(expectedCriteria);
        cellC3.setCellValue(expectedCriteria);

        cellD1.setCellValue(actualCriteria);
        cellD2.setCellValue(actualCriteria);
        cellD3.setCellValue(actualCriteria);


//        cellA1.setCellFormula("SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D3))");
        sheet.setArrayFormula("SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D3))", new CellRangeAddress(0, 0, 0, 0));

        // when
        CellValue cellValue = formulaEvaluator.evaluate(cellA1);

        // then
        assertThat(cellValue.getNumberValue()).isEqualTo(18); // expected: 3 * (val1 + val2 + val3)
    }

}