Problems with formular calculation in azure function

Aspose.Cells Product Family
1

Hi
I have a azure function application that does calculations via cells.
It is coded in .net 8 and using the newest aspose cells 24.10.

I have a simple formular
=Input!C23*24
C23 contains a hh:mm:ss value of 36:30:00.
When I run my azure function locally it returns 36,5 correctly and that is also the case when run it on our azure website.
But in the azure function it just returns #value!
If I download the sheet it shows #value, but if I go to the formular line and hits enter it will show the correct value.

I have tried setting culture to the same as my own computer and our website.
But no luck.
Changed the loadoptions to use da-DK but didn’t change anything.

I am using calculateformulars() with ignoreError = true.
I have tried to recalculate the formular in the cell several times.

Any suggestions to what I can try next ?

2

@nic11

It seems you’re encountering an issue with formula calculations in your Azure Function application using Aspose.Cells. The formula =Input!C23*24 is returning #VALUE! in the Azure Function environment, while it works correctly when run locally and on your Azure website.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

  1. Check Calculation Options: Ensure that you are using the correct calculation options when calculating the formula. You can specify options such as CalculationOptions.IgnoreError to handle any errors gracefully. For example:

    CalculationOptions options = new CalculationOptions();
options.IgnoreError = true; // Ignore errors during calculation
worksheet.CalculateFormula(options);

  2. Formula Parsing Options: If you are using specific regional settings (like da-DK), ensure that the formula parsing options are set correctly. You can use FormulaParseOptions to specify how the formula should be interpreted.

  3. Force Recalculation: Since the formula works when you manually edit it, you might want to force a recalculation of the entire worksheet after setting the formula. This can be done using:

    workbook.CalculateFormula();

  4. Culture Settings: Since you mentioned trying to set the culture, ensure that the culture settings are consistent across your local environment, Azure website, and Azure Function. This can affect how time values are interpreted.

  5. Debugging: Add logging to your Azure Function to capture the exact formula being calculated and any intermediate values. This can help identify if the input values are being set correctly before the calculation.

  6. Aspose.Cells Version: Ensure that you are using the same version of Aspose.Cells across all environments. Sometimes, discrepancies in library versions can lead to different behaviors.

If these suggestions do not resolve the issue, consider reaching out to the Aspose support community or checking their documentation for more specific guidance on formula calculations.

Feel free to ask if you have any further questions or need additional assistance!

3

@nic11
Based on your description, we have created a sample file and tested it on the latest version v24.10 using the following sample code. We can obtain the same results as Excel. Please refer to the attachment. sample.zip (7.4 KB)

The sample code as follows:

Workbook wb = new Workbook(filePath + "sample.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine("before: " + cells["A1"].StringValue);
wb.CalculateFormula();
Console.WriteLine("after: " + cells["A1"].StringValue);

The output result:

before: 876:00:00
after: 876:00:00

If you still have questions, please provide us with your sample files and runnable test code, and we will check it soon.

4

Hi John
I tried doing a simple version of this.
This is my functionscode:

public class TestFunction
{
private readonly ILogger _logger;

 public TestFunction(ILogger<TestFunction> logger)
 {
     _logger = logger;
 }

 [Function("TestFunction")]
 public IActionResult Run([HttpTrigger(AuthorizationLevel.Function, "get", "post")] HttpRequest req)
 {
     var path = Environment.CurrentDirectory + "/test/test.xlsx";
     var result = new List<string>();
     var loadOptions = new LoadOptions();
     loadOptions.CultureInfo = CultureInfo.CreateSpecificCulture("da-DK");
     var v = new Workbook(path, loadOptions);
     var sheet = v.Worksheets[0];
     var timeSpan = new TimeSpan(36, 30, 0);
     sheet.Cells["A1"].GetDisplayStyle().Custom = "hh:mm:ss";
     sheet.Cells["A1"].Value = string.Format("{0}:{1}:{2}", ((timeSpan.Days * 24) + timeSpan.Hours).ToString().PadLeft(2, '0'),timeSpan.Minutes.ToString().PadLeft(2, '0'),
              timeSpan.Seconds.ToString().PadLeft(2, '0'));
     result.Add("Calculating formula " + sheet.Cells["A2"].StringValue);
     result.Add("Calculating formula " + sheet.Cells["A3"].StringValue);
     v.CalculateFormula();
     result.Add("Calculating formula " + sheet.Cells["A2"].StringValue);
     result.Add("Calculating formula " + sheet.Cells["A3"].StringValue);
     //v.Save("c:\\temp\\testsaved.xlsx");
     _logger.LogInformation("C# HTTP trigger function processed a request.");
     return new OkObjectResult(result);
 }

}

A1 in input is a default field in my sheet;
A2 is defined as a numeric field because I want to turn 36:30:00 into a decimal number.
A3 is just defined as a default field.
A2 and a3 both has this formular =A1*24

This is what is returned from my function:
[
“Calculating formula 0,00”,
“Calculating formula 0”,
“Calculating formula #VALUE!”,
“Calculating formula #VALUE!”
]
This is the sheet I am using.

test.zip (6.5 KB)

If I run this locally it works fine.
In azure it does not.
I am not sure why, my suspicion is that it is related to us using a danish version of excel but I could be wrong.

5

@nic11
Please try the following codes:

var loadOptions = new LoadOptions();
  loadOptions.CultureInfo = CultureInfo.CreateSpecificCulture("da-DK");
  var v = new Workbook(dir +"test.xlsx", loadOptions);
  var sheet = v.Worksheets[0];
  var timeSpan = new TimeSpan(36, 30, 0);
  Cell cell = sheet.Cells["A1"];
  Style style = cell.GetStyle();
  style.Custom = "hh:mm:ss";
  cell.SetStyle(style);
  //sheet.Cells["A1"].GetDisplayStyle().Custom = "hh:mm:ss";
  DateTime dt = CellsHelper.GetDateTimeFromDouble(timeSpan.TotalDays, false);
  sheet.Cells["A1"].Value = dt;
Console.WriteLine( sheet.Cells["A2"].StringValue);
  Console.WriteLine(sheet.Cells["A3"].StringValue);
  v.CalculateFormula();
  Console.WriteLine(sheet.Cells["A2"].StringValue);
  Console.WriteLine(sheet.Cells["A3"].StringValue);

If you put a string value to the cell and it could not be parsed in the region on Azure, it could not be caculated fine.

6

Thank you so much, works like a charm :slight_smile:

7

@nic11,

We are glad that the suggested code segment works for your needs. You’re welcome to reach out to us again if you have any other questions or feedback.