Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: pass (nike.apache.org: domain of nawanit@amazon.com designates
 72.21.196.25 as permitted sender)
From: "Nawanit, Niraj" <nawanit@amazon.com>
To: POI Users List <user@poi.apache.org>,
        "dev@poi.apache.org"
	<dev@poi.apache.org>
Date: Sat, 11 Feb 2012 00:22:25 +0530
Subject: RE: problem while implementing dependent list in HSSF
Thread-Topic: problem while implementing dependent list in HSSF
Thread-Index: AczoDOa977Yz61ThTDiyNf1+K5FXmgAF5cKA
Message-ID: 
 <9CC8D6E4804E234A827D1782AE3DB7782EDDDB407A@EX-MAIL-HYD1-1.ant.amazon.com>
References: <CB5B3D40.1A2DB%nawanit@amazon.com>
In-Reply-To: <CB5B3D40.1A2DB%nawanit@amazon.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_9CC8D6E4804E234A827D1782AE3DB7782EDDDB407AEXMAILHYD11an_"
MIME-Version: 1.0

--_000_9CC8D6E4804E234A827D1782AE3DB7782EDDDB407AEXMAILHYD11an_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Re-indented the code.


Thanks

Niraj


-----Original Message-----
From: Nawanit, Niraj
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF


Hi all,


I am trying to implement dependent list on a column for XLS format and XLSX=
 format. For this I have to use reference to another cell in same row insid=
e the formula. I am using apache POI 3.7.


I am using formula as such: "=3DINDIRECT(UPPER($A7))" for list for 7th row =
onwards in 3rd column. I am facing weird problem. For HSSF implementation, =
in Office 2003 and office 2007, for every row, formula refers $A$7. In Offi=
ce 2010 I have not tested as of now. I want A8 to be referred for 8th row a=
nd A9 to be referred for 9th row and so on. In XSSF implementation, this wo=
rks quite well.


The same problem I am facing for conditional formatting too.


Can someone please help? I have provided my implementation below.


Thanks in advance!

Niraj


import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.util.CellRangeAddressList;


import org.apache.poi.hssf.usermodel.DVConstraint;

import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;


public static void addDropdown(Sheet sheet, String formula, int column, boo=
lean isXSSF) {

                CellRangeAddressList cellRangeAddressList =3D new CellRange=
AddressList(7, 500, column, column);

                DataValidationConstraint dvConstraint;

                If (isXSSF) {

                                dvConstraint  =3D new XSSFDataValidationCon=
straint(DataValidationConstraint.ValidationType.LIST, formula);

                } else {

                                dvConstraint =3D DVConstraint.createFormula=
ListConstraint(formula);

                }

                DataValidation dataValidation =3D sheet.getDataValidationHe=
lper().createValidation(dvConstraint, cellRangeAddressList);

                dataValidation.setShowErrorBox(true);

}


public static void main(String[] args) {

                Sheet sheet =3D ... // comes from somewhere

                addDropdown(sheet, "=3DINDIRECT(UPPER($A7))", 2, false); //=
 for hssf

                // addDropdown(sheet, "=3DINDIRECT(UPPER($A7))", 2, true); =
// for xssf

}


--_000_9CC8D6E4804E234A827D1782AE3DB7782EDDDB407AEXMAILHYD11an_--