INT function returns #VALUE!

Aspose.Cells Product Family
1

We are are using Aspose.Cells (NET), version 25.8.0.
We have a function =INT(MY_CUSTOME_FUNC) from custom function in Windows and everything works correctly and =INT(MY_CUSTOME_FUNC) returns 1232

However, in Mac or Docker with a *nix system, =INT(MY_CUSTOME_FUNC) returns #VALUE!

code:

var opts = new CalculationOptions { CustomEngine = this };

workbook.CalculateFormula(opts);

file:
input.zip (9.8 KB)

2

@aleksandr.l

It sounds like youā€™re encountering a compatibility issue with your custom function when using Aspose.Cells on different platforms. The #VALUE! error typically indicates that the function is not returning a valid numeric value in the environments where it fails.

Cause: The issue may stem from differences in how your custom function is executed across platforms, leading to unexpected results.

Solution: Here are some steps to diagnose and resolve the issue:

  1. Check Custom Function Implementation: Ensure that your custom function is correctly implemented and consistently returns a numeric value across all environments. Platform-specific behavior can sometimes lead to discrepancies.

  2. Debugging: Implement logging within your custom function to capture the returned value when called from the Mac or Docker environment. This will help identify if the function is executing properly.

  3. Calculation Options: Verify that the CalculationOptions you are using are set correctly. Testing with the default calculation options may help determine if the issue lies there.

  4. Environment Differences: Investigate any differences in environment settings between Windows and the other platforms (like Docker or Mac). Look for missing dependencies or configurations that could impact your custom functionā€™s execution.

  5. Update Aspose.Cells: If feasible, update to the latest version of Aspose.Cells, as recent updates may include bug fixes or enhancements that could resolve your issue.

Next Steps: Hereā€™s a minimal code snippet to ensure your custom function is being called correctly:

var opts = new CalculationOptions { CustomEngine = this };
workbook.CalculateFormula(opts);
var result = workbook.Worksheets[0].Cells["A1"].Value; // Assuming your function is in A1
Console.WriteLine(result);

Please try these steps and let me know if they help resolve the issue or if you require further assistance!

3

@aleksandr.l,

Thanks for the sample Excel file.

Could you provide details on how you define the custom functions in your code? We would appreciate it if you could provide a complete, runnable example or a sample application that we can execute to reproduce the issue on our end. We will check your issue soon.

4

code:

var wb = await Workbook.OpenAsync("input.xlsx");

wb.CalculateFormula(new CalculationOptions
{
    CustomEngine = new MyEngine()
});

wb.Save($"output.xlsx");

public class MyEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        if (!string.IsNullOrEmpty(data.FunctionName))
        {
            //data.CalculatedValue = "7/31/2025 12:00:00 AM";

            var dt = new DateTime(2025, 7, 31, 0, 0, 0);
            var str = string.Join("", dt.ToString());
            data.CalculatedValue = str;
        }
    }
}

BUT
I think I found the issue.
Everything works fine in Windows, BUT in Mac and *.nix systems,
Because I think DateTime in US format adds a non-breaking space U+202F NARROW NO-BREAK SPACE (NNBSP) between time and AM/PM (e.g: 7/31/2025 12:00:00 AM ā† There is NNBSP)

This breaks the formula.

Please check, I think Iā€™m right.

6

@aleksandr.l

For such kind of issue and similar situations for processing date time or numeric values, please do not use the default ToString() conversion if your application will be executed in different environments. The default ToString() is locale and environments setting(not only OS) dependent, which may produce different results for different environments and then you may get unexpected results for your application.

For the specific custom engine in your case, if the string result is not necessary for the specific cell, you may just convert the date time value to numeric(such as by CellsHelper.GetDoubleFromDateTime(DateTime, bool)) so you can get the fixed value always for all platforms and environments. If you do need the string format value for the cell, please build the string result manually to make the resultant string do not invariant for different environments.

Anyways, if you provide us the resultant file generated with #VALUE!, we will check whether we can handle such situation for you automatically and specifically. However, even though we can handle it specifically, it is not a good solution for your application because such kind of environment dependent issues may occur for other data for other situations.

7

Thank you, I will try to resolve this on my end, but it is strange that separator in different OS versions of DateTime disrupts logic of data output in Excel. @johnson.shi

8

@aleksandr.l
Please take your time to try the suggested solutions. Hopefully, your issue will be sorted out. Please let us know your feedback.

9

@aleksandr.l

For the issue that different string of DateTime may affect data output in excel, it is because for some functions, such as INT in your case, the string value needs to be parsed to numeric values and ms excel(and Cells component) can only recognize some special/fixed patterns of the date time or numeric values. If the formatting pattern for those data is out of those recognizable ones, #VALUE! may be produced.

Further more, even for the same string input for those functions, ms excel may produce different results for different environments. For example, INT(ā€œ8/9/2025ā€) will produce the int value corresponding August 9 2025 in ms excel if the regional setting of your environment is en_US. But if the regional setting is en_GB, you will find ms excel give one int value corresponding to September 8 2025 for this formula. It is also one of those reasons that using string value for those functions is not recommended especially when your application and the generated excel file may be used for variant environments.

Hope this may help you understand the issue better.

1 Like
10

Thank you @johnson.shi and @John.He
I implemented solution, date is always converted to double CellsHelper.GetDoubleFromDateTime(DateTime, bool)

11

@aleksandr.l,

It appears that your issue is resolved by utilizing CellsHelper.GetDoubleFromDateTime(DateTime, bool) to obtain the double value. If you have any additional questions or comments, please donā€™t hesitate to reach out. Weā€™ll be delighted to assist you soon.

1 Like