https://bz.apache.org/bugzilla/show_bug.cgi?id=69791
Bug ID: 69791
Summary: MINIFS fails to evaluate in Excel
Product: POI
Version: 5.4.2-FINAL
Hardware: PC
OS: Mac OS X 10.1
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
(This is a duplicate of https://github.com/apache/poi/issues/887, in case
issues are only tracked here)
Excel does not seem to be able to evaluate cells with `MINIFS` when generated
by Apache POI.
## Environment
```
Apache POI 5.4.1
Microsoft® Excel® for Microsoft 365 MSO (Version 2507 Build 16.0.19029.20184)
64-bit
Java HotSpot(TM) 64-Bit Server VM Oracle GraalVM 24.0.1+9.1 (build
24.0.1+9-jvmci-b01, mixed mode, sharing)
```
## Steps to reproduce
```java
public static void main(String[] args) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Sheet1");
// Fill sample data: Column A contains numbers, Column B contains
booleans
Object[][] sampleData = {
{5, true},
{7, false},
{3, true},
{10, false},
{2, false}
};
for (int i = 0; i < sampleData.length; i++) {
XSSFRow row = sheet.createRow(i);
row.createCell(0).setCellValue((Integer)sampleData[i][0]); //
Column A
row.createCell(1).setCellValue((Boolean)sampleData[i][1]); //
Column B
}
// Write MINIFS formula to, e.g., C1
XSSFRow formulaRow = sheet.getRow(0);
XSSFCell formulaCell = formulaRow.createCell(2);
formulaCell.setCellFormula("MINIFS(A1:A5,B1:B5,TRUE)");
workbook.setForceFormulaRecalculation(true);
// Write to file
try (FileOutputStream out = new
FileOutputStream("minifs2-example.xlsx")) {
workbook.write(out);
}
workbook.close();
}
```
## Expected behavior
When the Excel file is opened and the workbook is recalculated, the value 3
should be shown in the formula cell.
## Actual behavior
Excel shows the following error:
<img width="447" height="326" alt="Image"
src="https://github.com/user-attachments/assets/d4b66949-9336-4d48-bf76-8f580a55df2d";
/>
Removing the `@` manually from the formula makes it start working. Trying to
edit the formula without removing the `@` (not sure what is the meaning of `@`
in this context), triggers the following warning:
<img width="364" height="265" alt="Image"
src="https://github.com/user-attachments/assets/c1222348-ec97-4926-8525-177de371d36e";
/>
If the variation is accepted, the formula starts working too.
A comparison before and after opening the file in Excel shows the following
diff in the worksheet XML file:
```xml
<v>1</v>
</c>
- <c r="C1" s="0">
- <f>MINIFS(A1:A5,B1:B5,TRUE)</f>
+ <c r="C1">
+ <f>_xlfn.MINIFS(A1:A5,B1:B5,TRUE)</f>
+ <v>3</v>
</c>
</row>
```
Note the prefix `_xlfn` that Excel added to the formula.
## Workaround
Using `_xlfn.MINIFS` instead of `MINIFS` seems to work, not sure why. Example:
```java
formulaCell.setCellFormula("_xlfn.MINIFS(A1:A5,B1:B5,TRUE)");
```
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
