Hmm ok let me give it a try. -----Original Message----- From: Thomas Jiang [mailto:[email protected]] Sent: Thursday, August 02, 2012 11:03 PM To: POI Users List Subject: Re: POI 3.8 - XLS - Data Validation
You can use one cell to calculate SUM(I8,J8,K8,L8)<=100, and another cell to calculate ISNUMBER(J8), and then calculate what you want based on the above two cells. On Thu, Aug 2, 2012 at 9:32 AM, Abhay B. Chaware < [email protected]> wrote: > thanks thomas.. about your suggestion, can you give more details about > it? what do u mean by separating into multiple columns and combining them > together? > > > Thomas Jiang <[email protected]> wrote: > > > I found the issue is with Excel Office 2007. It will work in Excel Office > 2010. > > Also, you may want to separate your complex formulas into several different > columns, and combine them together. That works for me in 2007. > > On Thu, Aug 2, 2012 at 12:37 AM, Abhay B. Chaware < > [email protected]> wrote: > > > 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 > > > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
