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);

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

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

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.




Sep 21, 2009 at 8:16 PM

you can try using HSSF.Util.HSSFDataValidation class. here is a post about the corresponding POI class:

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);

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

             row0= sheet2.CreateRow(2);
             cell0= row0.CreateCell(4);
            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);