Unsupported function

Aspose.Cells Product Family
1

We are trying to replace the COM+ excel server calculator with Aspose.Cells, to see whether Aspose can replace it without issues. We stumbled on the following error:

Unsupported function in formula calculation engine - code 309. Error in calculating cell D158 in Worksheet output.

In attachment the excel file.

Do you have any idea why Aspose throws this error?

2

Hi,

Thank you for considering Aspose.

After an initial test, we have found the issue. We will fix it and get back to you soon.

Thank you for your patience.

3

Thanks, we will wait for a patch to continue our evaluation.

PS - I'm extremely happy about the speed and professionalism of the service desk of Aspose. You guys rock!

4

Hi,

Please try the attached version, we have supported to calculate the FORECAST function and fixed some other bugs in calculating formulas.

Thank you.

5

Thank you, I've tried it, but now there is a performance issue. What I do is:

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(excel);
// here I set various cells to certain values
wb.CalculateFormula(); // This takes 75 seconds!!!

This is of course a problem... any idea if this can be improved?

Thanks!

6

Hi,

Well, how do you set different values to cells. Could you create a simple console application using your template file to show it, we will check it soon.

BTW, I tried the following code and it takes less than 9 seconds for the whole process (opening file, calculating formulas, saving file etc.). My testing machine is Intel Pentium Dual 2.00GHz with 1GB RAM.

Sample code:

DateTime start = DateTime.Now;
Workbook workbook = new Workbook();
string myPath = @"f:\test\telephony_v1[1].0.xls";
workbook.Open(myPath);
workbook.CalculateFormula();
workbook.Save(@"f:\test\nouttelephony_v1[1].0.xls");
DateTime end = DateTime.Now;
TimeSpan time = end - start;
MessageBox.Show(time.TotalSeconds.ToString());
Thank you.
7

Thank you.

I tried your code, and indeed it took 9 seconds. However, in this code no cells have values, so I think that not a lot calculation is going on here.

But in my code, I first set a number of cells to a certain value. If I do that, the Recalculate method takes 75 seconds.

If I compare it with our COM excel way of doing things (using Excel interop), that takes 3,5 seconds for the whole process, which is a lot faster.

Our conclusion at the moment is that Aspose is good for small excel files; but for larger excel files it's rather slow (compared to the Excel interop)...

8

Hi,

Well, Aspose.Cells can load, manipulate, set formattings to cells, work with drawing objects and other tasks for huge excel files in quick time. You may compare other portions (except for formula calculation task) working with larger files with Excel interop or any other component. For formula calculation (this is the only area which needs to have improvements), we are already on it and working to enhance it. We are trying to analyze it in every respect to enhance it to the maximum level. We appreciate if you could create a simple console application using your template file to show it as it may help us to improve the formula calculation engine. We will check your code too and may help you to optimize it so that it may process in much quicker time for your requirement.

Thank you.

9

Hi,

I've created a test application (see attachment) that uses Aspose.Cells to set some cells and then does a recalculation. If you run it, it takes about 23 seconds to complete (if you run it in debug mode it takes 52 seconds).

The same process with Excel interop takes about 3.5 seconds.

If you need additional information, I'll be glad to help you.

Kind regards,
Ludwig

10

Hi,

Thank you for providing us with a sample application. Yes, you are right that it takes a bit more time to calculate the formulas using Aspose.Cells. We are always optimizing our formula calculation engine and we will improve / optimize the formulas used in your excel template file to improve the performance / speed of the formula calculation engine. We will further look into your issue and get back to you soon.

Thank You & Best Regards,

11

Hi Ludwig,

Today is Chinese new year. Most of our developers are Chinese so they are on their holidays now. We will provide a new patch next week that will optimize the calculation engine for your scenario.

12

Hi Ludwig,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have enhanced the performance of formula calculation engine.

Thank You & Best Regards,

13

Thank you. I have tried version 4.6.0.10 with the sample application I posted before, and it still takes more than 22 seconds to complete…

14

Hi,

Thank you for the feedback. We will further look into it and get back to you soon.

Thank You & Best Regards,

15

Hi,

Thank you for considering Aspose.

Please try the attached latest fix. We have improved the performance of calculating formulas.

Thank You & Best Regards,