Dependency based data validation in excel using POI

[theholychicken]

Hello,
I am looking to generate an excel with dependent validation checks.
For instance, cell A1 would have options Colours and Fruits
Cell A2 will display values based on what was chosed in Cell A1. So if cell A1 is selected as Colours, then Cell A2 will have a list of colours, otherwise fruits.

It would be great if you can guide me further on this.

Thank you!

[arielb]

hi, welcome the forum.
check this HSSF and XSSF Examples

[theholychicken]

Hello,
I am looking to generate an excel with dependent validation checks.
For instance, cell A1 would have options Colours and Fruits
Cell A2 will display values based on what was chosed in Cell A1. So if cell A1 is selected as Colours, then Cell A2 will have a list of colours, otherwise fruits.

It would be great if you can guide me further on this.

Thank you!

Thanks but I wasn't able to find what I was looking for.

I tried the following but the 2nd drop down(validation using the if statement) is not working. Any idea why?

package com.poi;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;



public class sd {

/**
* @param args
*/
public static void main(String[] args) {
try {
FileOutputStream fileOut = new FileOutputStream("pois-test.xls");
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"Daily", "Alternate", "Weekly"});
DataValidation dataValidation = new HSSFDataValidation
(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);


Row row1 = sheet.createRow((int) 1);

Cell cellA1 = row1.createCell((int) 3);
cellA1.setCellValue("M-W-F");
cellA1=row1.createCell((int) 4);
cellA1.setCellValue("T-T-S");

addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = DVConstraint.createFormulaListConstraint("IF(A1=\" Alternate\",'Sheet1'!$D$2:$E$2, \"Unknown\")");
dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);


workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

}

[arielb]

If, when you load the list does not put space to "Alternate" and in the validation if you put " Alternate"

String [] {"Daily", "Alternate", "Weekly"});

"IF (A1 = \" Alternate \ "

should be well

"IF (A1 = \"Alternate\"

[theholychicken]

If, when you load the list does not put space to "Alternate" and in the validation if you put " Alternate"

String [] {"Daily", "Alternate", "Weekly"});

"IF (A1 = \" Alternate \ "

should be well

"IF (A1 = \"Alternate\"

Thanks but its still not working :-(

[Tolls]

Isn't this really an Excel question?
I mean, surely you need to get this working simply from writing it in Excel and then think about how to write it in Java/POI.

[arielb]

Isn't this really an Excel question?

Yes,so is.
I tried this and it worked.

DVConstraint.createFormulaListConstraint("IF(A1=\"Alternate\",'Sheet1'!$D$2:$E$2, \"Unknown\")"

[theholychicken]

Yes,so is.
I tried this and it worked.

DVConstraint.createFormulaListConstraint("IF(A1=\"Alternate\",'Sheet1'!$D$2:$E$2, \"Unknown\")"

I was trying it on excel 2010 and hence it didn't work for me! Thats very strange.