Data not being pulled in the sheet with excel formulae

Aspose.Cells Product Family
1

Hi Aspose Cells Team,

We recently upgraded Aspose Cells version from 19.3.2 to 19.4. We encountered an issue of pulling data in those excel tabs that are using excel formulae when opening that in Excel 13. This was fine with 19.3.2.

Scenario: We have an excel with two sheets. One for data to be populated from the application with the following code sample and other with excel formulae to pull the data from the first tab.

                     data.each { current ->
                                int column = initialColumn
                                current.each {
                                    Cell cell = cells.get(row, column)
                                    Style style = cell.getStyle();
                                    def fieldType = it[1]
                                    def value = it[0]
                                    if (fieldType == 'PERCENTAGE') {
                                        if (value && !(value instanceof String))
                                            value = value / 100
                                        style.setNumber(10);
                                    } else (fieldType == 'CURRENCY') {
                                        style.setNumber(7);
                                    } 
                                   cell.setStyle(style);
                                    if (value == null) {
                                        value = "- -"
                                    }
                                    cell.setValue(value)
                                    column++
                                }
                                row++
                          }

Excel version screenshot attached. Could you please replicate this from your end.
Capture (1).PNG (1.4 KB)

Thanks,
Harish

2

@HThagunna,
Please share your sample file(s) and complete running solution (compilable without errors) with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

Sample file is attached here. While opening this in Excel 13 the used formulae are not populating the data. But I can manually populate the data using Ctrl+Alt+F9. The case is only in Excel 13. excel_formula_populate_error_test-05-10-2019-1557479786228.zip (26.1 KB)

4

@HThagunna,
I am afraid to share that this information is not sufficient to reproduce the scenario. You told that application was working fine with 19.3.2 but latest version 19.4 has some issue. We need that running application (you may simplify it if it contains some complex logic) which we can use to observe the difference between the two versions. Also please share the details of the difference by providing some snapshots or images identifying the exact issue. We will run your application to observe the issue and provide our feedback. Without these details, it is quite difficult to provide assistance in a timely manner.

5

We could resolve this issue by adding workbook.calculateFormula() before saving it as XLSX.
Not sure, in lower versions, this might have been executed implicitly.

Actually, the upgrade was from 18.12 to 19.4.

6

@HThagunna,
Well, the default CalculationId of the workbook has been upgraded between those versions. If you need the formulas being calculated automatically when opening template file in MS Excel, just as the result of old versions, you may reset the CalculationId to lower version, or just enable the calculation option for opening. The relevant APIs:

WorkbookSettings.CalculationId
WorkbookSettings.ReCalculateOnOpen

Hope, this helps.

7

Thanks. We will try these options as well. Could you please share us the examples in java showing use of these.

Thanks,
Harish

8

@HThagunna,
We are working on your requirement and will share the feedback soon.

1 Like
9

@HThagunna,
The usage of these APIs is simple, just add one line of code like following before saving the workbook:

wb.Settings.ReCalculateOnOpen = true;

Or

wb.Settings.CalculationId = "0"; //the calculationId varies for different MS Excel version. Here we set a much smaller value to make it work for all Excel versions

Any one of them can make the saved workbook be calculated automatically by MS Excel when opening.