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 (220.127.116.11).
In attach the (simplified) original file. There’s only validation in cells I25 and E42.
There’s also a small bug in 18.104.22.168 : the picture in our ‘logo’ disappears. No problem in 22.214.171.124
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 ,
Please try this fix. It solve the problems except the logo problem. I will check it tomorrow.
I'm still getting errors on the validation formula Laurence ? ?
The white lines are ok now.
I checked again and I'm really using version 126.96.36.199 everywhere in the project.
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();
Excel excel = new Excel();
The attached zip file includes the dll which also fix the image problem. And it includes book1.xls created with above code.
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 …
Finally found the problem …, 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 …
Thank your for this report. I will check this issue.
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
Please try this attached fix.
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 ... )
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.
Sorry. I did upload the old version. Please try this one. It's v188.8.131.52.
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' )
Please try this one.
Sorry Laurence, I hardly dare saying it , but I still have problems ....
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.
Thank you for the report. Globalization issue is always complex. Please try this version.
Yes ! As far as I see everything works perfect now. Thanks !