Monday, February 18, 2013

Create xls with drop down and Constrained Text for length using poi

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;

public class SDandLength {

/**
 * @param args
 * @throws IOException
 */
public static void main(String[] args) throws IOException {

DataValidation dataValidation1 = null;
DataValidationConstraint constraint1 = null;
DataValidationHelper validationHelper = null;

 HSSFWorkbook wb = new HSSFWorkbook();
 HSSFSheet sheet1=(HSSFSheet) wb.createSheet("sheet1");


    validationHelper=new HSSFDataValidationHelper(sheet1);
    // parameters are first row, last row, first col, last col
    CellRangeAddressList addressList1 = new  CellRangeAddressList(1,5,1,1);
   
    constraint1 =validationHelper.createExplicitListConstraint(new String[]{"Very High","High","Medium", "Low", "Very Low"});
    dataValidation1 = validationHelper.createValidation(constraint1, addressList1);
     // dataValidation.setSuppressDropDownArrow(false);     
    sheet1.addValidationData(dataValidation1);
   
    //  to set a default value, just setCellValue("first_item_value");
    //  sheet.getRow(index).getCell(index).setCellValue("my_default_value");

   
    DataValidationHelper validationHelper2=new HSSFDataValidationHelper(sheet1);
    DataValidationConstraint constraint2 = null;
 
    DataValidation dataValidation2 = null;
    // (ComparisonOperator.GT,"xxxx",null)     xxxx number is the upper limit
    // will show dialog if the length is greater than or equal to 5
    constraint2 =validationHelper.createTextLengthConstraint(ComparisonOperator.GT,"5",null);
    CellRangeAddressList addressList2 = new  CellRangeAddressList(1,5,0,0);
    dataValidation2 = validationHelper2.createValidation(constraint2, addressList2);
    sheet1.addValidationData(dataValidation2);
   

    FileOutputStream fileOut = new FileOutputStream("c:\\test\\drops1.xls");
    wb.write(fileOut);
    fileOut.close();
    System.out.println("Done");
    }
}

No comments: