Create an dropdown list dynamically in Worksheet

Aspose.Cells Product Family
1

Hello,


I am using Aspose.Cells to create an Excelsheet dynamically. I need to bind a list of strings to a dropdown in one of the cells. Each string may contain separators like commas, fullstops etc. I have noticed one of your solutions that imports objects to a different sheet in the workbook and creates a range from there. I don’t want to do this because a) I will have more than one dropdowns b) The excel sheet will be used by our clients. Is there a better way to do this?

Thanks in advance.
2

Hi Ishita,

Thank you for considering Aspose products, and welcome to Aspose.Cells support forum.

You can use the Worksheet.Shapes.AddComboBox method to create as many Combo Boxes/Dropdown lists as you may like. The list data will be present on Workbook it self to show the values in the list selection. Your overall process would be as follow,

  • Create or load an Excel file using Workbook class
  • Parse your string of values depending upon the delimiter specified with your own custom logic
  • Insert the individual values to any Worksheet of your Workbook
  • Create a new ComboBox object by specifying Location and Size as arguments
  • Assign the ComboBox’s InputRange property to the cell range having your newly inserted data
  • Save the Workbook

Please check the below provided sample code and the resultant Excel spreadsheet for your reference.

C#

//Create an instance of Workbook
var workbook = new Aspose.Cells.Workbook();

//Get the first Worksheet
var worksheet = workbook.Worksheets[0];

//Get all cells of Worksheet
var cells = worksheet.Cells;

//Insert some data in cells from A2 to A7
cells[“A2”].PutValue(“My first Value”);
cells[“A3”].PutValue(“My second Value”);
cells[“A4”].PutValue(“My third Value”);
cells[“A5”].PutValue(“My forth Value”);
cells[“A6”].PutValue(“My fifth Value”);
cells[“A7”].PutValue(“My sixth Value”);

//Add a new combo box.
var comboBox = worksheet.Shapes.AddComboBox(2, 0, 4, 0, 30, 130);

//Set the linked cell;
comboBox.LinkedCell = “A1”;

//Set the input range.
comboBox.InputRange = “A2:A7”;
//Save the file.
workbook.Save(myDir + “dest.xls”);

Please let us know if this suffice your needs.
3

Hi Babar,


Thanks for your reply. In your posted solution, my list of strings will have to stay copied in the cells A2 through A7 and only then the dropdown will contain all the options. If I delete the value from one of the cells, the dropdown loses that option as well.

I don’t want my string values to be copied to anywhere in the sheet. I just want them bound strictly to the dropdown and be displayed only as options. How do I achieve that?

Thanks,
Ishita
4

Hi Ishita,

Thank you for your feedback.

Please note, Dropdown in MS Excel could mean any of the below three,

  • Combo Box (Form Control)
  • Combo Box (ActiveX Control)
  • Data Validation List Control

Source
code provided earlier in my reply is for Combo Box (Form Control). I am
afraid; your requirement can not be achieved using Combo Box. Although
it is possible to assign comma separated static values to a Data
Validation List Control. Please check the below provided sample source
and attached resultant Excel spreadsheet for your reference. You can surely alter this code to best suit your requirement.

C#

var list = new List<string>();
list.Add(“Alpha”);
list.Add(“Bravo”);
list.Add(“Charlie”);
list.Add(“Delta”);
list.Add(“Echo”);


//Specify the separator for the data list.
var flatList = string.Join(",", list.ToArray());

//Create an instance of Workbook
var workbook = new Workbook();
//Get the first worksheet
var worksheet1 = workbook.Worksheets[0];

//Get the validations collection
var validations = worksheet1.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 = flatList;
//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.Stop;
// Set the error title
validation.ErrorTitle = “Invalid Name Error”;
//Set the error message
validation.ErrorMessage = “Please select a Name from the drop down”;

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

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

//Save the excel file
workbook.Save(myDir + “output.xlsx”);

I hope this helps. Please feel free to write back in case you have further questions.
5

Hi Babar,


I have already tried this solution. Problem is, Each of the strings in my list may contain special characters like “,” (e.g. “xyz, Inc.”). This means on String.Join with a “,” separator, “xyz, Inc.” will split into two different options (“xyz” and “Inc.”) in the dropdown instead of one. Do you have a workaround for this?

Thanks,
Ishita
6

Hi Ishita,

In my understanding, the only way you can do that is by setting the source to a list on a worksheet, e.g =A1:A3 or =MyList, where MyList is a named range. This is because, when you assign a static list source, it should be a comma separated list of values where each comma is treated as a list separator, and therefore a comma cannot be a part of any value.

Please manually create a spreadsheet as per your requirement while using MS Excel only. We will try to mimic that functionality with Aspose.Cells.

7

Hi,


I have created comboxes in an excel in a loop and populated them as you mentioned in this post.
I am facing an issue that on selecting a value from one of the combo box, all the other combo box automatically selects that value.
Want to get rid of this behavior.
Please help.
8

Hi Vipul Malhotra,


Thanks for providing some details.

Could you paste your sample code (runnable) and attach the template file here to reproduce the issue on our end, we will check it soon.

Thank you.
9

Hi,


I solved my error.
But now facing another one.
I tried adding data validation to my cell(s) but on opening the excel, I am getting an error that it contains data validation and needs to be removed and then the excel automatically removes all the validations and then open the file.
Please help around this.

Thanks in advance.
10

Hi Vipul Malhotra,


Good to know that your original issue is resolved now.

Could you create a simple console application (runnable), zip it and post us here with all the files to show the other issue, we will check it and help you through.

Thank you.
11

Hi,

Can you please advise how you solved the issue? We are facing the same problem in our team. We are unable to select a value from a validation list and download the excel spreadsheet.

If you can post the pseudocode it would be helpful.

thanks,
Ravi

12

@kini.ravi,

Did you create the problematic Excel file via Aspose.Cells API? If so, kindly provide more details and paste your sample code (runnable) here and attach the template file to reproduce the issue on our end, we will check it soon.

13

HI,

I want to create dynamic drop downs based on list of object. suppose list is containing 20 items then i want to add 20 drop downs to file and item number of list is not fix

14

@kiranmalunjkar,

Could you perform your desired task in MS Excel manually, save the Excel file and provide us (you may zip the file prior attaching here) Excel file to exhibit what you want to do, we will check on how to do it via Aspose.Cells APIs.