ValidateFormula Method

Aspose.Cells Product Family
1

I would like to know if the Aspose.Cells.Workbook.ValidateFormula Method is working properly.


I have found this post, raising the issue :

It says it has been resolved but I don’t think the method itself was fixed.

Should I use a try/catch on the Cell.Formula value? Cause the ValidateFormula method does not seem to validate anything, it always return true.

Thank you for looking into this. If not working properly, the method should probably be removed to avoid confusion.




2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us some sample code with one valid and one invalid formula so that we run the code at our end and investigate if the validate formula is working properly or not.

Please also download and try the latest version: Aspose.Cells
for .NET v8.4.0.4 and see if it makes any difference and resolves this issue.

3

Hi,


"=SUM(A1+" the method would correctly return FALSE

"=SUM(A1+A2" the method would incorrectly return TRUE, but it would crash when pushing this value in the .Formula property. The correct formula being of course "=SUM(A1+A2)"

Thanks
4

Hi,


You are right, Workbook.ValidateFormula() method does not validate all types of formulas fine. Well, in the newer model of Aspose.Cells, the component would throw exception of invalid formula when you specify invalid formula for a cell in the worksheet, so you do not need to utilize Workbook.ValidDateFormula() method to do the validation. You should simply use try/ catch block to evaluate it, see the sample code below for your reference:
e.g
Sample code:

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add(“ValidationSheet”);
var cell = worksheet.Cells[“A1”];
try
{
cell.Formula = “=SUM(A1+A2”;
}
catch (CellsException ce)
{
MessageBox.Show("The formula is not valid: " + ce.Message);
}

If you still want us to evaluate it by Workbook.ValidateFormula() method, we may look into it and fix the issue in the method though.

Thank you.
5

Allright, i’ll use the try/catch.


But I still think you should either fix the ValidateFormula method or remove it entirely to avoid misleading users.

Thanks a lot for your excellent service.
6

Hi,


Ok, we will check if we could enhance the Workbook.ValidateFormula() method or exclude it from the APIs list.

We will get back to you soon.

Thank you.
7

Hi,


As I already observed the issue as you mentioned. I found the Workbook.ValidateFormula() method does not validate the formula “=SUM(A1+A2” fine, it should return False but it reruns True, see the sample code below for your reference:
e.g
Sample code:

var workbook = new Workbook();
Console.WriteLine(workbook.ValidateFormula("=SUM(A1+A2"));//True - Not Ok
Console.WriteLine(workbook.ValidateFormula("=SUM(A1+")); //False - Ok

I have logged a ticket with an id “CELLSNET-43533” for your issue. We will check if we could enhance the Workbook.ValidateFormula() method or exclude it from the APIs list as it does not always validate the formulas fine. Our concerned developer will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
8

Hi,

Thanks for using Aspose.Cells.

We have obsoleted this method so it will be removed in our future releases after a year.