Cell object - Formula and Value setters are interfering one with the other

Aspose.Cells Product Family
1
Hello,

I have encountered a problem after a recent update for Aspose.Cells for .NET, from version 6.0.0.0 to 7.0.2.0.

The problem is related to cell formulas/values - in this new version, it seems that the Formula and Value property setters from the Aspose.Cells.Cell class are now excluding each other reciprocal.

My problem resumes to this:
1. The value of one cell is calculated through the ICustomFunction.CalculateCustomFunction mechanism.
2. After the Workbook.CalculateFormula(...) call is finished, on the involved cell I make another cell.Formula = "=SOMEFORMULA(...)". This overrides the value computed at the previous step.

I've tried setting back the Value after the cell.Formula call, by trying both cell.Value = ... and cell.PutValue(...), but this turns into the cell not being recognized as a formula anymore (cell.IsFormula returns false, cell.Formula returns null).

What I want is a way to specify both Formula and Value for a Cell, without interference between the two.

Thank you in advance,
Mihai Andrei
2

Hi,


Kindly create a demo program (a sample console application would be perfect, you may zip it) to show the issue, we will check it soon.

Thank you.
3

Hello,

I’ve attached a zip with a small test console application - the problem reduces to tests on the Cell.Formula setter, Cell.Value setter / Cell.PutValue(…).

The tests are in the CellFormulaTest class, methods:
- TestFormulaSetter() - after the Formula setter call, the cell value is set to NULL !
- TestValueSetter() - after the Value setter call, the cell formula is set to NULL !
- TestPutValue() - after the PutValue(…) method call, the cell formula is set to NULL !

Thank you,
Mihai

4

Hi,


Thanks for the project.

Well, you misunderstand the concept. Your code works fine as it should, same as MS Excel. For your information, when you use Cell.Value or Cell.PutValue API, the existing formula would be replaced with the value you enter (using Cell.Value or Cell.PutValue API). You may confirm this in MS Excel. For example, you have a cell with formula, now when you enter a value (by double clicking on the cell in edit mode) into it, it will replace the formula with the value you enter.

For your methods:

- TestFormulaSetter() - after the Formula setter call, the cell value is set to NULL !
Please call Workbook.CalculateFormula() method again before retrieving the cell value etc., so you final method would like:
public void TestFormulaSetter()
{
Console.WriteLine(Environment.NewLine);
Console.WriteLine(">> Test Formula Setter");

// calculate the formulas in the workbook
workbook.CalculateFormula(true);
string cellType;
object cellValue = GetCellValueAndType(currentWorksheet.Cells[“A10”], out cellType);
Console.WriteLine(“Cell value is: ‘{0}’, of type: ‘{1}’.”, cellValue ?? “NULL”, cellType);

// now set the formula again on the cell - the Value is now set to NULL
currentWorksheet.Cells[“A10”].Formula = cellFormula;

// calculate the formulas in the workbook
workbook.CalculateFormula(true);

cellValue = GetCellValueAndType(currentWorksheet.Cells[“A10”], out cellType);
Console.WriteLine(“After Formula Setter, cell value is: ‘{0}’, of type: ‘{1}’.”,
cellValue ?? “NULL”, cellType);
}

- TestValueSetter() - after the Value setter call, the cell formula is set to NULL !
It works accordingly as I described/mentioned in the above paragraph.

- TestPutValue() - after the PutValue(…) method call, the cell formula is set to NULL !
It works accordingly as I described/mentioned in the above paragraph.

5

Hello,

In the 6.0.0.0 version, this code was not causing any problems, maybe it was not fully implemented to work as Excel, as you’ve just described…

I was using it this way as a work-around in a particular scenario.

The question is: can I somehow set both Formula and Value for a cell ?, maybe there is a work-around for this…

If not, I have another question: can the ICustomFunction mechanism be extended so that Aspose calls CalculateCustomFunction(…) for Excel formulas as well ? E.g. CELL function.

Thanks a lot,
Mihai

6

Hi,


We need to investigate if this is possible by using ICustomFunction. I have logged a ticket with an id: CELLSNET-40112. We will look into it soon. Once we have any update, we will let you know here.

Thank you.
7

Hi,

After evaluating it, we come to conclusion.

  1. We will add a new method Cell.SetFormula(string formula, object value) method to set the formula and the value of the formula altogether.

  2. If the function is Excel’s built-in function, we will not call ICustomFunction. So, if you find any issue of CELL function, please post your sample codes, we will check it soon.

Thank you.

8

Hi,

Please try the attached version/fix: v7.0.3.2, we have added Cell.SetFormula(string formula, object
value) method for your need.

Thank you.

9

Hello,

Thanks for the feedback. Option 1) should be of great help - do you have an approximate estimate on when this will available for download ? LATER EDIT: I saw you’ve already provided a version, thanks, I will try it.

For 2), indeed with CELL function we’ve encountered some problems. I’ve attached an archive containing 2 files made when doing tests for CELL formula:
- CELL.xlsx , the file containing tested CELL formulas
- Results.xlsx, the file with the results: Excel result vs. Aspose result.

Thanks,
Mihai

10

Hi,


Thanks for your feedback, let us know about 1)

For 2), thanks for providing us the sample files with details (compared results of Aspose Vs Excel). We have logged your feedback and will look into it soon.

Thank you.
11

Hi,

We can fix most of the issues except about “Format”.

Could you share us some codes about “Format” as you can implement it in your custom function?

We will check it soon.

Thank you.

12

Hello,

I’ve managed to make a small test on the solution you provided - it seems ok so far. I will get back to you next week if I encounter other problems with it. Thanks a lot.

Question: I presume this feature (Cell.SetFormula(string
formula, object value)) will be included from now on in all future releases, correct ?

Regarding your previous question, I don’t understand it clearly - if you are relating to .NET code, I cannot share existing code, since it is not my intellectual property…

Thanks again.

13

Hi,


Good to know that you have sorted it out.

Q1) Yes, the functionality of the fix would be included in the up coming releases.

And, yes we mean it.

Thank you.
14

Hi,

After closely looking into your template file, we have a solution to calculate Function Cell (“format”,).

But we could not find the way to calculate the format “d/d$/yyyy” as “C13” in the cell “C85”.

Could you share some comments about it?

Thank you.

15

Hello,

I’ve found a small issue with Cell.SetFormula(string formula, object value): when value is String.Empty, after calling this function, Cell.Formula evaluates to null and Cell.IsFormula to false.

For the cell(“format”) option, I’ve reattached the CELL.xlsx test file, with 2 more columns: Differences and Notes. Indeed, for format option, CELL function seems to be implemented in Excel different than what the format says ! The conclusions I’ve reached to are present in the Notes column.

Thanks.

16

Hi,

We have calculated most of formulas in your template file as MS Excel except the cell “C85”.



a) It seems MS Excel will calculate Fraction number format in CELL function as “G”. We will work as MS Excel now .

b) Cell “C132”:

MS Excel is right.

The number format of “B132” is #",000,000". The actual number format is #, others part of the format is just simple appended string. So there is no group symbol.

c) Cell “C107”, “C143”:

We think the result of Ms Excel is right.

Only if there is () in positive number format (before semicolon) of the number format, () will be appended to the end in calculating CELL function.

17

Hi,

Please try the attached fixed version v7.0.3.3.

a) We have fixed the issue of setting formulas.

b) We have calculated most of formulas in your template file as MS Excel except the cell “C85”.

18

Hello,

I’ve tried the attached version, it seems ok.

Thanks.

19

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.