Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0

    Default Dependency based data validation in excel using POI

    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!

  2. #2
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

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

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

    Quote Originally Posted by theholychicken View Post
    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();
    }
    }

    }

  4. #4
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

    If, when you load the list does not put space to "Alternate" and in the validation if you put " Alternate"
    PHP Code:
    String [] {"Daily", "Alternate", "Weekly"});
    PHP Code:
    "IF (A1 = \" Alternate \ "
    should be well
    PHP Code:
    "IF (A1 = \"Alternate\"

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

    Quote Originally Posted by arielb View Post
    If, when you load the list does not put space to "Alternate" and in the validation if you put " Alternate"
    PHP Code:
    String [] {"Daily", "Alternate", "Weekly"});
    PHP Code:
    "IF (A1 = \" Alternate \ "
    should be well
    PHP Code:
    "IF (A1 = \"Alternate\"

    Thanks but its still not working :-(

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,797
    Rep Power
    19

    Default Re: Dependency based data validation in excel using POI

    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.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

    Quote Originally Posted by Tolls View Post
    Isn't this really an Excel question?
    Yes,so is.
    I tried this and it worked.

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

  8. #8
    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0

    Default Re: Dependency based data validation in excel using POI

    Quote Originally Posted by arielb View Post
    Yes,so is.
    I tried this and it worked.

    PHP Code:
    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.

Similar Threads

  1. Data Validation help
    By donewithmytime in forum Advanced Java
    Replies: 1
    Last Post: 01-28-2012, 04:25 PM
  2. data type validation
    By cagipple in forum New To Java
    Replies: 2
    Last Post: 12-14-2011, 07:57 AM
  3. Excel Sheet Validation
    By Raghuraman K in forum Advanced Java
    Replies: 3
    Last Post: 09-27-2010, 01:35 PM
  4. Replies: 0
    Last Post: 09-01-2008, 11:57 AM
  5. Adding Plugin Dependency for Feature-based Product
    By abiieez in forum SWT / JFace
    Replies: 2
    Last Post: 05-27-2008, 08:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •