Add LOV in Excel

This adds the first worksheet to an ExcelWorksheet variable, creates a loop based on a given value and adds DDLs in the cells with each iteration.

This script will require adding using OfficeOpenXml; to the namespaces section.

using (ExcelPackage p = new ExcelPackage()) 
{
           ExcelWorksheet ws = excelDocument.getWorkSheet(1);
           
           for(int i = 1; i<20; i++)
           {
                      ws.Cells[i,2].Value="Default";
                      var unitmeasure = ws.DataValidations.AddListValidation("b"+i);
                      unitmeasure.Formula.Values.Add("Test");
                      unitmeasure.Formula.Values.Add("Test1");
                      unitmeasure.Formula.Values.Add("Default");			
           }
}

The ExcelWorksheet variable has to be open using the “Open excel workbook” action before initializing the C# script, otherwise it will not be a valid reference to an Excel Workbook.

After the C# script action, use “Close excel workbook” to save the changes made.