Free Support Forum - aspose.com

�Exception thrown: ' ' in Aspose.Cells.dll� when calling CalculateFormula()

In certain situations, apparently when cell contains a formula starting with a space, Aspose.Cell will generate an internal exception.
Those exceptions, hundreds of them in the attached file, are slowing the CaclulateFormula call and in certain situations the caller will crash.

Replacing the space, reduces the time of processing from almost 2 minutes to under 1 second.

There is an exception raised in the Cell library, but is a first chance exception that is not propagated to the caller of Aspose.Cells library and will not be catch by a normal try and catch block of code.

In order to catch the exception in Visual Studio 2 things needs to be set:
1. Intercept common language runtime errors, go to Debug->Exceptions and in the new window check “Throw” on “Common language runtime” section (all the subsections will also be checked)
2. Intercept errors on code without source, go to Tools->Options->Debugging-General and disable “Enable Just My Code” option.

This issue needs to be fixed, since in some situations there will be a crash in the library messing up lots of things.

Thank You,

Cornel Turculet
Notes:
1. For project VS 2013 was used.
2. Internal reference number 30646.

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this issue as per your description and instructions. I have attached the screenshot showing the exception for a reference.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-44411.

Hi,

Thanks for using Aspose.Cells.

The exceptions are designed and
required by our formula engine. For some situations, such as for
handling StackOverflowException, we have to check the count of cells in
recursive calculation. When we found high risk of StackOverflowException
we will break the program stack by throwing special exception and
restart the calculation stack. For your template file, there are some
recursive calculations for shared formulas and we need to throw the
special exception to improve the performance on calculating shared
formulas. When you removed the space, in fact you removed all formulas.
Even you use Cell.Formula to reset the formulas, you will break the shared
formula and change them all to normal formulas so you cannot get this
exception when calculating again.

Hello Again,

First the file does not have any issues, if you try
to check all the
sheets for errors from Excel there are no problems. We are not in a
circular reference formulas scenarios and the way the cells API is doing
its
calculations is beside the point of this thread.
I was not aware that
those were shared formulas and that replacing the space will actually
remove them, now after your explanation I understand that the “fix” in
inappropriate.

The problem we are expending is due to those
FirstChanceExceptions that are thrown by the library and since our
application is hosted under IIS, those exceptions are crashing the
entire process without us to have any control or chance to intercept
them.

I’m adding a new sample program that it is not reproducing the problem, but is simulating the situation very closely.

In
a normal application, those internal exceptions will not be
intercepted, or if they are can be handled quite easy because the
calling process has complete control.
In a hosted environment this
are a bit more complicated, since the process is running/hosted under
IIS those exceptions will be considered unhandled exceptions by the IIS
and due to its protections/limitations the process will just crash,
without any other explanation.

Since we can’t intercept those
exceptions because they are encrypted or local to your library, we are a
sitting duck in our code waiting for the IIS to crash.

We have 2
options, you either bubble up that exception as a proper .Net exception
or you give us an alternative API call that is either returning just
the result of the calculation without any internal exceptions raised.

I hope this is clarifying our issue.

Regards,

Cornel Turculet

PS
-Process Runner should be the starting project.

Hi,

Thanks for using Aspose.Cells.

Firstly, recursive calculation for cells is not circular reference. Such as the simple formulas: A1’s formula is “=A2”, A2’s formula is “=A3”, …etc. When Calculating A1, we should calculate A2 recursively, and when calculating A2, we need to calculate A3… For such situation we should break the stack to avoid StackOverflowException.

Secondly, I have created one simple test to replace cells lib to reproduce the issue:

C#

class TestException
{
public void Test()
{
for (int i = 0; i < 3; i++)
{
try
{
Test1(i);
}
catch (SimpleException e)
{
Console.WriteLine(“The exception is designed and expected”);
}
}
}
private void Test1(int i)
{
if (i % 2 == 0)
{
throw new SimpleException();
}
}
private class SimpleException : Exception
{
}
}

When calling TestException.Test() method in the place of Workbook.CalculateFormula(), we can get the same result for the first chance exception. Do you mean the code in TestException is invalid?

Hi,

I’m glad that we are on same line on first point, the fact that those calls are recursive and exceptions are thrown to avoid stack overflow is good starting point. My point is that those should be handled differently in your library, living them as they are in this moment are just making things worst for everyone. Those exceptions can’t be intercepted by the caller of the library.

SimpleException example is too simple, you know the type of the exception you are intercepting (you guess it it is …SimpleException …your last question is just uncalled for…).

Take a look at the sample code again without Visual Studio attached and note that in none of my examples the FistTimeException raised by CaclulateFormula can be catch by the try catches just because it’s type is unknown (look at the picture you posted above the type is ’ ’ )

I should probably mention that with the Visual Studio debugger attached you could see the same behavior if you revert the settings I suggested to be changed in the initial post (remove the check from "Common Language Runtime and “Enable Just My Code”)

I’m not raising this issue just because it is a simple scenario where a SimpleException raise and a catch will work.
I’m raising it because the library it is not behaving as is supposed to in a production environment where a process is calling another process this exception can’t be handled without a change in your library code.

If you have more questions, let me know and I will guide you over the issue.

Regards,

Cornel Turculet

Hi Cornel,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database for further investigation. We will look into it and help you. Once, there is some news for you, we will update you asap.

Hi,

Thanks for using Aspose.Cells.

Did you try to call
TestException.Test() instead of Workbook.CalculateFormula() in your
project and verify the result? Even you know the exception type is
SimpleException, you still cannot handle it in the try…catch code. We
don’t think the issue has something to do with the exact type name of
the exception. Just take TestException as the code of cells library,
would you please tell us how to make it work for your project to avoid
the first chance exception?

Hi Shakeel,

If I running your application and call TestExceptin.Test() of course the exception will not be intercepted by my code since is not raised to higher code.

The solution is simple …
C#


try
{
Test1(i);
}
catch (SimpleException e)
{
Console.Write…
throw;
}

And if SimpleException is an accessible type from your library, we should be set. The exception can be intercepted from my code and I can stop my thread before it’s crashing IIS.

The only issue is probably, on the cases when this exception is justified and you want to hide it for a number of calls. Probably the problem in here is the high number of exceptions thrown in this particular file.

I’m sure your development team will have a better idea if they understand what’s the issue.

Let me know if other details are needed.

Regards,

Cornel Turculet

BTW my second sample should help in understanding the issue, try to run it and double check the code, it might explain better the problem.


Hi,

Thanks for your logical arguments and using Aspose.Cells.

We have logged your comment in our database for product team consideration and investigation. We will look into it further and update you. Once, there is some news for you, we will let you asap.

Hi,


We have evaluated your issue further. Well, it is the same logic for TestException and Aspose.Cells library. The inner exception of the Aspose.Cells library are also to be handled by the library itself and has not been thrown to your code. Without the “throw” code in your modification, you should also be able to reproduce the first chance exception with your test code in your previous post, isn’t it?

Thank you.
Hi,

Thanks for using Aspose.Cells.

After reviewing this problem, we think maybe the root cause of your issue is StackOverflowException with IIS environment. If so, you can try the solution at


Also, you can try our latest version: Aspose.Cells for .NET 16.11.0 because we have made some enhancements for calculating shared formulas to reduce the chance of throwing the internal exceptions.