1

I have an Excel document with a drop-down list and I am trying to get the values of the list and select one (or at least be able to select one by the index since they won't change).

I tried setting the value with SetCellValue on both ICell and XSSFCell but it doesn't work correctly, it just inputs the value and I need it to be selected since other parts of the Excel document change accordingly.

Is there a way to do this using NPOI or any other C# Excel libraries?

Dev Catalin
  • 1,265
  • 11
  • 25
  • related: https://stackoverflow.com/q/7446478/1132334 – Cee McSharpface May 03 '18 at 14:56
  • Possible duplicate of [How to read excel list elements (data validation) using C# Excel Interop?](https://stackoverflow.com/questions/27606170/how-to-read-excel-list-elements-data-validation-using-c-sharp-excel-interop) – PaulF May 03 '18 at 15:30

2 Answers2

1

if you are using NPOI. You may try one of these approaches. You can also setCellFormula, SetAsActiveCell , setErrorValue, setCellType etc. using same approach. 

//Approach 1
 var row = sheet.CreateRow(0);
 row.Cells[targetColumn].SetCellValue("whatertypevalue");

 //Approach 2 
 var namedRow = wb.GetSheetAt(sheetIndex).CreateRow(rowindex);
 namedRow.CreateCell(columnIndex).SetCellValue("whatertypevalue");

//Approach 3 
var namedRow1 = wb.GetSheetAt(0).GetRow(rowindex);
namedRow1.Cells[targetColumn].SetCellValue("whatertypevalue");
kumar chandraketu
  • 2,232
  • 2
  • 20
  • 25
0

Use this to navigate through the drop down list.If you are using drop down object. We are using shapes.item

You need to identify the shape object name from your excel.

var control = xlWorksheet.Shapes.Item("Drop Down 22").ControlFormat;
control.ListIndex = 5; \\This allows you to change the drop down to 5th element


 Excel.Range xlRangeloc= xlWorksheetH.get_Range("D5");
 xlRangeloc.Value = "OptionOne";\\If the drop down is a combo box bound to a excel cell value
Sai Kiran
  • 39
  • 4