Copy Excel DropDown from WorkBook to a new Workbook

Question

Again a Excel question. My client send me a Excel File that contains a Drop Down List (Combobox), and i need copy that Combobox or build a new with the information inside. For excel i find that dropdownlist was named as Data Validation (i already got it, but can do nothing with him).

I Have Gembox dll and NativeExcel dll and didnt find any solution.

With GemBox i already get this:

ExcelFile ef = ExcelFile.Load("Modelo_AA.xlsx");
ExcelWorksheet ws = ef.Worksheets[0];
ExcelFile efnovo = new ExcelFile();
ExcelWorksheet wsnovo = efnovo.Worksheets.Add("Hello");
DataValidationCollection dvc = ws.DataValidations;
DataValidation dv = dvc[0];
bool dd = dv.InCellDropdown; //here i get true

Thanks Andrew

Edit: I suposed the datavalitation is associated with dropdownlist!

Edit2: Main problem is copy a drop down list from a WorkBook to other!

What do you want to do with the data validation from the source workbook? — shahkalpesh, Mar 21 '13 at 16:14
I think the datavalitation is associated with dropdownliat. Right!? — andre, Mar 21 '13 at 16:29
A type of data validation is choosing an item from the list. i.e. a cell can have values only out of the list and hence it is shown with help of a dropdown. — shahkalpesh, Mar 21 '13 at 16:34
So isnt much import know the DataValidation. My real problem is copy a drop down list from a workbook to other... Thanks — andre, Mar 21 '13 at 16:42
On opening the source workbook, you'll see that the cell having dropdown shows a list of items. Are those hard-coded OR comes from a range in the workbook? How to find it - goto the cell showing the dropdown, click on Data tab (on ribbon), click on "Data Validation" -> "Data Validation...". It will show the "Allow" as List. What does the "Source" contain? — shahkalpesh, Mar 21 '13 at 16:48
Yes. The source is some cells. So i need copy that cells too, but still need copy the datavalidation proprieties, right? but now i understand a bit more. Thanks — andre, Mar 21 '13 at 16:58
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26665/discussion-between-shahkalpesh-and-andrecorreiait) — shahkalpesh, Mar 21 '13 at 18:25

score 0 · Answer 1 · answered Dec 03 '13 at 10:03

First I would like to say that yes in your case DataValidation is associated with dropdownlist because it is a List type (see dv.Type property).

Also to copy this list DataValidation into another excel file it will depend on DataValidation source, as mentioned in the comments by the shahkalpesh they can be hard-coded or they can come from a cell range. You can check the source of DataValidation by getting a dv.Formula1 object. For example if it is hard-coded then the Formula1 will be an array of those list items and you can just add that DataValidation into another ExcelFile:

wsnovo.DataValidations.Add(dv);

But if that DataValidation has a cell range in Formula1 then you will have to copy that cell range in a new ExcelFile as well or you can try reading the values of cells in that cell range and replace a Formula1 with a hard-coded list of items.

Copy Excel DropDown from WorkBook to a new Workbook

1 Answers1