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?
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");
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
