We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Formula cells not recalculating after report generation

Aspose.Cells Product Family
#1

Aspose version: Aspose.Cells 20.3
Excel Version: Microsoft Office 2016 (16.0.4939.1000) 32 bit

Code snippet

import com.aspose.cells.*;

public class WorkbookFormulasBug {

public static void main(String[] args) throws Exception {
    License license = new License();
    license.setLicense("aspose.lic");

    Workbook workbookTemplate = new Workbook("template.xltm");
    Workbook workbookPivotSource = new Workbook("pivotsource.xlsm");

    // Copy Pivot data
    for (int i = 0; i < workbookPivotSource.getWorksheets().getCount(); i++) {
        Worksheet pivotWorksheet = workbookPivotSource.getWorksheets().get(i);
        Worksheet templateWorksheet = workbookTemplate.getWorksheets().get(pivotWorksheet.getName());
        templateWorksheet.copy(pivotWorksheet);
    }

    workbookTemplate.save("generated_report.xlsm");
}

}

Code snippet source files

Steps to reproduce bug:

  1. Generate report using code snippet. You can see that cells with formulas has #N/A value, but should have calculated value.
Screenshot with description

wrong.png?dl=13429×2160

Normal behaviour (after generation using old Aspose.Cells 17.10):

  1. Generate report using code snippet. You can see that cells with formulas has calculated values by default.
Screenshot with description

good.png?dl=13430×2160

Expected output file: generated_report.xlsm

We catch this bug after upgrade from 17.10 to 20.3.
P.S. Our client (which use this report) is not considering to update Excel.

#2

@VasiliyKupchinskiy,

Thanks for the sample files.

Please try using our latest version/fix and see if it makes any difference.
[.NET]
Aspose.Cells20.4.6 For .NetStandard20.Zip (5.3 MB)
Aspose.Cells20.4.6 For .Net2_AuthenticodeSigned.Zip (5.3 MB)

[Java]
aspose-cells-20.4.5-java.zip (7.1 MB)

While we evaluate your issue based on your provided files which might take some time, also, it would be a bit hard to identify the issue using your generated files (one with older version and other with newer version) only. We appreciate if you could separate the issue and create two simple standalone (runnable) applications/programs (one with older version and other with newer version) to reproduce the issue on our end. Also, attach some screenshots to highlight the problematic areas in the output files. This will help us really to evaluate your issue precisely to consequently figure it out soon.

#3

Hi, I updated first post, please read. Also I tried your attached Java version with no success - see screenshot.

Screenshot with description

wrong.png?dl=13429×2160

#4

@VasiliyKupchinskiy,

Thanks for the sample code, template files, screenshots and details.

Could you also share your expected output file for reference. This will help us to evaluate your issue properly.

By the way, you may try to use the following line of code instead to merge two workbooks and check if you still find the issue:
e.g
Sample code:

workbookTemplate.combine(workbookPivotSource);

#5

workbookTemplate.combine(workbookPivotSource);

Combine is duplicating worksheets - not work for this case.

#6

Expected output file: generated_report.xlsm

#7

@VasiliyKupchinskiy,

Thanks for the expected output file.

Please notice we were able to reproduce the problem you mentioned using your original code and template files. We found some formula cells are not recalculated in the output file. When we manually edit those cells and press enter, it works.

For your issue, we have recorded a ticket with the ID “CELLSJAVA-43195”. We will investigate as soon as possible.

Once we have an update, we will notify you.

1 Like
#8

@VasiliyKupchinskiy,

We evaluated your issue further.
For your template file, we found it was created by higher version of MS Excel. In newer versions of Aspose.Cells component, we keep the original calculationid in the template file and save it to the generated file. Higher version of this id denotes it will not be calculated by lower versions of MS Excel, so those formulas have not been refreshed (re-calculated) when these are opened by MS Excel. The fact that file generated by old versions of Aspose.Cells component can work is because in older versions of Aspose.Cells component we do not keep the original calculation id of the template file, or set the calculation id as values corresponding to lower version of MS Excel. To get the same effect with old versions(that is, to make the generated file re-calculated by any version of MS Excel automatically). You may add one line of code before saving the workbook and it may figure out your issue:
i.e.,

.......
workbookTemplate.getSettings().setCalculationId("");
workbookTemplate.save(...);

Let us know if you still find any issue.

#9

Okay, then I open template, pressed Ctrl+Alt+Shift+F9 (recalculate all formulas), saved as new template and generate report from new template. After open generated report I see that formulas still have N/A value and not recalculating. Can you describe how I can modify calculationId in Excel without Aspose?

#10

@VasiliyKupchinskiy,

We are not sure if doing your way MS Excel re-configures the calculationId. We will still check if anything can be done in MS Excel manually and let you update soon.

In the mean time, kindly do as suggested, i.e., use the suggested line of code before saving if you still find this issue.

#11

@VasiliyKupchinskiy,
Forcing the recalculation of formulas in ms excel does not take effect for this case. There are such kind of settings in the file itself, but no corresponding operations in the UI of ms excel. So, for this case, you have to handle it by one of below code:

WorkbookSettings.CalculationId = "";

Or:

WorkbookSettings.ReCalculateOnOpen = true;

Let us know your feedback.

1 Like
#12

Thank you for help.
WorkbookSettings.CalculationId = "" or WorkbookSettings.ReCalculateOnOpen = true works good.

Can you post Issue ID where behavior around calculationId was changed?

#13

@VasiliyKupchinskiy,

We could not find exact issue id but this behavior regarding CalculationId was changed 7/8 months ago.

Once we got more information, we will update you.