Problem with calculation of custom functions

Different behavior is observed when opening .xlsb and .xlsx workbooks in Microsoft Excel that have been calculated and saved with Aspose Cells(version 8.0.2).





Please see that attached code and workbooks.





To reproduce:





1. Run the attached code.

2. Open Microsoft Excel.

3. Ensure Calculation mode within Excel is set to Automatic.

4. Open the “Calculation Issue Result.xlsb” workbook created by the attached code.

5. Notice cell B1 (with a call to a custom function) displays #NAME?.

6. Open the “Calculation Issue Result.xlsx” workbook created by the attached code.

7. Notice cell B1 (with a call to a custom function) displays “CustomFunction() Result”





The expected behavior is with the .xlsx (6 and 7 above).





For some reason, when opening the .xlsb workbook, Excel seems to try to re-calculate, and (correctly not find the Custom function), however when we open the .xlsx workbook, Excel seems not to calculate (as I am assuming the workbook was saved fully calculated), which is correct.



Code to reproduce:



using System.Collections;



using Aspose.Cells;



namespace AsposeCalculationIssue

{

class Program

{

static void Main(string[] args)

{

CalculationIssue();

}



private static void CalculationIssue()

{

CustomFunctionHandler customFunctionHandler = new CustomFunctionHandler();

CalculateFunctions(SaveFormat.Xlsb, customFunctionHandler);

CalculateFunctions(SaveFormat.Xlsx, customFunctionHandler);

}



private static void CalculateFunctions(SaveFormat SaveFormat, ICustomFunction CustomFunctionHandler)

{

Workbook sourceWorkbook = new Workbook();

Worksheet sourceWorksheet = sourceWorkbook.Worksheets[0];

sourceWorkbook.Settings.CalcMode = CalcModeType.Manual;

sourceWorksheet.Cells[0, 1].Formula = “=CustomFunction()”;

sourceWorksheet.Cells[0, 1].Calculate(false, CustomFunctionHandler);

// sourceWorksheet.CalculateFormula(false, true, CustomFunctionHandler);

sourceWorkbook.Save(@“C:\Calculation Issue Result.” + SaveFormat, SaveFormat);

}



private class CustomFunctionHandler : ICustomFunction

{

public object CalculateCustomFunction(string FunctionName, ArrayList ParamsList, ArrayList ContextObjects)

{

return “CustomFunction() Result”;

}

}

}

}

Hi Mike,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue using the latest version: Aspose.Cells for .NET 8.0.2. The issue was occurring because calculation mode of xlsb could not be set as manual. However, calculation mode of xlsx file could be set as manual successfully. We tested this issue with the following code. It works for xlsx file but not for xlsb file.

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-42654.

I have attached the output xlsx and xlsb files for a reference.

C#


Workbook workbook = new Workbook();

workbook.Settings.CalcMode = CalcModeType.Manual;


workbook.Save(“.out.xlsx”);

workbook.Save(“.out.xlsb”);

Hi Mike,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for .NET v8.0.2.1
and let us know your feedback.

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


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