Bug 66565 - NumCache - Do not load last data version on chart
Summary: NumCache - Do not load last data version on chart
Status: RESOLVED INFORMATIONPROVIDED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC Mac OS X 10.1
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-04-17 15:49 UTC by Damien Cuvillier
Modified: 2023-04-18 09:02 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Damien Cuvillier 2023-04-17 15:49:02 UTC
I use [Apache POI](https://poi.apache.org/) 4.1.2 to load chart data from **XLSx** file, parse it, and transform them to **ChartJS JSON** format with [chartjs4java](https://github.com/GotanDev/chartjs4java). The goal is to display excel data in a web app. 

My issue concerns data loading from apache POI (with extension `poi-ooxml-schema`).  
If data have been upgraded by a machine (without opening Excel), it does not upgrade chart data with new values.   

It seems to be located about [NumCache](https://developer.adobe.com/experience-manager/reference-materials/cloud-service/javadoc/org/openxmlformats/schemas/drawingml/x2006/chart/CTNumRef.html#getNumCache()) feature on lib.  
But I do not find how to load data without this kind of cache. 

### Steps to reproduce 
1. Create a simple xlsx file [example](https://tinyurl.com/24o69nqm).  

2. Opening and upgrading this file **without Excel** with external tool.  
> For instance, [here](https://gist.github.com/damiencuvillier/c5e440704f9ec5af1737b7959d412ec1) is an example 
3. Trying to get chart data from Apache POI in a separate code.  
Here is a code fragment where I load & browse into my chart data.
```
public LineDataset getDataset(CTLineSer ctLineSer, CTPlotArea plot,  List<CTUnsignedInt> axIdList) {
    LineDataset dataset = new LineDataset();
    int count = 0;

    dataset = this.setLabelToDataset(dataset, ctLineSer);

    CTNumData ctStrVals = ctLineSer.getVal().getNumRef().getNumCache();
    for (int i = 0; i < Integer.parseInt(String.valueOf(ctStrVals.getPtCount().getVal())); i++) {
        // My code here to parse data
    }
}
    
```

4. At the end, I put loaded data in a ChartJS format to display data.  

5. It still displays the old data.  
The right data is loaded only if I open & close (with saving but without doing anything else) the file with Excel.  


### Tries

I've done following tries, without any successful result

* Tried to force data calculation with [`setForceFormulaRecalculation`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Workbook.html#setForceFormulaRecalculation-boolean-) method  
```workbook.setForceFormulaRecalculation(true);```

* I've been looking for an other way to load data with [CTPlotArea]() class in Apache POI doc without any result
Comment 1 PJ Fanning 2023-04-17 18:39:39 UTC
* POI 4 is no longer supported
* the code in poi-ooxml-lite (the replacement for poi-ooxml-schemas in POI 5) is generated and I don't think we should hack the code in XMLBeans (the tool that generates this code) to hack around this
* noone has ever promised that POI will re-read a file that is modified after the initial read - you will simply have to re-read the file after it is modified using  `new XSSFWorkbook` or `WorkbookFactory.create`.
Comment 2 PJ Fanning 2023-04-18 09:02:29 UTC
Closing this - https://stackoverflow.com/questions/76036865/apache-poi-ctplotarea-num-cache-do-not-load-last-data-version-on-chart is a better place to discuss this