Adding a drop down to entire column in a excel

Aspose.Cells Product Family
1

I need to have a dropdown for the entire column in excel using Aspose.
I m using c# code and shapes.AddCombox to add a dropdown.

Aspose.Cells.Drawing.ComboBox comboBox = worksheet.Shapes.AddComboBox(1, 0, 2, 3, 22, 146);

comboBox.LinkedCell = “Sheet1!$C:$C”;
comboBox.InputRange = “Sheet1!N1:N109”;
comboBox.DropDownLines = 8;

Even if I have specified the entire range in LinkedCell, I m getting the combox in the first column alone. (Please refer Combox1 screenshot).
After selecting the value from the dropdown, the selectedindex value is appearing in the column header.Refer ComBox2 screenshot. I dont want the column header to change after selecting a value.
Please help herecomboBox1.JPG (27.1 KB)
comboBox2.JPG (63.3 KB)

2

@ramyaraj10,
You may please share the desired Excel file created by MS Excel for our reference. We will look into it and provide an alternative code using Aspose.Cells.

3

I am using the below code now, to create dropdown
//Get the validations collection
var validations = sheet.Validations;
//Create a new validation to the validations list
var validation = validations[validations.Add()];
//Set the validation type
validation.Type = Aspose.Cells.ValidationType.List;
//Set the operator
validation.Operator = OperatorType.Between;

            //Set the in cell drop down
            validation.InCellDropDown = true;
            //Set the formula1
            validation.Formula1 = "Sheet1!N1:N109";
            //Enable it to show error, you can turn it ON or OFF as desired
            validation.ShowError = true;
            //Set the alert type severity level
            validation.AlertStyle = ValidationAlertType.Warning;
            // Set the error title
            validation.ErrorTitle = "An invalid value was entered";
            //Set the error message
            validation.ErrorMessage = "Select a value from the list";

            //Specify the validation area A1:A15
            CellArea area;
            area.StartRow = 1;
            area.EndRow = 14;
            area.StartColumn = 2;
            area.EndColumn = 0;

            //Add the validation area
            validation.AddArea(area);

Problem here is I have added the dropdown values in ‘CoulmnN’ and in validation.Formula1 = “Sheet1!N1:N109”; i am assigning like this. When the sheet is download, I m not getting the values which are populated in ColumnN, instead it is displayed as Sheet1!N1:N109,
Problem 2 is I want to add the dropdown in cloumn C alone.
Please find the attached excel for reference.UserRoleMapping-2021-10-12-152445.zip (16.8 KB)

4

@ramyaraj10,

I checked your sample code with template file and found there is an error in your line of code:
validation.Formula1 = "Sheet1!N1:N109";
please change the line to:
validation.Formula1 = "=Sheet1!N1:N109"; //validation formula should always start with "=".

Here is the updated sample code that I used with your template file and it works fine. It adds List data validation type to cells range: C2"C15:
e.g.
Sample code:

Workbook workbook = new Workbook("e:\\test2\\UserRoleMapping-2021-10-12-152445.xlsx");

            Worksheet sheet = workbook.Worksheets[0];
            //Remove all the validations in the range A1:C15
            sheet.Validations.RemoveArea(CellArea.CreateCellArea("A1", "C15"));

            //Specify the validation area C2:C15
            CellArea area;
            area.StartRow = 1;
            area.EndRow = 14;
            area.StartColumn = 2;
            area.EndColumn = 2;
            
            //Get the validations collection
            var validations = sheet.Validations;
            //Create a new validation to the validations list
            var validation = validations[validations.Add(area)];
            //Set the validation type
            validation.Type = Aspose.Cells.ValidationType.List;
            //Set the operator
            validation.Operator = OperatorType.Between;

            //Set the in cell drop down
            validation.InCellDropDown = true;
            //Set the formula1
            validation.Formula1 = "=Sheet1!N1:N109";
            //Enable it to show error, you can turn it ON or OFF as desired
            validation.ShowError = true;
            //Set the alert type severity level
            validation.AlertStyle = ValidationAlertType.Warning;
            // Set the error title
            validation.ErrorTitle = "An invalid value was entered";
            //Set the error message
            validation.ErrorMessage = "Select a value from the list";

            workbook.Save("e:\\test2\\out1.xlsx");

Let us know if you still have any issue or confusion.

1 Like
5

Thank you the solution. It is working perfectly.

6

@ramyaraj10,

Good to know that the suggested code segment works for your needs well. In the event of further queries or issue, feel free to write us back.