c# excel Interop.XlDVType.xlValidateList AutoCompl

问题:

I am able to get dropdown in a cell using the code below

               Interop.Range validationAddressRange = ws.Worksheet.get_Range(startAddress, endAddress);
                validationAddressRange.Select();
                validationAddressRange.Cells.Validation.Delete();
                validationAddressRange.Cells.Validation.Add(Type: Interop.XlDVType.xlValidateList, 
                AlertStyle: Interop.XlDVAlertStyle.xlValidAlertStop, Formula1: formula);
                validationAddressRange.Cells.Validation.IgnoreBlank = true;
                validationAddressRange.Cells.Validation.InCellDropdown = true;
                validationAddressRange.Cells.Validation.ErrorMessage = "Invalid entry. Click 'Retry' to update the cell value, or 'Cancel' to undo the change made to the cell.";
                validationAddressRange.Cells.Validation.ErrorTitle = "Invalid Data Error";
                validationAddressRange.Cells.Validation.ShowError = true;
                ws.Worksheet.Cells[1,1].Select(); //selects the top-leftmost cell since excel doesn't furnish a de-select option.

Does anyone know how to tie in events and have autocomplete feature just like a regular windows form combobox?

回答1:

Here, I tried to solve this. it works for me.

var list = new System.Collections.Generic.List<string>();
list.Add("RFP");
list.Add("2nd Review");
list.Add("RESHOOT");
var flatList = string.Join(", ", list.ToArray());

var cell = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells.get_Range("B1");;
cell.Validation.Delete();
cell.Validation.Add( XlDVType.xlValidateList,
    XlDVAlertStyle.xlValidAlertInformation,
    XlFormatConditionOperator.xlBetween,
    flatList,
    Type.Missing);

Here, It will work when you will fill the cells from first to last continuously without leaving any cell. if you will fill the cell in the middle. It will not work.