An example file attached showing below implementation. Also is there a way to read xls binary file?
Thanks Niraj -----Original Message----- From: Nawanit, Niraj Sent: Friday, February 10, 2012 9:32 PM To: [email protected]; [email protected] 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 }
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
