Create XLSB file - F2 required to update the formula driven cells in .NET using C#

I have upgraded Aspose.dll
From version: 8.6.0.1
To Version: 19.3.0.0

After the upgrade when I download the excel generated from my .NET code, there is a formula driven column which doesn’t work as expected, I have to go to the specific cell and hit F2 on the formula driven cell and click anywhere else in the excel sheet and then the value gets derived from the formula else it shows #Value!.

This was working fine tell my earlier version of Aspose.

Type of excel I am using is .XLSB
When I tried using .XLSX instead of .XLSB, things work as per expectations, but the requirement is to generate the report with .XLSB format.

Can someone please help here?

Formula used in Excel is:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(‘Allocation Report’!$Y$9:$Y$118797,ROW(‘My Report’!$Y$9:$Y$118797)-ROW($Y$9),0,1)),–(‘My Report’!$P$9:$P$118797=“Add”),–(‘My Report’!$A$9:$A$118797<>“Total”),‘My Report’!$Y$9:$Y$118797)-SUMPRODUCT(SUBTOTAL(3,OFFSET(‘My Report’!$Y$9:$Y$118797,ROW(‘My Report’!$Y$9:$Y$118797)-ROW($Y$9),0,1)),–(‘My Report’!$P$9:$P$118797=“Subtract”),–(‘My Report’!$A$9:$A$118797<>“Total”),‘My Report’!$Y$9:$Y$118797)

@kchaniyal,

Thanks for providing us some details.

Please try using our latest version/fix: Aspose.Cells for .NET v19.7.x.
If you still find any issue, kindly do provide us template file and sample code (runnable) to reproduce the issue, we will check it soon.

Attached is the sample project with 2 issues, both of these issues have started occuiring only after we updated the Aspose:

From version: 8.6.0.1
To Version: 19.3.0.0

  1. Issue: After aspose upgrade the formula driven column in the first row of the data which is inserted in the excel template shows Ref! and the formula doesn’t get applied to the row but rest of the row gets the correct formula
    This is working fine with the earlier version of the Aspose.

Steps to replicate:
Once we run the sample we get a file generated in Docs folder of the sample.
File generated after the sample run: In docs folder with file name: Report_FormulaIssue_*.xlsx
Template used to generate the file is a static template which already has the formula: under location Templates Folder with file name called: MyTemplate_1.xlsx

The static Template has the formula in row 4 & 5.
In these rows cells C4,C5,D4 & D5 gets their values from row 6's cells C6 and D6 in the template.
After the sample is run, the data is copied to rows 4 and above till the datatable has data.
After the data is populated in the rows, the extra rows above last row which has the totals data are removed from .net code.
The data based upon formula gets auto calculated in rows 5 and above but doesnt get calculated in row 4 and fields C4 & D4 shows Ref! in the formula which I think is an error. This has started soon after the aspose got upgraded but with the earlier version it was working perfectly.
  1. Issue: After the upgrade when I download the excel generated from my .NET code, there is a formula driven column which doesn’t work as expected, I have to go to the specific cell and hit F2 on the formula driven cell and click anywhere else in the excel sheet and then the value gets derived from the formula else it shows #Value!.

    This was working fine till my earlier version of Aspose.

    In the excel file generated in docs folder named: Report_XLSB_Issue_*.xlsb
    There is a static template under location Templates Folder with file name called: MyTemplate_2.xlsx

Steps to replicate:
In the static Template, In row 4 (Subtotal) the numerical values are expected to show up.

When we run the sample and generate the report by inserting some records in rows 10 and above the data should automatically get calculated in row 4 which has a formula for the fields.
But the generated report doesn't reflect the autocalculated values in row 4 (subtotal).
to achieve this we have to click on the formula field in row 4>hit F2 and then anywhere else in the worksheet, after this- the autocalculated values shows up.
This has worked fine untill our last aspose version. 

AsposeUpgradeIssue.zip (9.2 MB)
AsposeUpgradeIssue.zip (9.2 MB)

@kchaniyal,
We have observed issue 2 and will log it for further analysis in our database.
Regarding issue 1, could you please share your output file which is generated using older version 8.6.0.1 for our reference.

Hi @ahsaniqbalsidiqui,

Issue 2 is a blocking issue for us right now. Could you please provide us the ETA on the same.
Issue 1:
Earlier, we’re using v17.6. Please find that deletion of the last row is not tampering the formula of first data row.
Once we upgraded to v19.3, we can see that formula in the first data row is giving #REF! error as we delete the last data row.
Please use the same code that was shared by @kchaniyal.

@kchaniyal, @iamanudeep202,
We were able to observe the issues but we need to look into it more. We have logged the issues in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issues have been logged as

CELLSNET-46873 – Ref! shown as formula not applied
CELLSNET-46874 – Values not derived from formula and require pressing F2 to get values in cells

@kchaniyal, @iamanudeep202,

This is to inform you that your issue (logged earlier as “CELLSNET-46873”) has been fixed. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@kchaniyal, @iamanudeep202,

Please try our latest version/fix: Aspose.Cells for .NET v19.8.2 (attached)

Your issue (logged earlier as “CELLSNET-46873”) should be fixed in it.

Let us know your feedback.
Aspose.Cells19.8.2 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.8.2 For .Net4.0.Zip (4.9 MB)

@kchaniyal, @iamanudeep202,

This is to inform you that your issue (logged earlier as “CELLSNET-46874”) has been fixed. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes

Thanks @Amjad_Sahi
Please keep us informed when this has been fixed.

@kchaniyal,

Sure, will do the same. We will keep you updated on it.

@kchaniyal,

Please try our latest version/fix: Aspose.Cells for .NET v19.8.4 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells19.8.4 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.8.4 For .Net4.0.Zip (4.9 MB)

The issues you have found earlier (filed as CELLSNET-46874,CELLSNET-46873) have been fixed in Aspose.Cells for .NET v19.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

@Amjad_Sahi
Hi Amjad,

This is regarding a similar issue I am facing.

Currently using Aspose Version: 19.10.0.0
Issue: There is a cell in my workbook where I have a following formula set: =SUMPRODUCT(H16:S16,$H$14:$S$14)/$T$14

But the value doesn’t get updated automatically until I press F2 on the cell where I need the updated value.
Solution I tried: Workbook.CalculateFormula()

Although the solution worked: But I am surprised that this was working well with my earlier version of Aspose which was: 19.4.0.0 where I didn’t had to use Workbook.CalculateFormula()

Any quick help will be much appreciated.

Thanks
Kunal Chaniyal
+91-9582799260

@kchaniyal,

We recommend you to kindly try using our latest version/fix: Aspose.Cells for .NET v20.1.
May be your MS Excel’s calculation options are set to Manual where as previously the calculation options were set to Automatic. If this is not the case, kindly do provide your template file and sample code (runnable) with v20.1 to reproduce the issue on our end, we will test using both older and newer versions.

Hi, I get the same problem with Aspose.cells 24.2.0 and C# .Net framework 4.51

Formula are not updated until editing source cell with F2 et hit return.

workbook.CalculateFormula is a workaround, but why ?

Olivier

@olivier57
Could you post your template file and sample project here?
We will check it soon.

@olivier57
By setting the mentioned formula to a cell and save as xlsb, we cannot find the issue. Which version of ms excel are you using to open the generated xlsb file? Maybe it is some old version whose calculationId is smaller than the default one used by latest versions of Aspose.Cells. Would you please try the code:

            wb.Settings.FormulaSettings.CalculateOnOpen = true;

or

            wb.Settings.FormulaSettings.CalculationId = "0";

before saving the xlsb file to see whether you can get the expected result?

If you still find the issue, we are afraid we need the runnable project with the used template file which can reproduce the issue so we can figure the issue out. Thank you.

Hi,
Sorry to answer late, I didn’t get any answer notification. It works if I set file name to xlsb.
So the question is, why does it do not work with xlsx which is rather the standard name ?
Regards
Olivier

@olivier57
Would you like to provide your sample file and executable Console project? We will check it soon.