import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
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;

class SumproductSumifsWrongEvalutationTest {

    @Test
    void testFormulaEvaluateAll() {
        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);


        cellB1.setCellValue(1);
        cellB2.setCellValue(2);
        cellB3.setCellValue(3);

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

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

        cellA1.setCellFormula("SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D3))");
        formulaEvaluator.evaluateAll();
        assertThat(cellA1.getNumericCellValue()).isEqualTo(18);
    }

}