Data Validation Formula

Aspose.Cells Product Family
1

Hello,

I get errors whenever I use a data validation formula in the original spreadsheet. In my test I only open and save the file again, without changing any cells. I thought 'cause one of the formula’s was a little harder, but even ‘between 0 and 0.5’ raises errors. I get the error 'Invalid formula in data validation settings’
I tested it with the latest version (3.4.0.0).
In attach the (simplified) original file. There’s only validation in cells I25 and E42.

There’s also a small bug in 3.4.0.0 : the picture in our ‘logo’ disappears. No problem in 3.2.4.1

And another small bug (while I’m giving feedback … ) ( also in 3.2.x ) : (some) white lines become black lines ( E36 - F41 )

Hope you can provide a solution for the validation error, 'cause we need this.

Kind regards ,

Luc

2

Hi Luc,

Please try this fix. It solve the problems except the logo problem. I will check it tomorrow.

3

I'm still getting errors on the validation formula Laurence ? ?Tongue Tied
The white lines are ok now.

I checked again and I'm really using version 3.4.0.2 everywhere in the project.

Luc

4

When does the error occurs? Could you please post your sample code here?

The following is my test code:

Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");

Excel excel = new Excel();

excel.Open("d:\\test.xlt);
excel.Save("d:\\book1.xls");


The attached zip file includes the dll which also fix the image problem. And it includes book1.xls created with above code.

5

Indifferent Strange … When I try it in my actual project ,( with a few lines commented ), I still get errors on the validation formula. But the picture is ok ( when I try it with the same file without validation ).
I just created a small test project, and there everything works fine ? ?

Gonna try to find out why it doesn’t work in my actual project …

Luc

6

Finally found the problem …[+o(], took me hours to debug. It did work on a new test page in my application, but it didn’t work no way where it had to work … Completely stripped down the application ( a copy of course … ) and I got it :
just add the line

Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(“nl-be”)
to your test aplication …
The point is , this sets “,” as decimal symbol, and “.” as grouping symbol. And I know you have to take care of this e.g. on building sql syntax by code. And I guess that a problem raises when you write back the validation formula ‘between 0 and 0.5’. Something will go wrong with the interpretation of the decimal symbol.
Please take care of this, because this can create a lot of problems, very hard to debug. I guess you didn’t found any problem when testing ?

A very tough one, but an important one …

Luc

7

Hi Luc,

Thank your for this report. I will check this issue.

8

Hello Laurence,

still no solution for 'my' data validation formula problem ? I just downloaded the latest version, tried it in my test-project but still got errors. I really need this validation quite soon now. If you can't fix the problem I can try referring to other cells for validation , but I didn't think it would be so hard to fix.

Kind regards, Luc

9

Please try this attached fix.

10

No sorry, still got error # 5 : "Invalid formula in data validation settings."

You didn't give by mistake the wrong dll, 'cause version and date are identical as the one I downloaded ? ( by the way, I think my test yesterday was wrong too,because I used the old dll ( I mean, referrenced the new one, but after compilation the program kept on using the old one), but I'am sure I use the new one this time ... )

Luc

11

When I change the validation formula 'between 0 and 0.5' in cell I25 , to 'between 0 and K25' ( and I put 0.5 in cell K25) this works, so I get a workaround. But ... it seems that my validation formula in cell E42 also gets corrupted ; it reads now max. =#REF!

So it seems that not only the decimal symbol is the problem. ( But this error doesn't prevent me to save, 'only' the validation doesn't work.

Luc

12

Hi Luc,

Sorry. I did upload the old version. Please try this one. It's v3.5.2.1.

13

The validation 'between 0 and 0.5' works perfect now, but the validation in cell E42 still gets corrupted ( 'between 0 and (C44+C45)/2-D42' )

Luc

14

Please try this one.

15

Sorry Laurence, I hardly dare saying it , but I still have problems ....Tongue Tied [:S]

Now I get error # 91 : "Object reference not set to an instance of an object." when I save the sheet. But, if I skip the line Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("nl-be")

no problem. And the validation is perfect then.

Luc


16

Hi Luc,

Thank you for the report. Globalization issue is always complex. Please try this version.

17

Yes ! Big Smile [:D] As far as I see everything works perfect now. Thanks !

Luc