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

Free Support Forum - aspose.com

Calculate formula over a range of cells

Aspose.Cells Product Family
#1

in our code, we fill values into a worksheet in a loop and calculate formulas after each update

when checking profiler results, the formula calculation is the most time consuming process

add an overload to your calculation function to calculate only one worksheet ? only a range of cells ?

this would help us a lot to make the application run faster

#2

Hi,


First of all, we recommend you to download and try this fix: Aspose.Cells for .NET (Latest Version) .

We do provide the CalculateFormula method for Worksheet class, see the topic:
http://www.aspose.com/docs/display/cellsnet/Direct+Calculation+of+Formula

Also, if you are calculating formulas only once after updating values in the sheets, you may try to disable calculation chain to be created, it will improve your performance and it will take less time surely, see the topic
http://www.aspose.com/docs/display/cellsnet/Calculating+Formulas+Once+Only

Well, you know the formulas may depend on other worksheets values or have references to other sheets cells/values accordingly. So, it might not be more worthy if we restrict formula calculations to only one worksheet, it may provide you wrong or invalid results.




Thank you.
#3

Dear Amjad,

Thanks for your full response

Please notice that Ms Excel provides calculation logic for a range of cells as well as for worksheet (screenshot attached)

I agree that Worksheet A formulas can depend from Worksheet B and you'll need to calculate the cells from worksheet B in order to perform a correct calculation of Worksheet A cells.

Still the calculation chain will be shorter and you'll run it faster

See that actually the calculation is the slowest stage of your code (see attached screen)

Please consider this feature in your development roadmap

This would be the main reason for us to renew the annual support to have an optimised component

Regards

Dimitri

#4

Hi,


Thanks for the screen shots.

Well, as per your screen shot, you are doing calculating formulas for a worksheet/range of cells by using VBA codes in MS Excel.

Well, we have further evaluated and I think we already provide this feature (Calculate Formulas for a Worksheet only), please check this overloaded method of Worksheet.CalculateFormula(), here is the complete description:


public void CalculateFormula(
bool recursive ,
bool ignoreError ,
ICustomFunction customFunction
);

Parameters

recursive
True means if the worksheet' cells depend on the cells of other worksheets, the dependent cells in other worksheets will be calculated too. False means all the formulas in the worksheet have been calculated and the values are right.
ignoreError
Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.
customFunction
The custom formula calculation functions to extend the calculation engine.


I have written a simple example for you here too. Please put null for the last parameter if you do not have any custom function.

Sample code:

//Create a workbook
Workbook workbook = new Workbook();

//Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

//Put 20 in cell A1
Cell cellA1 = worksheet.Cells["A1"];
cellA1.PutValue(20);

//Put 30 in cell A2
Cell cellA2 = worksheet.Cells["A2"];
cellA2.PutValue(30);


//Specify formula on A3
Cell cellA3 = worksheet.Cells["A3"];
cellA3.Formula = "=Sum(A1:A2)";

worksheet.CalculateFormula(true,false, null);

int val = cellA3.IntValue;
MessageBox.Show("Calculated Value: " + val.ToString()); //50 - OK

workbook.Save("e:\\test2\\output.xls");



Please try this overload and let me know how it goes related to performance.

If you still have any performance issue, kindly give us more details, e.g sample project, Excel files, screen shots etc. We will check your issue soon.

Thank you.
#5

Dear Amjad,

Thanks for your hints

Be sure i'm not using VBA to calculate formulas. That was just a way to demonstrate you that MsExcel calculates on ranges and on worksheet, so why don't you

The worksheet calculation logic with dependencies is just fine for me. I've tested it and it works fine

Thanks for your help

Happy new year 2013

Regards

Dimitri

#6

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are pleased to know that Worksheet.CalculateFormula fulfills your needs. Please let us know if you still need any further help. We will be glad to help you more.

Thanks for your wishes, happy new year to you as well. :slight_smile:

#7

okay, friends, we may recalculate sheet. but in MS Excel we can calculate formula in range. it’s critical for me because recalculate whole sheet too long in my case…

can I recalculate range like I did it in Excel?

void CalculateFormulaInRange(MSExcel.Range rng)
{
rng.Calculate();
}
#8

Hi Denis Fedorez,


I am afraid, there is no such method available for Range class. I think you may try to use Cell.Calculate() method (you may try to use some loop for the task) if it suits your needs.

Thank you.
#9

ok, got it. thank you!

#10

Hi Denis,


Thank you for your understanding. Please feel free to contact us back in case you need our further assistance with Aspose APIs.