Guy Starbuck

Load a ComboBox from Excel

This is another post that came from a question on StackOverflow – The idea is to load up a drop-down (I used a System.Windows.Forms.ComboBox) using values defined in an Excel document.

As usual, almost all of the Office Interop samples on the web are written in VB.NET – this makes sense, since it is a lot easier to program interop using VBNet and its support for optional parameters.  I got a great start from this forum post, I just simplified and converted it to C#.

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace CSharpClassLibrary
{
    public class ComboBoxLoader
    {
        // Define an object to pass to the API for missing parameters
        private static object missing = Type.Missing;

        public static void FillDropDown(ref System.Windows.Forms.ComboBox cbo)
        {
            Excel.Application excel = new Excel.Application();
            Excel.Workbook workbook;
            Excel.Worksheet worksheet;

            if (excel == null)
            {
                return;
            }
            else
            {
                // Set Excel to be hidden
                excel.Visible = false;

                // Open the workbook -- note the passing of the "missing" parameter
                // for everything except the file name
                workbook = excel.Workbooks.Open(@"c:\temp\test.xlsx", missing,
                    missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing);

                // This example assumes the first worksheet
                worksheet = workbook.Worksheets[1] as Excel.Worksheet;

                // This example assumes that there are five values, in cells
                // A1 to A5, that need to be loaded
                Excel.Range range;
                range = worksheet.get_Range("A1", "A5") as Excel.Range;

                // Loop through the cells in the Range and add their values to
                // the combo box
                foreach (Excel.Range cell in range.Cells)
                {
                    cbo.Items.Add(cell.Value2 as string);
                }

                // Clean up -- important with PIA Interop, as lots of
                // instances of Excel might be left around in memory
                workbook.Close(missing, missing, missing);
                excel.Quit();
            }
        }
    }
}

This is not something I would necessarily do (using Office Interop is a big thing to bite off in a project, and while I can see how feeding drop-down options from Excel could provide ease of use for non-technical admins, there are lots of less heavy-handed approaches), but it was interesting to figure out how to get the code working, and I thought it might help someone with other Excel automation tasks.