rmartinsanta opened a new issue, #887:
URL: https://github.com/apache/poi/issues/887
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)");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
