Excel runs Apache-POI generated XLSX file but complains about working formula

Question

The Problem

When opening my generated .xlxs in excel desktop I get this message:

Excel found a problem with one ore more formulas in this worksheet. Check the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.

If i klick okay and say I want to edit the document anyway everything works just fine. Every single formula evaluates just like it should. There are only 10 or so, so not hard to check.

Opening the file in excel for the web works 100% without this issue.

My implementation

I have an android application that generates this .xlsx document using apache.poi.

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

I use XXSFWorkbook

val workbook: Workbook = XSSFWorkbook()

I put in some values relevant for the user and some formulas building on those values and so far everything is fine.

I include two piecharts But I want to use this formula as part of the PieFormulas because as the user edits the xlsx file the number of slices should be dynamic. This seams to cause the problem.

createCell(5).apply {
    cellFormula = "SUMPRODUCT(--(LEN(Data!\$H\$12:\$H\$16)>0))"
}

I use named ranges in the pie charts. F1 refers to the cell created above, that formula used to also be a named range but I decided to try a normal cell as well.

categoriesNameRange.refersToFormula = "OFFSET(Data!\$H\$$rowStart,0,0,Data!\$F\$1,1)"
categoriesCountRange.refersToFormula = "OFFSET(Data!\$I\$$rowStart,0,0,Data!\$F\$1,1)"

One of the piechart codes

val drawing = this.createDrawingPatriarch()
val anchor = drawing.createAnchor(0, 0, 0, 0, chartStartAnchor, chartStartRowAnchor, chartMiddleColAnchor, chartEndRowAnchor)
val chart = drawing.createChart(anchor)
chart.setTitleText(context.getString(R.string.xlxsCount))
chart.titleOverlay = false
val legend = chart.orAddLegend
legend.position = LegendPosition.TOP_RIGHT

val columnStart = if(withCycleTime) 8 else 7
val cat: XDDFDataSource<String> = XDDFDataSourcesFactory.fromStringCellRange(
   this,
   CellRangeAddress(0, 0, 0, 0)
)
val `val` = XDDFDataSourcesFactory.fromNumericCellRange(
    this,
    CellRangeAddress(0, 1, 1, 1)
)
val data = chart.createData(ChartTypes.PIE, null, null)
data.setVaryColors(true)
data.addSeries(cat, `val`)
chart.plot(data)
//Modify the category and data ranges to use named ranges
chart.ctChart.apply {
    plotArea.getPieChartArray(0).getSerArray(0).cat.strRef.f = "Data!CategoryNames"
    plotArea.getPieChartArray(0).getSerArray(0).cat.strRef.strCache.ptCount.`val` = 1
    plotArea.getPieChartArray(0).getSerArray(0).cat.strRef.strCache.ptArray[0].v = "_"

    plotArea.getPieChartArray(0).getSerArray(0).`val`.numRef.f = "Data!CategoryCounts"
            
    plotArea.getPieChartArray(0).getSerArray(0).`val`.numRef.numCache.addNewPtCount().`val` = 1
    val pt2 = plotArea.getPieChartArray(0).getSerArray(0).`val`.numRef.numCache.addNewPt()
    pt2.idx = 0
    pt2.v = "$rows"
}

What I have tried

So If I remove the problem formula above and replace it with a constant then I do not get the error. This led me to investigate it. Extracting the relevant sheet1.xml I found that the ">" sign was not changed to >. (Saving the document in excel and extracting sheet1.xml replaces the ">" with ">") Doing that manually and zipping back to xlsx also fixed the problem. I tried several equivalent formulas but they all contained "<", ">" or both and had the same problem.

So I turned to java.util.zip, and programmatically unzip, edit the offending ">" to ">" and zip back.

However the error persisted. Only now simply unziping and ziping back with default settings on windows removes the error message. Using Beyond compare I can see several differences between the generated .xlsx and the unziped and ziped .xlsx. But using the same tool I find no differences between the extracted files of these two .xlsx.

Does anyone have tips on where to go from here? Thank you