poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Jiang <thomasji...@geotab.com>
Subject Re: POI 3.8 - XLS - Data Validation
Date Thu, 02 Aug 2012 13:14:39 GMT
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 <
Abhay.Chaware@kpitcummins.com> 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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message