Aspose.cell CalculateFormula issue

Aspose.Cells Product Family
1

Hi,

I am using Aspose.Cells 7.3.1.0 for .Net and getting issue in removing formulas from workbook.

Calculate formula is working fine. If I save file without removing formula, then all look ok. When I use RemoveFormulas, it is corrupting formula values.

Step 1.
Opening work book with formula.
Step 2.
Filling data
Step 3.
Calculating formula
mWorkbook.CalculateFormula(true);
Setp 4
Removing Formula
mWorkbook.Worksheets[sheetName].Cells.RemoveFormulas();
Step 5
Saving file.


Step 4 causing cell values to be different than step 3.

2

Additional info.
Issue found for column 25(Y). Sheet 1 contains values with formula . Sheet 2 contains corrupted values for columns 25 .
How remove formula is changing values for column 25

3

Hi,


Thanks for providing us details and sharing template file.

After an initial test, I observed the issue as you mentioned by using the following sample code with your template file. The output file has wrong values for Y column’s cells in the first worksheet in the output Excel file.
e.g
Sample code:
string filePath = @“e:\test2\formual1.xls”;
Workbook _wb = new Workbook(filePath);
_wb.Settings.CreateCalcChain = false;
Worksheet worksheet = _wb.Worksheets[“Sheet1”];
_wb.CalculateFormula();
Cells cells = worksheet.Cells;
cells.RemoveFormulas();

_wb.Save(“e:\test2\outformual1.xls”);

I have logged a ticket with an id “CELLSNET-42205” for your issue. We will look into your issue to figure it out soon.


Thank you.
4

Thank you very much for looking this. We have plan to go live by this Friday, everything working fine except this issue. If possible please look this urgently otherwise i may need to postpone my release.
Thank you

5

Hi,

Thanks for your posting and using Aspose.Cells.

We
have checked your issue status from our database and we are pleased to
inform you that your issue has been fixed. We will provide you a fix
soon possibly on Friday.

6

Hi Faiz.

Its good to here that issue has been fixed so quickly. Iam afread i can't download new api as we bought it for one time licence.

we bought aspose.cell last year with user id KotiReddy1976. We bought with not upgrade support option.

Can you please confirm what kind of fix you are going to provide. Is it code change or I need to download new API.

In case in need to downlad new API. can i download with existing licence.

7

Hi,


Well, we may provide you the fix/version here. If your license is expired (you may open your license file into notepad and check the subscription expiry date in it) to use new versions/ fixes of the product, I am afraid, there is no way/ alternatives but to upgrade your subscription to use latest versions and fixes.

Thanks for your understanding!
8

Hi,

Thanks for using Aspose.Cells for .NET.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.6.1.3 and let us know your feedback.

9

Hi Faiz,

We are getting licenece issue. we can't use upgraded binaries provided by you.

SubscriptionExpiry date is 20130717.
We are going to start subscription renewal process, this will got thru approval process and will take 2 weeks time.
As we are going live this week , we need alternate solution.
This is bug in aspose.cells and we are not looking for new features, is it possible for you to just provide fix only for this issue with my existing licence. Or do let me know what was wrong in formula. I can change formula. Is is because of R1C1reference formula.

Regards

Surendra Jaiswal
Any help will be appricited.

10

Hi Surendra,

Thanks for your posting and using Aspose.Cells.

We are afraid, you will have to use the latest version for a fix. We do not fix the issues in the older versions. All fixes are done in the latest version only. So, you will have to purchase a license to continue using this version.

You can also request the Aspose.Purchase department to provide you a temporary license. Please post your query in Aspose.Purchase forum.

There is nothing wrong with your formula, it was a bug in calculation engine which has been fixed. I have attached the output xlsx file generated by the above code for your reference.

11

Hi Faiz,
As I already explained getting new license is very difficult for us as this required approval from higher management. My team was not in favor of aspose.cells, And I went against him and represented aspose.cells as one of the best solution for excel report formatting. Now my team found bug in Aspose.cells and my team is against me , getting approval for new licensee is really difficult at this moment. It will go to evaluation team to evaluate aspose.cells again, and this will take time.
I am really sorry to say, but aspose has really disappointed me. We are going to rollback aspose.cells function as we can’t stop entire project. We will use Microsoft excel as an alternate for excel reporting until we are not getting any reliable tool for excel reporting. Getting aspose again, without guaranty of basic functionality, doesn’t make sense. if this would have been any complicated functionality issue, I would have ignored. Formula is very basic feature,and not working in aspose.cells is really disappointing.

Thanks for your support.

Regards
Surendra Jaiswal

12

Hi Surendra,

Thanks for your posting and using Aspose.Cells.

Please change the formula of the cell Y2 as =LEFT(TEXT(CA2,“hhmm”),4)&“00”, then you can work with the old version.

13

Hi Faiz,

I tried changing formula as per your suggestion. Still it is not working in old api.

Regards

Surendra Jaiswal

14

Hi Surendra,

Thanks for your posting and using Aspose.Cells.

I have logged your comment in our database. We will investigate it and see if there is any other way, you could use the old API and if it is possible. Once, there is some update for you, we will let you know asap.

15

Hi Faiz,

Got another issue. Follwoing formula is not working. Its giving same random value for all record.

=LEFT(RAND()*1000000000,8)

Please check.

Thanks

Surendra Jaiswal

16

Hi Surendra,

Thank you for writing back.

I have evaluated your recently presented issue with Aspose.Cells for .NET 7.6.1.3 and 7.3.1.0 (your current version). I am getting unique random numbers with both versions therefore I am unable to replicate the said problem on my end. My sample code is provided below,

C#


var workbook = new Aspose.Cells.Workbook();
var sheet = workbook.Worksheets[0];
sheet.Cells[“A1”].Formula = “=LEFT(RAND()*1000000000,8)”;
sheet.Cells[“A2”].Formula = “=LEFT(RAND()*1000000000,8)”;
sheet.Cells[“A3”].Formula = “=LEFT(RAND()*1000000000,8)”;
workbook.CalculateFormula();
workbook.Save(myDir + “_output.xlsx”, SaveFormat.Xlsx);

If you are using the formula in a different way then please provide your sample code in the form of an executable project along with your template file so we could investigate this matter further.