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
