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

Free Support Forum - aspose.com

Issues with WORKDAY formula

Hello, I think there is a bug when ASPOSE is using this formula. I have an Excel file containing a formula like this:



=WORKDAY(C3,-1,HOLIDAYS)



Where HOLIDAYS is defined in the excel document as a Range. ASPOSE specifically breaks when it tries to evaluate a date

that is inside the HOLIDAYS range (If the date to be evaluated by the WORKDAY formula is not inside the HOLIDAY range it WON’T fail). I also

tried the following options where ASPOSE will also fail evaluating:



=WORKDAY(C3,-1,H7:M7) —> Using range directly

=WORKDAY(E3,-1,{42729,42730,42736,42737,42795,42788}) —> Using a constant array



The cells in which will fail are C48, D48 and E48 which correspond to the evaluation of 25-Dec-2016 as a HOLIDAY.



The exception is thrown in the following method:



private readonly Workbook _workbook;

_workbook.CalculateFormula(false);



As: Error in calculating Cell[[0]Threshold Analysis!C48]





I attach an excel file for you to test.

Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and logged it in our database for a fix.

This issue has been logged as

  • CELLSNET-45190 - Exception occurred while calculating WORKDAY formula

C#
Workbook wb = new Workbook(“ASPOSE_Template.xlsx”);
wb.CalculateFormula();

Exception
Aspose.Cells.CellsException was unhandled
HResult=-2146232832
Message=Error in calculating Cell[[0]Threshold Analysis!C48]
Source=Aspose.Cells
StackTrace:
at . ( )
at ​ . ​ (CalculationOptions )
at ​ . ​ (Boolean , ICustomFunction )
at Aspose.Cells.Workbook.CalculateFormula()
at CELLSNET.Program.f1() in E:\Downloads\Misc\Aspose\Projects\AsposeSol\CELLSNET\Program.cs:line 211
at CELLSNET.Program.Main(String[] args) in E:\Downloads\Misc\Aspose\Projects\AsposeSol\CELLSNET\Program.cs:line 33
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
HResult=-2146233080
Message=Index was outside the bounds of the array.
Source=Aspose.Cells
StackTrace:
at . (Int32 , Int32 , Int32[] , , Int32 )
at . ( [] , )
at . ( )
at . ​ ( )
at . ​ ( )
at . ​ ​ ( , )
at . ( ​ , )
InnerException:

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45190 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Hi,


Please try our latest fix/version: Aspose.Cells for .NET v17.2.8:

Aspose.Cells for .NET v17.02.8 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.02.8 (.NET 4.0) compiled in .NET Framework 4.0.

Your issue “CELLSNET-45190” should be fixed in it.

Let us know your feedback.

Thank you

Hello I tried v17.02.8 (.NET 4.0) and it is not working, I sued the previous attached document, I am having the following error:

Error in calculating Cell[[0]Threshold Analysis!C53]

Not sure about the reason as that cell does not include any day within HOLIDAYS range.

Regards.

Hi,


I tested your scenario/ case again with v17.2.8 using your original file with the following sample code, it works fine and and I could not find the issue (no error occurred):
e.g
Sample code:

Workbook wb = new Workbook(“e:\test2\ASPOSE_Template.xlsx”);
wb.CalculateFormula();

Please make sure that you are using latest version/fix: Aspose.Cells for .NET v17.2.8. You may print the version number at runtime to get to know you are using the right version:
e.g
Sample code:

Console.WriteLine(CellsHelper.GetVersion());


If you still find the issue, kindly do create a separate console application with v17.2.8, zip the project and post us there to reproduce the issue on our end, we will check it soon.

Thank you.

The issues you have found earlier (filed as CELLSNET-45190) have been fixed in Aspose.Cells for .NET 17.3.0.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.