No Excel Formula Validation In Excel

Aspose.Cells Product Family
1

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

2
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.
3

Thanks for the reply.

And do notify me when the issue gets resolved.

Richa
4

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
5

Hi,

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

6

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

7

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.

8

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

9

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.
10

Hi,

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

11

Hey,


Thanks, now it worked… :slight_smile:

Richa
12

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.