List and dataValidation

Sep 20, 2009 at 5:54 PM

Great library - thanks to all who have worked so hard to put this together.

Is there any way to create a list and dataValidation?

I'd like to put a list on the second sheet, then grab the range to use for a listbox on the first sheet.

               HSSFSheet sheet2 = hssfworkbook.GetSheet("Sheet2");
                HSSFRow row = sheet2.CreateRow(0);
                HSSFCell cell = row.CreateCell(4);
                cell.SetCellValue("Product1");

                 row = sheet2.CreateRow(1);
                 cell = row.CreateCell(4);
                cell.SetCellValue("Product2");

                 row = sheet2.CreateRow(2);
                 cell = row.CreateCell(4);
                cell.SetCellValue("Product3");

something like:

HSSFName range = hssfworkbook.CreateName();
                range.Reference = "Sheet2!$E1:$E3";
                range.NameName = "range1";

and then use that list on sheet1.

 

Thanks for any help.

 

 

 

Coordinator
Sep 21, 2009 at 8:16 PM

you can try using HSSF.Util.HSSFDataValidation class. here is a post about the corresponding POI class: http://www.nabble.com/Using-HSSFDataValidation-for-list-constraints-td21914801.html

Sep 22, 2009 at 11:37 PM

Thank you for the quick reply. That helped immensely.

 I banged this together for a test, and it worked. The range from Sheet2 is displayed on Sheet1.

            HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1");
                                   
            HSSFSheet sheet2 = hssfworkbook.GetSheet("Sheet2");
            HSSFRow row0 = sheet2.CreateRow(0);
            HSSFCell cell0 = row0.CreateCell(4);
            cell0.SetCellValue("Product1");

             row0= sheet2.CreateRow(1);
             cell0= row0.CreateCell(4);
             cell0.SetCellValue("Product2");

             row0= sheet2.CreateRow(2);
             cell0= row0.CreateCell(4);
             cell0.SetCellValue("Product3");
          
            NPOI.HSSF.Util.CellRangeAddressList rangeList = new   NPOI.HSSF.Util.CellRangeAddressList();
                 
            rangeList.AddCellRangeAddress( new NPOI.HSSF.Util.CellRangeAddress(1,100,11,11));
            DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3");
            NPOI.HSSF.Util.HSSFDataValidation dataValidation = new
                    NPOI.HSSF.Util.HSSFDataValidation(rangeList, dvconstraint);
          sheet1.AddValidationData(dataValidation);