POI 3.8 - XLS - Data Validation

["Abhay B. Chaware" <Ab...@kpitcummins.com>]

Hi
I am generating an xls file and inserting data validations for some cells using POI.  There are 2 types of validations that I am inserting.

1)  simple validations             e.g.   =ISNUMBER(J8)=TRUE
2)  little complex validation e.g.   =AND((SUM(I8,J8,K8,L8)<=100),ISNUMBER(J8))=TRUE

Simple validations work with no problems. But if I add the complex validations like the one showed above, in generated xls, it shows validation alert message even if the values are correct.

What I also observed that, on the generated xls, if I click on the cell with validation and then click on "Data Validation" to edit the validation formula , it shows me the correct formula. Now if I save it, without changing anything, and then test, the formula works perfectly.

What could be happening ? is there any way  to compare what xls internally stores for the validation formula value after editing and saving, and how is it different from what I am assigning using the POI code ?  Point to be noted is that the validation formula is exactly the same in all these cases :
1)  Before inserting in the xls in POI code
2)  After opening up the generated xls and opening up the "data validation" box
3)  After saving ( without any changes ) and opening the "data validation" box again.

Also please note that, I am using Apache POI 3.8 and generating xls ( not xlsx ) and viewing the xls in office 2007.

If I generate xlsx version instead of xls, the generated validation works perfectly in the first shot.

Please help.
-abhay