How to Get and Set the DropDown Values of Excel using C#?


#1

`
Workbook workbook = new Workbook(@“C:\Users\test\Desktop\sampleExcel.xlsx”);

    Worksheet worksheet = workbook.Worksheets[0];
 
    Cells cells= workbook.Worksheets[0].Cells;
    var a = cells.GetCell(2, 7).Value;
    Validation v = cell.GetValidation();` - This piece of code is not supported - I dont see GetValidation() method existing .

Please help me to also set the value of drop down line to select “Orange” if it is in drop down

Thanks in advance


#2

@Purush,

Thanks for your query.

Well, you may try to use Cell.GetValidation method to get the validation of the cell. Alternatively, you may try to use ValidationCollection.GetValidationInCell() method for your needs. To set a value, you may directly set the value to the cell (But you need to evaluate by yourself using your own code it is from drop down values list prior inserting). See the following sample code with the template file (attached) for your reference:
e.g
Sample code:

            //Instantiate the workbook from template Excel file
            Workbook workbook = new Workbook("e:\\test2\\Bk_datavalidationlist1.xlsx");

            //Access its first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
                        
            //It can take only the values Between greater than 1
            Cell cell = worksheet.Cells["A1"];

            //Access the valditation applied on this cell and read all the dropdown list (validation) value.
            Validation validation = cell.GetValidation();
            if (validation.Type == ValidationType.List)
            {
                StringBuilder sbuf = new StringBuilder();
                object[] itemArray = (object[])validation.Value1;
                foreach (object item in itemArray)
                {
                    sbuf.Append(item);
                    sbuf.AppendLine();
                }
                Console.WriteLine(sbuf.ToString());
            }

            /* Alternatively you may try
            var validationForA1 = worksheet.Validations.GetValidationInCell(0, 0);
            if (validationForA1.Type == ValidationType.List)
            {
                StringBuilder sbuf = new StringBuilder();
                object[] itemArray = (object[])validationForA1.Value1;
                foreach (object item in itemArray)
                {
                    sbuf.Append(item);
                    sbuf.AppendLine();
                }
                Console.WriteLine(sbuf.ToString());
            }
            //*/

            
            //Also Read various properties of the validation
            Console.WriteLine("Reading Properties of Validation");
            Console.WriteLine("--------------------------------");
            Console.WriteLine("Type: " + validation.Type);
            Console.WriteLine("Operator: " + validation.Operator);
            Console.WriteLine("Formula1: " + validation.Formula1);
            Console.WriteLine("Formula2: " + validation.Formula2);            
            Console.WriteLine("Ignore blank: " + validation.IgnoreBlank);

            cell.PutValue("nein");//Set the value.
            
            workbook.Save("e:\\test2\\out1.xlsx");

files1.zip (5.8 KB)