We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

No Excel Formula Validation In Excel

Hey,


I was trying to validate an excel formula using aspose.cells for .net version 7.0 as shown:

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add(“ValidationSheet”);
var cell = worksheet.Cells[“A1”];
cell.Formula = “=(6±*7)”;
workbook.CalculateFormula(false);
Console.WriteLine(cell.IsErrorValue);
Console.WriteLine(workbook.ValidateFormula("=(6±*7)"));

The output is
False
True

The formula seems to be valid to the library even though the syntax is invalid.

My Questions are:

1) What level of validation is done by Aspose ?
2) Is there something else we need to do in order to validate the formula ?
3) Is this a known bug which will be fixed some time in the future ?

Thanks,

Richa

Hi,

Thanks for pointing it out.

Yes, it looks to me a bug as it should not validate this formula and also should point out that it is an error. I have logged a ticket for it with an id:
CELLSNET-40315. We will look into it soon to figure it out.

Thank you.

Thanks for the reply.

And do notify me when the issue gets resolved.

Richa

Hey,


Has the error for validating formula being resolved? I have not received a reply yet… We are using aspose a a part of our project and looking forward to buy the product if it fulfills our requirements, so can you please let us know if the problem is going to get resolved soon, so that we can proceed further.

Thanks,
Richa

Hi,

We will throw Exception if the formula is invalid when calling Cell.Formula.

Hey,

As you can see above we are using Cell.Formula to put the formula in a particular cell but it does not throw an exception, so how do we validate it then?

Thanks,
Richa

Hi,

Please download and try this fix: Aspose.Cells for .NET v7.1.0.2

If the formula is invalid, an exception will be thrown when setting the formula by Cell.Formula.

Hey,

Thanks, formula validation now works with the new dll, but there is an exception in another part as shown:

var workbook = new Workbook();
workbook.Worksheets.Add(“A sheet”);
var worksheet = workbook.Worksheets[0];
worksheet.Cells.CreateRange(“A1”);
worksheet.Cells.Ranges[0].Name = “special_Name”;
worksheet.Cells[“A1”].Value = “Should Be read”;

The above code throws the exception as shown below:
System.ArgumentOutOfRangeException : Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Even after CreateRange(), the count of ranges is coming as zero, but before when I used version 7.0.0. it used to work fine. What is the problem then?

Thanks,
Richa

Hi Richa,


Please change your code as following:
var workbook = new Workbook();
workbook.Worksheets.Add(“A sheet”);
var worksheet = workbook.Worksheets[0];
worksheet.Cells.CreateRange(“A1”).Name = “special_Name”;
MessageBox.Show(workbook.Worksheets.GetNamedRanges().Length.ToString()); //1 OK
worksheet.Cells[“A1”].Value = “Should Be read”;

Also, check the topic for your complete reference:
http://docs.aspose.com/display/cellsnet/Named+Ranges


Thank you.

Hi,

We do not add the range to the worksheet.Cells.Ranges list when you call Cells.CreateRange for performance issue.

Hey,


Thanks, now it worked… :slight_smile:

Richa

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


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