Conditional Data Validation on an Excel Sheet with NPOI-CodePudding

I'm trying to do an excel where to import some data with validations and I've never used npoi before (and my knowledge of excel is as user) so I'm kinda lost about my objective here and neither founded a straight answer on internet.

What I have is a Data sheet with three columns A, B and C.

Column A has two rows -> 'Option1', 'Option2'
Column B has five rows-> 'A', 'B', 'C', 'D', 'E'
Column C has two rows -> '1', '2'

Then I have another sheet where I want to input my validations. It has two columns:

Option -> here validation should be over column A (Option1 or option2)
Result -> here validation should be over 'option' selection.
- If I input 'option1' then Column2 list should be displayed
- If I input 'option2' it should have Column3 inputs.

Something like this:

My code is this. As you can see my formula constrain only is over columnB data because I'm not even able to do it over two columns ( I suppouse formule should be something like this: ='Data'!$C$2:$C$3;'Data'!$B$2:$B$6 but it doesn't work)

internal static void Validations(ISheet sheet, int countLim)
    {
        // Data Validation 
        XSSFDataValidationHelper validationHelper = new((XSSFSheet)sheet);
        // Option Validation
        CellRangeAddressList optionList = new(1, countLim - 1, 1, 1);
        XSSFDataValidationConstraint tclconstraint = (XSSFDataValidationConstraint)validationHelper.CreateFormulaListConstraint("=Data!$A$2:$A$3");
        XSSFDataValidation tclvalidation = (XSSFDataValidation)validationHelper.CreateValidation(tclconstraint, optionList);
        sheet.AddValidationData(tclvalidation);
        // Result Validation
        CellRangeAddressList resultList = new(1, countLim - 1, 2, 2);
        XSSFDataValidationConstraint opconstraint = (XSSFDataValidationConstraint)validationHelper.CreateFormulaListConstraint("=Data!$B$2:$B$6");
        XSSFDataValidation opvalidation = (XSSFDataValidation)validationHelper.CreateValidation(opconstraint, resultList);
        sheet.AddValidationData(opvalidation);
        
    }

Thank you!

CodePudding user response：

I finally could achieve my objective via java poi documentation (which is more extensive than npoi in my opinion). What I finally did is this:

//ColumnA/B List Range 
var namedRange = workbook.CreateName();
namedRange.NameName = "Columns";
string reference = "Data!$A$1:$B$1";
namedRange.RefersToFormula = reference;

//List when option 1
namedRange = workbook.CreateName();
namedRange.NameName = "1Options";
reference = "Data!$A$2:$A$6";
namedRange.RefersToFormula = reference;

//List when option 2
namedRange = workbook.CreateName();
namedRange.NameName = "2Options";
reference = "Data!$B$2:$B$3";
namedRange.RefersToFormula = reference;

//Conditional Validation
var rowNumber = 2;
for (int i = 0; i <= totalRows; i  )
{
    sheet.ActiveCell = new CellAddress("A"   rowNumber);
    var dvHelper = sheet.GetDataValidationHelper();
    var dvConstraint = dvHelper.CreateFormulaListConstraint("Columns");
    CellRangeAddressList checkList = new(1, totalRows - 1, 1, 1);
    var tclvalidation = dvHelper.CreateValidation(dvConstraint, checkList);
    sheet.AddValidationData(tclvalidation);

    dvConstraint = dvHelper.CreateFormulaListConstraint("INDIRECT($A$"   rowNumber   ")");
    CellRangeAddressList conditionList = new(1, totalRows - 1, 2, 2);
    tclvalidation = dvHelper.CreateValidation(dvConstraint, conditionList);
    sheet.AddValidationData(tclvalidation);

    rowNumber  ;
}

Notice that I finally made only two columns and use as first validation column's title. So Column A dissapeared and Column B title was converted to Option 1 and Column C title to Option 2.

Also, I finally worked with two sheets. So list ranges were made at a sheet called 'Data' and the validations on the sheet object