SetFormula not working after latest release version 25.2 of Aspose.Cell

Aspose.Cells Product Family
,
1

Hi,

After using the latest version 25.2.0 of Aspose.cell, SetFormula has stopped working in the excel.

We are using setFormula in our excels at many places. Instead of setting the result of formula, it is setting object value which we provide.

Below is sample code:

ws.Cells[referenceRow, referenceColumn].SetFormula(cell.CellFormula, “00000000-0000-0000-0000-000000000000”);

Actual result:
image.png (8.5 KB)

Expected result:
image.png (11.7 KB)

I have attached the image of actual result and expected result.

Can you please help me with this?

2

@JyotiMalviya

It seems that you are experiencing an issue with the SetFormula method in the latest version (25.2.0) of Aspose.Cells, where it is setting the object value instead of the expected result of the formula.

To address this, please ensure that you are using the correct overload of the SetFormula method. The method signature you should be using is:

public void SetFormula(string formula, object value)

This method sets both the formula and the calculated result of that formula. If you are passing the formula correctly but still getting the object value instead of the expected result, consider the following:

  1. Formula Validity: Ensure that the formula you are trying to set is valid and can be calculated. Invalid formulas may lead to unexpected behavior.

  2. Calculation Mode: Check if the calculation mode of the workbook is set to automatic. If it is set to manual, the formulas may not be recalculated automatically. You can set it to automatic using:

    workbook.CalculateFormula();

  3. FormulaParseOptions: If you are using any specific parsing options, ensure they are correctly configured. You might want to try using the overload that accepts FormulaParseOptions to see if it resolves the issue.

Here is an example of how to set a formula with a calculated value:

// Assuming 'ws' is your worksheet and 'cell' is the cell object
ws.Cells[referenceRow, referenceColumn].SetFormula("SUM(A1:A10)", 55); // Example formula and expected result

If the issue persists after checking these points, it may be beneficial to revert to a previous version of Aspose.Cells where the functionality was working as expected, or to reach out to Aspose support for further assistance.

If you have any additional details or error messages, please share them for more targeted help.

3

@JyotiMalviya
Would you like to provide you sample file and runnable test code? We will check it soon.

4

@JyotiMalviya

It is the designed behavior for this api, please see the corresponding api reference: SetFormula(string, object).

Which version were you using before 25.2? We have tested some old versions but cannot find the issue. By default, the generated excel file will be re-calculated automatically when you open it in ms excel, so you should get the expected value(calculated result) instead of the provided one(when setting formulas).

For your issue, one possible reason may be that you opened the generated excel file with old versions of ms excel. There is one calculation id defined by excel’s file format specification to denote corresponding excel’s version for formula calculation. For old versions of Aspose.Cells, the saved calculation id may be for old ms excel versions, so when you open it with old versions of ms excel, those formulas can be re-calculated. When you using newer version of Aspose.Cells, the saved calculation id will be corresponding to newer version of ms excel. Then if you open the generated file with old version of ms excel, those formulas cannot be calculated automatically, so you can only see the saved value(you provided when setting formula) for those cells.

If so, you may set the property CalculateOnOpen as true before saving the workbook:

workbook.Settings.FormulaSettings.CalculateOnOpen = true;

If it is not your case, we think you may try to calculate the workbook(
CalculateFormula) before saving it.

Anyways, you need not to provide the calculated value when setting formulas if you do not need them.

5

Hello @johnson.shi Thank you for your response.

Issue of SetFormula is now resolved.

But one issue, we are still facing. That is calculation of formula when we open excel and do any specific operation. Scenario is as follow.

One cell contains dropdown list.
On selection of a particular value from that dropdown, a formula should get calculated for former cell, and corresponding value should get displayed over it.

But we had to manually click on that cell, and do OK for data validation, then value gets displayed for that cell.

Below is link of a video of this scenario.

Can you please help me in this?

6

@JyotiMalviya
Please try setting the formula calculation as an automatic option. formula_calculation.png (58.1 KB)

If the issue still cannot be solved, would you like to provide your sample file? Please compress the sample file to zip format and upload it here. We will check it soon.

7

Hi @John.He

I have tried this, but still facing the issue. With version 20.2 everything was working fine. After update we are facing this issue.

It will take time for me to prepare sample application.

Till then sharing below code which is being used for data validation.

private void SetUpCellValidation(Worksheet ws, int startRow, int endRow, int startColumn, int endColumn, WorksheetCell worksheetCell, OperatorType operatorType = OperatorType.None)
{
ValidationType validationType = GetValidationType(worksheetCell.CellType);
bool isCellDropdown = worksheetCell.CellType.Equals(“DROPDOWN”, StringComparison.InvariantCultureIgnoreCase);
if (string.IsNullOrEmpty(worksheetCell.CellAddress)) { return; }
Cell currentCell = ws.Cells[worksheetCell.CellAddress];
Validation currentValidation = currentCell.GetValidation();
if (currentValidation != null && currentValidation.InCellDropDown && !isCellDropdown)
{
GetReferenceRowColumn(worksheetCell.CellAddress, out int referenceRow, out int referenceColumn);
currentValidation.RemoveACell(referenceRow, referenceColumn);
}
ValidationCollection validations = ws.Validations;
CellArea ca = new CellArea
{
StartRow = startRow,
EndRow = endRow,
StartColumn = startColumn,
EndColumn = endColumn
};
Validation validation = validations[validations.Add(ca)];
validation.Type = validationType;
validation.Operator = operatorType;
if (!string.IsNullOrEmpty(worksheetCell.CellFormula) && worksheetCell.IsCellFormulaApplicable)
{
if (worksheetCell.CellFieldName == “FIXED DROPDOWN” && worksheetCell.CellFormula.Split(‘|’).Length > 0)
{
validation.Formula1 = string.Join(“,”, worksheetCell.CellFormula.Split(‘|’));
}
else
{
validation.Formula1 = “=” + worksheetCell.CellFormula;
}
//JsonConvert.DeserializeObject

}
// Set the in cell drop down.
validation.InCellDropDown = isCellDropdown;
if (worksheetCell.CellFormulaName.Length > 0)
{
    validation.IgnoreBlank = false;
    validation.ErrorMessage = worksheetCell.CellFormulaName;
    validation.ErrorTitle = "Invalid Entry";
    validation.ShowError = true;
    validation.AlertStyle = ValidationAlertType.Stop;
}

}

Can you please check this and help me?

Thanks.

8

@JyotiMalviya,

Thanks for the code snippet.

We will try to review it if we could trace the issue. It would be best you provide a standalone app and template Excel file(s) which will help us evaluate your issue precisely and to consequently figure it out soon.

9

Hi @amjad.sahi

Thank you for the response.

I am working on creating sample application.

Till then providing you this sample excel file. You can refer the formulas also.

Actually here on selection of a value from dropdown in worksheet ‘InitiativeDetails’, formula is being calculated using values from ‘UdfHierarchicalData’ worksheet.
SampleExcelFile.zip (48.8 KB)

Please have a look into it.

10

@JyotiMalviya
Thank you for your feedback and provided sample files. We will further investigate your issue. Once there is an update, we will notify you promptly. We will get back to you soon.

11

@JyotiMalviya
By testing on the latest version v25.2 using sample files and the following sample code, we can reproduce the issue. After saving the xlsb file in xlsx format through the cells API. If the xlsx file is saved again in xlsb format through Excel, the dropdown list will work properly. If the cells API is used to save xlsx files in xlsb format, the dropdown list in the result file needs to undergo data validation before it can be used properly. Please check the attachment. result.zip (92.1 KB)

Workbook workbook = new Workbook(filePath + "SampleExcelFile.xlsb");
workbook.Save(filePath + "SampleExcelFile_out.xlsx");

workbook = new Workbook(filePath + "SampleExcelFile_out.xlsx");
workbook.Save(filePath + "SampleExcelFile_out.xlsb");

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57995

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

12

Hi @John.He

Thank you for the update.

By when can I expect the next release?

13

@JyotiMalviya,

The next release, Aspose.Cells for .NET v25.3, is scheduled for release this week. We are working on your issue, and if it is resolved within the next couple of days, the fix will be included in this release. Otherwise, it will be addressed in later release(s). You will be notified once the issue is resolved.

14

@JyotiMalviya,

We are pleased to inform you that your issue has been resolved. The fix will be included in an upcoming release (Aspose.Cells for .NET v25.3) that we plan to release in the next few days this week. You will be notified when the next version is released.

15

@JyotiMalviya

The issues you have found earlier (filed as CELLSNET-57995) have been fixed in this update. Please try it.