Error adding Data Validation List to Excel With OfficeOpenXml

Question

I am trying to add a data validation list to excel using OfficeOpenXml on D365fo but when the formula is assigned its throw an error.

worksheetTo = packageTo.get_Workbook().get_Worksheets().get_Item(1);
cellsTo = worksheetTo.Cells.get_Item(2, 2, totalRows, 2);
validation = worksheetTo.DataValidations.AddListValidation("B:B");
OfficeOpenXml.Datavalidation.Formulas.Contracts.IExcelDataValidationFormula formula = validation.Formula;
formula.ExcelFormula = "=Feuil2!$A:$A";

packageTo.Save();
file::SendFileToUser(streamTo, strDel(textFile, strLen(textFile) -4, 5) + "T.xlsx");

Error message:

Exception User-Unhandled

System.MethodAccessException: 'Attempt by method 'Dynamics.AX.Application.GMExcelTransformation.`run()' to access method 'OfficeOpenXml.DataValidation.ExcelDataValidationWithFormula`1<System._Canon>.set_Formula(System._Canon)' failed.'

validation variable is a OfficeOpenXml.DataValidation.ExcelDataValidationList;

Answer 1

I'm not sure what causes the exception in the question. The following code works, so it likely is caused by something not shown in the question.

using OfficeOpenXml;

class SOCreateExcelWithListValidation
{
    public static void main(Args _args)
    {
        using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
        {
            using (var package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add('ListValidation');
                
                // add some values that can be validated
                ExcelRange cell = worksheet.Cells.get_Item('A1');
                cell.Value = 'Value1';
                cell = worksheet.Cells.get_Item('A2');
                cell.Value = 'Value2';
                cell = worksheet.Cells.get_Item('A3');
                cell.Value = 'Value3';

                // add validation
                DataValidation.ExcelDataValidationList validation;
                validation = worksheet.DataValidations.AddListValidation("B:B");
                DataValidation.Formulas.Contracts.IExcelDataValidationFormula formula;
                formula = validation.Formula;
                formula.ExcelFormula = "=ListValidation!$A:$A";
                
                package.Save();
            }
            File::SendFileToUser(stream, 'ListValidation.xlsx');
        }
    }

}

The resulting Excel file looks like this: