0

I'm reading an Excel spreadsheet using the microsoft.office.interop.excel library in C#. I need to first determine that a cell in the sheet contains a Dropdown and then read the value of the Dropdown.

I'm running through the rows of the sheet and parsing values, but it's possible that a cell contains a Dropdown with a selection instead of a text value and I'm having problems trying to get the Dropdown from a cell in order to read it's selected value.

The pseudo code below is an example of what I'm trying:

for(int row = 1; row <= rowCount; row++)
{
   for(int col = 1; col <= colCount; col++)
   {
      //Have the row and column number of the individual cell here
      Range range = worksheet.Cells.Item[row,col];  //Get the thing in the cell?
      Type type = range.GetType();  //Try to get a type so I can compare it against a Dropdown?
      string menuVal = range.Value();  //Both this and range.Value2() is null
   }
}

What I'd like to do is something like this fantasy code:

for( -row loop- )
{
   for( -col loop- )
   {
      if (Current Cell contains a Dropdown)
      {
         Dropdown menu = Get the Dropdown in this cell;
         string value = Get the 'Dropdown' value;
      }
   }
}

Can someone please help me out here? I haven't been able to find any documentation on how to GET the value - plenty on how to create the Dropdown itself though.

Edit: Changed 'Menu' to 'Dropdown'. Dropdown is the actual class that's being used in the code.

Solution in a nutshell: The Dropdown class from microsoft.office.interop should -NOT- be used if you care about trying to actually retrieve the value of the dropdown programmatically. This has been either deprecated or is just something Microsoft doesn't want you using. Instead you should use the method that sets up the Dropdown validation by using a separate worksheet that contains the values for the dropdown menu. You can then read the Value of the Dropdown cell as though it's just text.

Cluless
  • 13
  • 4
  • To get all the possible values of a dropdown I use something akin to `var valuesRange = worksheet.Range[menuRange.Validation.Formula1];` . Hopefully that tidbit helps you figure it out. Remember to obey the single dot rule when dealing with com objects and release each object when done. Best of luck. – hijinxbassist Apr 14 '22 at 23:50
  • @hijinxbassist I think if I can determine if the cell has a Menu, and I can get the Menu the cell contains, I'll be good. Not sure if you have any advice on that offhand? – Cluless Apr 15 '22 at 00:23
  • For my task, I had the coordinate of the menu and needed to get all possible options from it. My research led to first checking for the `Validation` object of the cell (Range), and then checking for `Formula1`, which contains the range of cells that contain the actual values. It might be as simple as `if (cell.Validation != null && cell.Validation.Formula1 != null)`. You will need to split each object into a variable for releasing. eg. `var validation = cell.Validation;` and `var formula1 = validation.Formula1` so later you can release them with `Marshal.ReleaseComObject(...);` – hijinxbassist Apr 15 '22 at 00:37
  • Does this answer your question? [How do I read the values of Excel dropdowns or checkboxes from c# or vb.net?](https://stackoverflow.com/questions/3712312/how-do-i-read-the-values-of-excel-dropdowns-or-checkboxes-from-c-sharp-or-vb-net) – Shleemypants Apr 15 '22 at 01:30

0 Answers0