poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nawanit, Niraj" <nawa...@amazon.com>
Subject RE: problem while implementing dependent list in HSSF
Date Fri, 10 Feb 2012 18:52:25 GMT
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 inside the formula. I am using apache
POI 3.7.



I am using formula as such: "=INDIRECT(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 Office 2010 I have not tested as of now. I want A8 to be referred
for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works
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, boolean isXSSF) {

                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500,
column, column);

                DataValidationConstraint dvConstraint;

                If (isXSSF) {

                                dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,
formula);

                } else {

                                dvConstraint = DVConstraint.createFormulaListConstraint(formula);

                }

                DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint,
cellRangeAddressList);

                dataValidation.setShowErrorBox(true);

}



public static void main(String[] args) {

                Sheet sheet = ... // comes from somewhere

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

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

}



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