How to set Validation for a cell in Excel created using NPOI

Question

I have created an excell file using NPOI using following code

            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("Candidate");

            // Add header labels
            var rowIndex = 0;
            var row = sheet.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue("Name");
            row.CreateCell(1).SetCellValue("1,2,3");
            row.CreateCell(2).SetCellValue("4,5,6");
            row.CreateCell(3).SetCellValue("7,8,9");
            rowIndex++;


            // Add data rows
            for (int i = 1; i <= 5; i++)
            {
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Candidate" + i.ToString());
                row.CreateCell(1).SetCellValue("");
                row.CreateCell(2).SetCellValue("");
                row.CreateCell(3).SetCellValue("");
                rowIndex++;
            }

I just wanted to add some validation in each cell. For eg: restrict cell 2 with inputs only 1,2,3

In Excel we can Set Data Validation to whole number and can specify Min and Max Value.

Any idea for achieving this will be a great help.

Answer 1

I find out this and working greatly with following code.

    var markConstraint = DVConstraint.CreateExplicitListConstraint(new string[]{"1","2","3"});
    var markColumn = new CellRangeAddressList(1, 5, 1, 1);
    var markdv = new HSSFDataValidation(markColumn, markConstraint);
    markdv.EmptyCellAllowed = true;
    markdv.CreateErrorBox("Wrong Value", "Please Enter a correct value");
    sheet.AddValidationData(markdv);