MemoryException and Calculateformulas in cell

Aspose.Cells Product Family
1

Hi,


I’m trying the aspose.cells product for a project in my company.

Apparently the product have everything we need, but we are experimenting some difficulties in to related points:
1) In this new version (lunched today) I’m not able to open files bigger than 39Mb (already tried with a release in November and have the same error) - just a tip: in the version lunched in August I had no problem in opening this kind of files. Already try in a server (more memory) and the error persists

2) Isn’t possible to execute (and watch) the result of a formula in certain cell?
I mean, in this new version you have this new function “Worksheet”.CalculateFormula(), but it takes to long (and I don’t want to execute all the functions inside that sheet).
What i want is something like this:
1) Set formula into Cell
workGroup.Worksheets[0].Cells[“A1”].Formula = “=1<2”;
2) after insert this formula into cell i want to load immediately the result. So when I watch the value in Cell workGroup.Worksheets[0].Cells[“A1”].Value I should see “True”. Is this possible? How? already googleit and no “good” solution foud.

Thanks in advance,

Ricardo

2

Hi,

1) Please try the latest version:
Aspose.Cells
for .NET v7.0.4.3 and see if it works fine.

2) We will investigate your requirements, we have logged this issue in our database. Once, we will get some update/feedback relating to it from development team, we will let you know asap.

This issue has been logged as CELLSNET-40254.

3

Hi,

Please call Workbook. CalculateFormula() after setting the formulas to get calculated value of the formula. Also, if you call Workbook.CalculateFormula() again, it will only calculate the changed cells regarding values.

Thank you.

4

Hi,

1) I've downloaded that version at the error persists! (I attached the excel file that I'm testing)

Another thing that I noticed about this is that, with this new dll, the process is really slow. The dll from August (version 7.0.0.0) is much faster (and works with huge excel files).

2) This is very important to us. We can't use the other option that was talked here "Please call Workbook. CalculateFormula() after setting the formulas to get calculated value of the formula. Also, if you call Workbook.CalculateFormula() again, it will only calculate the changed cells regarding values."

Can't do this because the final excel file has N sheets, and all the sheets has formulas (already calculated), so I really don't want to calculate them again. I just want to process the formulas in the new sheet. Even there I just want to process the formulas in one column (I have formulas, already calculated, in the same sheet).


Thanks in advance

5

Hi,

1)

Thanks for providing the source xlsm file. Please also provide me a sample test code to replicate the problem.

2)
If you already have called the CalculateForumula() method, then you can retrieve the cells calculated value using Cell.StringValue property.

You can then just copy the calculated string values (not formulas) in your other worksheets.

Let me know if this works for you.

6
Hi,

We will check if we can improve formula calculation engine to calculate formulas more efficiently and quickly. I have logged a ticket with an id: CELLSNET-40257. We will soon check it.

I have tested using Workbook.CalculateFormula() method with your template file and it does takes some time and the memory consumed is also very high. Also, since your file is a huge one with lots of formulas in it, so, MS Excel also takes more memory and takes some time to open the file into it.

Anyways, we will surely look into it soon.

Thank you.
7

Hi,


1) You can find in attach one application that you can test what I’m saying.
The only thing that the application do is open the file.

Further, finally I could open one time the file with the application in attach. The time spent opening (the only time that I was able to do that) was 3x more that the older dll (which I never had problem opening the xlsm file).

To test:
a) Just like I sent and (If not outof memory exception appear) see the time spent
b) change the aspose.cells.dll in project by the one in OldAsposeAssembly folder, also in the project

2) What I said was that, I can’t use the CalculateFormula because it will take too much time to process ALL the formulas that I have in the excel file (regard that the file that I’ve sent in attach is not the final excel file which I process).
Last time I tried it spent 30 minutes and the process did not finish… I give up after that time.

I know that, after use the calculateFormulas I can see the result (I also did a sample for that, with only one row…).

What I really need is to process the formula in the moment that I insert in the cell.

Thanks in advance



8

Hi,

Thanks for time spent here.

The file that I sent to you is just a template. After open that template I’ll copy the sheets to a final excel file (larger than that).

My excel sample was just to let you know how huge will be the files that I’m working. What I really need is if there’s any way to process the formula of one, and only, cell.

Right now exists:
a. workbook.CalculateFormula()
b. (in this new versions) workbook.worksheet.CalculateFormula()

what I need would be
workbook.worksheet[0].Cells[0].CalculateFormula()

Thanks in advance

9

Hi,

Thanks for your postings.

We have logged your feedback in our database. We will look into them and get back to you asap.

10

Hi,

Please download and try the latest fix: Aspose.Cells for .NET v7.0.4.5

a) In the first calling Workbook.CalculateFormula, we will try to create a calculating chain.

There are too many formulas in your template file, so it takes long to time to create it and consumes much memory.

If you just want to calculate formulas in the file once, please try the following code:

C#


Console.WriteLine(DateTime.Now);

Workbook workbook = new Workbook(@“D:\FileTemp\TemplatesSAP.xlsm”);

Console.WriteLine(DateTime.Now);

workbook.Settings.CreateCalcChain = false;

workbook.CalculateFormula();


Console.WriteLine(DateTime.Now);


b)We also added Cell.Calculate() method. See following code:

C#

Workbook workbook = new Workbook();

Cell cell = workbook.Worksheets[0].Cells[“A1”];

cell.Formula = “=1<2”;

cell.Calculate(false,null);

Console.WriteLine(cell.Value);


11

Perfect!!!

It works just like we want!!

This will be in the next version of aspose release right?

Once again, thanks for the quick responses.

12

Hi,


Yes, sure this would be including in our upcoming official release of the product that is due in the next couple of weeks.

Moreover, you may use this fix as long as you wish without any problem, it will behave like an official release in your production server. It includes all the functionality/features and fixes of the previous official release v7.4.0.0 up to the new fix v7.0.4.5.

13

The issues you have found earlier (filed as CELLSNET-40257) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.